Question 114464 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to do SELECT UNNEST ARRAY on a list not stored in the db in django

I’m trying to reproduce this simplified Postgres query in Django (where [v1, v2, ...] is a python list):

SELECT * FROM UNNEST(ARRAY[v1, v2, ...]) objs (obj)
WHERE EXISTS(
  SELECT "table"."field1"
  FROM "table"
  WHERE "table"."field2" = 'value' AND "table"."field1" = fp
)

But I cannot find a way to use UNNEST(ARRAY(... on something that is not a table.

Tags:

2

Answers


  1. 0

    For this i use common table expression (CTE) :

    WITH cte AS
(
SELECT UNNEST(ARRAY[
v1,
v2,
......
])::int objs
)

    then in query use RIGHT JOIN

    full query:

    WITH cte AS
(
SELECT UNNEST(ARRAY[
v1,
v2,
......
])::int objs
)

select *
from your_table as t
RIGHT JOIN cte ON cte.objs = t."your objects from table"
    Login or Signup to reply.
  2. 0

    ARRAY(subquery) is special array constructor that requires relation.

    (2022-09-26 12:59:15) postgres=# select unnest(array(values(1),(2),(3)));
┌────────┐
│ unnest │
╞════════╡
│      1 │
│      2 │
│      3 │
└────────┘
(3 rows)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search