PG_DUMP producing incorrect SQL

From: <lindsay(dot)braine(at)emc(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: PG_DUMP producing incorrect SQL
Date: 2012-05-17 17:33:37
Message-ID: F5B98E9B38948E45995D08580C31B4330DDD3044@MX19A.corp.emc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I am sure I am doing something wrong - because I cannot find anything about this on Google or in the Community Archives - but here goes:

I am running postgres-9.1.3
I have one database, two schemas.

When using pg_dump -format plain to create an export file, the result is a file that contains SQL errors, preventing psql from parsing and executing.

Here is the problem:

Here is the ouput for one of my tables from pg_dump :

--
-- Name: attributevalue; Type: TABLE; Schema: SATURN; Owner: SATURNuser; Tablespace:
--
CREATE TABLE attributevalue (
dtype character varying(31) NOT NULL,
f_id uuid NOT NULL,
f_lastmodified timestamp without time zone NOT NULL,
f_credentials_id uuid,
f_value double precision,
f_flag_value boolean,
f_int_value integer,
f_family character varying(255),
f_processor_type character varying(255),
f_version character varying(255),
f_string_value character varying(255)
);

--
-- Data for Name: attributevalue; Type: TABLE DATA; Schema: SATURN; Owner: SATURNuser
--
COPY attributevalue (dtype, f_id, f_lastmodified, f_credentials_id, f_value, f_flag_value, f_int_value, f_family, f_processor_type, f_version, f_string_value) FROM stdin;
OSAttributeValue fdf0dba2-7683-421e-a58a-1478be78edcc 2012-05-17 09:30:04.565 \N \N \N \N WINDOWS amd64 5.2 \N
OSAttributeValue 26d4f4f9-0d97-4532-9f88-acd31d6e2474 2012-05-17 09:30:45.315 \N \N \N \N WINDOWS \N \N \N
\.

Note the presence of the default \N placeholder used for empty/null values.
I have another table

--
-- Name: config_sysvars; Type: TABLE; Schema: XXX; Owner: SATURNuser; Tablespace:
--
CREATE TABLE config_sysvars (
f_id uuid NOT NULL,
f_lastmodified timestamp without time zone NOT NULL,
f_name character varying(255),
f_type character varying(255),
f_value character varying(255)
);

--
-- Data for Name: config_sysvars; Type: TABLE DATA; Schema: XXX; Owner: SATURNuser
--
COPY config_sysvars (f_id, f_lastmodified, f_name, f_type, f_value) FROM stdin;
9fbfb915-c42e-4b5e-93ca-6a23abc0a661 2012-05-17 09:27:54.284 schemaVersion String 6.0.0
14c3bf58-e230-4353-9500-46fcb9632d7f 2012-05-17 09:29:52.909 mailHost String
a1c7824a-ef9c-4546-871a-637748407b5e 2012-05-17 09:29:52.924 mailFrom String
e8c338d4-be1f-4ad7-830b-905f7942223d 2012-05-17 09:30:04.612 XXXServerHostUUID UUID 1929b62f-e150-4ea2-aee4-35f8443bc368
\.

Note the missing \N after the mainHost String and mailFrom String.
This causes the COPY statement to ERROR with :

ERROR: missing data for column "f_value"
CONTEXT: COPY config_sysvars, line 2: "1649fe21-e806-4078-ab14-e7cf045c9265 2012-05-16 17:12:23.105 mailHost String"

What am I doing wrong/misunderstood about using pg_dump.

All advice gratefully received.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Nelson, Howard C 2012-05-17 17:44:48 Updating a chkpass column in postgres v8.1.21
Previous Message Valentin Gjorgjioski 2012-05-17 10:46:45 Re: remote connection problem