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 mytable (
history_coord varchar(50)
);
insert into mytable values
('one semicolon ;'),
('one comma ,'),
('one semicolon ; and one comma ,'),
('two semicolon ;; and one comma ,'),
('TEXT');
CREATE TABLE
INSERT 0 5
SELECT *, array_length(string_to_array(history_coord, ','), 1) - 1 AS nbComma,
array_length(string_to_array(history_coord, ';'), 1) - 1 AS nbSemiColon
FROM mytable
| history_coord | nbcomma | nbsemicolon |
|---|---|---|
| one semicolon ; | 0 | 1 |
| one comma , | 1 | 0 |
| one semicolon ; and one comma , | 1 | 1 |
| two semicolon ;; and one comma , | 1 | 2 |
| TEXT | 0 | 0 |
SELECT 5
SELECT *
FROM mytable
WHERE array_length(string_to_array(history_coord, ','), 1) - 1 = 1
and array_length(string_to_array(history_coord, ';'), 1) - 1 = 1
| history_coord |
|---|
| one semicolon ; and one comma , |
SELECT 1