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 🍣 (🍎 int, 🍓 text);
create type 🌶  as (🦀 🍣);
create table 🥭 of 🌶 ;

insert into 🥭 values (row(1,'小島南')::🍣),(row(2,'初川南')::🍣);

select (🦀).🍎, (🦀).🍓 from 🥭;

CREATE TABLE

CREATE TYPE

CREATE TABLE

INSERT 0 2

🍎
🍓

1
小島南

2
初川南

SELECT 2

alter type 🌶 add attribute 🥝 numeric;

ERROR:  cannot alter type "🌶" because it is the type of a typed table
HINT:  Use ALTER ... CASCADE to alter the typed tables too.

create table 🍉 of 🌶 ;

CREATE TABLE

alter type 🌶 add attribute 🥝 numeric cascade;

ALTER TYPE

select * from 🥭;

🦀
🥝

(1,小島南)
null

(2,初川南)
null

SELECT 2

select * from 🍉;

🦀
🥝

SELECT 0

select table_name, is_typed
  from information_schema.tables
 where table_name in ('🍣', '🥭', '🍉');

table_name
is_typed

🍣
NO

🥭
YES

🍉
YES

SELECT 3