sirve - Cómo generar la sentencia sql "create table" para una tabla existente en postgreSQL

postgresql vs mysql

Question

Tengo una tabla creada en postgreSQL. Quiero ver el enunciado SQL utilizado para crear la tabla pero no puedo entenderlo.

¿Cómo obtengo la declaración sql ''create table'' para una tabla existente en postgresql a través de commandline o sql statement?

Answer 1

Aquí hay una consulta con algunas ediciones,

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 > 0 AND NOT a.attisdropped  and pg_class.relkind=''r''
group by a.attrelid;

Answer 2

Aquí hay una versión mejorada de la de .
Genera la restricción de clave primaria y puede manejar tablas temporales:

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 > 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 el parámetro table_name para especificar el nombre de la tabla.

Answer 3

El método más sencillo que puedo pensar es instalar pgAdmin 3 (que se encuentra aquí ) y usarlo para ver su base de datos. Generará automáticamente una consulta que creará la tabla en cuestión.

Answer 4

En la base de datos pgadminIII >> esquemas >> tablas >> haga clic derecho en ''Su tabla'' >> guiones >> ''Seleccione cualquiera (Crear, Insertar, Actualizar, Eliminar ...)''

Answer 5

Esta es la variación que funciona para mí:

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

Además, si usa esquemas, por supuesto deberá especificar eso también:

pg_dump -U user_viktor -h localhost unit_test_database -t "949766e0-e81e-11e3-b325-1cc1de32fcb6".floorplanpreferences_table --schema-only

Obtendrá un resultado que puede usar para crear la tabla nuevamente, solo ejecute esa salida en psql.

Answer 6

Mi solución es iniciar sesión en postgres db utilizando psql con la opción -E de la siguiente manera:

psql -E -U username -d database   

En psql, ejecute los siguientes comandos para ver el sql que postgres usa para generar
la declaración de la tabla de descripción:

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

Sobre la base de la recuperación de sql después de ejecutar estos comandos de descripción, pude juntar
la siguiente función 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;

Aquí está el uso de la función:

SELECT generate_create_table_statement(''tablename'');

Y aquí está la declaración desplegable si no desea que esta función permanezca de forma permanente:

DROP FUNCTION generate_create_table_statement(p_table_name varchar);

Answer 7

Si desea encontrar la instrucción create para una tabla sin usar pg_dump, esta consulta podría funcionar para usted (cambie ''tablename'' con lo que se llame a su tabla):

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;

cuando se llama directamente desde psql, es útil hacer:

/pset linestyle old-ascii

Además, la función generate_create_table_statement en este hilo funciona muy bien.

Answer 8

Si desea hacer esto para varias tablas a la vez, debe usar el modificador -t varias veces (me llevó un tiempo averiguar por qué la lista de comas separadas no funcionaba). Además, puede ser útil para enviar resultados a un archivo de salida o a un servidor postgres en otra máquina

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

Answer 9

Una solución simple, en solo SQL puro. Entiendes la idea, puedes extenderla a más atributos que te gustaría mostrar.

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;

Salida:

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);

Answer 10

Dean Toader ¡ Simplemente excelente! Modificaría un poco tu código, para mostrar todas las restricciones en la tabla y hacer posible el uso de la máscara regexp en el nombre de la tabla.

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;

Ahora puede, por ejemplo, hacer la siguiente consulta

SELECT * FROM generate_create_table_statement(''.*'');

que resulta así:

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);

para cada tabla de usuario.

Answer 11

Genere la instrucción create table para una tabla en postgresql desde linux commandline:

Esta declaración genera la tabla create sql statement para mí:

pg_dump -U your_db_user_name your_database -t your_table_name --schema-only

Explicación:

pg_dump nos ayuda a obtener información sobre la base de datos en sí. -U significa nombre de usuario. Mi usuario de pgadmin no tiene contraseña establecida, así que no tengo que ingresar una contraseña. La opción -t significa especificar para una tabla. --schema-only significa imprimir solo datos sobre la tabla, y no los datos en la tabla. Aquí está el comando exacto que uso:

pg_dump -U pgadmin kurz_prod -t fact_stock_info --schema-only

Answer 12

pg_dump -t ''aschema.atable'' --schema-only database-name

Más información - en el manual .

Answer 13

pg_dump -h XXXXXXXXXXX.us-west-1.rds.amazonaws.com -U anyuser -t tablename -s