我已经在postgreSQL中创建了一个表。我想查看用于创建表的SQL语句,但无法找出它。
如何通过命令行或SQL语句获得Postgres中现有表的创建表SQL语句?
我已经在postgreSQL中创建了一个表。我想查看用于创建表的SQL语句,但无法找出它。
如何通过命令行或SQL语句获得Postgres中现有表的创建表SQL语句?
当前回答
你也可以使用一个免费的数据库管理工具,比如DBeaver,它允许你查看表的DDL,这里有一个例子:
其他回答
下面是一条语句,它将为指定模式(包括约束)中的单个表生成DDL。
SELECT 'CREATE TABLE ' || pn.nspname || '.' || pc.relname || E'(\n' ||
string_agg(pa.attname || ' ' || pg_catalog.format_type(pa.atttypid, pa.atttypmod) || coalesce(' DEFAULT ' || (
SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = pa.attrelid
AND d.adnum = pa.attnum
AND pa.atthasdef
),
'') || ' ' ||
CASE pa.attnotnull
WHEN TRUE THEN 'NOT NULL'
ELSE 'NULL'
END, E',\n') ||
coalesce((SELECT E',\n' || string_agg('CONSTRAINT ' || pc1.conname || ' ' || pg_get_constraintdef(pc1.oid), E',\n' ORDER BY pc1.conindid)
FROM pg_constraint pc1
WHERE pc1.conrelid = pa.attrelid), '') ||
E');'
FROM pg_catalog.pg_attribute pa
JOIN pg_catalog.pg_class pc
ON pc.oid = pa.attrelid
AND pc.relname = 'table_name'
JOIN pg_catalog.pg_namespace pn
ON pn.oid = pc.relnamespace
AND pn.nspname = 'schema_name'
WHERE pa.attnum > 0
AND NOT pa.attisdropped
GROUP BY pn.nspname, pc.relname, pa.attrelid;
你也可以使用一个免费的数据库管理工具,比如DBeaver,它允许你查看表的DDL,这里有一个例子:
生成创建特定表背后的SQL (DDL)。 我们可以简单地使用这个SQL查询-
SHOW TABLE your_schema_name.your_table_name
下面是对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;
使用table_name参数指定表的名称。
这是一个有一些编辑的查询,
select 'CREATE TABLE ' || a.attrelid::regclass::text || '(' ||
string_agg(a.attname || ' ' || pg_catalog.format_type(a.atttypid,
a.atttypmod)||
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
||
CASE WHEN a.attnotnull = true THEN
' NOT NULL'
ELSE
'' END,E'\n,') || ');'
FROM pg_catalog.pg_attribute a join pg_class on a.attrelid=pg_class.oid
WHERE a.attrelid::regclass::varchar =
'TABLENAME_with_or_without_schema'
AND a.attnum > 0 AND NOT a.attisdropped and pg_class.relkind='r'
group by a.attrelid;