CODEWITHSUNDEEP
postgresqlequivalentfind-in-set
selva
selva

Reputation: 91

mysql FIND_IN_SET equivalent to postgresql

select * 
from folder f,uploads u 
where u.id=f.folderId 
and FIND_IN_SET('8', '15,9,13,27')

Please tell to me equivalent to predefind or userdefined postgresql function

Upvotes: 5

Views: 17176

Answers (3)

user330315
user330315

Reputation:

You shouldn't be storing comma separated values in the first place, but you can do something like this:

select * 
from folder f
  join uploads u ON u.id = f.folderId 
where '8' = ANY (string_to_array(some_column,','))

string_to_array() converts a string into a real array based on the passed delimiter

Upvotes: 17

Patrick
Patrick

Reputation: 32264

The FIND_IN_SET() function in MySQL applies - not surprisingly - to sets. The equivalent of a MySQL SET in PostgreSQL is the enum type, with some minor differences in implementation.

The FIND_IN_SET() function returns the index of an item in the set, or 0 if not present in the set. That is logically non-sensical: "a set is an abstract data type that can store certain values, without any particular order, and no repeated values". PostgreSQL has no built-in way to find the order of an item in an enum type, it doesn't even have a way to find out if a string is also an item in an enum type. And that is just how it should be.

If you are working with "sets" of strings in a less restricted sense, you probably want to use a text[] data type for your column. Your query then becomes, assuming you test just for the presence of a value in the array:

SELECT * 
FROM folder f
JOIN uploads u ON u.id = f.folderId 
WHERE '8' = ANY (text_array_column);

If you want the specific index of '8' in the text array column you should specify in your question what you want to do with it; with the current information a better answer is impossible.

Upvotes: 1

Mesbah Gueffaf
Mesbah Gueffaf

Reputation: 558

    select * 
       from folder f,uploads u 
          where u.id=f.folderId and '8' in('15','9','13','27')

Upvotes: 0

Related Questions