0

I have a user and a log table. I want to keep adding new log, and if the user name does not exist in user, add that too.

What I have however, will only insert into log if user does not exist. If user already exist, no log gets added.

How can I fix this to make it work as intended? Thanks in advance.

CREATE TABLE user (
    id serial NOT NULL,
    name char(60) NOT NULL,
    CONSTRAINT user_pk PRIMARY KEY (id),
    CONSTRAINT user_un UNIQUE (name) 
)
CREATE TABLE log (
    id serial NOT NULL,
    name_id int NOT NULL,
    detail char(512) NULL,
    CONSTRAINT detail_pk PRIMARY KEY (id),
    CONSTRAINT detail_user_fk FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE RESTRICT ON UPDATE RESTRICT
)

my attempt

with ins1 as (
    insert into user (name) 
    values ('myname')
    on conflict do nothing
    returning id as user_id
)
insert into detail (user_id, detail)
select user_id, 'some detail' from ins1;

Following example from other question I changed do nothing to update where false, but still no log is being inserted

with ins1 as (
    insert into "user" (name) 
    values ('myuser')
    on conflict (name) do update
    set name = null where FALSE
    returning id as user_id
)
insert into log (user_id, detail)
select user_id, 'some description' from ins1;
Panupat
  • 452
  • 6
  • 21
  • 1
    Obviously `on conflict do nothing` returns nothing on conflict. – Abelisto Sep 04 '18 at 08:26
  • 2
    See [here](https://stackoverflow.com/questions/39779145) or [here](https://stackoverflow.com/questions/46586793) –  Sep 04 '18 at 08:29
  • Thank you for the link. I tried switching do nothing to update where false but it's still not inserting new log. I will edit the new query into my comment. – Panupat Sep 04 '18 at 08:49

0 Answers0