86

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

31

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

| improve this answer | |
  • 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
  • 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 '19 at 13:38
56

pg_dump:

pg_dump -st tablename dbname

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

| improve this answer | |
  • 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. – Joseph Lust Mar 17 '17 at 10:45
24

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

| improve this answer | |
  • 14
    This is helpful, but doesn't answer the question. – ryantuck Jul 13 '18 at 16:21
  • The question is different. – Ivailo Bardarov Apr 27 at 18:27
10

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;
| improve this answer | |
  • 1
    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
7

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.

| improve this answer | |
1

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

| improve this answer | |
  • 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. – Ivailo Bardarov Apr 27 at 18:34
1

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

| improve this answer | |
-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.

| improve this answer | |

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.