Is there an equivalent of MySQL's
SHOW CREATE TABLE in Postgres? Is this possible? If not what is the next best solution?
I need the statement because I use it to create the table on an remote server (over WCF).
Server Fault is a question and answer site for system and network administrators. It only takes a minute to sign up.Sign up to join this community
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;
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.
Postgres extension ddlx (https://github.com/lacanoid/pgddl) does exactly this and more.
DBeaver is one of the best tools for SQL database management. You can get the table query like
create table table_name in a very simple way in the DBeaver tool.
In pgAdmin 4, just find the table in the tree on the left, e.g.:
Servers + PostgreSQL 11 + Databases + MYDATABASENAME + Schemas + public + Tables + MYTABLENAME <-- click this tree element
When the table is selected, open the SQL tab on the right. It displays the
CREATE TABLE for the selected table.
As answered in https://serverfault.com/a/875414/333439, with the
\d <table> meta-command in
psql is possible to show the table structure in database. If you want to view the query used in meta-command, you can use the command
psql -E. As described in the manpage, the
-E switch echoes the
\d meta-commands queries. So, you can launch
psql -E, you can view the table structure with
\d <table> meta-command, and, according to
-E switch, you can view the query generated for describe the table structure