Is there a build-in function or a simple way to get DDL of a table in PostgreSQL 12?

Question

I want to get DDL of a table in PostgreSQL that looks like this:

CREATE TABLE public.person (
    id serial NOT NULL,
    "name" varchar(50) NOT NULL,
    age int4 NOT NULL,
    CONSTRAINT person_name_uk UNIQUE (name),
    CONSTRAINT person_pkey PRIMARY KEY (id)
);

I remember, in MySQL there is a query SHOW CREATE TABLE. Is there a similar way to get the same in PostgreSQL?

I am interested in solution for the version PostgreSQL 12.

I need a solution exactly in SQL, so I could use inside of SQL functions. Thus the pg_dump of \d+ don't fit.

Rajan Sharma · Answer 1 · 2019-10-23T13:55:10.490

1

Use pg_dump:

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

FYI

-s or --schema-only : Dump only ddl without data.
-t or --table Dump :  Dump only tables

You can try to trace in the PostgreSQL log file what pg_dump really does.You can use the same strategy.

edited Oct 23 '19 at 13:55

answered Oct 23 '19 at 13:47

Rajan Sharma

1,956
3
18
32

Thank you for your answer. But I need a solution exactly in terms of SQL, not with the help of special utilities in terminal. I will edit my question. – Fomalhaut Oct 23 '19 at 13:49
then the dump strategy used by pg_dump could be helpful to identify. – Rajan Sharma Oct 23 '19 at 13:53

score 0 · Answer 2 · answered Oct 23 '19 at 21:11

One place to go is the information_schema columns view:

https://www.postgresql.org/docs/current/infoschema-columns.html

You might find this thread relevant:

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

A zillion client tools offer to script a CREATE TABLE for you. That's another place to find relevant scripts, apart from the excellent suggestion of checking pg_dump itself.

Is there a build-in function or a simple way to get DDL of a table in PostgreSQL 12?

2 Answers2