How to copy structure of one table to another with foreign key constraints in psql?

Question

Foreign key constraints are not copied when using

create table table_name ( like source_table INCLUDING ALL)' 

in Postgres. How can I create a copy of an existing table including all foreign keys.

Answer 1

There is no option to automatically create foreign keys in CREATE TABLE ... LIKE ....

For the documentation:

LIKE source_table [ like_option ... ]

Not-null constraints are always copied to the new table. CHECK constraints will be copied only if INCLUDING CONSTRAINTS is specified [...]

Indexes, PRIMARY KEY, and UNIQUE constraints on the original table will be created on the new table only if the INCLUDING INDEXES clause is specified.

In practice it's easy with GUI tools. For example, in PgAdmin III:

• copy declaration (DDL) of source_table to query tool (ctrl-e),
• edit the declaration,
• execute sql.

---

In an SQL script you can use the following function. Important assumption: source table foreign keys have correct names i.e. their names contain source table name (what is a typical situation).

create or replace function create_table_like(source_table text, new_table text)
returns void language plpgsql
as $$
declare
    rec record;
begin
    execute format(
        'create table %s (like %s including all)',
        new_table, source_table);
    for rec in
        select oid, conname
        from pg_constraint
        where contype = 'f' 
        and conrelid = source_table::regclass
    loop
        execute format(
            'alter table %s add constraint %s %s',
            new_table,
            replace(rec.conname, source_table, new_table),
            pg_get_constraintdef(rec.oid));
    end loop;
end $$;

Example of use:

create table base_table (base_id int primary key);
create table source_table (id int primary key, base_id int references base_table);

select create_table_like('source_table', 'new_table');

\d new_table

   Table "public.new_table"
 Column  |  Type   | Modifiers 
---------+---------+-----------
 id      | integer | not null
 base_id | integer | 
Indexes:
    "new_table_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "new_table_base_id_fkey" FOREIGN KEY (base_id) REFERENCES base_table(base_id)