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

如果您想一次对多个表执行此操作，则需要多次使用-t开关(我花了一些时间才弄清楚为什么逗号分隔的列表不起作用)。此外，将结果发送到外文件或管道到另一台机器上的postgres服务器也很有用

pg_dump -t table1 -t table2 database_name --schema-only > dump.sql

pg_dump -t table1 -t table2 database_name --schema-only | psql -h server_name database_name

如果你想找到一个表的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函数工作得很好。

这是对我有用的变化:

pg_dump -U user_viktor -h localhost unit_test_database -t floorplanpreferences_table——schema-only

此外，如果你正在使用模式，你当然也需要指定:

pg_dump -U user_viktor -h localhost unit_test_database -t "949766e0-e81e-11e3-b325-1cc1de32fcb6"。floorplanpreferences_table——模式

您将得到一个输出，可以用来再次创建表，只需在psql中运行该输出。

生成创建特定表背后的SQL (DDL)。 我们可以简单地使用这个SQL查询-

SHOW TABLE your_schema_name.your_table_name

太棒了! 我会稍微修改一下您的代码，以显示表中的所有约束，并允许在表名中使用regexp掩码。

CREATE OR REPLACE FUNCTION public.generate_create_table_statement(p_table_name character varying)
  RETURNS SETOF text AS
$BODY$
DECLARE
    v_table_ddl   text;
    column_record record;
    table_rec record;
    constraint_rec record;
    firstrec boolean;
BEGIN
    FOR table_rec IN
        SELECT c.relname FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                WHERE relkind = 'r'
                AND relname~ ('^('||p_table_name||')$')
                AND n.nspname <> 'pg_catalog'
                AND n.nspname <> 'information_schema'
                AND n.nspname !~ '^pg_toast'
                AND pg_catalog.pg_table_is_visible(c.oid)
          ORDER BY c.relname
    LOOP

        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 = table_rec.relname
                    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;

        firstrec := TRUE;
        FOR constraint_rec IN
            SELECT conname, pg_get_constraintdef(c.oid) as constrainddef 
                FROM pg_constraint c 
                    WHERE conrelid=(
                        SELECT attrelid FROM pg_attribute
                        WHERE attrelid = (
                            SELECT oid FROM pg_class WHERE relname = table_rec.relname
                        ) AND attname='tableoid'
                    )
        LOOP
            v_table_ddl:=v_table_ddl||','||chr(10);
            v_table_ddl:=v_table_ddl||'CONSTRAINT '||constraint_rec.conname;
            v_table_ddl:=v_table_ddl||chr(10)||'    '||constraint_rec.constrainddef;
            firstrec := FALSE;
        END LOOP;
        v_table_ddl:=v_table_ddl||');';
        RETURN NEXT v_table_ddl;
    END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.generate_create_table_statement(character varying)
  OWNER TO postgres;

例如，现在您可以执行以下查询

SELECT * FROM generate_create_table_statement('.*');

结果是这样的:

CREATE TABLE public.answer (                                                                        
     id integer DEFAULT nextval('answer_id_seq'::regclass) NOT NULL,                               
     questionid integer  NOT NULL,                                                                  
     title character varying  NOT NULL,                                                             
     defaultvalue character varying  NULL,                                                          
     valuetype integer  NOT NULL,                                                                   
     isdefault boolean  NULL,                                                                       
     minval double precision  NULL,                                                                 
     maxval double precision  NULL,                                                                 
     followminmax integer DEFAULT 0 NOT NULL,                                                       
CONSTRAINT answer_pkey                                                                              
     PRIMARY KEY (id),                                                                              
CONSTRAINT answer_questionid_fkey                                                                  
     FOREIGN KEY (questionid) REFERENCES question(id) ON UPDATE RESTRICT ON DELETE RESTRICT,       
CONSTRAINT answer_valuetype_fkey                                                                   
     FOREIGN KEY (valuetype) REFERENCES answervaluetype(id) ON UPDATE RESTRICT ON DELETE RESTRICT);

对于每个用户表。