K
K
kealman2016-02-05 17:28:56
PostgreSQL
kealman, 2016-02-05 17:28:56

PgSQL. How to apply a condition in the selection, only if the field is not empty?

Good afternoon! Please tell me how to make a selection (without using functions) so that the condition works only if the field is not an empty array. Let me explain with an example:
There are 2 tables

CREATE TABLE public.t1
(
  id character varying(24) NOT NULL,
  ratings integer[]
)

CREATE TABLE public.t2
(
  id character varying(24) NOT NULL,
  rating integer
)

I would like to make the following selection (pseudocode):
SELECT * FROM t1
INNER JOIN t2 ON IF t1.ratings.length > 0 THEN t2.rating = any(t1.ratings) ELSE true

Thank you!

Answer the question

In order to leave comments, you need to log in

2 answer(s)
K
kealman, 2016-02-05
@kealman

Understood. The solution, as always, lay on the surface, may be useful to someone:

SELECT * FROM t1
INNER JOIN t2 ON array_length(t1.ratings,1) IS NULL OR t2.rating = any(t1.ratings)

A
Andrey, 2016-02-05
@VladimirAndreev

SELECT
    *
FROM t1
    JOIN t2 USING(id)
WHERE
    1 = IF(t1.ratings.length > 0, (IF(t2.rating = ANY(t1.ratings), 1, 0)), 1)

True, this is a dialect of mySQL, but the idea is something like this.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question