Migrate data with INSERT INTO for POSTGRESQL with constraints violated

Question

Assume I have two tables, oldstatistics and statistics, the newer table has a different constraint on the time column.

For example the newer table has the following contraint and the old table has data which violates:

CONSTRAINT check_ts_2013_03 CHECK (statistictime >= '2013-03-01 01:00:00+01'::timestamp with time zone AND statistictime < '2013-04-01 02:00:00+02'::timestamp with time zone)

and data that violates let's say they have statistictime '2013-04-01 00:15:00+01'

I found some solution for SQLite with INSERT OR IGNORE (if a constraint violation occurs the rows will be skipped, and it will continue) , but not for POSTGRESQL.

Do you have any suggestions?

and possible duplicate of [Is there a way to set an option that will cause a PostgreSQL script to continue even if there are errors?](http://stackoverflow.com/q/14908451/1288184) :) — Simo Kivistö, Mar 19 '15 at 09:58
@SimoKivistö The answer specified in your linked question is using psql, but this is not my case, as I'm using psycopg2 module with python. — valentin_nasta, Mar 19 '15 at 10:01
@valentin_nasta You can use `DO` to run PL/pgSQL in ordinary SQL: http://www.postgresql.org/docs/9.4/static/sql-do.html or create a stored function. — Jens Mühlenhoff, Mar 19 '15 at 10:03
Sorry I confused `psql` with `pgsql` ..., but still you can solve your problem with pgSQL as it supports row by row processing (procedural statements) — Jens Mühlenhoff, Mar 19 '15 at 10:05

Tom-db · Accepted Answer · 2015-03-19T11:11:34.283

You can do this with a plpgsql function and a EXCEPTION block (see documentation):

CREATE or REPLACE FUNCTION insert_or_ignore() RETURNS VOID AS $$
DECLARE _value timestamp;

BEGIN
  FOR _value IN SELECT statistictime FROM oldstatistics LOOP   
    BEGIN
      INSERT INTO statistics(statistictime) VALUES (_value);
    EXCEPTION when check_violation THEN
       -- DO NOTHING
    END;
  END LOOP;
END;
$$
LANGUAGE plpgsql;

-- run the function
SELECT insert_or_ignore();

You probably need to insert also other columns, not only statistictime. In this case you must declare them in the DECLARE block. You can also use the DO statement to do the same thing without create a function first (see documentation:

DO $$
DECLARE _value timestamp;    
BEGIN
      FOR _value IN SELECT statistictime FROM oldstatistics LOOP   
        BEGIN
          INSERT INTO statistics(statistictime) VALUES (_value);
        EXCEPTION when check_violation THEN
           -- DO NOTHING
        END;
      END LOOP;
END$$;

I'm catching here the "check_violation" exception, but check out also all PostgreSQL error codes, for the case you want to catch other kind of errors: error codes

ERROR: new row for relation "statistics_2013_03" violates check constraint "check_ts_2013_03" SQL state: 23514 Indeed is check_violation. The problem is that it goes on EXCEPTION branch at the first fail. But I want to continue the inserting and ignore, those ones that don't fit. — valentin_nasta, Mar 19 '15 at 10:18

score 0 · Answer 2 · answered Mar 19 '15 at 09:44

0

What do you want to do with those datas, wanna merge them ?

For using both tables I recommend to use a view.
If you want to insert oldStatisticTime into statisticTime without modifying the data, you can remove the constraint to a trigger, unable the trigger, insert the data and then enable the trigger.

answered Mar 19 '15 at 09:44

jvDx

41
6

I want to migrate data, from the old table to the newer one with select into, but I can't because of the constraint. I don't have any trigger in place. Should I add one? – valentin_nasta Mar 19 '15 at 09:50
A constraint on a table consist of a table saying 'those data are is this shape'; while a trigger (on insert ei) says 'all new data will fit these condition(s)' That's why I'd recommend to add a trigger instead of a constraint if you keep the data the same way. Especially if your other option is to bypass your own constraint – jvDx Mar 19 '15 at 10:31
I understand, but unfortunately I'm not allowed to remove the constraint. Will look more for some solution. I might iterate on the rows and figure it out on which partition should the data fit. – valentin_nasta Mar 19 '15 at 10:50

Migrate data with INSERT INTO for POSTGRESQL with constraints violated

2 Answers2