how to generate the "create table" sql statement for an existing table in postgreSQL

Question

I have a table created long ago in postgreSQL. Now i want to look at the sql statement used to create it but cannot figure it out.

Also when i do the \dS+ tablename it says table not found, but \dt+ tablename is working fine. The \dS+ lists all the table names owned by the root postgres user and doesn't show up tables that i created with my user account.

Any help is greatly appreciated.

Thanks

Answer 1

The easiest method I can think of is to install pgAdmin 3 (found here) and use it to view your database. It will automatically generate a query that will create the table in question.

Answer 2

pg_dump -t 'aschema.atable' --schema-only

More info - in the manual.