Copy a Table (Including Indexes) in Postgres

Completely copying a postgres table with SQL

Well, you're gonna have to do some of this stuff by hand, unfortunately. But it can all be done from something like psql. The first command is simple enough:

select * into newtable from oldtable

This will create newtable with oldtable's data but not indexes. Then you've got to create the indexes and sequences etc on your own. You can get a list of all the indexes on a table with the command:

select indexdef from pg_indexes where tablename='oldtable';

Then run psql -E to access your db and use \d to look at the old table. You can then mangle these two queries to get the info on the sequences:

SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(oldtable)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '74359' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

Replace that 74359 above with the oid you get from the previous query.

Cloning a Postgres table, including indexes and data

PostgreSQL doesn't provide a very elegant way of doing this. You could use pg_dump with -t and --section= to dump the pre-data and post-data for the table. Then you would replay the pre-data to create the table structure and the check constraints, then load the data from whereever you get it from, then replay the post-data to add the indexes and FK constraints.

What is the quickest way to duplicate/clone a table in Postgres?

A better way really depends on what exactly you're hoping to accomplish.

If you want to keep all the constraints and indexes from the original table you can use the LIKE clause in your CREATE TABLE statement like so:

CREATE TABLE tbl_2 (LIKE tbl_1 INCLUDING INDEXES INCLUDING CONSTRAINTS);

But that just creates an empty table. You would still have to copy in the data.

Include references while copying a table in postgresql

"Referenced by" means that another table references contests, i.e. there is a foreign key constraint in the referencing table pointing to contests.

So you cannot really "copy" that along with contents but had to either change the foreign key constraints in the referencing tables pointing to the new table or copy the referencing tables and then change their foreign key constraints.

How do I copy the table structure of a postgres table into a different postgres database without the data

This boils down to the question "how to create the DDL script for a table" which can easily be done using pg_dump on the command line.

pg_dump -d some_db -h production_server -t existing_table --schema-only -f create.sql 

The file create.sql then contains the CREATE TABLE script that you can run on your local Postgres installation.

---

Related Topics