add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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 dok(
id int primary key
,pais2obj text
,during tstzrange);
CREATE TABLE
create view dok_during_exclude
as table dok;
CREATE VIEW
alter table dok
add constraint dok_during_exclude EXCLUDE
USING gist ( pais2obj WITH =
,during WITH &&);
ERROR:  relation "dok_during_exclude" already exists
select relnamespace::regnamespace as schema_name
,case relkind
when 'r' then 'ordinary table'
when 'i' then 'index'
when 'S' then 'sequence'
when 't' then 'TOAST table'
when 'v' then 'view'
when 'm' then 'materialized view'
when 'c' then 'composite type'
when 'f' then 'foreign table'
when 'p' then 'partitioned table'
when 'I' then 'partitioned index'
end as relkind_,*
from pg_class
where relname='dok_during_exclude';
schema_name relkind_ oid relname relnamespace reltype reloftype relowner relam relfilenode reltablespace relpages reltuples relallvisible reltoastrelid relhasindex relisshared relpersistence relkind relnatts relchecks relhasrules relhastriggers relhassubclass relrowsecurity relforcerowsecurity relispopulated relreplident relispartition relrewrite relfrozenxid relminmxid relacl reloptions relpartbound
public view 8580922 dok_during_exclude 2200 8580924 0 8580912 0 0 0 0 -1 0 0 f f p v 3 0 t f f f f t n f 0 0 0 null null null 
SELECT 1
alter view dok_during_exclude
rename to v_dok;
ALTER VIEW
alter table dok
drop constraint if exists dok_during_exclude
,add constraint dok_during_exclude EXCLUDE
USING gist ( pais2obj WITH =
,during WITH &&);
ALTER TABLE
select relnamespace::regnamespace as schema_name
,case relkind
when 'r' then 'ordinary table'
when 'i' then 'index'
when 'S' then 'sequence'
when 't' then 'TOAST table'
when 'v' then 'view'
when 'm' then 'materialized view'
when 'c' then 'composite type'
when 'f' then 'foreign table'
when 'p' then 'partitioned table'
when 'I' then 'partitioned index'
end as relkind_,*
from pg_class
where relname='dok_during_exclude';
schema_name relkind_ oid relname relnamespace reltype reloftype relowner relam relfilenode reltablespace relpages reltuples relallvisible reltoastrelid relhasindex relisshared relpersistence relkind relnatts relchecks relhasrules relhastriggers relhassubclass relrowsecurity relforcerowsecurity relispopulated relreplident relispartition relrewrite relfrozenxid relminmxid relacl reloptions relpartbound
public index 8580926 dok_during_exclude 2200 0 0 8580912 783 8580926 0 1 0 0 0 f f p i 2 0 f f f f f t n f 0 0 0 null null null 
SELECT 1
select pg_class.relnamespace::regnamespace as schema_name
,case relkind
when 'r' then 'ordinary table'
when 'i' then 'index'
when 'S' then 'sequence'
when 't' then 'TOAST table'
when 'v' then 'view'
when 'm' then 'materialized view'
when 'c' then 'composite type'
when 'f' then 'foreign table'
when 'p' then 'partitioned table'
when 'I' then 'partitioned index'
end as relkind_
,pg_class.relname
,pg_constraint.connamespace::regnamespace as constraint_schema
,pg_constraint.conname as constraint_name
,pg_constraint.conrelid::regclass as what_table_this_constraint_is_attached_to
from pg_class
join pg_constraint on pg_class.oid=pg_constraint.conindid
where relname='dok_during_exclude';
schema_name relkind_ relname constraint_schema constraint_name what_table_this_constraint_is_attached_to
public index dok_during_exclude public dok_during_exclude dok 
SELECT 1