从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。

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;

与前面提到的其他答案一样，没有内置函数可以做到这一点。

下面是一个函数，它试图获取复制表或比较ddl中部署和检查所需的所有信息。

这个函数输出:

列(w/ precision, null/not-null，默认值) 约束 索引

CREATE OR REPLACE FUNCTION public.show_create_table(
  in_schema_name varchar,
  in_table_name varchar
)
RETURNS text
LANGUAGE plpgsql VOLATILE
AS
$$
  DECLARE
    -- the ddl we're building
    v_table_ddl text;

    -- data about the target table
    v_table_oid int;

    -- records for looping
    v_column_record record;
    v_constraint_record record;
    v_index_record record;
  BEGIN
    -- grab the oid of the table; https://www.postgresql.org/docs/8.3/catalog-pg-class.html
    SELECT c.oid INTO v_table_oid
    FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE 1=1
      AND c.relkind = 'r' -- r = ordinary table; https://www.postgresql.org/docs/9.3/catalog-pg-class.html
      AND c.relname = in_table_name -- the table name
      AND n.nspname = in_schema_name; -- the schema

    -- throw an error if table was not found
    IF (v_table_oid IS NULL) THEN
      RAISE EXCEPTION 'table does not exist';
    END IF;

    -- start the create definition
    v_table_ddl := 'CREATE TABLE ' || in_schema_name || '.' || in_table_name || ' (' || E'\n';

    -- define all of the columns in the table; https://stackoverflow.com/a/8153081/3068233
    FOR v_column_record IN
      SELECT
        c.column_name,
        c.data_type,
        c.character_maximum_length,
        c.is_nullable,
        c.column_default
      FROM information_schema.columns c
      WHERE (table_schema, table_name) = (in_schema_name, in_table_name)
      ORDER BY ordinal_position
    LOOP
      v_table_ddl := v_table_ddl || '  ' -- note: two char spacer to start, to indent the column
        || v_column_record.column_name || ' '
        || v_column_record.data_type || CASE WHEN v_column_record.character_maximum_length IS NOT NULL THEN ('(' || v_column_record.character_maximum_length || ')') ELSE '' END || ' '
        || CASE WHEN v_column_record.is_nullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
        || CASE WHEN v_column_record.column_default IS NOT null THEN (' DEFAULT ' || v_column_record.column_default) ELSE '' END
        || ',' || E'\n';
    END LOOP;

    -- define all the constraints in the; https://www.postgresql.org/docs/9.1/catalog-pg-constraint.html && https://dba.stackexchange.com/a/214877/75296
    FOR v_constraint_record IN
      SELECT
        con.conname as constraint_name,
        con.contype as constraint_type,
        CASE
          WHEN con.contype = 'p' THEN 1 -- primary key constraint
          WHEN con.contype = 'u' THEN 2 -- unique constraint
          WHEN con.contype = 'f' THEN 3 -- foreign key constraint
          WHEN con.contype = 'c' THEN 4
          ELSE 5
        END as type_rank,
        pg_get_constraintdef(con.oid) as constraint_definition
      FROM pg_catalog.pg_constraint con
      JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
      JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
      WHERE nsp.nspname = in_schema_name
      AND rel.relname = in_table_name
      ORDER BY type_rank
    LOOP
      v_table_ddl := v_table_ddl || '  ' -- note: two char spacer to start, to indent the column
        || 'CONSTRAINT' || ' '
        || v_constraint_record.constraint_name || ' '
        || v_constraint_record.constraint_definition
        || ',' || E'\n';
    END LOOP;

    -- drop the last comma before ending the create statement
    v_table_ddl = substr(v_table_ddl, 0, length(v_table_ddl) - 1) || E'\n';

    -- end the create definition
    v_table_ddl := v_table_ddl || ');' || E'\n';

    -- suffix create statement with all of the indexes on the table
    FOR v_index_record IN
      SELECT indexdef
      FROM pg_indexes
      WHERE (schemaname, tablename) = (in_schema_name, in_table_name)
    LOOP
      v_table_ddl := v_table_ddl
        || v_index_record.indexdef
        || ';' || E'\n';
    END LOOP;

    -- return the ddl
    RETURN v_table_ddl;
  END;
$$;

例子

SELECT * FROM public.show_create_table('public', 'example_table');

生产

CREATE TABLE public.example_table (
  id bigint NOT NULL DEFAULT nextval('test_tb_for_show_create_on_id_seq'::regclass),
  name character varying(150) NULL,
  level character varying(50) NULL,
  description text NOT NULL DEFAULT 'hello there!'::text,
  CONSTRAINT test_tb_for_show_create_on_pkey PRIMARY KEY (id),
  CONSTRAINT test_tb_for_show_create_on_level_check CHECK (((level)::text = ANY ((ARRAY['info'::character varying, 'warn'::character varying, 'error'::character varying])::text[])))
);
CREATE UNIQUE INDEX test_tb_for_show_create_on_pkey ON public.test_tb_for_show_create_on USING btree (id);

你也可以使用一个免费的数据库管理工具，比如DBeaver，它允许你查看表的DDL，这里有一个例子:

这是对我有用的变化:

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中运行该输出。

我能想到的最简单的方法是安装pgAdmin 3(在这里找到)，并用它来查看数据库。它将自动生成一个查询，该查询将创建所讨论的表。