Copy a table (including indexes) in postgres

sql

database-management

postgresql

table-operations

byNikita Barsukov·Nov 23, 2024

To swiftly clone a PostgreSQL table with its indexes:

Duplicate table data:

CREATE TABLE new_table AS TABLE existing_table;  -- Here's the magic.

Generate index creation commands from the original table:

SELECT 'CREATE INDEX ' || replace(indexname, 'existing_table', 'new_table') || ' ON new_table USING ' || 
regexp_replace(indexdef, 'ON .+ USING', 'USING') FROM pg_indexes WHERE tablename = 'existing_table'; 
-- It's like the magic wand waving for Harry Potter, but in SQL.

Execute the generated commands for each index. It's like delegating the spell casting.

This method clones both the data and indexes efficiently, making it perfect for creating a backup or testing environment.

Utilizing 'pg_dump' for table copying

Leverage pg_dump with -t option to specify the table you want to clone, edit the table name in the dump, and load it again:

pg_dump -t existing_table -f dump.sql dbname
sed -i '' 's/existing_table/new_table/g' dump.sql
psql dbname -f dump.sql
# It's like master chess player moves, perfectly planned and executed

sed command is a power tool, so use it wisely.

Exploiting PostgreSQL 8.3 features to clone a table

For PostgreSQL versions 8.3 and above, utilize the LIKE clause to copy a table with its indexes:

CREATE TABLE new_table (LIKE existing_table INCLUDING INDEXES); 
#LIKE… in SQL, not in Instagram!

Ensure your PostgreSQL version supports this feature:

SELECT version(); --Have we time-traveled or not?

Always, I repeat, always check your table's structure and associated indexes after you are done.

Ensuring accuracy in table and index creation

After the table is baked, or cloned:

Inspect the structure with this command:

\d new_table  -- Show me your skeleton, Frankenstein!

Now, ensure the right amount of icing (indexes) have been put on your cake (table):
```
\di+ new_table  -- Cake without icing would be just sweet bread!
```

Dealing with subset data and sequence issues

You can use a WHERE clause to copy only a subset of rows when full cloning is overkill:

CREATE TABLE new_table AS SELECT * FROM existing_table WHERE condition;

It's like picking only the ripe apples from the tree!

If you encounter issues with SERIAL column sequence linkage, don't fret. Here's how to diagnose and resolve them:

List existing linkage:

SELECT pg_get_serial_sequence('new_table', 'serial_column_name');

Reestablish the linkage:

ALTER SEQUENCE sequence_name OWNED BY new_table.serial_column_name;

Fine-tuning the process

For performance and utility, try these tips:

Wrap the process within a transaction for atomicity.
Use CREATE INDEX CONCURRENTLY to quickly index without locking the table.
Use UNLOGGED tables to reduce Write-Ahead Logging overhead in non-production environments.

explain-codes / Sql / Copy a table (including indexes) in postgres

Linked

Select INTO in MySQL



Postgresql: How to create a table only if it does not already exist?



How can I create a copy of an Oracle table without copying the data?



Postgres Case Sensitivity



How can I copy data from one column to another in the same table?

