我已经在postgreSQL中创建了一个表。我想查看用于创建表的SQL语句,但无法找出它。
如何通过命令行或SQL语句获得Postgres中现有表的创建表SQL语句?
我已经在postgreSQL中创建了一个表。我想查看用于创建表的SQL语句,但无法找出它。
如何通过命令行或SQL语句获得Postgres中现有表的创建表SQL语句?
当前回答
太棒了! 我会稍微修改一下您的代码,以显示表中的所有约束,并允许在表名中使用regexp掩码。
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;
例如,现在您可以执行以下查询
SELECT * FROM generate_create_table_statement('.*');
结果是这样的:
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);
对于每个用户表。
其他回答
这是这个老问题的另一个解决方案。多年来,关于这个问题有许多优秀的答案,我的尝试在很大程度上借鉴了它们。
我使用安德烈·列别登科的解决方案作为起点,因为它的输出已经非常接近我的要求。
特点:
following common practice I have moved the foreign key constraints outside the table definition. They are now included as ALTER TABLE statements at the bottom. The reason is that a foreign key can also link to a column of the same table. In that fringe case the constraint can only be created after the table creation is completed. The create table statement would throw an error otherwise. The layout and indenting looks nicer now (at least to my eye) Drop command (commented out) in the header of the definition The solution is offered here as a plpgsql function. The algorithm does however not use any procedural language. The function just wraps one single query that can be used in a pure sql context as well. removed redundant subqueries Identifiers are now quoted if they are identical to reserved postgresql language elements replaced the string concatenation operator || with the appropriate string functions to improve performance, security and readability of the code. Note: the || operator produces NULL if one of the combined strings is NULL. It should only be used when that is the desired behaviour. (check out the usage in the code below for an example)
CREATE OR REPLACE FUNCTION public.wmv_get_table_definition (
p_schema_name character varying,
p_table_name character varying
)
RETURNS SETOF TEXT
AS $BODY$
BEGIN
RETURN query
WITH table_rec AS (
SELECT
c.relname, n.nspname, c.oid
FROM
pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE
relkind = 'r'
AND n.nspname = p_schema_name
AND c.relname LIKE p_table_name
ORDER BY
c.relname
),
col_rec AS (
SELECT
a.attname AS colname,
pg_catalog.format_type(a.atttypid, a.atttypmod) AS coltype,
a.attrelid AS oid,
' DEFAULT ' || (
SELECT
pg_catalog.pg_get_expr(d.adbin, d.adrelid)
FROM
pg_catalog.pg_attrdef d
WHERE
d.adrelid = a.attrelid
AND d.adnum = a.attnum
AND a.atthasdef) AS column_default_value,
CASE WHEN a.attnotnull = TRUE THEN
'NOT NULL'
ELSE
'NULL'
END AS column_not_null,
a.attnum AS attnum
FROM
pg_catalog.pg_attribute a
WHERE
a.attnum > 0
AND NOT a.attisdropped
ORDER BY
a.attnum
),
con_rec AS (
SELECT
conrelid::regclass::text AS relname,
n.nspname,
conname,
pg_get_constraintdef(c.oid) AS condef,
contype,
conrelid AS oid
FROM
pg_constraint c
JOIN pg_namespace n ON n.oid = c.connamespace
),
glue AS (
SELECT
format( E'-- %1$I.%2$I definition\n\n-- Drop table\n\n-- DROP TABLE IF EXISTS %1$I.%2$I\n\nCREATE TABLE %1$I.%2$I (\n', table_rec.nspname, table_rec.relname) AS top,
format( E'\n);\n\n\n-- adempiere.wmv_ghgaudit foreign keys\n\n', table_rec.nspname, table_rec.relname) AS bottom,
oid
FROM
table_rec
),
cols AS (
SELECT
string_agg(format(' %I %s%s %s', colname, coltype, column_default_value, column_not_null), E',\n') AS lines,
oid
FROM
col_rec
GROUP BY
oid
),
constrnt AS (
SELECT
string_agg(format(' CONSTRAINT %s %s', con_rec.conname, con_rec.condef), E',\n') AS lines,
oid
FROM
con_rec
WHERE
contype <> 'f'
GROUP BY
oid
),
frnkey AS (
SELECT
string_agg(format('ALTER TABLE %I.%I ADD CONSTRAINT %s %s', nspname, relname, conname, condef), E';\n') AS lines,
oid
FROM
con_rec
WHERE
contype = 'f'
GROUP BY
oid
)
SELECT
concat(glue.top, cols.lines, E',\n', constrnt.lines, glue.bottom, frnkey.lines, ';')
FROM
glue
JOIN cols ON cols.oid = glue.oid
LEFT JOIN constrnt ON constrnt.oid = glue.oid
LEFT JOIN frnkey ON frnkey.oid = glue.oid;
END;
$BODY$
LANGUAGE plpgsql;
如果您想一次对多个表执行此操作,则需要多次使用-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
如果你想找到一个表的create语句而不使用pg_dump,这个查询可能对你有用(改变'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;
当直接从psql调用时,这样做是有用的:
\pset linestyle old-ascii
另外,这个线程中的generate_create_table_statement函数工作得很好。
在pgadminIII数据库>>schema >>tables>>右键单击“Your table”>>scripts>>“选择任何一个(创建,插入,更新,删除..)”
pg_dump -t 'schema-name.table-name' --schema-only database-name
更多信息-在手册。