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

arrays, database, null, postgresql, sql

Solution

The most simple way of doing this, is to use the `ARRAY` constructor's sub-query variant:

SELECT
  person.person_name,
  ARRAY(SELECT animal_name FROM pet WHERE person.person_name = pet.person_name) AS pets
FROM
  person;

SQLFiddle

Problem

I am using the following schema: ``` CREATE TABLE person ( person_name VARCHAR PRIMARY KEY ); CREATE TABLE pet ( animal_name VARCHAR, person_name VARCHAR REFERENCES person(person_name), PRIMARY KEY (animal_name, person_name) ); ``` I wish to create a table where, for each `person_name`, I get an array with the pets of that person. I am using PostgreSQL 9.3.4. I have the following values in each table: Person ``` PERSON_NAME ----------- Alice Bob ``` Pet ``` ANIMAL_NAME | PERSON_NAME ------------------------- Woof | Alice Meow | Alice ``` I wish to create the following table: ``` PERSON_NAME | PETS -------------------------- Alice | {Woof, Meow} Bob | {} ``` I cannot, however, create the empty array. What I get is the following: ``` PERSON_NAME | PETS -------------------------- Alice | {Woof, Meow} Bob | {NULL} ``` This is the query I am using: ``` SELECT person.person_name, array_agg(pet.animal_name) AS pets FROM person LEFT JOIN pet ON person.person_name = pet.person_name GROUP BY person.person_name; ``` I understand why I am getting the array with the `NULL` value inside, I want to know how to get an empty array instead. Here is a fiddle with the code needed to create the schema, insert the values and with the query I am using. The result shown in the website doesn't show the `NULL` value, although it is there. EDIT The result will be parsed to JSON, that is why `{NULL}` is not an acceptable result, as it will be parsed to `[null]`, which is different from the `[]` I require. For the same reason, something like `{""}` is not an acceptable result either.

Original source