140

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

1
  • Are you looking for a single SQL query statement that would generate a CREATE statement? It can not be done in PostgreSQL. In MySQL, that would work because everything can be defined in a single CREATE statement. In PostgreSQL, that would not work because parts are managed as different things. For example, the AUTO_INCREMENT of MySQL is managed within its table while the counterpart SEQUENCE of PostgreSQL is managed independently. Thus, it would require multiple query statements to fully mimic a single PostgreSQL table. Unlike in MySQL, a single CREATE query can work by itself. Jan 26, 2022 at 0:28

10 Answers 10

57

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.

2
  • 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, 2011 at 7:41
  • 2
    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!
    – Vérace
    Oct 4, 2019 at 13:38
77

pg_dump:

pg_dump -st tablename dbname

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

5
  • 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, 2011 at 22:00
  • 3
    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, 2011 at 22:10
  • look at the information_schema views: postgresql.org/docs/9.0/interactive/information-schema.html
    – alvosu
    Feb 6, 2011 at 22:12
  • 2
    @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, 2011 at 22:36
  • 1
    If dumping to .tar, grab the restore.sql file from the archive. It has all the create statements. Mar 17, 2017 at 10:45
42

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

5
  • 30
    This is helpful, but doesn't answer the question.
    – ryantuck
    Jul 13, 2018 at 16:21
  • The question is different. Apr 27, 2020 at 18:27
  • 4
    Does not answer the question, and can't understand why so many people upvoted this
    – Farley
    Dec 8, 2020 at 2:01
  • 5
    Because it's helpful. :) +1 Apr 27, 2021 at 18:44
  • Answers only have to be useful, they don't have to answer the exact question. This is exactly what I was after - I don't need the exact create table statement, just a simple check within the CLI that will show me if the expected change I just applied to the table worked. I don't want the overhead of pg_dump.
    – icc97
    Nov 8, 2023 at 16:12
18

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;
1
  • You might be interested in this - only a slight mod to your code means that the DDL can be copied and pasted from psql without worrying about those pesky + signs at the end? HTH...
    – Vérace
    Dec 15, 2021 at 13:04
12

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.

3

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

3

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
1
  • Interestingly, going to the Properties > DDL sub-tab for the table is giving me SQL that is a bit more compact (e.g. it has a single CREATE TABLE containing all constraints whereas Generate SQL > DDL is giving me CREATE TABLE containing all but one of them plus an additional ALTER TABLE to add the last one.
    – Michael
    Oct 2, 2023 at 9:41
2

Better

pg_dump -U <user> -h <host> -st <tablename> <db name>
1
  • Variation that will also leave out SQL COMMENT statements for brevity, and filter out -- comment lines and duplicate empty lines: pg_dump -h <host> -p <port> -d <db name> -U <user> -t <tablename> --schema-only --no-comments | sed -e '/^--/d' | sed -e '/^$/N;/^\n$/D'
    – tanius
    Feb 17, 2023 at 4:33
1

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.

0

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
  • 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, 2020 at 18:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .