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