Thu, 07 Apr 2011

SHOW CREATE TABLE in PostgreSQL

MySQL has a command called SHOW CREATE TABLE, which dumps the actual SQL that can be used to create a table. PostgreSQL doesn't have the same command, but it is possible to do. The program pg_dump can dump the database, and also creates the required SQL. If you have to do this from within psql, you can do something like:

\! pg_dump -s -t mytable

The output of this is pretty verbose, so since we're already running a subshell, why not use grep?

\! pg_dump -s -t mytable | grep -v ^-- | grep .

Now you see the required SQL, including the implicitly created objects.

If you just wanted to see the structure of the table, use

\d mytable

instead.

posted at: 16:07 | path: /2011/04 | permanent link to this entry