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

Question

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.

Solution 1

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

Solution 2

I just wanna add tho this is 6 years old

array[]::varchar[]

Solution 3

Two possibilities come to mind.

One option is a UNION.

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

The first part uses a JOIN to get the people with pets, then the second grabs those poor people that don't have any pets at all. Separating them lets you supply a literal empty array for the pet-less people.

Another option would be to do pretty much the same thing using a LEFT JOIN to a derived table:

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

This one is a little more natural to me as it lets you use COALESCE (the first thing you reach for you want to map NULL to something else) to supply the empty array.

Updated demo: http://sqlfiddle.com/#!15/24ccc/7

There may be other solutions, these two just seem like natural approaches to me.

Solution 4

You can use COALESCE to replace NULL values:

SELECT
  person.person_name,
  array_agg(coalesce(pet.animal_name,'')) AS pets
FROM
  person LEFT JOIN pet ON person.person_name = pet.person_name
GROUP BY
  person.person_name
;

View more solutions