mysql FIND_IN_SET equivalent to postgresql

Question

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

[This](http://stackoverflow.com/a/6363756/477878) may work (and actually be faily efficient) — Joachim Isaksson, Feb 03 '16 at 05:51
i am giving example only the actual query: select * from folder f,uploads u where u.id=f.folderId and FIND_IN_SET('8', another_column); This is mysql query. I want only for postgresql. Is there any function available. — selva, Feb 03 '16 at 06:00
What data type is this `another_column`? Please edit your question and show the definition of the table (as `create table`) , some sample data and the expected output — , Feb 03 '16 at 06:39
FIND_IN_SET(int, text) datatype, another_column is text datatype — selva, Feb 03 '16 at 07:04
The PostgreSQL way would be to not use the CSV-in-a-column anti-pattern at all, if you need all those values in a single column then you'd use an array (probably `int[]`) and `= any(...)` or other array operations. — mu is too short, Feb 03 '16 at 07:09

score 17 · Answer 1 · answered Feb 03 '16 at 08:16

17

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

answered Feb 03 '16 at 08:16

@Mayhem: there are some best practices for designing a relational database. Normalization is one of them. And storing only atomic values is the first step in normalization. Storing (comma) delimited values in a column violates this most basic principle. If you want to ignore 30 years of design guidelines, then by all means do it. – Jan 09 '18 at 06:51
1

Again it depends on the dataset.. seeing one row or a extract does not show their full dataset. There are many cases where having a column per item can be wrong.. specifically if the amount of csv is an unknown length... if some rows have 10 columns and in the rare case it could be 100 or 1000.. would you really have a table with 1000 columns? When 99% of rows are only 10 items.. never assume... if you think boxed.. you end up being in a box.. anyway the point stands that real world data does not always follow rules in paper.. and storing a string of csv in a single field does not violate it. – Angry 84 Jan 11 '18 at 07:29
It seems that in Postgres ANY does not respect the order of elements: https://stackoverflow.com/questions/67345219/is-there-a-way-to-preserve-order-or-array-when-using-any-in-postgres-query unlike MySQL's `FIND_IN_SET`: https://stackoverflow.com/questions/2813884/how-do-you-keep-the-order-using-select-where-in – Ciro Santilli OurBigBook.com May 14 '22 at 06:57
@CiroSantilliПутлерКапут六四事: not sure what you mean with "preserve order". The `ANY` operator just tests if the value on the left hand side occurs *any*where in the array on the right hand side. The query has no `order by` so there is no implied sort order in the result. – May 14 '22 at 07:23
https://stackoverflow.com/questions/2813884/how-do-you-keep-the-order-using-select-where-in suggests that mysql FIND_IN_SET returns rows in the order of the arguments given, so it seems that postgres ANY is slightly different from mysql FIND_IN_SET in that sense. – Ciro Santilli OurBigBook.com May 14 '22 at 07:45
If a query does not have an `ORDER BY`, then the database is free to return the rows in any order it likes. _If_ there is a certain order you see without an ORDER BY this is a coincidence. The **only** way to get a *guaranteed* sort order, is to use ORDER BY. – May 14 '22 at 07:51

score 1 · Answer 2 · answered Feb 03 '16 at 08:07

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.

Actually `find_in_set` applies to string values that contain a comma separated list of values. It has nothing to do with enums — , Feb 05 '16 at 06:48

Mesbah Gueffaf · Answer 3 · 2016-02-03T07:08:31.860

0

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

edited Feb 03 '16 at 07:08

answered Feb 03 '16 at 07:00

Mesbah Gueffaf

518
1
7
21

mysql FIND_IN_SET equivalent to postgresql

3 Answers3