Cómo generar la instrucción SQL "crear tabla" para una tabla existente en PostgreSQL
He creado una tabla en postgreSQL. Quiero ver la declaración SQL utilizada para crear la tabla pero no puedo entenderla.
¿Cómo obtengo la create tabledeclaración SQL para una tabla existente en Postgres mediante la línea de comandos o la declaración SQL?
pg_dump -t 'schema-name.table-name' --schema-only database-name
Más información en el manual .
(AVISO: esta solución no funciona con PostgreSQL v12+)
Mi solución es iniciar sesión en la base de datos de Postgres usando 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 usa postgres para generar
la declaración de descripción de la tabla:
-- 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
Según el sql que se repitió después de ejecutar estos comandos de descripción, pude armar
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 de caída si no desea que esta función persista permanentemente:
DROP FUNCTION generate_create_table_statement(p_table_name varchar);
Apr 22 '2013 18:04
Genere la instrucción de creación de tabla para una tabla en postgresql desde la línea de comandos de Linux:
Cree una tabla de demostración:
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 manual , puede generar la tabla crear declaración psql:
pg_dump -U your_user your_database -t your_table --schema-only
Que imprime:
-- 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
Explicación:
pg_dump man pg_dumpes una utilidad para realizar copias de seguridad de una base de datos PostgreSQL. -Uaquí significa nombre de usuario. -topción significa especificar para una tabla. --schema-onlysignifica imprimir solo datos sobre esta tabla y no volcar los datos en la tabla. Esto es bueno porque también incluye información sobre índices, claves externas y otras condiciones conocidas de la base de datos.
pg_dumpAquí está el código C de be dragons que intenta funcionar bien con los estándares SQL en evolución y se ocupa de los mil detalles que surgen entre el lenguaje de consulta de PostgreSQL a medida que cambia con el tiempo y su representación en el disco. Si desea crear su propio arreglo de "disco psql para crear una declaración", haga clic en: https://doxygen.postgresql.org/pg__dump_8c_source.html
O obtenga el nombre de la tabla, el nombre de la columna y la información del tipo de datos de postgresql con 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';
Que imprime:
┌────────────┬─────────────┬───────────────────┐
│ 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 │
└────────────┴─────────────┴───────────────────┘
Haga el suyo propio o haga que una biblioteca se encargue de esto por usted:
Tenía su declaración SQL de creación de tabla al principio, cuando creó la tabla. Guárdelo como myschema/file_01.sqly guárdelo en el control de versiones. Allí puede recuperarlo cuando lo necesite o simplemente ejecutar file_01.sql en file_nn.sql uno tras otro.
Cualquier cantidad de bibliotecas pueden manejar esto automáticamente por usted, o usted puede crear la suya propia. Pero entonces el riesgo es que el estado real de su base de datos se aleja del estado definido en el control de versiones, lo que significa que para verificarlo debe borrar la base de datos, luego descargarla y volver a cargarla para asegurarse de que los cambios de esquema registrados sean idénticos a los que tiene.
