如何为 postgreSQL 中的现有表生成“创建表”sql 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2593803/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to generate the "create table" sql statement for an existing table in postgreSQL
提问by Raja
I have created a table in postgreSQL. I want to look at the SQL statement used to create the table but cannot figure it out.
我在 postgreSQL 中创建了一个表。我想查看用于创建表的 SQL 语句，但无法弄清楚。
How do I get the create tableSQL statement for an existing table in Postgres via commandline or SQL statement?
如何create table通过命令行或 SQL 语句获取 Postgres 中现有表的 SQL 语句？
回答by Milen A. Radev
回答by Raja
My solution is to log in to the postgres db using psql with the -E option as follows:
我的解决方案是使用带有 -E 选项的 psql 登录 postgres 数据库，如下所示：
psql -E -U username -d database
In psql, run the following commands to see the sql that postgres uses to generate
the describe table statement:
在psql中，运行以下命令查看postgres用于生成
describe table语句的sql ：
-- List all tables in the schema (my example schema name is public)
\dt public.*
-- Choose a table name from above
-- For create table of one public.tablename
\d+ public.tablename
Based on the sql echoed out after running these describe commands, I was able to put together
the following plpgsql function:
根据运行这些描述命令后回显的 sql，我能够将
以下 plpgsql 函数放在一起：
CREATE OR REPLACE FUNCTION generate_create_table_statement(p_table_name varchar)
RETURNS text AS
$BODY$
DECLARE
v_table_ddl text;
column_record record;
BEGIN
FOR column_record IN
SELECT
b.nspname as schema_name,
b.relname as table_name,
a.attname as column_name,
pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type,
CASE WHEN
(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) IS NOT NULL THEN
'DEFAULT '|| (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)
ELSE
''
END as column_default_value,
CASE WHEN a.attnotnull = true THEN
'NOT NULL'
ELSE
'NULL'
END as column_not_null,
a.attnum as attnum,
e.max_attnum as max_attnum
FROM
pg_catalog.pg_attribute a
INNER JOIN
(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 ~ ('^('||p_table_name||')$')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3) b
ON a.attrelid = b.oid
INNER JOIN
(SELECT
a.attrelid,
max(a.attnum) as max_attnum
FROM pg_catalog.pg_attribute a
WHERE a.attnum > 0
AND NOT a.attisdropped
GROUP BY a.attrelid) e
ON a.attrelid=e.attrelid
WHERE a.attnum > 0
AND NOT a.attisdropped
ORDER BY a.attnum
LOOP
IF column_record.attnum = 1 THEN
v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' (';
ELSE
v_table_ddl:=v_table_ddl||',';
END IF;
IF column_record.attnum <= column_record.max_attnum THEN
v_table_ddl:=v_table_ddl||chr(10)||
' '||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null;
END IF;
END LOOP;
v_table_ddl:=v_table_ddl||');';
RETURN v_table_ddl;
END;
$BODY$
LANGUAGE 'plpgsql' COST 100.0 SECURITY INVOKER;
Here is the function usage:
下面是函数用法：
SELECT generate_create_table_statement('tablename');
And here is the drop statement if you don't want this function to persist permanently:
如果您不希望此函数永久存在，这里是 drop 语句：
DROP FUNCTION generate_create_table_statement(p_table_name varchar);
回答by Eric Leschinski
Generate the create table statement for a table in postgresql from linux commandline:
从 linux 命令行为 postgresql 中的表生成 create table 语句：
This statement outputs the table create sql statement for me:
此语句为我输出 table create sql 语句：
pg_dump -U your_db_user_name your_database -t your_table_name --schema-only
Explanation:
解释：
pg_dump helps us get information about the database itself. -Ustands for username. My pgadmin user has no password set, so I don't have to put in a password. The -toption means specify for one table. --schema-onlymeans print only data about the table, and not the data in the table. Here is the exact command I use:
pg_dump 帮助我们获取有关数据库本身的信息。 -U代表用户名。我的 pgadmin 用户没有设置密码，所以我不必输入密码。该-t选项表示为一张表指定。 --schema-only表示只打印表的数据，不打印表中的数据。这是我使用的确切命令：
pg_dump -U pgadmin kurz_prod -t fact_stock_info --schema-only
回答by shekwi
If you want to find the create statement for a table without using pg_dump, This query might work for you (change 'tablename' with whatever your table is called):
如果您想在不使用 pg_dump 的情况下查找表的 create 语句，此查询可能对您有用（更改 'tablename' 与您的表的名称相同）：
SELECT
'CREATE TABLE ' || relname || E'\n(\n' ||
array_to_string(
array_agg(
' ' || column_name || ' ' || type || ' '|| not_null
)
, E',\n'
) || E'\n);\n'
from
(
SELECT
c.relname, a.attname AS column_name,
pg_catalog.format_type(a.atttypid, a.atttypmod) as type,
case
when a.attnotnull
then 'NOT NULL'
else 'NULL'
END as not_null
FROM pg_class c,
pg_attribute a,
pg_type t
WHERE c.relname = 'tablename'
AND a.attnum > 0
AND a.attrelid = c.oid
AND a.atttypid = t.oid
ORDER BY a.attnum
) as tabledefinition
group by relname;
when called directly from psql, it is usefult to do:
当直接从 psql 调用时，这样做很有用：
\pset linestyle old-ascii
Also, the function generate_create_table_statementin this thread works very well.
此外，该线程中的函数generate_create_table_statement效果很好。
回答by vkkeeper
Dean ToaderJust excellent! I'd modify your code a little, to show all constraints in the table and to make possible to use regexp mask in table name.
Dean Toader太棒了！我会稍微修改您的代码，以显示表中的所有约束，并使在表名中使用正则表达式掩码成为可能。
CREATE OR REPLACE FUNCTION public.generate_create_table_statement(p_table_name character varying)
RETURNS SETOF text AS
$BODY$
DECLARE
v_table_ddl text;
column_record record;
table_rec record;
constraint_rec record;
firstrec boolean;
BEGIN
FOR table_rec IN
SELECT c.relname FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
AND relname~ ('^('||p_table_name||')$')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY c.relname
LOOP
FOR column_record IN
SELECT
b.nspname as schema_name,
b.relname as table_name,
a.attname as column_name,
pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type,
CASE WHEN
(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) IS NOT NULL THEN
'DEFAULT '|| (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)
ELSE
''
END as column_default_value,
CASE WHEN a.attnotnull = true THEN
'NOT NULL'
ELSE
'NULL'
END as column_not_null,
a.attnum as attnum,
e.max_attnum as max_attnum
FROM
pg_catalog.pg_attribute a
INNER JOIN
(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 = table_rec.relname
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3) b
ON a.attrelid = b.oid
INNER JOIN
(SELECT
a.attrelid,
max(a.attnum) as max_attnum
FROM pg_catalog.pg_attribute a
WHERE a.attnum > 0
AND NOT a.attisdropped
GROUP BY a.attrelid) e
ON a.attrelid=e.attrelid
WHERE a.attnum > 0
AND NOT a.attisdropped
ORDER BY a.attnum
LOOP
IF column_record.attnum = 1 THEN
v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' (';
ELSE
v_table_ddl:=v_table_ddl||',';
END IF;
IF column_record.attnum <= column_record.max_attnum THEN
v_table_ddl:=v_table_ddl||chr(10)||
' '||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null;
END IF;
END LOOP;
firstrec := TRUE;
FOR constraint_rec IN
SELECT conname, pg_get_constraintdef(c.oid) as constrainddef
FROM pg_constraint c
WHERE conrelid=(
SELECT attrelid FROM pg_attribute
WHERE attrelid = (
SELECT oid FROM pg_class WHERE relname = table_rec.relname
) AND attname='tableoid'
)
LOOP
v_table_ddl:=v_table_ddl||','||chr(10);
v_table_ddl:=v_table_ddl||'CONSTRAINT '||constraint_rec.conname;
v_table_ddl:=v_table_ddl||chr(10)||' '||constraint_rec.constrainddef;
firstrec := FALSE;
END LOOP;
v_table_ddl:=v_table_ddl||');';
RETURN NEXT v_table_ddl;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.generate_create_table_statement(character varying)
OWNER TO postgres;
Now you can, for example, make the following query
例如，现在您可以进行以下查询
SELECT * FROM generate_create_table_statement('.*');
which results like this:
结果如下：
CREATE TABLE public.answer (
id integer DEFAULT nextval('answer_id_seq'::regclass) NOT NULL,
questionid integer NOT NULL,
title character varying NOT NULL,
defaultvalue character varying NULL,
valuetype integer NOT NULL,
isdefault boolean NULL,
minval double precision NULL,
maxval double precision NULL,
followminmax integer DEFAULT 0 NOT NULL,
CONSTRAINT answer_pkey
PRIMARY KEY (id),
CONSTRAINT answer_questionid_fkey
FOREIGN KEY (questionid) REFERENCES question(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT answer_valuetype_fkey
FOREIGN KEY (valuetype) REFERENCES answervaluetype(id) ON UPDATE RESTRICT ON DELETE RESTRICT);
for each user table.
对于每个用户表。
回答by Corin
The easiest method I can think of is to install pgAdmin 3 (found here) and use it to view your database. It will automatically generate a query that will create the table in question.
我能想到的最简单的方法是安装 pgAdmin 3（在这里找到）并使用它来查看您的数据库。它将自动生成一个查询来创建有问题的表。
回答by John Powell
If you want to do this for various tables at once, you meed to use the -t switch multiple times (took me a while to figure out why comma separated list wasn't working). Also, can be useful to send results to an outfile or pipe to a postgres server on another machine
如果您想一次对多个表执行此操作，您需要多次使用 -t 开关（我花了一段时间才弄清楚为什么逗号分隔列表不起作用）。此外，将结果发送到输出文件或管道到另一台机器上的 postgres 服务器也很有用
pg_dump -t table1 -t table2 database_name --schema-only > dump.sql
pg_dump -t table1 -t table2 database_name --schema-only | psql -h server_name database_name
回答by yallie
Here is a bit improved version of shekwi's query.
It generates the primary key constraint and is able to handle temporary tables:
这里是有点改进版shekwi的查询。
它生成主键约束并能够处理临时表：
with pkey as
(
select cc.conrelid, format(E',
constraint %I primary key(%s)', cc.conname,
string_agg(a.attname, ', '
order by array_position(cc.conkey, a.attnum))) pkey
from pg_catalog.pg_constraint cc
join pg_catalog.pg_class c on c.oid = cc.conrelid
join pg_catalog.pg_attribute a on a.attrelid = cc.conrelid
and a.attnum = any(cc.conkey)
where cc.contype = 'p'
group by cc.conrelid, cc.conname
)
select format(E'create %stable %s%I\n(\n%s%s\n);\n',
case c.relpersistence when 't' then 'temporary ' else '' end,
case c.relpersistence when 't' then '' else n.nspname || '.' end,
c.relname,
string_agg(
format(E'\t%I %s%s',
a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
case when a.attnotnull then ' not null' else '' end
), E',\n'
order by a.attnum
),
(select pkey from pkey where pkey.conrelid = c.oid)) as sql
from pg_catalog.pg_class c
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
join pg_catalog.pg_attribute a on a.attrelid = c.oid and a.attnum > 0
join pg_catalog.pg_type t on a.atttypid = t.oid
where c.relname = :table_name
group by c.oid, c.relname, c.relpersistence, n.nspname;
Use table_nameparameter to specify the name of the table.
使用table_name参数指定表名。
回答by user3662456
pg_dump -h XXXXXXXXXXX.us-west-1.rds.amazonaws.com -U anyuser -t tablename -s
回答by Sandy Simonton
This is the variation that works for me:
这是对我有用的变体：
pg_dump -U user_viktor -h localhost unit_test_database -t floorplanpreferences_table --schema-only
pg_dump -U user_viktor -h localhost unit_test_database -t floorplanpreferences_table --schema-only
In addition, if you're using schemas, you'll of course need to specify that as well:
此外，如果您使用模式，当然也需要指定：
pg_dump -U user_viktor -h localhost unit_test_database -t "949766e0-e81e-11e3-b325-1cc1de32fcb6".floorplanpreferences_table --schema-only
pg_dump -U user_viktor -h localhost unit_test_database -t "949766e0-e81e-11e3-b325-1cc1de32fcb6".floorplanpreferences_table --schema-only
You will get an output that you can use to create the table again, just run that output in psql.
您将获得可用于再次创建表的输出，只需在 psql 中运行该输出即可。
