PostgreSQL part of a string is in an array

Question

I am trying to get values for which part of their ids is in a defined list. Let's say that we have a table called ABC

CREATE TABLE abc
AS
  SELECT post_id
  FROM ( VALUES 
    ( '868164246578472_912876412107255' ),
    ( '868164246578472_912883258773237' ),
    ( '868164246578472_913049595423270' )
  ) AS t(post_id);

Then I just take a part after the underscore

select (regexp_split_to_array(element_id, '_'))[2] as element_id from ABC limit 3;
        element_id     
    -----------------
     912876412107255
     912883258773237
     913049595423270

Now I want to take only those elements, where their element_ids are in a defined list yet I get no results

select (regexp_split_to_array(post_id, '_'))[2] as post_id from ABC where post_id = ANY('{912876412107255, 912883258773237}'::text[]) limit 3;
 post_id 
---------
(0 rows)

I also tried this:

select (regexp_split_to_array(post_id, '_'))[2]::text[] as post_id from ABC where post_id IN ('912876412107255', '912876412107255') limit 3;
 post_id 
---------
(0 rows)

The structure of the table is as follows:

Table "public.ABC"
    Column     |            Type             |                      Modifiers                       
---------------+-----------------------------+------------------------------------------------------
 id            | integer                     | not null default nextval('ABC_id_seq'::regclass)
 element_id    | text                        | not null

Why are you using the column alias in the where clause? (did not think that was allowed) Also, why are you putting the selected expression into an array when there will only be 1 element. — Joe Love
– Joe Love, Commented Apr 13, 2017 at 20:44
@JoeLove: it is not allowed. And this why it fails for Godric. — kmkaplan
– kmkaplan, Commented Apr 13, 2017 at 20:46
Yes, thank you. I figured it out and immediately posted the answer — Godric
– Godric, Commented Apr 13, 2017 at 20:49

klin · Accepted Answer · 2017-04-14 05:31:31Z

2

Use the function string_to_array() which is much cheaper than the regex function.

You should use the expression in WHERE clause:

select (string_to_array(post_id, '_'))[2] as post_id
from abc
where (string_to_array(post_id, '_'))[2] = any('{912876412107255, 912883258773237}');

or a derived table:

select post_id
from (
    select (string_to_array(post_id, '_'))[2] as post_id
    from abc
    ) s
where post_id = any('{912876412107255, 912883258773237}');

A derived table does not generate additional costs, the queries are equivalent.

Update. The function split_part() even better suits your query:

select split_part(post_id, '_', 2) as post_id
from abc
where split_part(post_id, '_', 2) = any('{912876412107255, 912883258773237}');

edited Apr 14, 2017 at 5:31

answered Apr 13, 2017 at 21:05

klin

123k15 gold badges240 silver badges262 bronze badges

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

Comments

kmkaplan · Accepted Answer · 2017-04-13 21:00:14Z

0

Untested (from my phone):

SELECT kmkid, element_id
    FROM (SELECT (regexp_split_to_array(element_id, '_'))[2] as kmkid, element_id FROM ABC)
    WHERE kmkid IN ('912876412107255', '912876412107255');

answered Apr 13, 2017 at 21:00

kmkaplan

19k4 gold badges55 silver badges65 bronze badges

Comments

Evan Carroll · Accepted Answer · 2017-04-14 06:52:36Z

0

As a quick note, the problem here is that you have two values serialized inside the same field. This is bad. If you're doing this it's because those values are different.

What you should do instead is break them apart, or if they are a list store them as an array.

ALTER TABLE abc
  ALTER COLUMN post_Id
  SET DATA TYPE numeric[] USING ( string_to_array(post_Id, '_')::numeric[] );

Now, you can query on foo directly if any of those fields are equal

SELECT * FROM abc
WHERE post_id @> ARRAY[912876412107255::numeric];

Or if one of them is

SELECT * FROM abc
WHERE post_id[2] = 912876412107255::numeric;

edited Apr 14, 2017 at 6:52

answered Apr 14, 2017 at 5:02

Evan Carroll

1

Comments

Godric · Accepted Answer · 2017-04-13 20:41:27Z

-1

OK, I've just found the answer:

select (regexp_split_to_array(element_id, '_'))[2] as element_id from ABC where element_id similar to '%(912876412107255|912883258773237)%';
     element_id     
-----------------
 912876412107255
 912883258773237
(2 rows)

answered Apr 13, 2017 at 20:41

Godric

1091 silver badge7 bronze badges

3 Comments

kmkaplan Over a year ago

Add a _ and remove the trailing % to get less spurious matches. You can still get some if some element_id happen to be the prefix of the ones you want. An ugly solution to sum it up.

klin Over a year ago

Avoid regex functions when they are not necessary. Simple string manipulation functions are cheaper (faster).

Evan Carroll Over a year ago

Also the whole SIMILAR TO is stupid. It's always slower than a regex. And, you know what the entire ID is. So why use % after you've split it out?

Collectives™ on Stack Overflow

PostgreSQL part of a string is in an array

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related