Sooner or later you need the CREATE TABLE statement for a table that already exists. Maybe you want to copy it to another database, put it in version control, or just see how someone defined it.
In MySQL you’d type SHOW CREATE TABLE orders; and you’d be done. PostgreSQL has no such command, and there’s no built-in pg_get_tabledef() function either.
You still have several good options, though. This guide walks through each one with examples, from the most complete to the quickest.
Table of Contents
The Example Table
We’ll use one table with a bit of everything in it, so you can see which method captures what:
CREATE TABLE customers (
id bigint PRIMARY KEY
);
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers (id),
status text NOT NULL DEFAULT 'new',
total numeric(10,2) CHECK (total >= 0),
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
COMMENT ON TABLE orders IS 'Customer orders';
That gives us an identity column, defaults, a CHECK constraint, a foreign key, an extra index, and a comment.
Method 1: pg_dump (The Complete Answer)
pg_dump is the tool PostgreSQL itself uses for backups. If you ask it for just the schema of one table, you get exactly the DDL you need:
pg_dump -d shop --schema-only --table=public.orders
What the options mean:
--schema-only(or-s): structure only, no data rows.--table=public.orders(or-t): only this table.
The output, trimmed a little:
CREATE TABLE public.orders (
id bigint NOT NULL,
customer_id bigint NOT NULL,
status text DEFAULT 'new'::text NOT NULL,
total numeric(10,2),
created_at timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT orders_total_check CHECK ((total >= (0)::numeric))
);
ALTER TABLE public.orders ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
SEQUENCE NAME public.orders_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);
ALTER TABLE ONLY public.orders
ADD CONSTRAINT orders_pkey PRIMARY KEY (id);
CREATE INDEX orders_customer_id_idx ON public.orders USING btree (customer_id);
ALTER TABLE ONLY public.orders
ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES public.customers(id);
COMMENT ON TABLE public.orders IS 'Customer orders';
Everything is there: columns, defaults, the identity column, constraints, indexes, the foreign key, and the comment. Ownership, grants, and triggers are included too if the table has them.
It may look different from what you originally typed. pg_dump splits the primary key and foreign key into separate ALTER TABLE statements. It does that so tables can be restored in any order. The result is the same.
See also: Mastering the Linux Command Line — Your Complete Linux Training Course
Useful Variations
Save the DDL to a file:
pg_dump -d shop -s -t public.orders > orders_ddl.sql
Leave out owner and permission lines, which is handy when you’re copying to a database with different users:
pg_dump -d shop -s -t public.orders --no-owner --no-privileges
Dump several tables with a pattern:
pg_dump -d shop -s -t 'public.order*'
Connect to a remote server:
pg_dump -h db.example.com -U postgres -d shop -s -t public.orders
If a table name has capital letters, you need double quotes inside single quotes:
pg_dump -d shop -s -t '"public"."OrderItems"'
Two Things to Watch Out For
1. The table you depend on isn’t included. pg_dump -t orders adds the foreign key to customers but doesn’t dump customers itself. If you run the file on an empty database, the foreign key line fails. Either dump both tables or create customers first.
2. Version mismatch. pg_dump won’t dump from a server with a newer major version than itself. If you see an error like server version mismatch, use a pg_dump from the same or a newer major version as your server.
Method 2: psql \d+ (Quick Look, Not Real DDL)
If you just want to see how a table is defined, without copying the SQL anywhere:
\d+ orders
Table "public.orders"
Column | Type | Nullable | Default | ...
-------------+--------------------------+----------+------------------------------+----
id | bigint | not null | generated always as identity |
customer_id | bigint | not null | |
status | text | not null | 'new'::text |
total | numeric(10,2) | | |
created_at | timestamp with time zone | not null | now() |
Indexes:
"orders_pkey" PRIMARY KEY, btree (id)
"orders_customer_id_idx" btree (customer_id)
Check constraints:
"orders_total_check" CHECK (total >= 0::numeric)
Foreign-key constraints:
"orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id)
This gives you the full picture at a glance. But it’s a description, not SQL. You can’t run it. When you need a script you can execute, use pg_dump.
Method 3: Build DDL with SQL Functions
Sometimes you can’t use pg_dump. Maybe you only have a SQL connection through a GUI or an app, or you’re on a managed service with no shell access.
PostgreSQL doesn’t have one function for the whole table, but it does have functions for each piece. You can put them together yourself.
Columns:
SELECT 'CREATE TABLE public.orders (' || E'\n' ||
string_agg(
' ' || quote_ident(a.attname) || ' ' ||
format_type(a.atttypid, a.atttypmod) ||
CASE WHEN a.attnotnull THEN ' NOT NULL' ELSE '' END ||
COALESCE(' DEFAULT ' || pg_get_expr(d.adbin, d.adrelid), ''),
E',\n' ORDER BY a.attnum
) || E'\n);' AS ddl
FROM pg_attribute a
LEFT JOIN pg_attrdef d
ON d.adrelid = a.attrelid AND d.adnum = a.attnum
WHERE a.attrelid = 'public.orders'::regclass
AND a.attnum > 0
AND NOT a.attisdropped;
CREATE TABLE public.orders (
id bigint NOT NULL,
customer_id bigint NOT NULL,
status text NOT NULL DEFAULT 'new'::text,
total numeric(10,2),
created_at timestamp with time zone NOT NULL DEFAULT now()
);
Constraints (primary key, unique, check, foreign key, exclusion):
SELECT 'ALTER TABLE public.orders ADD CONSTRAINT ' ||
quote_ident(conname) || ' ' || pg_get_constraintdef(oid) || ';'
FROM pg_constraint
WHERE conrelid = 'public.orders'::regclass
AND contype IN ('p', 'u', 'c', 'f', 'x');
ALTER TABLE public.orders ADD CONSTRAINT orders_pkey PRIMARY KEY (id);
ALTER TABLE public.orders ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customers(id);
ALTER TABLE public.orders ADD CONSTRAINT orders_total_check CHECK ((total >= (0)::numeric));
Indexes (skipping the ones that constraints create automatically):
SELECT pg_get_indexdef(x.indexrelid) || ';'
FROM pg_index x
WHERE x.indrelid = 'public.orders'::regclass
AND NOT EXISTS (
SELECT 1 FROM pg_constraint c
WHERE c.conindid = x.indexrelid
AND c.conrelid = x.indrelid
);
CREATE INDEX orders_customer_id_idx ON public.orders USING btree (customer_id);
This approach is good enough for simple tables, but it’s not complete. Look at the column output above: the id column lost its GENERATED ALWAYS AS IDENTITY. It also misses generated columns, partitioning, triggers, storage settings, and comments. If a table uses any of those, use pg_dump.
Bonus: Views and Functions Have Built-In Helpers
Tables don’t have a DDL function, but views and functions do:
SELECT pg_get_viewdef('public.active_orders', true);
SELECT pg_get_functiondef('public.calc_total(bigint)'::regprocedure);
Method 4: GUI Tools
If you use a graphical client, the DDL is usually a right-click away:
- pgAdmin: select the table and open the SQL tab, or right-click → Scripts → CREATE Script.
- DBeaver: right-click the table → Generate SQL → DDL.
- DataGrip: right-click the table → SQL Scripts → Generate DDL to Clipboard.
These tools build the DDL from the system catalogs, so the output looks a lot like pg_dump‘s. Menu names change between versions, so look around if yours is slightly different.
Which Method Should You Use?
| You want to… | Use |
|---|---|
| Get complete, runnable DDL | pg_dump -s -t |
| Quickly see how a table is defined | \d+ tablename |
| Get DDL with only a SQL connection | Catalog functions (Method 3) |
| Click instead of type | pgAdmin, DBeaver, or DataGrip |
Summary
PostgreSQL has no SHOW CREATE TABLE, but pg_dump --schema-only --table=... does the same job and does it more completely. It includes indexes, constraints, comments, and permissions too. Use \d+ when you just want to look. Build DDL from catalog functions only when you can’t run pg_dump, and remember that this method skips identity columns and a few other details.
