CODEWITHSUNDEEP
postgresql
ManInMoon
ManInMoon

Reputation: 7005

Postgres ignore insert error and carry on

I have a PostgeresDB with the following constraint:

  CONSTRAINT "Car_Data_3PM_pkey" PRIMARY KEY ("F_ID", "Date"),
  CONSTRAINT "Car_Data_3PM_F_ID_fkey" FOREIGN KEY ("F_ID")
      REFERENCES "Bike_Data" ("F_ID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION

When I try to insert multiple values using:

INSERT INTO "Car_Data_3PM" ("F_ID","Date","Price_Type","O","H","L","LT","EQ","V","NAD") VALUES (38,'2016-10-02 08:19:40.056679','x',0,0,0,112.145,0,0,112.145),(14,'2016-10-02 08:19:40.056679','x',0,0,0,5476,0,0,5476),(13,'2016-10-02

I get this error:

ERROR: insert or update on table "Car_Data_3PM" violates foreign key constraint "Car_Data_3PM_F_ID_fkey" SQL state: 23503 Detail: Key (F_ID)=(38) is not present in table "Bike_Data".

NO ROWS are inserted.

How can I make Postgres ONLY miss out the rows where the constraint is an issue? i.e Insert most of them?

Upvotes: 2

Views: 3500

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246413

One way is to write a trigger that filters out the bad values, like this:

CREATE FUNCTION car_insert_filter() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF EXISTS(SELECT 1 FROM "Bike_Data" WHERE "F_ID" = NEW."F_ID")
   THEN
      RETURN NEW;
   ELSE
      RAISE NOTICE 'Skipping row with "F_ID"=% and "Date"=%',
         NEW."F_ID", NEW."Date";
      RETURN NULL;
   END IF;
END;$$;

CREATE TRIGGER car_insert_filter
   BEFORE INSERT ON "Car_Data_3PM" FOR EACH ROW
   EXECUTE PROCEDURE car_insert_filter();

Upvotes: 3

user330315
user330315

Reputation:

You can't make Postgres ignore the values, but you can rewrite your statement to not insert those rows:

INSERT INTO "Car_Data_3PM" ("F_ID","Date","Price_Type","O","H","L","LT","EQ","V","NAD") 
select *
from (
   VALUES
     (38,'2016-10-02 08:19:40.056679','x',0,0,0,112.145,0,0,112.145),
     (14,'2016-10-02 08:19:40.056679','x',0,0,0,5476,0,0,5476),
     ... -- all other rows
) as x (id, date, price_type, o, h, l, lt, eq, v nad)
where exists (select 1 
              from "Bike_Data" bd
              where bd."F_ID" = x .id)

Upvotes: 3

Related Questions