Is there an equivalent of MySQL's SHOW CREATE TABLE in Postgres?

Question

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

Answer 1

pg_dump:

pg_dump -st tablename dbname

or use PostgreSQL GUI Tools(pgAdmin,phpPgAdmin,etc.)

Answer 2

You can try to trace in the PostgreSQL log file what pg_dump --table table --schema-only really does. Then you can use the same method to write your own sql function.

Answer 3

In command line (psql) you can run: \d <table name> to list all columns, their types and indexes.

Answer 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;

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

Answer 6

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.

• Right-click on the table name.
• Then, Click on Generate SQL and then DDL DDLimage

Answer 7

Postgres extension ddlx (https://github.com/lacanoid/pgddl) does exactly this and more.

Answer 8

Better

pg_dump -U <user> -h <host> -st <tablename> <db name>

Answer 9

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.

Answer 10

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