Insert row in table if value from referenced table not found

Question

I have the following two tables:

CREATE TABLE employee
(
  id serial NOT NULL,
  name character(32) NOT NULL,
  CONSTRAINT employee_id_pkey PRIMARY KEY (id),
  CONSTRAINT employee_name_ukey UNIQUE (name)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE employee
  OWNER TO postgres;

CREATE TABLE worklog
(
  id serial NOT NULL,
  activity character(32),
  employee integer NOT NULL,
  "time" timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT log_id_pkey PRIMARY KEY (id),
  CONSTRAINT fk_employee FOREIGN KEY (employee)
      REFERENCES employee (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE worklog
  OWNER TO postgres;

-- Index: fki_employee

-- DROP INDEX fki_employee;

CREATE INDEX fki_employee
  ON worklog
  USING btree
  (employee);

I would like to do something like this:

insert into
    worklog (activity, employee)
values
('work in progress',
    coalesce(
        (select id from employee where name = 'jonathan'),
        (insert into employee (name) values ('jonathan') returning id)
    )
);

However, this returns the error:

ERROR:  syntax error at or near "into"
LINE 8:   (insert into employee (name) values ('jonathan') returning...)

I have read somewhere, that I could just insert the 'name' in employee beforehand and ignore a possible duplicate key error by utilizing the ON CONFLICT ... mechanism. However, there are two issues for me with that approach:

1. I am using postgres 9.4 and AFAIK the ON CONFLICT construct requires 9.5
2. More importantly, the situation where an employee would not be in the database is exremely rare. So most of the time a leading insert would just be a waste of time.

Is what I have in mind possible with postgres 9.4?

Answer 1

Something like this should work:

with new_name as (
   insert into employee (name) 
   select 'jonathan'
   where not exists (select * 
                     from employee
                     where name = 'jonathan')
   returning id
), name as (
   select id
   from employee
   where name = 'jonathan'
   union all
   select id
   from new_name
)
insert into worklog (activity, employee)
select 'work in progress', id 
from name;

Note that this is not safe for concurrent execution - if two transactions run this at the same time, you might wind up with the same employee name twice with different IDs.

The second CTE name will only return a single row. If a row is inserted in the first step, the second CTE will not see it and thus the second part of the union will return the ID of the inserted row. If no row was inserted then the existing employee will be returned by the first part of the UNION, but the second one will not return a row.

Quote from the manual

Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot “see” one another's effects on the target tables

This alleviates the effects of the unpredictability of the actual order of row updates, and means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query

Online example: http://rextester.com/HYEN40017