Take the 2-minute tour ×

Is there a MySQL equivalent of 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).

share|improve this question

3 Answers 3

up vote 13 down vote accepted

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.

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


pg_dump -st tablename dbname

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

share|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
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
@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

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.

share|improve this answer

Your Answer


