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


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.

  • 1
    ok, i traced the pg_dump. But it wasn't as easy, becouse i had to write my own function in C# to write the create table statement. But it was a big help, so thank you veeeeerrrryyyy much. :D
    – vlebar
    Feb 9 '11 at 7:41
  • 1
    No need to go to all that trouble - according to here you can use the -E option to trace what's going on in the background when you run a psql command - -E: will describe the underlaying queries of the \ commands (cool for learning!) - HTH! Oct 4 '19 at 13:38


pg_dump -st tablename dbname

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

  • i'll try to explain the problem in more detail. I need the sql statement (select or stored procedure) becouse I execute the sql command in C# (NpgsqlCommand). So I think the pg_dump is not the solution in this case. :(
    – vlebar
    Feb 6 '11 at 22:00
  • 2
    Why not? --schema-only has this exact purpose: Show the SQL statements to create the schema/table. You can than feed this output into your C# program somehow.
    – Sven
    Feb 6 '11 at 22:10
  • look at the information_schema views: postgresql.org/docs/9.0/interactive/information-schema.html
    – alvosu
    Feb 6 '11 at 22:12
  • 1
    @SvenW: I would do that if i'd have a known set of tables. The problem is that the user can, if he chooses to, sync any table in selected database and then create the selected table on a remote server behind a wcf service. I can do that with mssql and now i want to expand the client program to other sql servers (mysql, oracle, postgresql, ...) OK, I can do pg_dump for a table that the user wants to sync, but if it is at all possible to do that just in plsql, i want to do it in plsql
    – vlebar
    Feb 6 '11 at 22:36
  • 1
    If dumping to .tar, grab the restore.sql file from the archive. It has all the create statements. Mar 17 '17 at 10:45

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

  • 25
    This is helpful, but doesn't answer the question.
    – ryantuck
    Jul 13 '18 at 16:21
  • The question is different. Apr 27 '20 at 18:27
  • 3
    Does not answer the question, and can't understand why so many people upvoted this
    – Farley
    Dec 8 '20 at 2:01
  • 1
    Because it's helpful. :) +1 Apr 27 at 18:44

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 
SELECT 'CREATE TABLE ' || $1 || ' (' || $2 || '' || 
    string_agg(column_list.column_expr, ', ' || $2 || '') || 
    '' || $2 || ');'
  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;
  • 5
    This should be the accepted answer
    – exebook
    Jan 7 '19 at 9:25
  • Thank you for this!
    – frank
    Jun 13 '19 at 4:16
  • EoghanM, that is excellent. Thanks!
    – Eric M
    Nov 30 '19 at 20:22

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:

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

  • 1
    I can see the SQL commands used to display the output of the "\d table", but I can't see the SQL statement used to create the table. Apr 27 '20 at 18:34

In pgAdmin 4, just find the table in the tree on the left, e.g.:

+ PostgreSQL 11
  + Databases
      + 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.


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.