Postgresql Query to split the array into rows

Question

I am running a query which is creating a view for me with following details

id      name       brand_id
1        E1          {3,4}
2        E2          {5,7,8}
3        E4          {1}

I want to split the records for brand_id into equal number of rows. Hence the above view should look like:

id      name       brand_id
1        E1          {3}
1        E1          {4}
2        E2          {5}
2        E2          {7}
2        E2          {8}
3        E4          {1}

Over here the brand_id is calculated from a subquery by matching the creation date of record with the date of brand

SQL Query:

CREATE OR REPLACE VIEW %I AS 
    SELECT row_number() over(),
    id,
    name,              
    (select array(select id
                  from brand b
                  where status = true and (i.creation_date = b.creation_date)
                  order by b asc) ) as brand_id
  FROM events i
  group by id order by id

Patrick · Accepted Answer · 2016-12-12 07:29:33Z

1

The simplest is probably:

CREATE VIEW some_name AS
    SELECT i.id, i.name, b.id AS brand_id
    FROM events i
    JOIN brand b USING (creation_date)
    WHERE b.status
    ORDER BY 1;

answered Dec 12, 2016 at 7:29

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

3 Comments

Vivek S. Over a year ago

Did you miss b.status=true ?

Patrick Over a year ago

@wingedpanther status is obviously a boolean so just the column name suffices. The WHERE clause needs an expression that evaluates to a boolean, a boolean column will do just fine.

Vivek S. Over a year ago

I did not know that, How embarrassing !!

jhinghaus · Accepted Answer · 2016-12-12 07:54:42Z

0

select id, name, unnest(brand_id) from events;

answered Dec 12, 2016 at 7:54

jhinghaus

86511 silver badges28 bronze badges

Comments

Vivek S. · Accepted Answer · 2016-12-12 12:01:25Z

0

Please use below query to extract data.

select id  
      ,name 
      ,'{'||replace(replace(regexp_split_to_table(brand_id, E','),'}',''),'{','')||'}'  
from umang.t_st_ques  
order by 1,2,3;

edited Dec 12, 2016 at 12:01

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

answered Dec 12, 2016 at 10:56

Riya Bansal

1,3111 gold badge11 silver badges10 bronze badges

Collectives™ on Stack Overflow

Postgresql Query to split the array into rows

3 Answers 3

3 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Linked

Related