我已经在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);
对于每个用户表。
其他回答
与前面提到的其他答案一样，没有内置函数可以做到这一点。
下面是一个函数，它试图获取复制表或比较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);
另一个简单的选择是使用[HeidiSQL client][1]作为PostgreSQL数据库。
如何进入列出所有数据库和表的数据库选项卡。
单击想要查看DDL/创建特定表的语句的任何表/视图。
现在这个客户端为你在右边窗口的桌子上做以下工作:
第一个窗口将用于表中的数据
第二个是SQL主机信息
第三是数据库级别的信息，比如哪些表，大小是多少
第四，我们更关心的表/视图信息选项卡将有create table语句随时为您提供。
我不能在快照中显示你在处理机密数据，自己试试，让我知道如果你们发现了任何问题。
甚至根据@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;
如果您不想创建函数，而只想让查询创建一个基本的表结构，这里有一个解决方案。
select 'CREATE TABLE ' || table_name ||'(' ||STRING_AGG (
column_name || ' ' || data_type ,
','
ORDER BY
table_name,
ordinal_position
) ||');'
from
information_schema.columns
where table_schema = 'public'
group by
table_name
这是对我有用的变化:
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——模式
您将得到一个输出，可以用来再次创建表，只需在psql中运行该输出。
推荐文章
- SQL:从时间戳日期减去1天
- PostgreSQL删除所有内容
- 为什么PostgreSQL要对索引列进行顺序扫描?
- PostgreSQL INSERT ON冲突更新(upsert)使用所有排除的值
- 如何检查一个表是否存在于给定的模式中
- 如何将整数转换为字符串作为PostgreSQL查询的一部分?
- Psycopg2:用一个查询插入多行
- PostgreSQL返回JSON数组的结果集?
- PostgreSQL通配符LIKE用于单词列表中的任何一个
- 检查Postgres JSON数组是否包含字符串
- psql: FATAL: Peer authentication failed for user "dev"
- 如何在Postgres/SQL中获得两个整数的最小/最大值?
- 如何在postgresql中显示函数，过程，触发器源代码?
- 如何用postgres将间隔转换为小时数?
- 在postgresql中将查询结果按月和年分组
