clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591627 fiddles created (45700 in the last week).

CREATE TABLE tbl ( id int , storage "char" , col text , CONSTRAINT tbl_col_uni UNIQUE (col) ); -- Also tested with the same result, as expected: -- - UNIQUE index -- - plain btree index -- - PK constraint.
 hidden batch(es)


-- default for text is EXTENDED INSERT INTO tbl VALUES (111, 'x', rpad(md5(random()::text), 10000, md5(random()::text))); -- works INSERT INTO tbl VALUES (112, 'x', rpad(md5(random()::text), 8000, md5(random()::text))); -- works ALTER TABLE tbl ALTER col SET STORAGE PLAIN; -- no compression, no toast --INSERT INTO tbl VALUES -- (121, 'p', rpad(md5(random()::text), 10000, md5(random()::text))); -- 10000 = too big for "plain" storage anywhere with 8k page size -- ERROR: row is too big: size 10040, maximum size 8160 INSERT INTO tbl VALUES (122, 'p', rpad(md5(random()::text), 8000, md5(random()::text))); -- works ALTER TABLE tbl ALTER col SET STORAGE EXTERNAL; -- no compression INSERT INTO tbl VALUES (131, 'e', rpad(md5(random()::text), 10000, md5(random()::text))); INSERT INTO tbl VALUES (132, 'e', rpad(md5(random()::text), 8000, md5(random()::text))); ALTER TABLE tbl ALTER col SET STORAGE MAIN; -- toast = only last restort INSERT INTO tbl VALUES (141, 'm', rpad(md5(random()::text), 10000, md5(random()::text))); INSERT INTO tbl VALUES (142, 'm', rpad(md5(random()::text), 8000, md5(random()::text))); ALTER TABLE tbl ALTER col SET STORAGE EXTENDED; -- return to default with compression & toast
ERROR: index row size 8016 exceeds maximum 2712 for index "tbl_col_uni" HINT: Values larger than 1/3 of a buffer page cannot be indexed. Consider a function index of an MD5 hash of the value, or use full text indexing.
 hidden batch(es)


-- Error case ALTER TABLE tbl ALTER col SET STORAGE PLAIN; -- no compression, no toast INSERT INTO tbl VALUES (121, 'p', rpad(md5(random()::text), 10000, md5(random()::text))); -- 10000 = too big for "plain" storage anywhere with 8k page size -- ERROR: row is too big: size 10040, maximum size 8160
ERROR: row is too big: size 10040, maximum size 8160
 hidden batch(es)


-- Test INDEX itself UPDATE pg_attribute SET attstorage = 'p' WHERE attrelid = 'tbl_col_uni'::regclass AND attname = 'col'; -- hack idx col setting is sys catalog to PLAIN -- INSERT INTO tbl VALUES -- (221, 'p', rpad(md5(random()::text), 10000, md5(random()::text))); -- ERROR: index row requires 10016 bytes, maximum size is 8191 INSERT INTO tbl VALUES (222, 'p', rpad(md5(random()::text), 8000, md5(random()::text))); -- ERROR: index row size 8016 exceeds maximum 2712 for index "tbl_col_uni" -- HINT: Values larger than 1/3 of a buffer page cannot be indexed. -- Consider a function index of an MD5 hash of the value, or use full text indexing. UPDATE pg_attribute SET attstorage = 'e' WHERE attrelid = 'tbl_col_uni'::regclass AND attname = 'col'; -- hack idx col setting to EXTERNAL -- INSERT INTO tbl VALUES -- (231, 'e', rpad(md5(random()::text), 10000, md5(random()::text))); -- ERROR: index row requires 10016 bytes, maximum size is 8191 -- INSERT INTO tbl VALUES -- (232, 'e', rpad(md5(random()::text), 8000, md5(random()::text))); -- ERROR: index row size 8016 exceeds maximum 2712 for index "tbl_col_uni" ... UPDATE pg_attribute SET attstorage = 'm' WHERE attrelid = 'tbl_col_uni'::regclass AND attname = 'col'; -- hack idx col setting to MAIN; INSERT INTO tbl VALUES (241, 'm', rpad(md5(random()::text), 10000, md5(random()::text))); -- works INSERT INTO tbl VALUES (242, 'm', rpad(md5(random()::text), 8000, md5(random()::text))); -- works
ERROR: permission denied for table pg_attribute
 hidden batch(es)


-- Error case 221 UPDATE pg_attribute SET attstorage = 'p' WHERE attrelid = 'tbl_col_uni'::regclass AND attname = 'txt'; -- hack idx col setting is sys catalog to PLAIN INSERT INTO tbl VALUES (221, 'p', rpad(md5(random()::text), 10000, md5(random()::text)));
ERROR: permission denied for table pg_attribute
 hidden batch(es)


-- Error case 222 UPDATE pg_attribute SET attstorage = 'p' WHERE attrelid = 'tbl_col_uni'::regclass AND attname = 'txt'; -- hack idx col setting is sys catalog to PLAIN INSERT INTO tbl VALUES (222, 'p', rpad(md5(random()::text), 8000, md5(random()::text)));
ERROR: permission denied for table pg_attribute
 hidden batch(es)


-- Error case 231 UPDATE pg_attribute SET attstorage = 'e' WHERE attrelid = 'tbl_col_uni'::regclass AND attname = 'txt'; -- hack idx col setting to EXTERNAL INSERT INTO tbl VALUES (231, 'e', rpad(md5(random()::text), 10000, md5(random()::text)));
ERROR: permission denied for table pg_attribute
 hidden batch(es)


-- Error case 232 UPDATE pg_attribute SET attstorage = 'e' WHERE attrelid = 'tbl_col_uni'::regclass AND attname = 'txt'; -- hack idx col setting to EXTERNAL INSERT INTO tbl VALUES (232, 'e', rpad(md5(random()::text), 8000, md5(random()::text)));
ERROR: permission denied for table pg_attribute
 hidden batch(es)


-- Index never breaks in any case! -- UPDATE pg_attribute SET attstorage = 'x' -- WHERE attrelid = 'tbl_col_uni'::regclass AND attname = 'txt'; -- reset to default INSERT INTO tbl VALUES (311, 'm', rpad(md5('non_random'::text), 10000, md5('non_random'::text))); INSERT INTO tbl VALUES (311, 'm', rpad(md5('non_random'::text), 10000, md5('non_random'::text))); -- ERROR: duplicate key value violates unique constraint "idxmax_txt_uni" -- DETAIL: Key (txt)=(67ad0f29faa14a2d6183779 ...
ERROR: duplicate key value violates unique constraint "tbl_col_uni" DETAIL: Key (col)=(67ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d) already exists.
 hidden batch(es)


-- Even 100000 characters work (hint: repeating pattern -> good compression) -- UPDATE pg_attribute SET attstorage = 'x' -- WHERE attrelid = 'tbl_col_uni'::regclass AND attname = 'txt'; -- reset to default INSERT INTO tbl VALUES (411, 'x', rpad(md5('non_random'::text), 100000, md5('non_random'::text)));
1 rows affected
 hidden batch(es)


-- Too much is too much, finally, we exceed the index row size: -- UPDATE pg_attribute SET attstorage = 'x' -- WHERE attrelid = 'tbl_col_uni'::regclass AND attname = 'txt'; -- reset to default INSERT INTO tbl VALUES (511, 'x', rpad(md5('non_random'::text), 500000, md5('non_random'::text))); -- ERROR: index row size 5784 exceeds maximum 2712 for index "tbl_col_uni"
ERROR: index row size 5784 exceeds maximum 2712 for index "tbl_col_uni" HINT: Values larger than 1/3 of a buffer page cannot be indexed. Consider a function index of an MD5 hash of the value, or use full text indexing.
 hidden batch(es)


--.. and the page size -- UPDATE pg_attribute SET attstorage = 'x' -- WHERE attrelid = 'tbl_col_uni'::regclass AND attname = 'txt'; -- reset to default INSERT INTO tbl VALUES (611, 'x', rpad(md5('non_random'::text), 1000000, md5('non_random'::text))); -- ERROR: index row requires 11504 bytes, maximum size is 8191
ERROR: index row requires 11504 bytes, maximum size is 8191
 hidden batch(es)


SELECT id, storage , left(col, 15), length(col), pg_column_size(col) FROM tbl ORDER BY id;
id storage left length pg_column_size
411 x 67ad0f29faa14a2 100000 1191
 hidden batch(es)