Create an empty array in an SQL query using PostgreSQL instead of an array with NULL inside

In SQL, you can utilize the COALESCE function to replace NULL values with an empty array. This allows you to handle scenarios where there might be missing data or no related records exist. Here's an example:

SELECT
  person.person_name,
  COALESCE(array_agg(pet.animal_name), ARRAY[]::text[]) AS pets
FROM
  person LEFT JOIN pet ON person.person_name = pet.person_name
GROUP BY
  person.person_name;

In this query, we use COALESCE to replace NULL values in the array_agg(pet.animal_name) expression with an empty array (ARRAY[]::text[]). This ensures that we always have an array, even if there are no pets associated with a particular person.

Another approach involves using a UNION statement to combine two result sets: one for people with pets and one for people without pets. This allows us to explicitly define an empty array for the latter group.

SELECT person.person_name, array_agg(pet.animal_name) as pets
FROM person
JOIN pet ON person.person_name = pet.person_name
GROUP BY person.person_name

UNION

SELECT person.person_name, array[]::text[] as pets
FROM person
LEFT JOIN pet ON person.person_name = pet.person_name
WHERE pet.animal_name is null;

In this case, the UNION statement combines the results from two separate queries. The first query retrieves data for people with pets, while the second query retrieves data for people without pets and assigns an empty array to the pets column.

You can also use a LEFT JOIN to a derived table to achieve a similar result:

WITH pet_names AS (
  SELECT person.person_name, array_agg(pet.animal_name) as pets
  FROM person
  JOIN pet ON person.person_name = pet.person_name
  GROUP BY person.person_name
)
SELECT p.person_name, COALESCE(n.pets, ARRAY[]::text[])
FROM person p
LEFT JOIN pet_names n ON p.person_name = n.person_name;

Here, we create a derived table called pet_names that contains data for people with pets. We then use a LEFT JOIN to combine this derived table with the person table, ensuring that all people are included in the result set. The COALESCE function is used to replace NULL values with an empty array for people without pets.

These methods provide different approaches to creating an empty array in an SQL query using PostgreSQL. The most suitable method depends on the specific requirements and structure of your data.