Skip to content

Instantly share code, notes, and snippets.

@dillonhafer
Last active August 23, 2019 16:22
Show Gist options
  • Save dillonhafer/12d07ce863746db92bbf083696e7e6e1 to your computer and use it in GitHub Desktop.
Save dillonhafer/12d07ce863746db92bbf083696e7e6e1 to your computer and use it in GitHub Desktop.
The only thing I can think of is that the insert statement inside of the CTE is forcing all the connection constraints to deferred, but there’s no documentation that’s even remotely close to that explanation.
create table users (
name text primary key
);
create table groups (
name text primary key,
user_name text not null references users
);
alter table users
add column group_name text not null references groups;
-- this fails
insert into users (name, group_name) values ('my name', 'my group');
insert into groups (name, user_name) values ('my group', 'my name');
-- this fails
begin;
insert into users (name, group_name) values ('my name', 'my group');
insert into groups (name, user_name) values ('my group', 'my name');
commit;
-- this works
with new_user as (
insert into users (name, group_name)
values ('my name', 'my group')
returning name, group_name
)
insert into groups (name, user_name)
select new_user.group_name, new_user.name
from new_user;
--cleanup
drop table groups cascade;
drop table users cascade;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment