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

Postgresql – SQL Query that returns all records whose values are contained in an array. Data Retrieval with Specific Conditions

I want to return all cities whose all postal codes are contained in an array of certain postal codes.

On the backend I have an array containing postal codes. The array looks as follows:
[41200, 41201, 41202, 42500, 42501, 42504, 42505, 42506].

Using sql query, I retrieve all available cities from the database along with all postal codes assigned to them. Example:

City Postcodes
New York 10001, 10002, 10003, 10004, …

I want to add a certain condition to this query that will make the query return all cities whose all postal codes are included in this array.

Easy example:
Table: [1, 2, 3, 4, 5, 6] SQL query result:

City Postcodes
City 1 1, 2, 7
City 2 1, 4
City 3 1, 2, 3, 4, 5, 6
City 4 1, 3, 2, 8

After adding the condition, the query should return city 2 and city 3 BECAUSE table includes all of city 2 / city 3 postcodes but it does not include all the postcodes from city 1 and 4. Thank you.

That’s my query:

SELECT gmina, kody
FROM (
    SELECT gmina, STRING_AGG(DISTINCT kod, ', ') AS kody
    FROM ser_strumieniowanie_kody ser
    LEFT JOIN hog_miasta hog ON ser.st_kod = hog.kod
    WHERE ser.pm = ser.av
    GROUP BY gmina
) AS subquery where ...
Tags:

2

Answers


  1. 0

    That needs a lot of conversions to have on bioth side arays

    athen you can use <@ as array operator see
    https://www.postgresql.org/docs/current/functions-array.html

    select version();CREATE TABLE tab_city (
  "City" VARCHAR(6),
  "Postcodes" VARCHAR(16)
);

INSERT INTO tab_city
  ("City", "Postcodes")
VALUES
  ('City 1', '1, 2, 7'),
  ('City 2', '1, 4'),
  ('City 3', '1, 2, 3, 4, 5, 6'),
  ('City 4', '1, 3, 2, 8');

    SELECT "City"
FROM tab_city WHERE regexp_split_to_array("Postcodes", ',')::int[] <@ ARRAY[1, 2, 3, 4, 5, 6]
    City
    City 2
    City 3 
    SELECT 2

    fiddle

    Login or Signup to reply.
  2. 0

    Based on the query you added, I’m assuming you are dealing with a normalised structure and you only presented the values in an aggregated form to illustrate their intermediate state in your array-oriented approach.

    If I’m guessing your columns right, you can swap out string_agg() for an array_agg() to get access to array containment operator @> and its commutator <@ and specify your condition in the HAVING clause. Demo at db<>fiddle:

    SELECT gmina
FROM hog_miasta hog
GROUP BY gmina
HAVING ARRAY_AGG(DISTINCT kod) 
    <@ (SELECT ARRAY_AGG(DISTINCT st_kod) 
        FROM ser_strumieniowanie_kody ser
        WHERE ser.pm = ser.av);
    gmina
    City 2
    City 3

    In a left join, rows in the left table without a match in the right table get a null in all right table’s columns. You could use that to only keep those cities where every code had a match:

    SELECT gmina
FROM hog_miasta hog
LEFT JOIN ser_strumieniowanie_kody ser
       ON ser.st_kod = hog.kod
      AND ser.pm = ser.av
GROUP BY gmina
HAVING EVERY(ser.st_kod IS NOT NULL);

    That’s demonstrably faster, especially when supported by covering indexes:

    create index on hog_miasta (kod)
  include(gmina)
  with(fillfactor=100);

create index on ser_strumieniowanie_kody(st_kod)
  with(fillfactor=100)
  where pm=av;

    At the end of the demo, there’s a test on 200k records from 10k cities with up to 40 out of 300 codes.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search