在pgadminIII数据库>>schema >>tables>>右键单击“Your table”>>scripts>>“选择任何一个(创建，插入，更新，删除..)”

(注意-此解决方案不适用于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;

从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           │ 
└────────────┴─────────────┴───────────────────┘ 

使用它并在ddl中获得输出。出文件

~/bin/pg_dump -p 30000 -d <db_name> -U <db_user> --schema=<schema_name> -t <table_name> --schema-only >> /tmp/ddl.out

因此这将在路径:/tmp/ DDL .out中生成DDL

一个简单的解决方案，在纯SQL。 您可以将其扩展到您想要显示的更多属性。

with c as (
SELECT table_name, ordinal_position, 
 column_name|| ' ' || data_type col
, row_number() over (partition by table_name order by ordinal_position asc) rn
, count(*) over (partition by table_name) cnt
FROM information_schema.columns
WHERE table_name   in ('pg_index', 'pg_tables')
order by table_name, ordinal_position
)
select case when rn = 1 then 'create table ' || table_name || '(' else '' end
 || col 
 || case when rn < cnt then ',' else '); ' end
from c 
order by table_name, rn asc;

输出:

create table pg_index(indexrelid oid,
 indrelid oid,
 indnatts smallint,
 indisunique boolean,
 indisprimary boolean,
 indisexclusion boolean,
 indimmediate boolean,
 indisclustered boolean,
 indisvalid boolean,
 indcheckxmin boolean,
 indisready boolean,
 indislive boolean,
 indisreplident boolean,
 indkey ARRAY,
 indcollation ARRAY,
 indclass ARRAY,
 indoption ARRAY,
 indexprs pg_node_tree,
 indpred pg_node_tree);

 create table pg_tables(schemaname name,
 tablename name,
 tableowner name,
 tablespace name,
 hasindexes boolean,
 hasrules boolean,
 hastriggers boolean,
 rowsecurity boolean);