-- 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
'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;
Dean Toader 非常好!我稍微修改你的代码,以显示表中的所有约束,并使表名中使用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);
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 参数指定表的名称 .
-1
这是一个包含一些编辑的查询,
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;
280
纯单一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 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;
15 回答
更多信息 - 在manual .
我的解决方案是使用psql和-E选项登录postgres db,如下所示:
在psql中,运行以下命令以查看postgres用于生成的sql
describe table语句:
基于运行这些描述命令后回显的sql,我能够把它放在一起
以下plpgsql函数:
这是功能用法:
如果您不希望此函数永久保留,则这是drop语句:
Generate the create table statement for a table in postgresql from linux commandline:
这个语句为我输出表create sql语句:
Explanation:
pg_dump帮助我们获取有关数据库本身的信息 .
-U
代表用户名 . 我的pgadmin用户没有设置密码,所以我不必输入密码 .-t
选项表示指定一个表 .--schema-only
表示仅打印有关表的数据,而不打印表中的数据 . 这是我使用的确切命令:如果要在不使用pg_dump的情况下查找表的create语句,则此查询可能适用于您(使用调用的表更改'tablename'):
当直接从psql调用时,这是有用的:
此外,此线程中的函数generate_create_table_statement非常有效 .
我能想到的最简单的方法是安装pgAdmin 3(found here)并使用它来查看您的数据库 . 它将自动生成将创建相关表的查询 .
如果你想一次为各种表执行此操作,你需要多次使用-t开关(花了一些时间来弄清楚为什么逗号分隔列表不起作用) . 此外,将结果发送到outfile或管道到另一台机器上的postgres服务器也很有用
Dean Toader 非常好!我稍微修改你的代码,以显示表中的所有约束,并使表名中使用regexp掩码成为可能 .
现在,您可以进行以下查询
结果如下:
对于每个用户表 .
这是适合我的变化:
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中运行该输出即可 .
这是shekwi的query的一个改进版本 .
它生成主键约束并能够处理临时表:
使用
table_name
参数指定表的名称 .这是一个包含一些编辑的查询,
纯单一SQL中的简单解决方案 . 你明白了,你可以将它扩展到你想要显示的更多属性 .
输出:
基于@vkkeeper的响应进行更多修改 . 增加了从特定模式查询表的可能性 .
在pgadminIII数据库>> schemas >> tables >>右键单击'Your table'>> scripts >>'选择任何一个(Create,Insert,Update,Delete ..)'
另一个选择是使用IntelliJ IDEA / DataGrip工具(适用于Postgres和Redshift) . 有关详细信息jetbrains site .