1

I have a column with type Array, I need to get all the elements in the array, I tried:

SELECT managers_ids::INTEGER[] FROM teams WHERE id = 14

the result is '{8,9,10}'. How can I iterate on the results when it's returned as a string?

Any ideas?

  • I'm unsure what you want. Show so you have an array {8,9,14} you want to check if 14 exists and when you find out it doesn't you want it to return nothing? Can you write an example? – Evan Carroll Mar 15 '17 at 15:37
  • The problem that when i try to handle it in php, php see it as string not as array so i could not collect the ids from it – Mohammad AL-Raoosh Mar 15 '17 at 15:43
  • What is the data type of the manager_ids column? – a_horse_with_no_name Mar 15 '17 at 15:48
  • The type is INTEGER[] – Mohammad AL-Raoosh Mar 15 '17 at 15:49
  • 1
    If the type of managers_ids is INTEGER[], then the cast in your SELECT is redundant noise. – Erwin Brandstetter Mar 15 '17 at 18:57
1

Using PHP with SQL Arrays

I would highly suggest never using PHP. However, when you can't avoid it you may find this post useful on reading PostgreSQL arrays as PHP Arrays. People have been complaining about this for 15 years now. PHP doesn't migrate SQL arrays into native PHP array structures.

Getting around the problem with JSON

The new method of working around PHP's long-standing inadequacies is to use JSON to move the array.

SELECT array_to_json(pg_array_result) AS new_name FROM tbl1;
$array = json_decode($returned_field);

Attribution: shamefully stolen from michaelbn's answer on StackExchange..

Getting around the problem with unnest

You have another option to use unnest and return a result set which PHP can handle.

SELECT * FROM unnest(ARRAY[1,2,3]) AS t(x);
 x 
---
 1
 2
 3
(3 rows)
0

Based on official documentation. You can select from an array in the same way you normally access a multidimensional data object.

https://www.postgresql.org/docs/current/static/arrays.html

Specifically review section 8.14.3. Accessing Arrays

Keep in mind in Postgres, arrays begin at 1 not 0. In your example, the following query:

SELECT managers_ids[2] FROM teams WHERE id = 14

Would yield:

{9}

You could then cast this to an integer.

  • Thanks, but we need to read all the array, we actually did so, but we could not iterate through the result, as we got a string, while we expected to have an array as a result.. – Mohammad AL-Raoosh Mar 15 '17 at 13:21
  • 1
    managers_ids[2] already returns integer, so 9, not {9}. No cast needed. Also, is this an answer to the question? – Erwin Brandstetter Mar 15 '17 at 18:52

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.