我已经在postgreSQL中创建了一个表。我想查看用于创建表的SQL语句，但无法找出它。
如何通过命令行或SQL语句获得Postgres中现有表的创建表SQL语句?
当前回答
从linux命令行在postgresql中为一个表生成create table语句:
为演示创建一个表:
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_dump手动，可以输出表创建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的选项是在创建表时保存表创建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 │
└────────────┴─────────────┴───────────────────┘
其他回答
如果你想找到一个表的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函数工作得很好。
(注意-此解决方案不适用于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);
下面是一条语句，它将为指定模式(包括约束)中的单个表生成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;
另一个简单的选择是使用[HeidiSQL client][1]作为PostgreSQL数据库。
如何进入列出所有数据库和表的数据库选项卡。
单击想要查看DDL/创建特定表的语句的任何表/视图。
现在这个客户端为你在右边窗口的桌子上做以下工作:
第一个窗口将用于表中的数据
第二个是SQL主机信息
第三是数据库级别的信息，比如哪些表，大小是多少
第四，我们更关心的表/视图信息选项卡将有create table语句随时为您提供。
我不能在快照中显示你在处理机密数据，自己试试，让我知道如果你们发现了任何问题。
从linux命令行在postgresql中为一个表生成create table语句:
为演示创建一个表:
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_dump手动，可以输出表创建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的选项是在创建表时保存表创建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 │
└────────────┴─────────────┴───────────────────┘
推荐文章
- 查询JSON类型内的数组元素
- 获得PostgreSQL数据库中当前连接数的正确查询
- 纬度和经度的数据类型是什么?
- 如何在PostgreSQL中临时禁用触发器?
- 输入文件似乎是一个文本格式转储。请使用psql
- 使用LIMIT/OFFSET运行查询，还可以获得总行数
- 当恢复sql时，psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何添加列，如果不存在PostgreSQL?
- 如何在Postgres中获得两个字段的MIN() ?
- 截断Postgres数据库中的所有表
- 如何连接列在Postgres选择?
- 将varchar字段的类型更改为整数:"不能自动转换为整数类型"
- PostgreSQL可以索引数组列吗?
- PostgreSQL:角色不允许登录
