I have a postgres table where the column type is text but it has data which looks like in an array:
["97aa63c0-c562-11e3-b216-000180810a27","33fff220-b6be-11e3-be89-000180810a27"]
My first question is what is this data structure?
How can I match in a query: For Example:
If the table name id X and column name where data exist is products
Select * from X where product='97aa63c0-c562-11e3-b216-000180810a27'
How can I do something like that?
That looks like a json or python array literal containing uuid values.
There are many reasons not to store data like this but it sounds like you didn't design the DB.
In modern PostgreSQL I'd parse it as json. In older versions I'd use string functions to convert it. In 9.4 this will work:
WITH arr(uuids) AS (
VALUES('["97aa63c0-c562-11e3-b216-000180810a27","33fff220-b6be-11e3-be89-000180810a27"]')
)
SELECT
elem::uuid
FROM arr, json_array_elements_text(
uuids::json
) elem;
but 9.3 doesn't have json_array_elements_text so you have to write:
WITH arr(uuids) AS (VALUES('["97aa63c0-c562-11e3-b216-000180810a27","33fff220-b6be-11e3-be89-000180810a27"]'))
SELECT json_array_element(uuids::json, ss-1) FROM arr, generate_series( 1, json_array_length(uuids::json) ) ss;
and in still older versions:
WITH arr(uuids) AS (VALUES('["97aa63c0-c562-11e3-b216-000180810a27","33fff220-b6be-11e3-be89-000180810a27"]'))
SELECT left(right(elem,-1),-1)::uuid FROM arr, unnest(string_to_array(left(right(uuids, -1),-1), ',')) elem;
producthas comma separated values like97aa63c0-c562-11e3-b216-000180810a27,33fff220-b6be-11e3-be89-000180810a27? that's a bad DB design. Try normalizing your table and don't store comma separated values.=will not help. try usingLIKEinstead sayingwhere product='%97aa63c0-c562-11e3-b216-000180810a27%'TEXTtype column and not array type but not sure though.