Re: how to continue after error in batch mode with psql - Mailing list pgsql-novice
From | Mladen Gogala |
---|---|
Subject | Re: how to continue after error in batch mode with psql |
Date | |
Msg-id | 4BE2BD35.7040803@vmsinfo.com Whole thread Raw |
In response to | how to continue after error in batch mode with psql (Urs Rau <urs.rau@uk.om.org>) |
List | 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
pgsql-novice by date: