pg_dump to dump table schema with constraint as compact

Question

My pg_dump returning the constraints are in alter query at the end of the dump file. I want those to be inside the create table (...) section.

For example. I have created a table as below with a constraint:

CREATE TABLE "test_constraints" (
    "id" serial NOT NULL,
    "user_id" bigint NOT NULL,
    PRIMARY KEY ("id"),
    CONSTRAINT "user_id" UNIQUE ("user_id")
);

And taking the schema dump using the following command:

pg_dump.exe -U postgres -t "test_constraints" -f "D:\dump.sql" "postgres"

Is it possible to have the table schema as it is(or near to that) when I have created it? I mean the constraints need to be inside the create table(...);

Answer 1

No, it is not possible. Things are done this way for a reason, actually.

The problem is that a dump is usually intended to be bulk loaded, and so you want to create indexes after the loading of the data. This means, effectively, you want to do this in three steps:

1. Define the table
2. Load the data
3. create the indexes

Now the problem is that certain constraints are internally handled through indexes and so they need to be created in stage 3. I.e. you want to create primary keys and unique constraints after the bulk load, for performance reasons.