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 versions (
id int primary key,
version text
);
CREATE TABLE
insert into versions (id, version) values
(1, '1.0.0'),
(2, '1.0.1'),
(3, '1.1.0'),
(4, '1.2.0'),
(5, '2.0.0')
;
INSERT 0 5
select * from versions
| id | version |
|---|---|
| 1 | 1.0.0 |
| 2 | 1.0.1 |
| 3 | 1.1.0 |
| 4 | 1.2.0 |
| 5 | 2.0.0 |
SELECT 5
select * from information_schema.tables where table_name = 'versions'
| table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action |
|---|---|---|---|---|---|---|---|---|---|---|---|
| postgres | public | versions | BASE TABLE | null | null | null | null | null | YES | NO | null |
SELECT 1
with x(d) as (
select query_to_xml('select version from ' || (
select case count(*) when 0 then '(select 0 as version)' else 'versions' end
from information_schema.tables t
where t.table_name = 'versions' -- try 'nonexistent' table_name instead
) || ' limit 1', false, false, '')::xml
)
select xmltable.*
from x
, xmltable('/table/row'
passing d
columns
version text path 'version'
)
| version |
|---|
| 1.0.0 |
SELECT 1