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
\.
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