Изменили уже, это оказывается, были индивидуумы, которые проморгали правило..
Вот и хотелось бы узнать, можно ли как-то убрать их

собственно @vyegorov предугадал причины почему хочется секционировать. То что с FK не получится как планировал ответ услышал - спасибо. Про совет с констрейнтами на подтаблицы (секции?) не понял честно говоря. Поясните, если у вас есть время.

> (ради производительности и удобства администрирования, полагаю)

Так вот, ради производительности делить не нужно — есть некоторые "типичные" ситуации (и их немного), когда это работает (настолько везёт).
По умолчанию partitioning снижает производительность, но делать его приходится всё равно, по причине "удобства администрирования" (даже его эффективности, скорее).

Так вот Вам, очевидно, не повезло (FK сделать не удаётся, PK Вы тоже "сломали", не так ли?) — так почему бы не партиционировать по id?

> Про совет с констрейнтами на подтаблицы (секции?) не понял честно говоря.

Имелось в виду вот это: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=16b98a4d666b58b8c15dca244bfad662
Между прочим, pg_partman "умеет" так делать автоматически, если настроить.

Хм, есть над чем поразмыслить. Большое вам спасибо.

> По умолчанию partitioning снижает производительность

я бы уточнил, что однозначно проседает время планирования.
однако, если ключ партиционирования “всегда” используется в запросах, то время ответа будет существенно лучше. т.е. партиции сами по себе не панацея.

я работаю в основном с RANGE партициями по дате

> однако, если ключ партиционирования “всегда” используется в запросах, то время ответа будет существенно лучше.

Нет, тоже не всегда — зависит от того, сколько partitions выбирается и каким механизмом (compile-time или run-time pruning).
А уж при многоуровневом партиционировании время планирования может "перебить" вообще всё. ;)

> я работаю в основном с RANGE партициями по дате

Ну так и это и есть:

>  некоторые "типичные" ситуации (и их немного), когда это работает (настолько везёт).

;)

Вообще, мой посыл состоит в следующем (цитируя документацию): "Partitioning effectively substitutes for the upper tree levels of indexes ...". Так вот если в какой-то СУБД этот ad-hoc механизм (частично заменяющий "родную" реализацию индексов!), работает лучше, чем эти самые индексы... лично я держался бы подальше от такой СУБД и её разработчиков. ;)

К PostgreSQL это, разумеется, не относится.

а вы можете данные предоставить например в https://dbfiddle.uk/?rdbms=postgres_11

coalesce(to_date((regexp_match(f1, '(\d{4,4}\d{2,2}\d{2,2})_*')::text[])[1], 'YYYYMMDD'), '1970-01-01'::date)

, что-то типа такого?

Оо, огонь, докурю проверю..
Я думал сделать через матч, но отвлекли

Мистика. Это не связано с языком сортировки, потому что при любом языке буквы Б оказались бы вместе. Возможно связано с каким-нибудь RULE ?
Можете попробовать написать ORDER BY 2  (где 2 — номер колонки в результатах)?

ну запросто возможен результат, где ещё перед видимыми нам символами оказались какие-то не отображаемые символы, или за место кириллицы оказались символы другого алфавита, которые выглядят также

да, в качестве дальнейшей диагностики добавить вывод encode(title::bytea, 'hex')

Ля, тут ещё какие-то записи нашлись, которые имеют формат в виде DDMMYYYY

эти строки и в текстовом виде отсортируются по дате скорее всего

Не-а, он выдаёт ошибку

это не "годмесяцдата" и не соответствует условию задачи

Знаю, поэтому и отписал человечку который помог.
Это DDMMYYYY как я и указал выше.

Собственно, я думал, можно сделать условия, мол, если смог то пробуй YYYYMMDD, нет, пробуй DDMMYYYY, нет, тогда ставь 0 дату

если это не алфавитный порядок дат, надо регуляркой парсить и переводить в дату

Чтобы перевести в дату, желательно, чтобы оно было в одной маске

можно и обе конверсии засунуть в coalesce