CODEWITHSUNDEEP
postgresqlddl
ruipacheco
ruipacheco

Reputation: 16402

Generate DDL programmatically on Postgresql

How can I generate the DDL of a table programmatically on Postgresql? Is there a system query or command to do it? Googling the issue returned no pointers.

Upvotes: 51

Views: 116007

Answers (6)

Brain90
Brain90

Reputation: 1694

Use pg_dump with this options:

pg_dump -U user_name -h host database -s -t table_or_view_names -f table_or_view_names.sql

Description:

-s or --schema-only : Dump only ddl / the object definitions (schema), without data.
-t or --table Dump :  Dump only tables (or views or sequences) matching table

Examples:

-- dump each ddl table elon build.
$ pg_dump -U elon -h localhost -s -t spacex -t tesla -t solarcity -t boring > companies.sql

Upvotes: 60

Vao Tsun
Vao Tsun

Reputation: 51406

I saved 4 functions to mock up pg_dump -s behaviour partially. Based on \d+ metacommand. The usage would be smth alike:

\pset format unaligned
select get_ddl_t(schemaname,tablename) as "--" from pg_tables where tableowner <> 'postgres';

Of course you have to create functions prior.

Working sample here at rextester

Upvotes: 5

iavci
iavci

Reputation: 209

Here is a good article on how to get the meta information from information schema, http://www.alberton.info/postgresql_meta_info.html.

Upvotes: 5

Wayne Conrad
Wayne Conrad

Reputation: 107959

Why would shelling out to psql not count as "programmatically?" It'll dump the entire schema very nicely.

Anyhow, you can get data types (and much more) from the information_schema (8.4 docs referenced here, but this is not a new feature):

=# select column_name, data_type from information_schema.columns
-# where table_name = 'config';
    column_name     | data_type 
--------------------+-----------
 id                 | integer
 default_printer_id | integer
 master_host_enable | boolean
(3 rows)

Upvotes: 6

ruipacheco
ruipacheco

Reputation: 16402

The answer is to check the source code for pg_dump and follow the switches it uses to generate the DDL. Somewhere inside the code there's a number of queries used to retrieve the metadata used to generate the DDL.

Upvotes: 4

Greg Hewgill
Greg Hewgill

Reputation: 992707

You can use the pg_dump command to dump the contents of the database (both schema and data). The --schema-only switch will dump only the DDL for your table(s).

Upvotes: 27

Related Questions