Convert array to rows in Postgres

Question

If I have something like this in SQL statement ('A','B','C'), how do I convert it into a column with multiple rows like this

col
---
 A
 B
 C

I cannot change the way that string is created (as it is injected into SQL query from external program). For example, I cannot make it as ['A','B','C'] (replace with square brackets). I could wrap anything around it though like [('A','B','C')] or whatever.

Any help?

UPDATE 1

I have PostgreSQL 8.4.20

@jpmc26 No, not really. In my case, the constraint is the exact ('A','B','C') string I have to deal with. — HP.
– HP., Commented May 22, 2018 at 21:12
I retracted the close vote before you said anything, but it apparently didn't delete the comment. — jpmc26
– jpmc26, Commented May 22, 2018 at 21:14
Your version of PG has not received security updates for almost 4 years. It's extremely important that you update to a supported version; this will also make a wide range of new features available to you. — jpmc26
– jpmc26, Commented May 22, 2018 at 21:27

thibautg · Accepted Answer · 2018-05-22 22:42:20Z

5

You could create an ARRAY from VALUES and then unnest it:

SELECT 
    unnest(ARRAY[col_a, col_b, col_c]) 
FROM 
    (VALUES('A','B','C')) AS x(col_a, col_b, col_c)

Result:

| unnest |
|--------|
|      A |
|      B |
|      C |

Edit: you could also tweak jspcal's answer by using dollar quotes ($$) like this so you can concatenate your string into the SQL statement:

  SELECT * FROM regexp_split_to_table(
    regexp_replace(
      $$('A','B','C','D','foo')$$,
      '^\(''|''\)+', '', 'g'),
      ''','''
    );

edited May 22, 2018 at 22:42

answered May 22, 2018 at 21:51

thibautg

2,0521 gold badge16 silver badges18 bronze badges

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

7 Comments

HP. Over a year ago

This is very creative. I am doing more testing but look promising... :D Is it possible to have variable number of values? Like ('A','B','C','D','E') without knowing ahead of time?

thibautg Over a year ago

This solution only works for a fixed number of columns. I am still searching for a more dynamic solution.

thibautg Over a year ago

I've added a solution based on @jspcal's answer but using dollar quotes $$ to quote your string.

HP. Over a year ago

Awesome. We are getting to somewhere. I got the first row as ('A though. There is extra ('... Still monkeying around here.

Abelisto Over a year ago

@HP. select unnest(cast(translate($$( 'A','B','C','D','foo' )$$, '()''', '{}"') as text[])); dbfiddle.uk/…

|

jspcal · Accepted Answer · 2018-05-22 22:55:57Z

2

The built-in regexp_split_to_table function will do this for you. Since you plan to inject it directly without escaping, use $$ (dollar quoting) from thibautg's answer.

select * from regexp_split_to_table(
    regexp_replace($$('A','B','C')$$, '^\(''|''\)+', '', 'g'),
    ''','''
);

edited May 22, 2018 at 22:55

answered May 22, 2018 at 20:56

jspcal

52k7 gold badges76 silver badges79 bronze badges

8 Comments

HP. Over a year ago

If I do select regexp_split_to_table('A B C', E'\\s+');, it will work. But my original string is ('A','B','C'). So how to make it into 'A B C'?

jspcal Over a year ago

You could strip the extraneous chars with regexp_replace.

HP. Over a year ago

I cannot change it to (''A'',''B'',''C'') but I can make it surrounded with quotes for example "('A','B','C')"

jspcal Over a year ago

Take a look at the answer above that passes the original text to a function.

jpmc26 Over a year ago

@HP. REPLACE: REPLACE(my_incoming_var, $$'$$, $$''$$). If you have control over any code here (which you must for your question to make even 1% sense), then you can do whatever the heck you want to the string.

|

Collectives™ on Stack Overflow

Convert array to rows in Postgres

2 Answers 2

7 Comments

8 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

8 Comments

Linked

Related