1

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?

Improve this question
4

2 Answers 2

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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.
Ops, sorry. I changed my answer, check again.
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.
Improve this answer

3 Comments

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?
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
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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.