0

I have a direct_selector column

        direct_selector                
----------------------------------------------
 a:2:{i:0;s:7:"transit";i:1;s:9:"departure";}
 a:1:{i:0;s:9:"departure";}
 a:1:{i:0;s:7:"arrival";}
select * 
from disc_agent 
where direct_selector = ANY('{"departure"}');

This query gives me nothing
When I try

select * 
from disc_agent 
where '"departure"' = ANY(direct_selector);

It gives me an error

ERROR: op ANY/ALL (array) requires array on right side

So what I should do to check if value exists?

  • I work in symfony and in entity it has an array type, and I added values in array. And yes this column defined as text but inside is array – Максим Якубенко Jan 3 at 16:28
  • the data appears to be encoded using php serialize, – Jasen Jan 3 at 20:56
0

The problem is is that this,

a:2:{i:0;s:7:"transit";i:1;s:9:"departure";}
a:1:{i:0;s:9:"departure";}
a:1:{i:0;s:7:"arrival";}

is text without schema and doesn't mean anything to anyone, except maybe you. ;) The questions you need to ask right away that seem apparent to me (and a_horse_with_no_name) is if they all follow an a followed by a number, followed by a series of i/s/n pairs.

If that's the case, you probably want to make a schema something like this..

CREATE TABLE a_table ( a_id int PRIMARY KEY );
CREATE TABLE action ( a_id int REFERENCES a_table, i int, s int, desc text );

A database isn't going to be useful set up the way you've got it now (without relations). If you set it up like the above your query becomes

SELECT *
FROM a_table
JOIN action USING (a_id)
WHERE action.desc = 'DEPARTURE';

Consider checking out a book on a normalization.

1

That is not an "array column". The contents of a real array (or even JSON) would be displayed in a different way. I guess that's just defined as text or varchar.

It's not a valid syntax for an array so you can't use any of Postgres' array functions and it's not valid JSON either so you can't use any of Postgres JSON functions. As this is plain text, the only thing you can use is LIKE or regex comparisons.

Something like:

where direct_selector LIKE '%"departure"%'; 
-- or
WHERE direct_selector LIKE '%"departure"%' AND direct_selector LIKE '%"transit"%'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.