sql or postgresql to query table column with comma separator

Question

PostgreSQL table1

id | columnA             | columnB
---+---------------------+--------
1  | A 01,A 02,A 03,A 04 | FALSE
2  | A 01,A 02           | FALSE
3  | A 01,A 02,A 03,A 04 | TRUE 
4  | A 01,A 02           | TRUE

I used to use below query to find out how many records:

Query 1:

SELECT *
FROM DB1.table1
WHERE columnA LIKE 'A 04,%'
  OR columnA LIKE '%,A 04,%'
  OR columnA LIKE '%,A 04'
  AND columnB = 'false'

Query 2:

SELECT *
FROM DB1.table1
WHERE columnA LIKE 'A 02,%'
  OR columnA LIKE '%,A 02,%'
  OR columnA LIKE '%,A 02'
  AND columnB = 'false'

Is there better way to just have one query to find which column has 'A 02' or 'A 04' and 'FALSE'?

Tough luck, comma separated data... Will cause you lots of trouble. — jarlh
– jarlh, Commented Sep 19, 2019 at 9:49

Belayer · Accepted Answer · 2019-09-19 19:10:18Z

1

Your initial posted query indicates you're searching for text at the beginning of the string (A 04,), at the end of the string (,A 04) or in the middle of the string (,A 04,). These can simply be reduced to anywhere in the string or simply (A 04). Combining with the (A 02) predicate value and the columnB predicate we get

select *
  from db1.table1
 where not columnB
   and (columnA like '%A 02%' or columnA like '%A 02%');

But I reiterate having multiple values in the same column, or even same data in separate columns with in a row is very bad design. Ther should be in sepreate rows.

edited Sep 19, 2019 at 19:10

answered Sep 19, 2019 at 19:02

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

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

1 Comment

404 Over a year ago

This will fail real quick with higher values, e.g. BA 02 and A 021. The array or regex versions are more robust.

S-Man · Accepted Answer · 2019-09-19 09:55:07Z

1

demo:db<>fiddle

SELECT *
FROM
    mytable
WHERE
    columnB = false AND ARRAY['A 02', 'A 04', 'clinic 02'] && string_to_array(columnA, ',')

Create an array out of your interesting values: ARRAY['value1','value2']
Convert your string list into an array: string_to_array(list, ',')
Use the && comparator to check if both arrays overlap, meaning if both arrays share at least one element.

9 Comments

404 Over a year ago

Alternative would be WHERE columnB = false AND columnA ~ ,?A 0[24],?, not sure how regex performance compares to creating an array for every record.

S-Man Over a year ago

@404 Very nice idea! In the fiddle engine the query planner show not very different cost results: dbfiddle.uk/… But it's definitely worth a try with the real data.

user2201789 Over a year ago

What could be the problem? When query? Best approach to store multiple value?

S-Man Over a year ago

Hard to query has you can see here: You always need to split the value. Better approach is taking a simple array instead of a concatenated string. Best approach is to store every value in its own row. In that case you could simply use WHERE to filter. Furthermore indexes can be appended. And if you need the concatenated string you simply do a GROUP BY and string_agg()

user2201789 Over a year ago

Store every value in its own row. The application behave as one row per unique id only. One unique id has many values. Unless business logic changed, otherwise I couldn't see how designer can change the model. Make many columns not right too. As the values can be up to 50 different values .

|

Gordon Linoff · Accepted Answer · 2019-09-19 11:05:29Z

1

You should fix the data model! Storing multiple values in a single column is very bad -- and Postgres has many better alternatives than strings. That said, sometimes we are stuck with other people's really, really bad decisions.

You can use like for this purpose:

SELECT *
FROM DB1.table1
WHERE ',' || columnA || ',' ~ ',A 02,|,A 04,' AND
      NOT columnB   -- assuming this is boolean

For a single value, you would use:

WHERE ',' || columnA || ',' LIKE '%,A 02,%' AND
      NOT columnB   -- assuming this is boolean

answered Sep 19, 2019 at 11:05

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

2 Comments

user2201789 Over a year ago

What could be the potential problems? What is the best approaches if not store it in one column? Create many column does not sounds right.

Gordon Linoff Over a year ago

@user2201789 . . . The values should be stored in separate rows of a junction/association table.

Collectives™ on Stack Overflow

sql or postgresql to query table column with comma separator

3 Answers 3

1 Comment

9 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

9 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related