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?
23 Answers
pg_dump -t 'schema-name.table-name' --schema-only database-nameMore info - in the manual.
10(NOTICE - this solution is not working with PostgreSQL v12+)
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 > 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;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);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 manual, can output the table create psql statement:
pg_dump -U your_user your_database -t your_table --schema-onlyWhich 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.
pg_dump is elite C code that tries to play nicely with the evolving sql standards, and takes care of the thousand details that arise between postgresql's query language, and its representation on a disk. If you want to roll your own "psql disk to create statement" arrangement, ye be dragons:
Another option to get around pg_dump is to save the table-create SQL statement when you create the table. Keep it somewhere safe and retrieve it when you need it.
Or get the table name, column 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 │
└────────────┴─────────────┴───────────────────┘ 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 > 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-asciiAlso, the function generate_create_table_statement in this thread works very well.
6Dean 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 <> '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;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.
1The 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.
2If 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 > dump.sql
pg_dump -t table1 -t table2 database_name --schema-only | psql -h server_name database_nameHere 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 > 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 <> '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;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 > 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 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;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 > 0 AND NOT pa.attisdropped
GROUP BY pn.nspname, pc.relname, pa.attrelid;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 > 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.
If you have PgAdmin4, then open it. Go to your database--> schema---> table--> right click on table name whose create script you want---> Scripts---> CREATE SCRIPT
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.
pg_dump -h XXXXXXXXXXX.us-west-1.rds.amazonaws.com -U anyuser -t tablename -sLike 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; 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; 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; 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; && 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);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.
YOu can also use a free DB management tool, such as DBeaver, which allows you to view DDL for the tables, here's an example:
Use this and get your output in ddl.out file
~/bin/pg_dump -p 30000 -d <db_name> -U <db_user> --schema=<schema_name> -t <table_name> --schema-only >> /tmp/ddl.outSo this will generate DDL in the path: /tmp/ddl.out
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 < 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);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.
In pgadminIII database>>schemas>>tables>> right click on 'Your table'>>scripts>> 'Select any one (Create,Insert,Update,Delete..)'
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 > 0 AND NOT a.attisdropped and pg_class.relkind='r'
group by a.attrelid;To generate the SQL (DDL) behind the creation of a particular table. We can simply use this SQL query -
SHOW TABLE your_schema_name.your_table_name
