How to cast an array of numbers from a VARCHAR field to INTEGER in PostgreSQL?

Kind time of day dear!

There is here such request:
SELECT field1::integer FROM table1 WHERE field1 ~ E'^\\d+$' AND field1::integer > 3;

Which allows us to choose fields of type VARCHAR and allow them to INTEGER for later comparison thereof with the numerical values.

It was now necessary to carry out such an operation with field-array VARCHAR[]. That is, we have field-array VARCHAR[], in which there is some set of values. You need each of the array values to choose, to try to bring him to INITEGER type if it is not given (for example, because of the content of the field in characters other than numbers) - just ignore this value, with values which are provided to hold the ACC. the comparison operation, such as:
... WHERE 10 < ANY(array_column).
----------

Colleagues, thank you very much for the help! JsQuery is generally a great thing, sorry for the LIKE operator is not supported :)
July 2nd 19 at 13:46
2 answers
July 2nd 19 at 13:48
Solution
So gonococal not necessary - do the normalization, or use a json field
||
Give an array in the server PL
||
Write a procedure
If instead of the usual array of JSON field with data it is possible to conduct such operations without type casting? JSON field in Postgres fully indexed? - Salvado commented on July 2nd 19 at 13:51
Pass an array to the server YAP I certainly can, but the sample is 1.5 million arrays on the back end of YAP would be a bad idea, in my opinion. - Salvado commented on July 2nd 19 at 13:54
And if not difficult, please describe in more detail what is meant by the phrase "the procedure"? Ie that she is in total must do (its logic)? - Salvado commented on July 2nd 19 at 13:57
:
procedure
https://www.postgresql.org/docs/9.1/static/sql-cre...

"such an operation without a type cast?"
"JSON field in Postgres fully indexed?"
What konkrentno is it?

https://www.postgresql.org/docs/9.4/static/datatyp... - marilyne_Roh commented on July 2nd 19 at 14:00
: About the indexing read, indexed, excellent!
There was only one question perhaps on the same topic. There is a JSON array stored in the field (JSONB), such for example, values: ["a", "b", 10]. How do I select all records that have an element of the JSON array (stored in a JSONB field) which is >= 10 ? - Salvado commented on July 2nd 19 at 14:03
I think the idea of using the JSON field, does in essence not differ from the idea of using arrays. Comparison operators, or the LIKE for JSON'and in the documentation I have not found.

The first link I found only information how to create a procedure/function, nothing about what logical role, this is the procedure or function must perform the task in a partial cast (whenever possible) array of strings to array of integers - I have not found it. - Salvado commented on July 2nd 19 at 14:06
:
normalizes data and do the queries like a white man

If the data structure is not strongly stable - use EAV - marilyne_Roh commented on July 2nd 19 at 14:09
Don't quite understand how "it" can normalize... if it's really hard to abstract the situation, it will look like this: user comes from a dataset (array, one-dimensional), or of strings or numbers. Depending on the conditions/situation, we must either select all records which contain some text, or all records that fall within a certain range, e.g. >=10 AND <= 100, these conditions are valid with regard to the element of the array (one, any element a one-dimensional array).

The data structure is stable is strokovo one-dimensional numerical array, in view of which he stored in VARCHAR format. - Salvado commented on July 2nd 19 at 14:12
instead of saving to varchar maintain each value in its field - marilyne_Roh commented on July 2nd 19 at 14:15
: the number of fields is not known in advance, moreover, they can be quite a large number, sometimes >1000. Plus, the value can act as a number and as a string (if it is a pure numbers), depending on search conditions. For this reason, "normalize" the data above described way is not possible.

But one condition is always respected 100% is a one - dimensional simple array. - Salvado commented on July 2nd 19 at 14:18
:

Use https://en.wikipedia.org/wiki/Entity%E2%80%93attri... - marilyne_Roh commented on July 2nd 19 at 14:21
July 2nd 19 at 13:50
Solution
With this problem probably already as advised to use jsonb.

But for the query to use this https://postgrespro.ru/docs/postgrespro/9.5/jsquery - would make it relatively easy to do complex conditions on the internal structure of the json, also the indexes will be supported.
Thanks for the reference to the interesting decision! - Salvado commented on July 2nd 19 at 13:53

Find more questions by tags PostgreSQL