Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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:

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

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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