1

I have this simple setup using PostgreSQL 13, latest dockerized:

    CREATE TABLE stop (
      id INT UNIQUE,
      name TEXT,
      PRIMARY KEY(id)
    );

    CREATE TABLE line (
      id INT UNIQUE,
      name TEXT,
      PRIMARY KEY(id)
    );

    CREATE TABLE line_stops_sequence(
      id SERIAL PRIMARY KEY,
      line INT,
      stop INT,
      CONSTRAINT fk_line FOREIGN KEY(line) REFERENCES line(id),
      CONSTRAINT fk_stop FOREIGN KEY(stop) REFERENCES stop(id)
    );

When I launch the following sentence

INSERT INTO line(id, name) VALUES (1, 'Line 1');
INSERT INTO stop(id, name) VALUES (2, 'Stop 2');
INSERT INTO line_stops_sequence(line, stop) VALUES (1,1), (1,2) ON CONFLICT ON CONSTRAINT fk_stop DO NOTHING;

I get constraint in ON CONFLICT clause has no associated index;

I know I'm inserting an stop id that is not included in the stop table. The idea is that I want to ignore the INSERTs that have conflicts. In the example it is just two items, but I'm inserting bulk data.

How can I do it without having to manually check the result of the query? Is there anyway to do it with the SQL sentence?

New contributor
jlanza is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
3
  • 1
    Whoever's voting to close this: guys, the OP is misusing the ON CONFLICT option, yes, but they are not asking how to make the DB change its mind and accept the specific command the OP is feeding it. The questions at the end of the post are very clearly not about that: "How can I do it without having to manually check the result of the query? Is there anyway to do it with the SQL sentence?" – Andriy M Jun 10 at 8:17
  • Thanks a lot. I misunderstood the usage of ON CONFLICT in combination with ON CONSTRAINT. I will have to split the insert sentence :( – jlanza Jun 10 at 9:02
  • Why would anyone want to close this decent question? This almost feels like vandalism. Sure, the problem is obvious as soon as you know it. But that's the case for most questions. And an alternative may not be so obvious - and useful to the general public. Also, leading with a minimal reproducible setup is a big plus to begin with. My only gripe is the missing Postgres version. – Erwin Brandstetter Jun 10 at 13:22
3

The ON CONFLICT clause is strictly for handling unique violation and exclusion constraint violation errors, as documented in the manual. There is no similar syntax allowing you to handle foreign key violation errors.

You can ignore invalid references by incorporating checks into your insert statement, although you will need to switch from INSERT...VALUES to INSERT...SELECT to be able to do that. Here is one way:

INSERT INTO
  line_stops_sequence(line, stop)
SELECT
  new.line, new.stop
FROM
  (VALUES (1,1), (1,2)) AS new (line, stop)
WHERE
  new.line IN (SELECT id FROM line)
  AND
  new.stop IN (SELECT id FROM stop)
;

Your Answer

jlanza is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.