Postgres 10

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 IF NOT EXISTS users (
  id serial UNIQUE NOT NULL,
  email varchar(255) NOT NULL,
  user_name varchar(255) NOT NULL,
  first_name varchar(255) NOT NULL,
  last_name varchar(255) NOT NULL,
  active bool DEFAULT TRUE,
  created_at DATE
);

CREATE TABLE IF NOT EXISTS profiles (
  id serial UNIQUE NOT NULL,
  user_id serial NOT NULL,
  handle varchar(255) NOT NULL,
  name varchar(255) NOT NULL,
  city varchar(255) NULL,
  FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON 
  UPDATE CASCADE
);
with ins1
AS
(
  insert into users (email, user_name, first_name, last_name, active, created_at) 
       values ('john@doe.com', 'johndoe', 'John', 'Doe', true, '2018-03-16T17:39:54Z')
       returning id as ret_user_id, user_name as ret_user_name
 )      
  insert into profiles (user_id, handle, name, city) 
     select ret_user_id, ret_user_name ,'John Doe', 'Tawala' from ins1;
       
1 rows affected

select * from profiles;


id
user_id
handle
name
city


1
1
johndoe
John Doe
Tawala


select * from users;


id
email
user_name
first_name
last_name
active
created_at


1
john@doe.com
johndoe
John
Doe
t
2018-03-16