81

Using PostgreSQL v9.1. I have the following tables:

CREATE TABLE foo
(
    id BIGSERIAL     NOT NULL UNIQUE PRIMARY KEY,
    type VARCHAR(60) NOT NULL UNIQUE
);

CREATE TABLE bar
(
    id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
    description VARCHAR(40) NOT NULL UNIQUE,
    foo_id BIGINT NOT NULL REFERENCES foo ON DELETE RESTRICT
);

Say the first table foo is populated like this:

INSERT INTO foo (type) VALUES
    ( 'red' ),
    ( 'green' ),
    ( 'blue' );

Is there any way to insert rows into bar easily by referencing the foo table? Or must I do it in two steps, first by looking up the foo type I want, and then inserting a new row into bar?

Here is an example of pseudo-code showing what I was hoping could be done:

INSERT INTO bar (description, foo_id) VALUES
    ( 'testing',     SELECT id from foo WHERE type='blue' ),
    ( 'another row', SELECT id from foo WHERE type='red'  );
1
  • 1
    This is a nicely worded question that is exactly what I'm looking for, thank you for the post Jul 23 at 13:41
100

Your syntax is almost good, needs some parenthesis around the subqueries and it will work:

INSERT INTO bar (description, foo_id) VALUES
    ( 'testing',     (SELECT id from foo WHERE type='blue') ),
    ( 'another row', (SELECT id from foo WHERE type='red' ) );

Tested at DB-Fiddle

Another way, with shorter syntax if you have a lot of values to insert:

WITH ins (description, type) AS
( VALUES
    ( 'more testing',   'blue') ,
    ( 'yet another row', 'green' )
)  
INSERT INTO bar
   (description, foo_id) 
SELECT 
    ins.description, foo.id
FROM 
  foo JOIN ins
    ON ins.type = foo.type ;
1
  • 3
    Took reading it a few times, but I now understand that 2nd solution you provided. I like it. Using it now to bootstrap my database with a handful of known values when the system first comes up.
    – Stéphane
    Jul 18 '13 at 18:03
52

Plain INSERT

INSERT INTO bar (description, foo_id)
SELECT val.description, f.id
FROM  (
   VALUES
      (text 'testing', text 'blue')  -- explicit type declaration; see below
    , ('another row' , 'red' )
    , ('new row1'    , 'purple')      -- purple does not exist in foo, yet
    , ('new row2'    , 'purple')
   ) val (description, type)
LEFT   JOIN foo f USING (type);
  • LEFT [OUTER] JOIN instead of [INNER] JOIN means that rows from val all rows are kept, even when no match is found in foo. Instead, NULL is entered for foo_id (which raises an exception if the column is defined NOT NULL).

  • The VALUES expression in the subquery does the same as @ypercube's CTE. Common Table Expressions offer additional features and are easier to read in big queries, but they also pose as optimization barriers (up to Postgres 12). Subqueries are typically a bit faster when none of the above is needed.

  • You may need explicit type casts. Since the VALUES expression is not directly attached to a table (like in INSERT ... VALUES ...), types cannot be derived and default data types are used unless typed explicitly. This may not work in all cases. It's enough to do it in the first row, the rest falls in line.

INSERT missing FK rows at the same time

To create missing entries in foo on the fly, in a single SQL statement, CTEs are instrumental:

WITH sel AS (
   SELECT val.description, val.type, f.id AS foo_id
   FROM  (
      VALUES
         (text 'testing', text 'blue')
       , ('another row', 'red'   )
       , ('new row1'   , 'purple')
       , ('new row2'   , 'purple')
      ) val (description, type)
   LEFT   JOIN foo f USING (type)
   )
, ins AS ( 
   INSERT INTO foo (type)
   SELECT DISTINCT type FROM sel WHERE foo_id IS NULL
   RETURNING id AS foo_id, type
   )
INSERT INTO bar (description, foo_id)
SELECT sel.description, COALESCE(sel.foo_id, ins.foo_id)
FROM   sel
LEFT   JOIN ins USING (type);

Old sqlfiddle for Postgres 9.6 - works the same in 9.1. Also see new fiddle below!

Note the two additional rows to insert. Both are purple, which does not exist in foo, yet. Two rows to illustrate the need for DISTINCT in the first INSERT statement.

Step-by-step explanation

  1. The 1st CTE sel provides multiple rows of input data. The subquery val with the VALUES expression can be replaced with a table or subquery as source. Immediately LEFT JOIN to foo to append the foo_id for pre-existing type rows. All other rows get foo_id IS NULL this way.

  2. The 2nd CTE ins inserts distinct new types (foo_id IS NULL) into foo, and returns the newly generated foo_id - together with the type to join back to insert rows.

  3. The final outer INSERT can now insert a foo_id for every row: either the type pre-existed, or it was inserted in step 2.

Strictly speaking, both inserts happen "in parallel", but since this is a single statement, default FOREIGN KEY constraints will not complain. Referential integrity is enforced at the end of the statement by default.

There is a tiny race condition if you run multiple of these queries concurrently. See:

Really only happens under heavy concurrent load, if ever. In comparison to caching solutions like advertised in another answer, the chance is super-tiny.

Function for repeated use

Create an SQL function that takes an array of composite type as parameter and use unnest(param) in place of the VALUES expression.

Or, if the syntax for such an array seems too messy, use a comma-separated string as parameter _param. For instance of the form:

'description1,type1;description2,type2;description3,type3'

Then use this to replace the VALUES expression in above statement:

SELECT split_part(x, ',', 1) AS description
       split_part(x, ',', 2) AS type
FROM   unnest(string_to_array(_param, ';')) x;

Function with UPSERT in Postgres 9.5 or later

Create a custom row type for parameter passing. We could do without it, but it's simpler:

CREATE TYPE foobar AS (description text, type text);

Function:

CREATE OR REPLACE FUNCTION f_insert_foobar(VARIADIC _val foobar[])
  RETURNS void
  LANGUAGE sql AS
$func$
WITH val AS (SELECT * FROM unnest(_val))    -- well-known row type
,    typ AS (
   SELECT v.type, f.id                      -- id NOT NULL where type already exists
   FROM  (SELECT DISTINCT type FROM val) v  -- DISTINCT!
   LEFT   JOIN foo f USING (type)           -- assuming no concurrent update/delete on foo
                                            -- else you might lock rows here.
   )
,    ins AS ( 
   INSERT INTO foo AS f (type)
   SELECT type
   FROM   typ
   WHERE  id IS NULL
   ON     CONFLICT (type) DO UPDATE         -- RARE cases of concurrent inserts
   SET    type = EXCLUDED.type              -- overwrite to make visible
   RETURNING f.type, f.id
   )
INSERT INTO bar AS b (description, foo_id)
SELECT v.description, COALESCE(t.id, i.id)  -- assuming most types pre-exist
FROM        val v
LEFT   JOIN typ t USING (type)              -- already existed
LEFT   JOIN ins i USING (type)              -- newly inserted
ON     CONFLICT (description) DO UPDATE     -- description already exists
SET    foo_id = EXCLUDED.foo_id             -- real UPSERT this time
WHERE  b.foo_id <> EXCLUDED.foo_id;         -- only if actually changed
$func$;

Call:

SELECT f_insert_foobar(
     '(testing,blue)'
   , '(another row,red)'
   , '(new row1,purple)'
   , '(new row2,green)'
   , '("with,comma",green)'  -- added to demonstrate row syntax
   );

db<>fiddle here

Fast and rock-solid for environments with concurrent transactions.

In addition to the queries above, this function ...

Related:

8
  • In your INSERT missing FK rows at the same time example, would putting this in a Transaction reduce the risk of race conditions in SQL Server?
    – element11
    Jul 21 '16 at 14:57
  • 1
    @element11: The answer is for Postgres, but since we are talking about a single SQL command, it's a single transaction in any case. Executing it inside a bigger transaction would only increase the time window for possible race conditions. As for SQL Server: data-modifying CTEs are not supported at all (only SELECT inside a WITH clause) . Source: MS documentation. Jul 21 '16 at 20:26
  • 1
    You can also do this with INSERT ... RETURNING \gset in psql then use the returned values as psql :'variables', but this only works for single row inserts. Oct 21 '16 at 12:50
  • @ErwinBrandstetter this is great, but I'm too new to sql to understand it all, could you add some comments to "INSERT missing FK rows at the same time" explaining how it works? also, thanks for the SQLFiddle working examples!
    – glallen
    Feb 8 '17 at 15:32
  • @glallen: I added a step-by-step explanation. There are also many links to related answers and the manual with more explanation. You need to understand what the query does or you may be in over your head. Feb 8 '17 at 17:59
6

Lookup. You basically need the foo id's to insert them into bar.

Not postgres specific, btw. (and you did not tag it like that) - this is generally how SQL works. No shortcuts here.

Application wise, though, you may have a cache of foo items in memory. My tables often have up to 3 unique fields:

  • Id (integer or something) that is the table level primary key.
  • Identifier, which is a GUID that is used as stable ID application level wise (and may be exposed to the customer in URL's etc.)
  • Code - a string that may be there and has to be unique if it is there (sql server: filtered unique index on not null). That is a customer set identifier.

Example:

  • Account (in a trading application) -> Id is a int used for foreign keys. -> Identifier is a Guid and used in the web portals etc. - always accepted. -> Code is manually set. Rule: once set it does not change.

Obviously when you want to link something to an account - you first must, technically, get the Id - but given both Identifier and Code never change once they are there, a positive cache in memory kan stop most lookups from hitting the database.

5
  • 10
    You are aware that you can let the RDBMS do the lookup for you, in a single SQL statement, avoiding error-prone cache? Jul 16 '13 at 21:39
  • You are aware that looking up non-changing elements is not error prone? Also, typically, the RDBMS is not scalable and the most expensive element in the game, due to licensing costs. Taking as much load from it as possible is not exactly bad. Also, not many ORM's do support that to start with.
    – TomTom
    Jul 17 '13 at 3:08
  • 15
    Non-changing elements? Most expensive element? Licensing costs (for PostgreSQL)? ORMs defining what's sane? No I wasn't aware of all of that. Jul 17 '13 at 3:38
  • Hi! Learning SQL. So even though every entry has a unique ID, you would still need other unique fields to help with lookups on the db is what you're saying? I looked at other solutions on this page and was wondering this. Your response is at the bottom but seems to confirm that.
    – DeltaFlyer
    Jun 28 at 2:41
  • "Non-changing elements?" - yeah, an account i.e. is non changing. "Licensing costs (for PostgreSQL)?" - yeah, better learn it in a way that gets you laughed at when you work with high end databases like Oracle. After all, it is a virtue to be non professional. " ORMs defining what's sane?" - no, orm's defining what is common. Sane is another thing, but many ORM's are hardly allowing all features. And given that the ORM reduces development cost by about 40% NOT caring for it is insane. But then, on your world money seems to grow on trees.
    – TomTom
    Jun 28 at 7:51

Your Answer

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.