PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to create column and rows using select statement CTE?

I’m able to create the table using CTE with select statement like below but it’s not behaving like I want.

with sorting(item_code, index) as (
 select (10001, 1), (10002, 2)
)
select * from sorting

Table result :

item_code|index    |
---------+---------+
(10001,1)|(10002,2)|

Basically I want to create table with value like this :

item_code | index
10001     | 1
10002     | 2

How could I do that using SQL?

Tags:

2

Answers


  1. 0

    you can try this:

    with sorting(item_code, index) as (
    select 10001 as item_code, 1 as index
    union all
    select 10002 as item_code, 2 as index
)
select * from sorting
    Login or Signup to reply.
  2. 0

    This is most probably what you want using the values clause

    with sorting as (
select * from (values
(10001, 1), (10002, 2)
) sorting(item_code, index)
)
select * from sorting

item_code|index|
---------+-----+
    10001|    1|
    10002|    2|
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search