CODEWITHSUNDEEP
postgresql
aquitted-mind
aquitted-mind

Reputation: 303

Replacing a comma seperate value in table with another in select query (postgres)

I have two tables, table A has ID column whose values are comma separated, each of those ID value has a representation in table B.

   Table A
+-----------------+
| Name      | ID   |
+------------------
| A1        | 1,2,3|
| A2        | 2    |
| A3        | 3,2  |
+------------------

   Table B
+-------------------+
| ID        | Value |
+-------------------+
| 1        | Apple  |
| 2        | Orange |
| 3        | Mango  |
+-------------------+

I was wondering if there is an efficient way to do a select where the result would as below,

  Name, Value
  A1    Apple, Orange, Mango
  A2    Orange
  A3    Mango, Orange

Any suggestions would be welcome. Thanks.

Upvotes: 0

Views: 949

Answers (2)

user330315
user330315

Reputation:

You need to first "normalize" table_a into a new table using the following:

select name, regexp_split_to_table(id, ',')  id
from table_a;

The result of this can be joined to table_b and the result of the join then needs to be grouped in order to get the comma separated list of the names:

select a.name, string_agg(b.value, ',')
from (
  select name, regexp_split_to_table(id, ',')  id
  from table_a
) a
  JOIN table_b b on b.id = a.id
group by a.name;

SQLFiddle: http://sqlfiddle.com/#!12/77fdf/1

Upvotes: 3

Denis de Bernardy
Denis de Bernardy

Reputation: 78463

There are two regex related functions that can be useful:

http://www.postgresql.org/docs/current/static/functions-string.html

  • regexp_split_to_table()
  • regexp_split_to_array()

Code below is untested, but you'd use something like it to match A and B:

select name, value
from A
join B on B.id = ANY(regexp_split_to_array(A.id, E'\\s*,\\s*', 'g')::int[]))

You can then use array_agg(value), grouping by name, and format using array_to_string().

Two notes, though:

  1. It won't be as efficient as normalizing things.
  2. The formatting itself ought to be done further down, in your views.

Upvotes: 0

Related Questions