Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE users (
  user_id  serial PRIMARY KEY
, username text NOT NULL
, email_id int NOT NULL  -- remove NOT NULL to make it "at most one"
);

CREATE TABLE email (
  email_id serial PRIMARY KEY
, user_id  int NOT NULL REFERENCES users ON DELETE CASCADE ON UPDATE CASCADE 
, email    text NOT NULL
, CONSTRAINT email_fk_uni UNIQUE(user_id, email_id)  -- constraint for FK below
);

ALTER TABLE users ADD CONSTRAINT active_email_fkey
FOREIGN KEY (user_id, email_id) REFERENCES email(user_id, email_id);

-- optional view
CREATE VIEW user_with_active_email AS
SELECT *
FROM   users u
JOIN   email USING (user_id, email_id);
-- insert new users
WITH new_data(username, email) AS (
   VALUES
      ('usr1', 'abc@d.com')   -- new users with *1* active email
    , ('usr2', 'def3@d.com')
    , ('usr3', 'ghi1@d.com')
   )
, u AS (
   INSERT INTO users(username, email_id)
   SELECT n.username, nextval('email_email_id_seq'::regclass)
   FROM   new_data n
   RETURNING *
   )
INSERT INTO email(email_id, user_id, email)
SELECT u.email_id, u.user_id, n.email
FROM   u
JOIN   new_data n USING (username)
RETURNING *;


email_id
user_id
email


1
1
abc@d.com

2
2
def3@d.com

3
3
ghi1@d.com


TABLE users;
TABLE email;
TABLE user_with_active_email;


user_id
username
email_id


1
usr1
1

2
usr2
2

3
usr3
3




email_id
user_id
email


1
1
abc@d.com

2
2
def3@d.com

3
3
ghi1@d.com




user_id
email_id
username
email


1
1
usr1
abc@d.com

2
2
usr2
def3@d.com

3
3
usr3
ghi1@d.com


-- insert new email for user 3 and make it the active one
WITH e AS (
   INSERT INTO email (user_id, email)
   VALUES  (3, 'new_active@d.com')
   RETURNING *
   )
UPDATE users u
SET    email_id = e.email_id
FROM   e
WHERE  u.user_id = e.user_id
RETURNING *;


user_id
username
email_id
email_id
user_id
email


3
usr3
4
4
3
new_active@d.com


TABLE email;
TABLE user_with_active_email;


email_id
user_id
email


1
1
abc@d.com

2
2
def3@d.com

3
3
ghi1@d.com

4
3
new_active@d.com




user_id
email_id
username
email


1
1
usr1
abc@d.com

2
2
usr2
def3@d.com

3
4
usr3
new_active@d.com