Question 89927 in PostgreSQL Question posted in
The official documentation can be found here.

Select rows from table with specific string value endings from list – Postgresql

I have a table with two columns item_name, value where item_names looks like "abracadabra_prefix.tag_name". And I need to select rows with tag_names from a list that doesn’t have a prefix.

Should be somthing like:

tag_names = ['f1', 'k500', '23_g']

SELECT * FROM table WHERE item_name IN (LIKE "%{tag_names});

input table:

item_name value
fasdaf.f1 1
asdfe.f2 2
eywvs.24_g 2
asdfe.l500 2
asdfe.k500 2
eywvs.23_g 2

output table:

item_name value
fasdaf.f1 1
asdfe.k500 2
eywvs.23_g 2

I have tried concatenating a string in a loop to get a query like this:

SELECT * FROM table WHERE item_name LIKE '%f1' OR item_name LIKE '%k500' OR item_name LIKE '%23_g';

But I can have from 1 to 200 tags, and with a large number of tags, this makes the query too complicated,as I understand it.

Tags:

2

Answers


  1. 0

    You can use:

    • UNNEST to extract tag values from your array,
    • CROSS JOIN to associate tag value to each row of your table
    • LIKE to make a comparison between your item_name and your tag
    SELECT item_name, value_ 
FROM tab
CROSS JOIN UNNEST(ARRAY['f1', 'k500', '23_g']) AS tag
WHERE item_name LIKE '%' || tag || '%'

    Output:

    item_name value_
    fasdaf.f1 1
    asdfe.k500 2
    eywvs.23_g 2

    Check the demo here.

    Login or Signup to reply.
  2. 0

    You can extract the suffix of item_name using substring with regexp and then use the any operator for comparison in the where clause.

    select * from the_table
where substring (item_name from '.(w+)$') = any('{f1,k500,23_g}'::text[]);

    SQL fiddle demo
    If you intend to use the query as a parameterized one then it will be convenient to replace '{f1,k500,23_g}'::text[] with string_to_array('f1,k500,23_g', ','), i.e. pass the list of suffixes as a comma-separated string. Please note that this query will result in a sequential scan.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search