Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'  );
share|improve this question

3 Answers 3

up vote 11 down vote accepted

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 SQL-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 ;
share|improve this answer
1  
+1 on the second. Not only shorter, also a lot faster. –  Erwin Brandstetter Jul 16 '13 at 19:55
    
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

Plain INSERT

INSERT INTO bar (description, foo_id)
SELECT val.description, f.id
FROM  (
   VALUES
      ('testing',     'blue')
     ,('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);
  • The use of a LEFT [OUTER] JOIN instead of [INNER] JOIN means that rows from val aren't dropped when no match is found in foo. Instead, NULL is entered for foo_id.

  • 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. So subqueries are typically a bit faster when none of the above is needed.

  • Don't use id as column name. Should be foo_id and bar_id or anything descriptive. When joining a bunch of tables, you end up with multiple columns all named id ...
    Use text or varchar instead of varchar(n). If you really need to impose a length restriction, use a CHECK constraint:

INSERT missing FK rows at the same time

If you want to create non-existent 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
         ('testing',     '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);
  • Note the two new dummy 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.

SQL Fiddle for Postgres 9.1.

Note there is tiny race condition if you run many of these queries concurrently. Read more under related questions here and here and here. 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

For repeated use I would create an SQL function that takes an array of records as parameter and use unnest(param) in place of the VALUES expression.

Or, if the syntax for arrays of records is to messy for you, 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
share|improve this answer
2  
Wow (for the RETURNING one.) I've spent too much time with MySQL. –  ypercube Jul 16 '13 at 20:46

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.

share|improve this answer
7  
You are aware that you can let the RDBMS do the lookup for you, in a single SQL statement, avoiding error-prone cache? –  Erwin Brandstetter 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
7  
Non-changing elements? Most expensive element? Licensing costs (for PostgreSQL)? ORMs defining what's sane? No I wasn't aware of all of that. –  Erwin Brandstetter Jul 17 '13 at 3:38
2  
@ErwinBrandstetter +1 for the "ORMs defining what's sane" comment. Made me laugh :-) –  redguy Jul 17 '13 at 10:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.