postgresql – 如何在psql中使用外键约束将一个表的结构复制到另

对于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.

在实践中,使用GUI工具很容易.例如,在PgAdmin III中：

>将source_table的复制声明(DDL)复制到查询工具(ctrl-e),
>编辑声明,
>执行sql.

在SQL脚本中,您可以使用以下函数.重要的假设：源表外键具有正确的名称,即它们的名称包含源表名称(典型情况是什么).

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',replace(rec.conname,source_table,new_table),pg_get_constraintdef(rec.oid));
    end loop;
end $$;

使用示例：

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)