PG_DUMP producing incorrect SQL - Mailing list pgsql-novice

From
Subject PG_DUMP producing incorrect SQL
Date
Msg-id F5B98E9B38948E45995D08580C31B4330DDD3044@MX19A.corp.emc.com
Whole thread Raw
Responses Re: PG_DUMP producing incorrect SQL
List 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.

 

 

 

 

 

pgsql-novice by date:

Previous
From: Valentin Gjorgjioski
Date:
Subject: Re: remote connection problem
Next
From: "Nelson, Howard C"
Date:
Subject: Updating a chkpass column in postgres v8.1.21