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