Search code examples
postgresqluuid

pg_dump and load table with UUID column PostgreSQL


With a postgresql v9.5 database, I want to move a table using a sql dump pg_dump:

pg_dump --host=localhost --username=postgres --port=5432 --dbname=my_db -t ,my_table > /D:\my_table.sql

The table has a UUID identifier column:

CREATE TABLE public.my_table
(
  uuid uuid NOT NULL DEFAULT gen_random_uuid(),
  created timestamp with time zone DEFAULT now(),
  content text,
)

load .sql

This works for tables without a UUID column:

cd C:\Program Files (x86)\PostgreSQL\9.5\bin
psql --dbname=my_db --host=localhost --username=postgres -w --port=5432 -f D:\my_table.sql

But for the specific table with a UUID column, I get the following error at the position of the first UUID:

psql:D:\my_table.sql:1327: ERROR: syntax error at or near "c9ccacd8"

LINE 1: c9ccacd8-7e37-40a2-8eca-e589dbe42a59 2016-06-04 09:48:00.676...

sql file

Here are the referred lines in the my_table.sql file:

ALTER TABLE my_table OWNER TO postgres;

COPY my_table (uuid, created, content) FROM stdin;
c9ccacd8-7e37-40a2-8eca-e589dbe42a59    2016-06-04 09:48:00.676592+00   \N
40ce5512-f566-495f-b709-0b2ec45e09c6    2016-06-04 09:48:57.148606+00   \N
\.

Solution

  • You can add --inserts flag to avoid COPY problem

    pg_dump --inserts --host=localhost --username=postgres --port=5432 --dbname=my_db -t ,my_table > /D:\my_table.sql
    

    © to Vao Tsun