Is there an equivalent of MySQL's SHOW CREATE TABLE in Postgres?
Solution 1:
pg_dump:
pg_dump -st tablename dbname
or use PostgreSQL GUI Tools(pgAdmin,phpPgAdmin,etc.)
Solution 2:
You can try to trace in the PostgreSQL log file what "pg_dump -t table -s" really does.Then you can use the same method to write your own sql function.
Solution 3:
In command line (psql
) you can run: \d <table name>
to list all columns, their types and indexes.
Solution 4:
Building on the first part of @CubicalSoft's answer you can drop in the following function which should work for simple tables (assumes the default 'public' schema' and omits constraints, indexes and user defined data types etc. etc.). @RJS answer is the only way to do it properly at the moment; this is something that should be built into psql!
CREATE OR REPLACE FUNCTION show_create_table(table_name text, join_char text = E'\n' )
RETURNS text AS
$BODY$
SELECT 'CREATE TABLE ' || $1 || ' (' || $2 || '' ||
string_agg(column_list.column_expr, ', ' || $2 || '') ||
'' || $2 || ');'
FROM (
SELECT ' ' || column_name || ' ' || data_type ||
coalesce('(' || character_maximum_length || ')', '') ||
case when is_nullable = 'YES' then '' else ' NOT NULL' end as column_expr
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = $1
ORDER BY ordinal_position) column_list;
$BODY$
LANGUAGE SQL STABLE;
Solution 5:
I realize I'm a bit late to this party, but this was the first result to my Google Search so I figured I'd answer with what I came up with.
You can get pretty far toward a solution with this query to get the columns:
SELECT *
FROM information_schema.columns
WHERE table_schema = 'YOURSCHEMA' AND table_name = 'YOURTABLE'
ORDER BY ordinal_position;
And then this query for most common indexes:
SELECT c.oid, c.relname, a.attname, a.attnum, i.indisprimary, i.indisunique
FROM pg_index AS i, pg_class AS c, pg_attribute AS a
WHERE i.indexrelid = c.oid AND i.indexrelid = a.attrelid AND i.indrelid = 'YOURSCHEMA.YOURTABLE'::regclass
ORDER BY" => "c.oid, a.attnum
Then it is a matter of building out the query string(s) in the right format.