CODEWITHSUNDEEP
sqldatabasepostgresql
Annamat
Annamat

Reputation: 11

"Concatenate" PostgreSQL ints from select

I have a table Digits that contains INTs from 0 to 9. What i want to do is to join in three or more times and make this values to be a value like 0,...,124,125,...998,999;

I'm doing something like this:

SELECT *
FROM Digits AS Fir JOIN
     Digits AS Sec
     ON TRUE JOIN
     Digits AS Thi
     ON TRUE;

But what i get is:
--+ --+---
0 | 0 | 0
0 | 0 | 1
0 | 0 | 2
0 | 0 | 3
0 | 0 | 4
0 | 0 | 5
0 | 0 | 6
0 | 0 | 7
0 | 0 | 8
0 | 0 | 9
0 | 1 | 0
0 | 1 | 1
0 | 1 | 2
.
.
.
.
9 | 9 | 9

Any ideas how can I convert it to get:
0
1
2
3
.
.
.
999

Greetings.

Upvotes: 1

Views: 88

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I would just use generate_series():

select val
from generate_series(0, 999) gs(val);

Upvotes: 1

Mureinik
Mureinik

Reputation: 312219

You could explicitly multiply each digit by its position:

SELECT     100 * fir.digit + 10 * sec.digit + thi.digit
FROM       digits AS fir 
CROSS JOIN digits AS sec 
CROSS JOIN digits AS thi

(but in all fairness, Gordon's solution is neater.)

Upvotes: 0

Related Questions