CODEWITHSUNDEEP
postgresql
Fomalhaut
Fomalhaut

Reputation: 9727

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

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.

Upvotes: 1

Views: 3330

Answers (2)

Morris de Oryx
Morris de Oryx

Reputation: 2183

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.

Upvotes: 0

Rajan Sharma
Rajan Sharma

Reputation: 2273

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.

Upvotes: 1

Related Questions