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