From: | Mladen Gogala <mgogala(at)vmsinfo(dot)com> |
---|---|
To: | urs(dot)rau(at)uk(dot)om(dot)org |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: how to continue after error in batch mode with psql |
Date: | 2010-05-06 12:59:33 |
Message-ID: | 4BE2BD35.7040803@vmsinfo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Urs, psql is not a programming or reporting tool. Use perl or python
instead.
Urs Rau wrote:
> I have a nightly process that pulls data over into postgresql 8.3.10
> from a progress server runing under8.x. unfortunately the progress db
> does not enforce record size/length limitations. It does not care if a
> record of 20 characters contains 21, or 100 for that matter.
>
> we have a simple pipe that taks the progress data export dump and
> imports it into postgresql
>
> su - postgres -c "cat test.csv | psql -X -q test -c \"COPY t_test FROM
> stdin WITH DELIMITER AS ',' NULL AS '?' CSV QUOTE AS '\\\"' ESCAPE AS
> '\\\"'\""
>
> If any of the fields are over-length, we do get a error message that
> tells us which row needs fixing.
>
> ERROR: value too long for type character varying(20)
> CONTEXT: COPY t_test, line 2, column t_comment_c: "'comment 3 that is
> too long'"
>
> But how do we get psql to run through and continue after an error and
> import as much as possible of the rest of the import data and give us
> error messages about all lines with errors?
>
> So I want:
> ERROR: value too long for type character varying(20)
> CONTEXT: COPY t_test, line 2, column t_comment_c: "'comment 3 that is
> too long'"
> ERROR: value too long for type character varying(20)
> CONTEXT: COPY t_test, line 4, column t_comment_c: "'comment 5 that is
> too long'"
> ERROR: value too long for type character varying(20)
> CONTEXT: COPY t_test, line 6, column t_comment_c: "'comment 7 that is
> too long'"
> ERROR: value too long for type character varying(20)
> CONTEXT: COPY t_test, line 8, column t_comment_c: "'comment 9 that is
> too long'"
>
>
> I have tried adding the following to psql, but that did not make any
> difference.
>
> "-v ON_ERROR_STOP"
> "-v ON_ERROR_STOP=0"
> "--set "ON_ERROR_STOP"
> "--set "ON_ERROR_STOP=0"
>
>
> Here is my test database definition, followed by my test.csv data:
>
> SET client_encoding = 'UTF8';
> SET standard_conforming_strings = on;
> SET check_function_bodies = false;
> SET client_min_messages = warning;
> SET search_path = public, pg_catalog;
> SET default_tablespace = '';
> SET default_with_oids = false;
> --
> -- Name: t_test; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
> --
> CREATE TABLE t_test (
> t_record_i integer DEFAULT 0 NOT NULL,
> t_comment_c character varying(20)
> );
> ALTER TABLE public.t_test OWNER TO postgres;
> --
> -- Data for Name: t_test; Type: TABLE DATA; Schema: public; Owner: postgres
> --
> INSERT INTO t_test VALUES (1, 'comment 1');
> --
> -- Name: t_table_pkey; Type: CONSTRAINT; Schema: public; Owner:
> postgres; Tablespace:
> --
> ALTER TABLE ONLY t_test
> ADD CONSTRAINT t_table_pkey PRIMARY KEY (t_record_i);
> --
> -- Name: t_test; Type: ACL; Schema: public; Owner: postgres
> --
> REVOKE ALL ON TABLE t_test FROM PUBLIC;
> REVOKE ALL ON TABLE t_test FROM postgres;
> GRANT ALL ON TABLE t_test TO postgres;
>
> and my test.csv data:
> 2,'comment 2'
> 3,'comment 3 that is too long'
> 4,'comment 4'
> 5,'comment 5 that is too long'
> 6,'comment 6'
> 7,'comment 7 that is too long'
> 8,'comment 8'
> 9,'comment 9 that is too long'
> 10,'comment 10'
>
>
> What I am hoping for is to get an error message about the rows 3,5,7 and
> 9 (and - if I am lucky all the data up to the defined length in the
> record) and definitely all the data for row 2,4,6,8 and 10 in the t_test
> table in the test database.
>
> What does it take, top get this working? I am a newbie, am I barking up
> the wrong tree with trying to unset ON_ERROR_STOP?
>
> Many thanks for any hints.
>
> Regards,
>
>
>
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com
From | Date | Subject | |
---|---|---|---|
Next Message | Siddharth Shah | 2010-05-07 08:03:08 | ERROR: XX001 (Critical and Urgent) |
Previous Message | Urs Rau (UK) | 2010-05-06 08:05:39 | Re: how to continue after error in batch mode with psql |