我已经在postgreSQL中创建了一个表。我想查看用于创建表的SQL语句,但无法找出它。
如何通过命令行或SQL语句获得Postgres中现有表的创建表SQL语句?
我已经在postgreSQL中创建了一个表。我想查看用于创建表的SQL语句,但无法找出它。
如何通过命令行或SQL语句获得Postgres中现有表的创建表SQL语句?
当前回答
(注意-此解决方案不适用于PostgreSQL v12+)
我的解决方案是使用psql和-E选项登录postgres db,如下所示:
psql -E -U username -d database
在psql中,运行以下命令查看postgres用来生成的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);
其他回答
我能想到的最简单的方法是安装pgAdmin 3(在这里找到),并用它来查看数据库。它将自动生成一个查询,该查询将创建所讨论的表。
生成创建特定表背后的SQL (DDL)。 我们可以简单地使用这个SQL查询-
SHOW TABLE your_schema_name.your_table_name
另一个简单的选择是使用[HeidiSQL client][1]作为PostgreSQL数据库。
如何进入列出所有数据库和表的数据库选项卡。
单击想要查看DDL/创建特定表的语句的任何表/视图。
现在这个客户端为你在右边窗口的桌子上做以下工作:
第一个窗口将用于表中的数据
第二个是SQL主机信息
第三是数据库级别的信息,比如哪些表,大小是多少
第四,我们更关心的表/视图信息选项卡将有create table语句随时为您提供。
我不能在快照中显示你在处理机密数据,自己试试,让我知道如果你们发现了任何问题。
如果你想找到一个表的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函数工作得很好。
如果您不想创建函数,而只想让查询创建一个基本的表结构,这里有一个解决方案。
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