0

I have this SQL query which I need to run on different databases. In some databases it's missing. In other this record is present.

INSERT INTO common.pair_aliases (pair_id, alias)
VALUES (356, 'parisrome');

Is it possible to implement a logic first to check is this record present before executing the SQL query?

user1285928
  • 1,328
  • 29
  • 98
  • 147
  • Could you not do a INSERT SELECT? – JamesS Jan 27 '22 at 09:42
  • 2
    There are a *lot* of similar questions. The pure SQL way is to add a `WHERE NOT EXISTS` clause. The vendor-specific way is to use the `ON CONFLICT DO NOTHING` clause to ignore the operation if the PK value already exists – Panagiotis Kanavos Jan 27 '22 at 09:45
  • Does `different databases` mean different database products? That would be a different question - is there a cross-database way to INSERT if a row doesn't exist? The answer would be `kind of`, as different products, even different versions, provide different levels of SQL support. MySQL for example has the nasty habit of adding significant features in point releases. For example, ROW constructors, which are relevant to your question, were added in 8.0.19 – Panagiotis Kanavos Jan 27 '22 at 09:59

1 Answers1

1

If I understand you correctly, maybe WHERE NOT EXISTS could help you:

INSERT INTO common.pair_aliases (pair_id, alias)
SELECT R.*
FROM (VALUES (356, 'pairsrome')) R (pair_id, alias)
WHERE NOT EXISTS (SELECT 1 FROM common.pair_aliases PR WHERE PR.pair_id = R.pair_id)
Emin Mesic
  • 1,681
  • 2
  • 8
  • 18
  • Can you show me solution with pure SQL? – user1285928 Jan 27 '22 at 09:47
  • @user1285928 that *is* pure SQL - even if by `pure` you mean standard. Row constructors are part of the standard. The duplicate question contains many techniques, including this one. – Panagiotis Kanavos Jan 27 '22 at 09:47
  • @user1285928 are you trying to use the same query in multiple products? That may not be possible - no database product supports the SQL standard fully beyond a basic level. All databases have severe quirks and completely custom features. Some, like MySQL, are notorious for their quirks and semi-unsupported "features". Others, like SQLite, put more emphasis in reducing overhead than SQL features – Panagiotis Kanavos Jan 27 '22 at 09:51