How to create a table with a field 99% INT; 1% STR values?

Question

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.

Like: 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?

Answer 1

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.

Answer 2

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 generate_series

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);

Result -

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);

Result:

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

Result:

letters
addebafaeb
ccdfaddbdccadc
debdbdfabefbeff
debdaecabef
ebfbaeefdbc

p.s. welcome to the forum!