Kind time of day dear!
In continuation of my previous questions
[PostgreSQL] How to put strings in INT and other data types?
How to cast an array of numbers from a VARCHAR field to INTEGER in PostgreSQL?The problem:
there is a set of data (from 1 to 1000+) records, strictly tied to another record (simple connection, one-to-many). In view of the fact that these data are of mixed type (can be strings, with all the consequences) and numbers. The data stored in VARCHAR format, in view of the above described reasons.
At the same time, work with the data we need depending on the situation and as strings and as numbers. That is, if the search condition is set to "search by rows" - we're looking for around the right and by rows and numbers, the Bud is all the rows (technically it is). If the condition is to search for ranges, for example:
... WHERE n >= 10 AND n <= 100;
we have to choose only numbers and respectively compare them.
I see a solution:Option 1
We keep lowercase the data in the table for the rows of numeric data in table for numbers (and apparently, for the fractional'll have to have a personal table), and depending on the search terms make a selection from two tables. There is a small problem:
a) fragmented Data
b) the System will work so that identifying the format of the input data, will record them in the appropriate table, in this case there is some probability of an erroneous determination, because it is not the fact that "333555" - is the amount of something, not a phone number or something else other than this amount.Option 2.1
We store all the data in one table, format VARCHAR, and overhead sign do discharge number, for example:
SELECT field1::integer FROM table1 WHERE field1 ~ E'^\\d+$' AND field1::integer > 3;
in this embodiment confuses me reguraly... although She is very small, but still the regular season.The version 2.2
We add another field-flag (number -> true/false) which will determine what is stored in this string, a number or a string. Accordingly, the search for rows works in a regular mode, and search by numbers works is not in the format of a regular expression, and based on the flags.
In this version I don't like the additional entity and the additional logic, but we already got rid of the regular (though very small).
Please tell me which option is better, how much better and why? What are the objective pros and cons of each approach be? How slow primitive of the regular season will slow down the system and/or how they are better/worse variants with an extra field?
P. S. I understand that you can "take a check", but as having no ideas about how and why the database will behave in different situations and not being able to simulate such situations on different hardware with a different set (volume) of data - I would like to hear the opinion of someone who understands how it works logically.