Postgres Error in running SQL from dump on COPY command [duplicate]

Question

I created a dump from one local database and attempted to load it on another machine. When I attempt to load the dumped database it runs the first 1000+ lines until it hits this code;

--
-- Data for Name: active_storage_attachments; Type: TABLE DATA; Schema: public; Owner: jmt
--

COPY public.active_storage_attachments (id, name, record_type, record_id, blob_id, created_at) FROM stdin;
7   screenshot  Template    1   7   2023-03-08 03:22:18.528923
\.

The error message is as follows; ERROR: syntax error at or near "7" LINE 1106: 7 screenshot Template 1 7 2023-03-08 03:22:18.528923

Line 1106 is the line with the values, starting with the number 7.

This whole file is just a dump. I tried deleting this particular line and all of the commands involve COPY and experience the same problem. How do I resolve this?

Needs more information 1) Postgres version on both ends. 2) The exact pg_dump command and the exact pg_restore or psql command. 3) Relevant messages from the Postgres log. — Adrian Klaver
– Adrian Klaver, Commented Sep 25, 2024 at 14:31

Laurenz Albe · Accepted Answer · 2024-09-25 14:29:35Z

2

You can restore such a plain-format dump only with the psql command line client. Other PostgreSQL clients will gag on the data mixed with the COPY statement.

If you want to restore a pg_dump with a different PostgreSQL client, you have to take the dump with the option --inserts.

answered Sep 25, 2024 at 14:29

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Error in running SQL from dump on COPY command [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related