參考https://www.modb.pro/db/60164
在PG裡面檢視表結構，不如mysql那樣show create table xx 這樣友善。
在PG裡面常用如下3種方法：
第一種，最樸實的方法就是 pg_dump --schema-only 導出全部的表結構
第二種，建立一個function，具體如下：
CREATE OR REPLACE FUNCTION tabledef(text,text) RETURNS text
LANGUAGE sql STRICT AS
$$
WITH attrdef AS (
SELECT n.nspname, c.relname, c.oid, pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ') as relopts,
c.relpersistence, a.attnum, a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) as atttype,
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as attdefault,
a.attnotnull, (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) as attcollation,
a.attidentity, a.attgenerated
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
WHERE n.nspname = $1 AND c.relname = $2 AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
), coldef AS (
SELECT attrdef.nspname, attrdef.relname, attrdef.oid, attrdef.relopts, attrdef.relpersistence, pg_catalog.format('%I %s%s%s%s%s', attrdef.attname, attrdef.atttype,
case when attrdef.attcollation is null then '' else pg_catalog.format(' COLLATE %I', attrdef.attcollation) end,
case when attrdef.attnotnull then ' NOT NULL' else '' end,
case when attrdef.attdefault is null then '' else case when attrdef.attgenerated = 's' then pg_catalog.format(' GENERATED ALWAYS AS (%s) STORED', attrdef.attdefault) when attrdef.attgenerated <> '' then ' GENERATED AS NOT_IMPLEMENTED' else pg_catalog.format(' DEFAULT %s', attrdef.attdefault) end end,
case when attrdef.attidentity<>'' then pg_catalog.format(' GENERATED %s AS IDENTITY', case attrdef.attidentity when 'd' then 'BY DEFAULT' when 'a' then 'ALWAYS' else 'NOT_IMPLEMENTED' end) else '' end ) as col_create_sql
FROM attrdef
ORDER BY attrdef.attnum
), tabdef AS (
SELECT coldef.nspname, coldef.relname, coldef.oid, coldef.relopts, coldef.relpersistence, concat(string_agg(coldef.col_create_sql, E',\n ') , (select concat(E',\n ',pg_get_constraintdef(oid)) from pg_constraint where contype='p' and conrelid = coldef.oid)) as cols_create_sql
FROM coldef
GROUP BY coldef.nspname, coldef.relname, coldef.oid, coldef.relopts, coldef.relpersistence
)
SELECT FORMAT( 'CREATE%s TABLE %I.%I%s%s%s;',
case tabdef.relpersistence when 't' then ' TEMP' when 'u' then ' UNLOGGED' else '' end,
tabdef.nspname,
tabdef.relname,
coalesce( (
SELECT FORMAT( E'\n PARTITION OF %I.%I %s\n', pn.nspname, pc.relname, pg_get_expr(c.relpartbound, c.oid) )
FROM pg_class c
JOIN pg_inherits i ON c.oid = i.inhrelid
JOIN pg_class pc ON pc.oid = i.inhparent
JOIN pg_namespace pn ON pn.oid = pc.relnamespace
WHERE c.oid = tabdef.oid ),
FORMAT( E' (\n %s\n)', tabdef.cols_create_sql)
),
case when tabdef.relopts <> '' then format(' WITH (%s)', tabdef.relopts) else '' end,
coalesce(E'\nPARTITION BY '||pg_get_partkeydef(tabdef.oid), '')
) as table_create_sql
FROM tabdef
$$;
效果：
=# select tabledef('public','pgbench_accounts');
tabledef
────────────────────────────────────────
CREATE TABLE public.pgbench_accounts (↵
aid integer NOT NULL, ↵
bid integer, ↵
abalance integer, ↵
filler character(84), ↵
PRIMARY KEY (aid) ↵
) WITH (fillfactor=100);
(1
row) 注意：
1、這個function是database級别的，如果換到其它database 需要重建建立function。
2、經測試，支援view的導出檢視
3、經測試，得出的建表語句中，是不會帶有外鍵的資訊，如果用到了外鍵的請注意！
第三種，使用pgddl插件
要使用root使用者安裝此插件，并在安裝時設定好環境變量。 稍微費點事。
官方倉庫： https://github.com/lacanoid/pgddl
cd postgresql-14.0/contrib
unzip pgddl-master.zip
cd pgddl-master
export PG_CONFIG=/usr/local/pgsql-14.0/bin/pg_config
make
make install
=# \c dbatest
=# create extension ddlx;
-- 提取建庫語句
=# SELECT ddlx_create(oid) FROM pg_database WHERE datname=current_database();
ddlx_create
─────────────────────────────────────────────────────
CREATE DATABASE dbatest WITH ↵
ENCODING = UTF8 ↵
LC_COLLATE = "en_US.UTF-8" ↵
LC_CTYPE = "en_US.UTF-8"; ↵
↵
ALTER DATABASE dbatest SET TABLESPACE pg_default; ↵
↵
COMMENT ON DATABASE dbatest IS NULL; ↵
↵
ALTER DATABASE dbatest WITH ALLOW_CONNECTIONS true;↵
ALTER DATABASE dbatest WITH IS_TEMPLATE false; ↵
(1 row)
=# \dt+
List of relations
Schema │ Name │ Type │ Owner │ Persistence │ Access method │ Size │ Description
────────┼─────────────────┼───────┼──────────┼─────────────┼───────────────┼────────────┼─────────────
public │ pgbench_tellers │ table │ dts │ permanent │ heap │ 0 bytes │
public │ users │ table │ postgres │ permanent │ heap │ 8192 bytes │
(2 rows)
-- 提取建表語句
=# SELECT ddlx_script('users') ;
ddlx_script
───────────────────────────────────────────────
BEGIN; ↵
↵
/* ↵
DROP TABLE users; -- !!!ATTENTION!!! -- ↵
*/ ↵
↵
-- Type: TABLE ; Name: users; Owner: postgres↵
↵
CREATE TABLE users ( ↵
id integer NOT NULL, ↵
name text ↵
); ↵
↵
COMMENT ON TABLE users IS NULL; ↵
↵
ALTER TABLE users ADD CONSTRAINT users_pkey ↵
PRIMARY KEY (id); ↵
↵
↵
END; ↵
(1 row)
=# select ddlx_script('log');
ddlx_script
────────────────────────────────────────────
BEGIN; ↵
↵
/* ↵
DROP VIEW log; ↵
*/ ↵
↵
-- Type: VIEW ; Name: log; Owner: postgres↵
↵
CREATE OR REPLACE VIEW log AS ↵
SELECT pglog.id, ↵
pglog.message ↵
FROM pglog; ↵
↵
COMMENT ON VIEW log IS NULL; ↵
↵
↵
END; ↵
(1 row)
=# \d+ s1
Sequence "public.s1"
Type │ Start │ Minimum │ Maximum │ Increment │ Cycles? │ Cache
────────┼───────┼─────────┼─────────────────────┼───────────┼─────────┼───────
bigint │ 1 │ 1 │ 9223372036854775807 │ 1 │ no │ 1
=# select ddlx_script('s1');
ERROR: function ddlx_script(unknown) does not exist
LINE 1: select ddlx_script('s1');
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
目前遇到的問題： 它不能提取出 sequence。 ![Mysql表結構轉Postgresql建表語句快速操作[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)