How to generate the create table sql statement for an existing table in postgreSQL

Question

I have created a table in postgreSQL  I want to look at the SQL statement used to create the table but cannot figure it out   How do I get the create table SQL statement for an existing table in Postgres via commandline or SQL statement

User · Answer

In pgadminIII database  schemas  tables   right click on  Your table   scripts    Select any one  Create Insert Update Delete

User · Answer

Another easy option was to use  HeidiSQL client  1  for PostgreSQL database  How to go into the database tab where all the databases and tables are listed  Click on any of the table View which you wanted to see the DDL create a statement of the particular table  Now there this client do the following jobs for you for that table  on the right-hand side windows  The first window would be for data of table Second for your SQL Host information Third for database-level information like which tables and what is the size  Forth which we are more concern about table view information tab will have the create table statement readily available for you   I can not show you in the snapshot as working with confidential data  Try it with yourself and let me know if any issues you guys found

User · Answer

Like the other answers mentioned  there is no built in function that does this  Here is a function that attempts to get all of the information that would be needed to replicate the table - or to compare deployed and checked in ddl  This function outputs   columns  w  precision  null not-null  default value  constraints indexes   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  amp  amp  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       example SELECT   FROM public show create table  public    example table     produces 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

User · Answer

If you want to find the create statement for a table without using pg dump  This query might work for you  change  tablename  with whatever your table is called    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  gt  0    AND a attrelid   c oid    AND a atttypid   t oid  ORDER BY a attnum   as tabledefinition group by relname    when called directly from psql  it is usefult to do    pset linestyle old-ascii   Also  the function generate create table statement in this thread works very well

User · Answer

Here is a query with some edits   select  CREATE TABLE      a attrelid  regclass  text           string agg a attname           pg catalog format type a atttypid   a atttypmod            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            CASE WHEN a attnotnull   true THEN                NOT NULL          ELSE                END E  n             FROM pg catalog pg attribute a join pg class on a attrelid pg class oid WHERE a attrelid  regclass  varchar      TABLENAME with or without schema  AND a attnum  gt  0 AND NOT a attisdropped  and pg class relkind  r  group by a attrelid

User · Answer

Generate the create table statement for a table in postgresql from linux commandline  Create a table for a demo  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 can output the table-create psql  pg dump -U your user your database -t your table --schema-only  Which prints  -- 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     Explanation  pg dump helps us get information about the database itself   -U stands for username   My pgadmin user has no password set  so I don t have to put in a password   The -t option means specify for one table   --schema-only means print only data about the table  and not the data in the table   Here is the exact command I use  Get the table name and datatype information from postgresql with SQL  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     Which prints   ----------------------------------------------      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                ----------------------------------------------

User · Answer

Dean Toader Just excellent  I d modify your code a little  to show all constraints in the table and to make possible to use regexp mask in table name   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  lt  gt   pg catalog                  AND n nspname  lt  gt   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  gt  0                      AND NOT a attisdropped                   GROUP BY a attrelid  e                 ON a attrelid e attrelid             WHERE a attnum  gt  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  lt   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    Now you can  for example  make the following query  SELECT   FROM generate create table statement          which results like this   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     for each user table

User · Answer

If you want to do this for various tables at once  you meed to use the -t switch multiple times  took me a while to figure out why comma separated list wasn t working   Also  can be useful to send results to an outfile or pipe to a postgres server on another machine  pg dump -t table1 -t table2 database name --schema-only  gt  dump sql  pg dump -t table1 -t table2 database name --schema-only   psql -h server name database name

User · Answer

DataGrip has the same functionality as pgAdmin  You can right click on a table and you will see option to auto-generate create table statement

User · Answer

My solution is to log in to the postgres db using psql with the -E option as follows   psql -E -U username -d database      In psql  run the following commands to see the sql that postgres uses to generate the describe table statement     -- 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     Based on the sql echoed out after running these describe commands  I was able to put together the following plpgsql function   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  gt  0                  AND NOT a attisdropped               GROUP BY a attrelid  e             ON a attrelid e attrelid         WHERE a attnum  gt  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  lt   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    Here is the function usage     SELECT generate create table statement  tablename      And here is the drop statement if you don t want this function to persist permanently     DROP FUNCTION generate create table statement p table name varchar

User · Answer

A simple solution  in pure single SQL  You get the idea  you may extend it to more attributes you like to show   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  lt  cnt then     else       end from c  order by table name  rn asc    Output   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

User · Answer

The easiest method I can think of is to install pgAdmin 3  found here  and use it to view your database   It will automatically generate a query that will create the table in question

User · Answer

pg dump -t  schema-name table-name  --schema-only database-name   More info - in the manual

User · Answer

Here is another solution to the old question  There have been many excellent answers to this question over the years and my attempt borrows heavily from them  I used Andrey Lebedenko s solution as a starting point because its output was already very close to my requirements  Features   following common practice I have moved the foreign key constraints outside the table definition  They are now included as ALTER TABLE statements at the bottom  The reason is that a foreign key can also link to a column of the same table  In that fringe case the constraint can only be created after the table creation is completed  The create table statement would throw an error otherwise  The layout and indenting looks nicer now  at least to my eye  Drop command  commented out  in the header of the definition The solution is offered here as a plpgsql function  The algorithm does however not use any procedural language  The function just wraps one single query that can be used in a pure sql context as well  removed redundant subqueries Identifiers are now quoted if they are identical to reserved postgresql language elements replaced the string concatenation operator    with the appropriate string functions to improve performance  security and readability of the code  Note  the    operator produces NULL if one of the combined strings is NULL  It should only be used when that is the desired behaviour   check out the usage in the code below for an example    CREATE OR REPLACE FUNCTION public wmv get table definition       p schema name character varying      p table name character varying       RETURNS SETOF TEXT     AS  BODY  BEGIN     RETURN query      WITH table rec AS           SELECT             c relname  n nspname  c oid         FROM             pg catalog pg class c             LEFT JOIN pg catalog pg namespace n ON n oid   c relnamespace         WHERE             relkind    r              AND n nspname   p schema name             AND c relname LIKE p table name         ORDER BY             c relname            col rec AS           SELECT             a attname AS colname              pg catalog format type a atttypid  a atttypmod  AS coltype              a attrelid AS oid                DEFAULT                        SELECT                     pg catalog pg get expr d adbin  d adrelid                  FROM                     pg catalog pg attrdef d                 WHERE                     d adrelid   a attrelid                     AND d adnum   a attnum                     AND a atthasdef  AS column default value              CASE WHEN a attnotnull   TRUE THEN                  NOT NULL              ELSE                  NULL              END AS column not null              a attnum AS attnum         FROM             pg catalog pg attribute a         WHERE             a attnum  gt  0             AND NOT a attisdropped         ORDER BY             a attnum            con rec AS           SELECT             conrelid  regclass  text AS relname              n nspname              conname              pg get constraintdef c oid  AS condef              contype              conrelid AS oid         FROM             pg constraint c             JOIN pg namespace n ON n oid   c connamespace            glue AS           SELECT             format  E --  1 I  2 I definition n n-- Drop table n n-- DROP TABLE IF EXISTS  1 I  2 I n nCREATE TABLE  1 I  2 I   n   table rec nspname  table rec relname  AS top              format  E  n   n n n-- adempiere wmv ghgaudit foreign keys n n   table rec nspname  table rec relname  AS bottom              oid         FROM             table rec            cols AS           SELECT             string agg format       I  s s  s   colname  coltype  column default value  column not null   E   n   AS lines              oid         FROM             col rec         GROUP BY             oid            constrnt AS           SELECT             string agg format      CONSTRAINT  s  s   con rec conname  con rec condef   E   n   AS lines              oid         FROM             con rec         WHERE             contype  lt  gt   f          GROUP BY             oid            frnkey AS           SELECT             string agg format  ALTER TABLE  I  I ADD CONSTRAINT  s  s   nspname  relname  conname  condef   E   n   AS lines              oid         FROM             con rec         WHERE             contype    f          GROUP BY             oid           SELECT         concat glue top  cols lines  E   n   constrnt lines  glue bottom  frnkey lines           FROM         glue         JOIN cols ON cols oid   glue oid         LEFT JOIN constrnt ON constrnt oid   glue oid         LEFT JOIN frnkey ON frnkey oid   glue oid  END   BODY  LANGUAGE plpgsql

User · Answer

Here is a single statement that will generate the DDL for a single table in a specified schema  including constraints   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  gt  0     AND NOT pa attisdropped GROUP BY pn nspname  pc relname  pa attrelid

User · Answer

pg dump -h XXXXXXXXXXX us-west-1 rds amazonaws com -U anyuser -t tablename -s

User · Answer

Even more modification based on response from  vkkeeper  Added possibility to query table from the specific schema   CREATE OR REPLACE FUNCTION public describe table p schema name character varying  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  c oid FROM pg catalog pg class c             LEFT JOIN pg catalog pg namespace n ON n oid   c relnamespace                 WHERE relkind    r                  AND n nspname   p schema name                 AND relname         p table name                  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 oid   table rec 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  gt  0                     AND NOT a attisdropped                   GROUP BY a attrelid  e                 ON a attrelid e attrelid             WHERE a attnum  gt  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  lt   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 relnamespace    SELECT ns oid FROM pg namespace ns WHERE ns nspname   p schema name                            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

User · Answer

This is the variation that works for me   pg dump -U user viktor -h localhost unit test database -t floorplanpreferences table --schema-only  In addition  if you re using schemas  you ll of course need to specify that as well   pg dump -U user viktor -h localhost unit test database -t  949766e0-e81e-11e3-b325-1cc1de32fcb6  floorplanpreferences table --schema-only  You will get an output that you can use to create the table again  just run that output in psql

User · Answer

Here is a bit improved version of shekwi s query  It generates the primary key constraint and is able to handle temporary tables   with pkey as       select cc conrelid  format E       constraint  I primary key  s    cc conname          string agg a attname                    order by array position cc conkey  a attnum    pkey     from pg catalog pg constraint cc         join pg catalog pg class c on c oid   cc conrelid         join pg catalog pg attribute a on a attrelid   cc conrelid              and a attnum   any cc conkey      where cc contype    p      group by cc conrelid  cc conname   select format E create  stable  s I n  n s s n   n       case c relpersistence when  t  then  temporary   else    end      case c relpersistence when  t  then    else n nspname        end      c relname      string agg          format E  t I  s s               a attname              pg catalog format type a atttypid  a atttypmod               case when a attnotnull then   not null  else    end            E   n          order by a attnum             select pkey from pkey where pkey conrelid   c oid   as sql from pg catalog pg class c     join pg catalog pg namespace n on n oid   c relnamespace     join pg catalog pg attribute a on a attrelid   c oid and a attnum  gt  0     join pg catalog pg type t on a atttypid   t oid where c relname    table name group by c oid  c relname  c relpersistence  n nspname    Use table name parameter to specify the name of the table

[postgresql] How to generate the "create table" sql statement for an existing table in postgreSQL

Examples related to postgresql