Generate DDL programmatically on Postgresql

Question

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.

Brain90 · Answer 1 · 2021-07-13T01:08:59.047

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

Sorry if out of topic. Just wanna help who googling "psql dump ddl" and got this thread.

score 25 · Answer 2 · answered Dec 10 '09 at 23:30

25

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

answered Dec 10 '09 at 23:30

Greg Hewgill

951,095
183
1,149
1,285

10

I don't quite understand your comment. `pg_dump` is installed with PostgreSQL and is available on all servers. No special user privileges are required to use it (normal PostgreSQL access controls apply to the database you're dumping). If you don't consider this to be a programmatic solution, you will have to specify what programming environment you *are* using in order to get an appropriate answer. – Greg Hewgill Dec 11 '09 at 20:47

score 4 · Answer 3 · answered Jan 27 '10 at 12:55

4

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)

answered Jan 27 '10 at 12:55

Wayne Conrad

103,207
26
155
191

As I said above, most of my users won't have access to pg_dump or a PostgreSQL server and so I must reproduce the behaviour of pg_dump. – ruipacheco Jan 29 '10 at 12:48
Right. So *your code* queries the information schema (or shells out to psql), parses the results, and shows the result to your users. – Wayne Conrad Jan 29 '10 at 13:40

score 3 · Accepted Answer · answered Jan 27 '10 at 11:23

3

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.

answered Jan 27 '10 at 11:23

ruipacheco

15,025
19
82
138

score 3 · Answer 5 · answered Aug 09 '10 at 08:35

3

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

answered Aug 09 '10 at 08:35

iavci

189
1
4

2

This article may be useful, but you should post the relevant bits here; links often go dead. – Brad Koch May 15 '14 at 16:19

score 3 · Answer 6 · answered Mar 06 '17 at 16:15

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

Generate DDL programmatically on Postgresql

6 Answers6

Linked

Related