I am creating a table in PostgreSQL and looking for a best approach that I should follow.
One of my fields are mostly consists of integer values. But really mostly.
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20 and over
Last one, 20 and over, kills everything. If it wouldn’t be there I am going to create that table with integer field.
Should I create the field in
VARCHAR(11) type, just because of the 1% string field? Or is there any other method to solve this issue smoothly?
Apparently you want to store some kind of range where most of the ranges just contain a single element and one of them is open ended.
You did not explain how you want to use those values.
If you only use them for display purposes and never, ever under no circumstance (ever!) have to do any calculations on them, a
text column might be enough.
However, I have never encountered a situation where numbers weren’t intended to be treated as numbers in the long run.
Depending on how you use the values, I can think of two solutions: either use a range type where most ranges only contain a single element. Whether or not that makes sense depends on the way you use that value. Maybe the 20 single values actually mean a range from 1 to 20 – it’s hard to tell from your question
Or use a two columns: one integer and one indicating the “and above“. If you only have the choice between “exactly that value” and “that value and above”, I would probably use a
boolean column that indicates that the value is the lower end of a range.
I would strongly advise you to use the most appropriate data type possible – if the data is integers, then use an integer type!
You want PostgreSQL’s
This code will generate as many random integers between 0 and 20 as you want.
SELECT (20 * random())::INTEGER AS rand FROM generate_series(1, 10);
rand 0 15 2 10 14 14 10 0 18 18
See the fiddle here! You can vary the second parameter to whatever value suits you!
To generate random text, you can use this code:
SELECT MD5(random()::TEXT) FROM GENERATE_SERIES(1, 5);
md5 de5161e6ed7492d046d02bfc5c166c86 a8818e4e472c4a8a923abf2c9e97a70c 398300e6a10e4c493b64403adeeac3c7 bf548d3db7763e11ee05e41ab0f2b3d9 c914d60c26a5c75fa794ef119ed70c10
To generate text only, you can use the
REPLACE and/or the
TRANSLATE functions to change the MD5 numeric data to the empty string. Lots of good “tricks” to be found here, here and here.
The best though would be the
REGEXP_REPLACE function. You could do something like this:
WITH cte AS ( SELECT MD5(random()::TEXT) AS rand FROM GENERATE_SERIES(1, 5) ) SELECT REGEXP_REPLACE(rand,'[[:digit:]]','','g') AS letters FROM cte
letters addebafaeb ccdfaddbdccadc debdbdfabefbeff debdaecabef ebfbaeefdbc
p.s. welcome to the forum!