Postgres: Query for list of ids in a mapping table and create If they don't exist

Question

Assume we have the following table whose purpose is to autogenerate a numeric id for distinct (name, location) tuples:

CREATE TABLE mapping
(
  id bigserial PRIMARY KEY,
  name text NOT NULL,
  location text NOT NULL,
);
CREATE UNIQUE INDEX idx_name_loc on mapping(name location)

What is the most efficient way to query for a set of (name, location) tuples and autocreate any mappings that don't already exist, with all mappings (including the ones we created) being returned to the user.

My naive implementation would be something like:

SELECT id, name, location 
FROM mappings 
WHERE (name, location) IN ((name_1, location_1)...(name_n, location_n))

do something with the results in a programming language of may choice to work out which results are missing.

INSERT 
INTO mappings (name, location) 
VALUES (missing_name_1, missing_loc_1), ... (missing_name_2, missing_loc_2) 
ON CONFLICT DO NOTHING

This gets the job done but I get the feeling there's probably something that can a) be done in pure sql and b) is more efficient.

(DISTINCT name) CROSS JOIN (DISTINCT location) WHERE NOT EXISTS (...) — wildplasser
– wildplasser, Commented May 26, 2022 at 8:08

wildplasser · Accepted Answer · 2022-05-26 10:50:30Z

You can use DISTINCT to get all possible values for the two columns, and CROSS JOIN to get their Carthesian product.

LEFT JOIN with the original table to get the actual records (if any):

CREATE TABLE mapping
  ( id bigserial PRIMARY KEY
  , name text NOT NULL
  , location text NOT NULL
 , UNIQUE (name, location)
);

INSERT INTO mapping(name, location) VALUES ('Alice', 'kitchen'), ('Bob', 'bedroom' );

SELECT * FROM mapping;

SELECT n.name, l.location, m.id
FROM (SELECT DISTINCT name from mapping) n
CROSS JOIN (SELECT DISTINCT location from mapping) l
LEFT JOIN mapping m ON m.name = n.name AND m.location = l.location
        ;

Results:

DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 2
 id | name  | location 
----+-------+----------
  1 | Alice | kitchen
  2 | Bob   | bedroom
(2 rows)

 name  | location | id 
-------+----------+----
 Alice | kitchen  |  1
 Alice | bedroom  |   
 Bob   | kitchen  |   
 Bob   | bedroom  |  2
(4 rows)

And if you want to physically INSERT the missing combinations:

INSERT INTO mapping(name, location)
SELECT n.name, l.location
FROM (SELECT DISTINCT name from mapping) n
CROSS JOIN (SELECT DISTINCT location from mapping) l
WHERE NOT EXISTS(
        SELECT *
        FROM mapping m
        WHERE m.name = n.name AND m.location = l.location
        )
        ;

SELECT * FROM mapping;

INSERT 0 2
 id | name  | location 
----+-------+----------
  1 | Alice | kitchen
  2 | Bob   | bedroom
  3 | Alice | bedroom
  4 | Bob   | kitchen
(4 rows)

Collectives™ on Stack Overflow

Postgres: Query for list of ids in a mapping table and create If they don't exist

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related