我已经在postgreSQL中创建了一个表。我想查看用于创建表的SQL语句,但无法找出它。
如何通过命令行或SQL语句获得Postgres中现有表的创建表SQL语句?
我已经在postgreSQL中创建了一个表。我想查看用于创建表的SQL语句,但无法找出它。
如何通过命令行或SQL语句获得Postgres中现有表的创建表SQL语句?
当前回答
你也可以使用一个免费的数据库管理工具,比如DBeaver,它允许你查看表的DDL,这里有一个例子:
其他回答
在pgadminIII数据库>>schema >>tables>>右键单击“Your table”>>scripts>>“选择任何一个(创建,插入,更新,删除..)”
与前面提到的其他答案一样,没有内置函数可以做到这一点。
下面是一个函数,它试图获取复制表或比较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);
如果您不想创建函数,而只想让查询创建一个基本的表结构,这里有一个解决方案。
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 -h XXXXXXXXXXX.us-west-1.rds.amazonaws.com -U anyuser -t tablename -s
如果你想找到一个表的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函数工作得很好。