PostgreSQL – How to Insert a Row with a Foreign Key

foreign keyinsertpostgresqlpostgresql-9.1

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'  );

Best Answer

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 ;
Related Question