Как привести массив чисел из поля VARCHAR к типу INTEGER в PostgreSQL?

Question

[Евгений Вольф]

Доброго времени суток уважаемые!

Есть вот такой запрос:

SELECT field1::integer FROM table1 WHERE field1 ~ E'^\\d+$' AND field1::integer > 3;

Который позволяет нам выбирать поля типа VARCHAR и приводить их к типу INTEGER для последующего сравнения оных с числовыми значениями.

Теперь возникла необходимость провести такую операцию с полем-массивом VARCHAR[]. То есть, у нас есть поле-массив VARCHAR[], в котором есть некоторый набор значений. Нужно каждое из значений массива выбрать, попробовать его привести к INITEGER типу, если оно не приводится (например, из-за содержания в поле символов отличных от цифр) - просто проигнорировать это значение, со значениями которые приводятся - провести соотв. операцию сравнения, типа:
... WHERE 10 < ANY(array_column).
----------

Коллеги, спасибо большое за помощь! JsQuery вообще отличная штука, жаль оператор LIKE не поддерживает :)

Answer 1

[sim3x]

Так говнокодить не нужно - сделай нормализацию или используй json field
||
Предай массив в серверный ЯП
||
Напиши процедуру

Comments

[Евгений Вольф]

Если вместо обычного массива будет JSON-поле, с его данными можно проводить подобные операции без приведения типов? JSON-поля в Postgres полностью индексируются?

[Евгений Вольф]

Передать массив в серверный ЯП я конечно могу, но выборка на 1.5млн. массивов, на уровне серверного ЯП будет плохой идеей, на мой взгляд.

[Евгений Вольф]

И если не сложно, опишите пожалуйста более подробно, что имеется в виду под фразой "процедура"? Т.е. что она в общей сложности должна делать (логика её работы)?

[sim3x]

Евгений Вольф:
процедура
https://www.postgresql.org/docs/9.1/static/sql-cre...

"подобные операции без приведения типов?"
"JSON-поля в Postgres полностью индексируются?"
О чем конкрентно идет речь?

https://www.postgresql.org/docs/9.4/static/datatyp...

[Евгений Вольф]

sim3x: Про индексацию прочитал, индексируются, отлично!
Остался только один вопрос пожалуй, по этой же теме. Есть JSON-массив, сохраненный в поле (JSONB), с такими для примера значениями: ["a", "b", 10]. Как мне выбрать все записи, у которых есть элемент JSON-массива (сохраненного в JSONB поле) который >= 10 ?

[Евгений Вольф]

Мне кажется идея использовать JSON-поля, ничем по своей сути не отличается от идеи использования массивов. Операторов сравнения, или LIKE для JSON'а в документации, я не нашел.

По первой ссылке я нашел только информацию как создать процедуру/функцию, ничего на тему того, какую логическую роль эта самая процедура или функция должна выполнять в задаче частичного приведения (по мере возможности) массива строк к массиву целых - я там не нашел.

[sim3x]

Евгений Вольф:
нормализуй данные и делай запросы как белый человек

Если структура данных сильно не стабильна - используй EAV

[Евгений Вольф]

Не совсем понимаю, каким образом "это" можно нормализовать... если очень сильно абстрагировать ситуацию, то она будет выглядеть так: от пользователя приходит набор данных (массив, одномерный) либо строк, либо чисел. В зависимости от условий/ситуации, мы должны либо выбирать все записи которые содержат какой-то текст, либо все записи которые попадают в некоторый диапазон, например >=10 AND <= 100, все эти условия действительны применительно к элементу массива (одному, любому элементу одномерного массива).

Структура данных стабильна - это одномерный строково-числовой массив, в виду чего он храниться в формате VARCHAR.

[sim3x]

Евгений Вольф: вместо сохранения в варчар сохраняем каждое значение в свое поле

[Евгений Вольф]

sim3x: количество полей заранее неизвестно, к тому же, их может быть довольно большое кол-во, иногда >1000. Плюс, значение может выступать и в роли числа и в роли строки (если это исключительно цифры), в зависимости от условий поиска. По этой причине, "нормализовать" данные выше описанным способом - не представляется возможным.

Но одно условие всегда соблюдается на 100% - это одномерный, простой массив.

[sim3x]

Евгений Вольф:

Используй https://en.wikipedia.org/wiki/Entity%E2%80%93attri...

Answer 2

[Артём Клименко]

С этой задачей уже наверно как и посоветовали использовать jsonb.

А вот для запросов воспользоваться вот этим https://postgrespro.ru/docs/postgrespro/9.5/jsquery - позволит относительно просто делать сложные условия на внутреннюю структуру json, ещё и индексы буду поддерживаться.

Comments

[Евгений Вольф]

Спасибо за отсылку к интересному решению!