How to select multiple row in postgresql?

Question

I can give a result set consisting of a single value, say 1, as follows:

SELECT 1 as column;

and it gives me the result set:

column
------
  1

But I have a list of such values represented as a string (1, 4, 7, ...) and I need to produce the following result set:

column
------
  1
  4
  7
  .
  .
  .

I tried SELECT * FROM (1, 4, 7) but it didn't work. I also tried to SELECT 1, 4, 7 but it produces the following result set:

col1   col2    col3
 1       4      7

Which was not what I was looking for.

You need to look at the String split or union

Mahesh
– Mahesh

2015-03-04 07:21:30 +00:00
Commented Mar 4, 2015 at 7:21 — Mahesh
– Mahesh, Commented Mar 4, 2015 at 7:21

user330315 · Accepted Answer · 2015-03-04 08:05:45Z

13

If those are constant values, you can use the values clause:

select * 
from (
   values (1), (4), (7)
) as t(id);

If your values are inside a string literal, you can use this:

select *
from unnest(string_to_array('1,2,3,4', ',')) as id;

answered Mar 4, 2015 at 8:05

user330315

Sign up to request clarification or add additional context in comments.

Comments

Mureinik · Accepted Answer · 2015-03-04 07:27:06Z

5

You could unnest it as an array:

SELECT UNNEST(ARRAY[1, 4, 7])

answered Mar 4, 2015 at 7:27

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:50:51Z

3

You can use the union To get what you want.But if this is the sting as 1,4,7 comma seprated then you need to use the regexp_split_to_table function. Mentioned here and here

Select 1
UNION
select 4
UNION
select 7

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered Mar 4, 2015 at 7:21

Mahesh

8,9422 gold badges37 silver badges55 bronze badges

1 Comment

user330315 Over a year ago

Your links are for SQL Server. Postgres already has everything built in to split a string into an array or even create a table of it.

Collectives™ on Stack Overflow

How to select multiple row in postgresql?

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related