我在 postgreSQL 中创建了一个表。我想查看用于创建表的 SQL 语句,但无法弄清楚。
如何通过命令行或 SQL 语句获取 Postgres 中现有表的 create table
SQL 语句?
(注意 - 此解决方案不适用于 PostgreSQL v12+)
我的解决方案是使用带有 -E 选项的 psql 登录到 postgres db,如下所示:
psql -E -U username -d database
在 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
基于运行这些描述命令后回显的 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;
下面是函数用法:
SELECT generate_create_table_statement('tablename');
如果您不希望此功能永久存在,这里是 drop 语句:
DROP FUNCTION generate_create_table_statement(p_table_name varchar);
NULL
......架构是如何处理的?它应该为所有模式中具有指定名称的表返回 CREATE 语句吗?
从 linux 命令行为 postgresql 中的表生成创建表语句:
为演示创建一个表:
CREATE TABLE your_table(
thekey integer NOT NULL,
ticker character varying(10) NOT NULL,
date_val date,
open_val numeric(10,4) NOT NULL
);
pg_dumpmanual,可以输出table create psql语句:
pg_dump -U your_user your_database -t your_table --schema-only
哪个打印:
-- pre-requisite database and table configuration omitted
CREATE TABLE your_table (
thekey integer NOT NULL,
ticker character varying(10) NOT NULL,
date_val date,
open_val numeric(10,4) NOT NULL
);
-- post-requisite database and table configuration omitted
解释:
pg_dump 帮助我们获取有关数据库本身的信息。 -U
代表用户名。我的 pgadmin 用户没有设置密码,所以我不必输入密码。 -t
选项表示为一个表指定。 --schema-only
表示只打印关于表的数据,而不是表中的数据。
pg_dump
是优秀的 C 代码,它试图很好地适应不断发展的 sql 标准,并处理在 postgresql 的查询语言及其在磁盘上的表示之间出现的数千个细节。如果你想滚动你自己的“psql磁盘创建语句”安排,你是龙:https://doxygen.postgresql.org/pg__dump_8c_source.html
绕过 pg_dump
的另一个选项是在创建表时保存 table-create SQL 语句。将其保存在安全的地方,并在需要时取回。
或者用 SQL 从 postgresql 中获取表名、列名和数据类型信息:
CREATE TABLE your_table( thekey integer NOT NULL,
ticker character varying(10) NOT NULL,
date_val date,
open_val numeric(10,4) NOT NULL
);
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_name = 'your_table';
哪个打印:
┌────────────┬─────────────┬───────────────────┐
│ table_name │ column_name │ data_type │
├────────────┼─────────────┼───────────────────┤
│ your_table │ thekey │ integer │
│ your_table │ ticker │ character varying │
│ your_table │ date_val │ date │
│ your_table │ open_val │ numeric │
└────────────┴─────────────┴───────────────────┘
如果您想在不使用 pg_dump 的情况下查找表的创建语句,此查询可能对您有用(使用您的表调用的任何名称更改“表名”):
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 运行良好。
pg_dump
末尾看到的权限和约束。一样有用+1
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;
例如,现在您可以进行以下查询
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);
对于每个用户表。
我能想到的最简单的方法是安装 pgAdmin 3 (found here) 并使用它来查看您的数据库。它将自动生成一个查询,该查询将创建相关表。
如果您想一次对多个表执行此操作,您需要多次使用 -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
这是旧问题的另一个解决方案。多年来,这个问题已经有很多很好的答案,我的尝试大量借鉴了他们。
我使用 Andrey Lebedenko 的解决方案作为起点,因为它的输出已经非常接近我的要求。
特征:
按照惯例,我将外键约束移到了表定义之外。它们现在作为 ALTER TABLE 语句包含在底部。原因是外键也可以链接到同一张表的列。在这种边缘情况下,只能在表创建完成后创建约束。否则 create table 语句将引发错误。
布局和缩进现在看起来更好(至少在我看来)
在定义的标题中删除命令(注释掉)
该解决方案在此处作为 plpgsql 函数提供。然而,该算法不使用任何程序语言。该函数只包装了一个查询,该查询也可以在纯 sql 上下文中使用。
删除了多余的子查询
如果标识符与保留的 postgresql 语言元素相同,则现在引用标识符
替换了字符串连接运算符 ||使用适当的字符串函数来提高代码的性能、安全性和可读性。注意:||如果组合字符串之一为 NULL,则运算符产生 NULL。仅当这是所需的行为时才应使用它。 (查看下面代码中的用法示例)
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;
根据@vkkeeper 的回复进行了更多修改。增加了从特定模式查询表的可能性。
CREATE OR REPLACE FUNCTION public.describe_table(p_schema_name character varying, 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, 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 relname~ ('^('||p_table_name||')$')
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.oid = table_rec.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 relnamespace = (SELECT ns.oid FROM pg_namespace ns WHERE ns.nspname = p_schema_name)
) 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;
这是一个语句,它将为指定模式中的单个表生成 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;
这是 shekwi 的 query 的稍微改进版本。
它生成主键约束并能够处理临时表:
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
参数指定表的名称。
如果您有 PgAdmin4,请打开它。转到您的数据库->架构--->表->右键单击您想要创建脚本的表名--->脚本--->创建脚本
这是对我有用的变化:
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 "949766e0-e81e-11e3-b325-1cc1de32fcb6".floorplanpreferences_table --schema-only
您将获得可用于再次创建表的输出,只需在 psql 中运行该输出即可。
pg_dump -h XXXXXXXXXXX.us-west-1.rds.amazonaws.com -U anyuser -t tablename -s
像提到的其他答案一样,没有内置函数可以做到这一点。
这是一个尝试获取复制表所需的所有信息的函数 - 或比较已部署和在 ddl 中检查的信息。
该函数输出:
列(w/precision,null/not-null,默认值)
约束
索引
CREATE OR REPLACE FUNCTION public.show_create_table(
in_schema_name varchar,
in_table_name varchar
)
RETURNS text
LANGUAGE plpgsql VOLATILE
AS
$$
DECLARE
-- the ddl we're building
v_table_ddl text;
-- data about the target table
v_table_oid int;
-- records for looping
v_column_record record;
v_constraint_record record;
v_index_record record;
BEGIN
-- grab the oid of the table; https://www.postgresql.org/docs/8.3/catalog-pg-class.html
SELECT c.oid INTO v_table_oid
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE 1=1
AND c.relkind = 'r' -- r = ordinary table; https://www.postgresql.org/docs/9.3/catalog-pg-class.html
AND c.relname = in_table_name -- the table name
AND n.nspname = in_schema_name; -- the schema
-- throw an error if table was not found
IF (v_table_oid IS NULL) THEN
RAISE EXCEPTION 'table does not exist';
END IF;
-- start the create definition
v_table_ddl := 'CREATE TABLE ' || in_schema_name || '.' || in_table_name || ' (' || E'\n';
-- define all of the columns in the table; https://stackoverflow.com/a/8153081/3068233
FOR v_column_record IN
SELECT
c.column_name,
c.data_type,
c.character_maximum_length,
c.is_nullable,
c.column_default
FROM information_schema.columns c
WHERE (table_schema, table_name) = (in_schema_name, in_table_name)
ORDER BY ordinal_position
LOOP
v_table_ddl := v_table_ddl || ' ' -- note: two char spacer to start, to indent the column
|| v_column_record.column_name || ' '
|| v_column_record.data_type || CASE WHEN v_column_record.character_maximum_length IS NOT NULL THEN ('(' || v_column_record.character_maximum_length || ')') ELSE '' END || ' '
|| CASE WHEN v_column_record.is_nullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
|| CASE WHEN v_column_record.column_default IS NOT null THEN (' DEFAULT ' || v_column_record.column_default) ELSE '' END
|| ',' || E'\n';
END LOOP;
-- define all the constraints in the; https://www.postgresql.org/docs/9.1/catalog-pg-constraint.html && https://dba.stackexchange.com/a/214877/75296
FOR v_constraint_record IN
SELECT
con.conname as constraint_name,
con.contype as constraint_type,
CASE
WHEN con.contype = 'p' THEN 1 -- primary key constraint
WHEN con.contype = 'u' THEN 2 -- unique constraint
WHEN con.contype = 'f' THEN 3 -- foreign key constraint
WHEN con.contype = 'c' THEN 4
ELSE 5
END as type_rank,
pg_get_constraintdef(con.oid) as constraint_definition
FROM pg_catalog.pg_constraint con
JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
WHERE nsp.nspname = in_schema_name
AND rel.relname = in_table_name
ORDER BY type_rank
LOOP
v_table_ddl := v_table_ddl || ' ' -- note: two char spacer to start, to indent the column
|| 'CONSTRAINT' || ' '
|| v_constraint_record.constraint_name || ' '
|| v_constraint_record.constraint_definition
|| ',' || E'\n';
END LOOP;
-- drop the last comma before ending the create statement
v_table_ddl = substr(v_table_ddl, 0, length(v_table_ddl) - 1) || E'\n';
-- end the create definition
v_table_ddl := v_table_ddl || ');' || E'\n';
-- suffix create statement with all of the indexes on the table
FOR v_index_record IN
SELECT indexdef
FROM pg_indexes
WHERE (schemaname, tablename) = (in_schema_name, in_table_name)
LOOP
v_table_ddl := v_table_ddl
|| v_index_record.indexdef
|| ';' || E'\n';
END LOOP;
-- return the ddl
RETURN v_table_ddl;
END;
$$;
例子
SELECT * FROM public.show_create_table('public', 'example_table');
生产
CREATE TABLE public.example_table (
id bigint NOT NULL DEFAULT nextval('test_tb_for_show_create_on_id_seq'::regclass),
name character varying(150) NULL,
level character varying(50) NULL,
description text NOT NULL DEFAULT 'hello there!'::text,
CONSTRAINT test_tb_for_show_create_on_pkey PRIMARY KEY (id),
CONSTRAINT test_tb_for_show_create_on_level_check CHECK (((level)::text = ANY ((ARRAY['info'::character varying, 'warn'::character varying, 'error'::character varying])::text[])))
);
CREATE UNIQUE INDEX test_tb_for_show_create_on_pkey ON public.test_tb_for_show_create_on USING btree (id);
https://i.stack.imgur.com/OAE2y.png
您还可以使用免费的数据库管理工具,例如 DBeaver,它允许您查看表的 DDL,这是一个示例:
https://i.stack.imgur.com/iZbjW.png
使用它并在 ddl.out 文件中获取输出
~/bin/pg_dump -p 30000 -d <db_name> -U <db_user> --schema=<schema_name> -t <table_name> --schema-only >> /tmp/ddl.out
所以这将在路径中生成 DDL:/tmp/ddl.out
一个简单的解决方案,在纯单个 SQL 中。你明白了,你可以将它扩展到你喜欢展示的更多属性。
with c as (
SELECT table_name, ordinal_position,
column_name|| ' ' || data_type col
, row_number() over (partition by table_name order by ordinal_position asc) rn
, count(*) over (partition by table_name) cnt
FROM information_schema.columns
WHERE table_name in ('pg_index', 'pg_tables')
order by table_name, ordinal_position
)
select case when rn = 1 then 'create table ' || table_name || '(' else '' end
|| col
|| case when rn < cnt then ',' else '); ' end
from c
order by table_name, rn asc;
输出:
create table pg_index(indexrelid oid,
indrelid oid,
indnatts smallint,
indisunique boolean,
indisprimary boolean,
indisexclusion boolean,
indimmediate boolean,
indisclustered boolean,
indisvalid boolean,
indcheckxmin boolean,
indisready boolean,
indislive boolean,
indisreplident boolean,
indkey ARRAY,
indcollation ARRAY,
indclass ARRAY,
indoption ARRAY,
indexprs pg_node_tree,
indpred pg_node_tree);
create table pg_tables(schemaname name,
tablename name,
tableowner name,
tablespace name,
hasindexes boolean,
hasrules boolean,
hastriggers boolean,
rowsecurity boolean);
另一个简单的选择是使用 [HeidiSQL client][1] for PostgreSQL
数据库。
如何进入列出所有数据库和表的数据库选项卡。
单击您想要查看 DDL/创建特定表的语句的任何表/视图。
现在,该客户端在右侧窗口为该表为您执行以下工作:
第一个窗口将用于表格数据
第二个用于您的 SQL 主机信息
第三个用于数据库级信息,例如哪些表和大小
对于我们更关心的表/视图信息选项卡,我们将随时为您提供 create table 语句。
我无法在快照中向您展示使用机密数据的情况,请自己尝试一下,如果你们发现任何问题,请告诉我。
在 pgadminIII 数据库>>模式>>表>>右键单击'你的表'>>脚本>>'选择任何一个(创建,插入,更新,删除..)'
这是一个带有一些编辑的查询,
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;
在创建特定表之后生成 SQL (DDL)。我们可以简单地使用这个 SQL 查询 -
SHOW TABLE your_schema_name.your_table_name
pg_dump mydb -t mytable --schema-only
。aschema
替换为您要转储的表的实际架构名称。将atable
替换为您要转储的表的实际表名。pg_dump mydb -t '"TableName"' --schema-only
- 否则 pg_dump 将无法识别表名。