Postgresql, running sql and get error: COPY from stdin failed

Question

Postgresql 12 using pgAdmin4, menu Tools/Backup/Format-Plain to backup a db to a sql file like this:

--
-- PostgreSQL database dump
--
-- Dumped from database version 12.3
-- Dumped by pg_dump version 12.3
-- Started on 2020-08-06 17:38:54
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- TOC entry 202 (class 1259 OID 17973)
-- Name: customers; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.customers (
    "CustomerID" character varying(32) NOT NULL,
    "Name" character varying(128),
    "AddressLine" character varying(128)
);
ALTER TABLE public.customers OWNER TO postgres;
--
-- TOC entry 3232 (class 0 OID 17973)
-- Dependencies: 202
-- Data for Name: customers; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.customers ("CustomerID", "Name", "AddressLine") FROM stdin;
\.
-- Completed on 2020-08-06 17:38:54
--
-- PostgreSQL database dump complete
--

Create a new empty db and try to run above sql file to create schema and data. Error happens on the backslash under "COPY": pgAdmin got error:

ERROR:  syntax error at or near "\"
LINE 45: \.
         ^
SQL state: 42601
Character: 1092

How could I correctly run the sql file to restore schema and data?

CREATE TABLE has "AddressLine" but the COPY has "AddressLine1" -- sounds like you pasted wrong? — richyen
– richyen, Commented Sep 2, 2020 at 5:12

Laurenz Albe · Accepted Answer · 2020-09-02 05:19:54Z

4

You must restore a plain format pg_dump with psql. pgAdmin or other clients juat won't do, unless you used the --inserts option of pg_dump.

The reason is that other clients cannot handle the mixture of SQL statements and COPY data in a single input file.

answered Sep 2, 2020 at 5:19

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

brewphone Over a year ago

Thank you Laurenz. Works well with psql and the db was restored.

Collectives™ on Stack Overflow

Postgresql, running sql and get error: COPY from stdin failed

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related