PostgreSQL – INSERT if it doesn’t exist/RETURN if it does (query optimization)


This query will insert a new IP if there isn’t one and return it’s ID. And if there already is the same value it only returns the ID.

I’m a newb with SQL and am curious if this could be further optimized?

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;

I am running PostgreSQL 12.