PostgreSQL: INSERT if it doesn't exist, RETURN the ID if it does

user_2253323 0
Viewed 146

The following query inserts the IP address if there is none and returns the ID.

WITH variables AS (
  SELECT '1.1.1.2'::inet AS ip
)
INSERT INTO globals.ips (ip)
SELECT v.ip
FROM variables AS v
WHERE NOT EXISTS (
    SELECT i.global_ip_id 
    FROM globals.ips AS i 
    WHERE i.ip = v.ip
)
RETURNING global_ip_id

However, when the value is already present in the database it does not return the ID.

How can I get the ID when the value is already present?

I'm running PostgreSQL 12.

postgresql sql
2 Answers
WITH variables AS (
  SELECT '1.1.1.2'::inet AS ip
), wt0 as (
INSERT INTO globals.ips (ip)
SELECT v.ip
FROM variables AS v
WHERE NOT EXISTS (
    SELECT i.global_ip_id 
    FROM globals.ips AS i 
    WHERE i.ip = v.ip
)
RETURNING global_ip_id
)
SELECT global_ip_id FROM wt0
UNION
SELECT id FROM globals.ips WHERE ip = 'SOMETHING';
user_11189780

I'm not sure it's the most elegant; but it returns the ID of the IP every time. And I only need to enter the IP once.

WITH v AS (
   SELECT '1.1.1.110'::inet AS ip
), i AS (
    INSERT INTO globals.ips (ip)
    SELECT * FROM v
    WHERE NOT EXISTS (
        SELECT i.global_ip_id
        FROM globals.ips AS i
        WHERE i.ip = v.ip
    )
    RETURNING global_ip_id
)
SELECT global_ip_id FROM i
UNION ALL
SELECT global_ip_id FROM globals.ips g
LEFT JOIN v ON g.ip = v.ip
WHERE g.ip = v.ip
LIMIT 1;

If anyone has a suggestion on how to improve this please let me know.

A more elegant solution:

INSERT INTO globals.ips (ip) VALUES ('1.1.1.1'::inet)
ON CONFLICT (ip) DO UPDATE SET ip = EXCLUDED.ip
RETURNING global_ip_id AS id;

However, a bit slower than the original one.

user_22533230
Related