1

If I use pg_dump to get the schema from my database, I get separate sections for things like keys, constraints etc., like this:

CREATE TABLE public.account (
    id uuid NOT NULL,
    db_version bigint,
    email_address character varying(255) NOT NULL,
    full_name character varying(255) NOT NULL
);

....and then later....

ALTER TABLE ONLY public.account
    ADD CONSTRAINT account_pkey PRIMARY KEY (id);

This makes the entire dump incredibly verbose. How can I condense the dump so that it puts everything in the create table section?

1

Short Answer: No

The pg_dump help does not include a documented option to allow you to manipulate the output syntax in this way.

Workaround: Write it yourself

You can either loop table-by-table using pg_dump natively with the -t|--table filter...

for tbl in $(psql -D my_database -Xtc "select tablename from pg_tables where schemaname = 'my_schema';"); do
    pg_dump --dbname my_database --table "$tbl" >> "./my_database.sql"
done

...or you can use a fancier custom scripting with something like SQLAlchemy refection (as I have done on occasion)

But...

There's reasons the workaround might fail you. pg_dump is only guaranteed to produce you a consistent, portable, re-importable copy of your database. Some of the choices it makes to keep constraint definitions separate from the table are by design so that it doesn't fail to recreate the database in certain edge cases.

Any custom scripting you write is likely to be less stable than the builtin formatting, but if you prioritise the cleanliness of the plaintext, then you are free to go nuts with it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.