天天看點

PostgreSQL檢視表結構語句

參考​​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。      

繼續閱讀