CODEWITHSUNDEEP
postgresql
add-semi-colons
add-semi-colons

Reputation: 18800

Postgres match data in string array

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?

Upvotes: 0

Views: 1028

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324325

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;

Upvotes: 2

Related Questions