How to create a table with a column of values from 1 to 10?

Question

I'd like to create the following table using SQL and Redshift, but I'm unsure how to go about creating this sort of table from scratch

I've tried the following query based on this question

SELECT ones.n + 10*tens.n + 1000
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
     (VALUES(0),(1),(2),(3),(4),(5)                ) tens(n)
WHERE ones.n + 10*tens.n + 1000 BETWEEN 0 AND 10

But I get the following error:

syntax error at or near ","
  Position: 52
SELECT ones.n + 10*tens.n + 1000
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
               ^
     (VALUES(0),(1),(2),(3),(4),(5)                ) tens(n)

it is because of the [unsupported features of redshift](http://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-features.html) — Vamsi Prabhala, Oct 11 '16 at 20:33
Are you wanting to actually store the results in a table, or are you generating a table purely for use within a query? The former works with `generate_series()`, the latter does not. — John Rotenstein, Oct 12 '16 at 02:13

score 1 · Answer 1 · answered Oct 11 '16 at 20:54

I'm not sure what you are ultimately trying to do, but you can always just do an inline view of union'd values if you need a single SELECT statement:

SELECT t.Number
FROM
(
    SELECT 0 AS Number UNION
    SELECT 1 AS Number UNION
    SELECT 2 AS Number UNION
    SELECT 3 AS Number UNION
    SELECT 4 AS Number UNION
    SELECT 5 AS Number UNION
    SELECT 6 AS Number UNION
    SELECT 7 AS Number UNION
    SELECT 8 AS Number
) AS t

score 1 · Answer 2 · answered Jul 27 '17 at 13:03

According to the official documentation from amazon, VALUES list used as constant tables are not supported on redshift.

As a workaround, you could either create your own, temporary table and insert the ten numbers, or use something like this:

SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ...

Hint: Probably you can even generate large number tables, by using the known approach:

SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ...) ones(n),
     (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ...) tens(n),
     (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ...) hundreds(n),
     (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ...) thousands(n)
ORDER BY 1

score 0 · Answer 3 · answered Oct 11 '16 at 20:35

0

Why not create a table and then insert the data into it?

Create table t1(value int);
insert into t1 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

answered Oct 11 '16 at 20:35

buqing

925
8
25

How to create a table with a column of values from 1 to 10?

3 Answers3