Selecting Data into a Postgres Array

PL/pgSQL SELECT into an array

Faster and simpler with a FROM clause in your UPDATE statement:

UPDATE team_prsnl p
SET    updt_dt_tm = now()
     , last_access_dt_tm = now()
FROM   tmp_team_list t
WHERE  p.team_id = t.team_id;

That aside, while operating with an array, the WHERE clause would have to be:

WHERE p.team_id = ANY (team_ids)

The IN construct works with lists or sets, not with arrays. See:

• How to use ANY instead of IN in a WHERE clause with Rails?

Selecting data into a Postgres array

You cannot use array_agg() to produce multi-dimensional arrays, at least not up to PostgreSQL 9.4.

(But the upcoming Postgres 9.5 ships a new variant of array_agg() that can!)

What you get out of @Matt Ball's query is an array of records (the_table[]).

An array can only hold elements of the same base type. You obviously have number and string types. Convert all columns (that aren't already) to text to make it work.

You can create an aggregate function for this like I demonstrated to you here before.

CREATE AGGREGATE array_agg_mult (anyarray)  (
    SFUNC     = array_cat
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
);

Call:

SELECT array_agg_mult(ARRAY[ARRAY[name, id::text, url]]) AS tbl_mult_arr
FROM   tbl;

Note the additional ARRAY[] layer to make it a multidimensional array (2-dimenstional, to be precise).

Instant demo:

WITH tbl(id, txt) AS (
    VALUES
      (1::int, 'foo'::text)
     ,(2,      'bar')
     ,(3,      '}b",') -- txt has meta-characters
    )
    , x AS (
    SELECT array_agg_mult(ARRAY[ARRAY[id::text,txt]]) AS t
    FROM   tbl
    )
SELECT *, t[1][3] AS arr_element_1_1, t[3][4] AS arr_element_3_2
FROM   x;

Postgres SELECT where value in array

Any should work, but what you are showing me for output for the table is not Life but 'Life'. See the examples below (1. correctly inserted data and what it looks like; 2. incorrectly inserted data and what it looks like-looks like yours; 3. all data):

testdb=# select * from test where 'Life' = ANY(col1);
 id |          col1           
----+-------------------------
  1 | {Life,Health,"VA Data"}
(1 row)

testdb=# select * from test where '''Life''' = ANY(col1);
 id |           col1            
----+---------------------------
  2 | {'Life',Health,"VA Data"}
(1 row)

testdb=# select * from test;
 id |           col1            
----+---------------------------
  1 | {Life,Health,"VA Data"}
  2 | {'Life',Health,"VA Data"}

How to ARRAY values from a SELECT query in POSTGRES

Use the array constructor:

DECLARE res integer[];
BEGIN
   SELECT ARRAY[c.big, c.mid, c.small] INTO res
   FROM ...
   RETURN res;
END;

Get the NOT IN values into an array in postgres SQL

You can make your query dynamic, if you make the userid as a parameter and use prepared statements.

Also, the below query can give you the list of all books NOT reviewed by each user, you can modify the query to suit your needs.

select u.userid, array( select bookid from books 
where bookid not in (select 
           bookid from reviews r 
            where r.userid=u.userid) )
    from users u

---

Related Topics