Re: how to continue after error in batch mode with psql

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