CODEWITHSUNDEEP
sqlpostgresql
Godric
Godric

Reputation: 109

PostgreSQL part of a string is in an array

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

Upvotes: 2

Views: 2303

Answers (4)

Evan Carroll
Evan Carroll

Reputation: 1

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;

Upvotes: 0

klin
klin

Reputation: 121504

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}');

Upvotes: 2

kmkaplan
kmkaplan

Reputation: 18960

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');

Upvotes: 0

Godric
Godric

Reputation: 109

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)

Upvotes: -1

Related Questions