1

I have postgresql table that looks like this:

+----+---------------------+
| id |        names        |
+----+---------------------+
|  1 | foo|bar and biz|pop |
+----+---------------------+

I want to select row containing given name. Something like

SELECT "id" FROM "table" WHERE "names" LIKE '%foo%';
 id
-----
1
(1 row)

I want the query to return this row as well if I ask for bar and biz but return nothing if I ask from bar.

For now I'm adding pipe symbols to the beginning and the end of the line and ask LIKE '%|bar and biz|%'. Anyway, I wonder is there a way to find that row without additional pipes.

Is there a way to do such query in postgresql?

UPD: It seems like I explain my problem bad. Well, I want following:

SELECT "id" FROM "table" WHERE "names" LIKE '%bar and biz%';
 id
-----
1
(1 row)

and

SELECT "id" FROM "table" WHERE "names" LIKE '%bar%';
 id
-----
(0 rows)
Boris Zagoruiko
  • 12,705
  • 15
  • 47
  • 79

5 Answers5

2

First, storing multiple values in a single column is a bad idea:

  • SQL is not very good at string operations.
  • Such operations cannot make use of indexes.
  • You cannot use foreign key relationships to validate values.

Instead, you should be using a junction table. Postgres also has other solutions for storing lists, such as arrays and JSON.

Sometimes, we are stuck with other people's bad design decisions. One method using like is:

SELECT "id"
FROM "table"
WHERE '|' || "names" || '|' LIKE '%|bar|%';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • To be honest this solution is mine and I've created this column just a moment ago. I tried to created something as simple as I can to keep aliases in table but you're right, the discussion here and your sql snippet point me that my idea was completely wrong. I better rewrite this to be array or something. Thank you for knowledge sharing. – Boris Zagoruiko Apr 17 '16 at 12:28
2

While stuck with your unfortunate design, convert to an array and use the ANY construct:

SELECT id
FROM   table
WHERE  'bar' = ANY (string_to_array(names, '|'));

About ANY, @>, arrays and indexes:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

Did you try the following?

where "names" = 'foo' or
      "names" LIKE 'foo|%' or 
      "names" LIKE '%|foo' or
      "names" LIKE '%|foo|%'
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
1

Since your column is already separated with '|' you could cast it to an array an use the contains to get your desired result

SELECT "id"
FROM "table"
WHERE string_to_array(names, '|')::varchar[] @> '{bar}'::varchar[];
Alex
  • 865
  • 13
  • 24
0

I just checked in my system and it is working fine for me. First of all i don't understand why you have double quotes in column name. It's not recommended.

My table schema

CREATE TABLE table (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  names varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And to fetch data, i used this

SELECT id FROM table WHERE names LIKE '%foo%' or names LIKE '%bar and biz%';

Update:-

SELECT * FROM A WHERE names LIKE '%bar%';

enter image description here

Remove double quotes from coulmn names and table names.

Naruto
  • 4,221
  • 1
  • 21
  • 32
  • Seems like I explain my problem bad. Pls, check out update in the question. Btw I use double quotes because I have camel-case named columns and postresql automatically converts everything to lowercase in case there are no double quotes around it. – Boris Zagoruiko Apr 17 '16 at 12:13