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(...);

Many ERD generators parse table definitions naively (in the way described by the OP). But without the constraints, these tools fail to add relations between tables. I think this is a fairly common use case where bulk loading of data is irrelevant since there may even be no data in the dump. — Romain Vincent, Mar 19 '21 at 13:59

score 6 · Accepted Answer · answered Dec 22 '13 at 07:15

6

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:

Define the table
Load the data
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.

answered Dec 22 '13 at 07:15

Chris Travers

25,424
6
65
182

I understand the reasons for adding indexes etc. after the data loading. Still it would be very useful to have an option to generate the ddl where the primary key is in the create table statement. – Jul 05 '17 at 06:18
Many ERD generators parse table definitions naively (in the way described by the OP). But without the constraints, these tools fail to add relations between tables. I think this is a fairly common use case where bulk loading of data is irrelevant since there may even be no data in the dump. – Romain Vincent Mar 19 '21 at 14:00

Whinger · Answer 2 · 2015-03-27T10:47:23.067

I know that this is fairly old question and in the majority case I agree with Chris that it's not something you would actually want to do (since any foreign keys will present problems unless you restore tables in the exact order); however since it's a fairly high result in a google search for the question I asked and no-one actually gave an answer to the question itself, this might be helpful with the caveat that you probably shouldn't use it unless you know what you're doing...

This bash script (I won't call it a one-liner because it's not really, although it will work if you just paste it into the command line) outputs the table list (for the public schema) from psql and runs each table separately through pg_dump, then merges the constraints into the main table definition, outputting each table result to a file named based on the tablename.

You could also SELECT from the schema tables to grab the table names rather than parsing the output from \dt; I reckon it's probably about as difficult to remember how to do one as the other.

It's what I needed for my particular application, I make no guarantees that it will work for yours (or indeed for a particular version of pg_dump), but you shouldn't have many problems modifying it. There are certainly better ways to do the second part (using awk, probably) but this was simple and did the job. I ignore everything except the table creation, constraints and ownership; if you want to add other lines then you need to add extra tests to the /OWNER TO/ line...

DB=mydb; s=1; psql -tA -F, -c '\dt public.*' $DB | cut -f2 -d, | while read t ; do 
  (
    rm -f "$t.sql2"; pg_dump -sx -t "$t" $DB | grep -v -e '^--' -e '^set' -i | while read a; do
      if [ $s -eq 1 ] ; then 
        if [ "a$a" = 'a);' ] ; then s=2 ; else echo $a ; fi; 
      elif [ $s -eq 2 ] ; then 
        b=$(echo $a | sed -e 's/\s*ADD CONSTRAINT\(.*\);/CONSTRAINT \1/')
        if [ "a$b" != "a$a" ] ; then 
          echo ",$b"
        elif [ "a$a" != "a${a/OWNER TO/}" ] ; then 
          echo $a >> "$t.sql2"
        fi
      fi
    done
    echo ");"
  ) > "$t.sql"
  if test -f "$t.sql2" ; then cat "$t.sql2" >> "$t.sql"; rm -f "$t.sql2"; fi
done

Neat solution (not that I'd recommend running it of course). Thanks for answering OP question — delano, Dec 01 '22 at 23:16

pg_dump to dump table schema with constraint as compact

2 Answers2