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. 2555272 fiddles created (37426 in the last week).

CREATE TABLE city( city_id serial primary key , city text COLLATE "C" , other_names_lower text COLLATE "C" ); INSERT INTO city (city, other_names_lower) SELECT t, t -- second column just to make row bigger FROM ( SELECT left(string_agg(chr(65 + (random() * 25)::int), ''), 3 + (random() * 7)::int) || ' ' || left(string_agg(chr(65 + (random() * 25)::int), ''), 3 + (random() * 7)::int) AS t FROM generate_series(1, 110000) g GROUP BY g%11000 -- random words ) sub; INSERT INTO city (city, other_names_lower) VALUES ('%foobar','%fooелезbar'); DELETE FROM city WHERE random() > 0.9; -- some dead rows ANALYZE city;
11000 rows affected
1 rows affected
1131 rows affected
 hidden batch(es)


SELECT ctid, * FROM city ORDER BY ctid DESC LIMIT 3;
ctid city_id city other_names_lower
(87,93) 11001 %foobar %fooелезbar
(87,92) 11000 UPA BBTSQM UPA BBTSQM
(87,91) 10999 DGPPGIRCE HCCNE DGPPGIRCE HCCNE
 hidden batch(es)


DELETE FROM city c USING ( SELECT (split_part(ctid::text, ',', 1) || ',0)')::tid AS min_tid , (split_part(ctid::text, ',', 1) || ',65535)')::tid AS max_tid -- max uint2 65535 = 2^16 - 1 FROM city ORDER BY ctid DESC LIMIT 1 ) d WHERE c.ctid BETWEEN min_tid AND max_tid;
86 rows affected
 hidden batch(es)


SELECT ctid, * FROM city ORDER BY ctid DESC LIMIT 3;
ctid city_id city other_names_lower
(86,124) 10908 TCDPX TYFPTT TCDPX TYFPTT
(86,123) 10907 TUWQXMRRF HMEUMHNX TUWQXMRRF HMEUMHNX
(86,122) 10906 BIDIBVS GNYNW BIDIBVS GNYNW
 hidden batch(es)