How to copy pgvector table including indices?

Question

I am able to copy a table containing vector column with command:

CREATE TABLE dst_table AS TABLE src_table;

but how can I avoid creating re-creating an HNSW index and just copy it?

Answer 1

To copy table without recreating the HNSW index, you should first create table without indexes, then then copy table to this destination table from your source table and at the last copy HNSW index separately, use these three commands respectively.

CREATE TABLE dst_table AS TABLE src_table WITH NO DATA;

INSERT INTO dst_table SELECT * FROM src_table;

CREATE INDEX hnsw_index_dst ON dst_table USING hnsw (vector_column);

In the above commands, 'vector_column' should be the name of your vector column, and 'hnsw_index' should be the real name of your HNSW index. By using this method, you may duplicate the index and table data independently, saving you from having to recreate the index when the table is being created.

Hope it works :)