current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

share|improve this question

3 Answers 3

up vote 2 down vote accepted

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

share|improve this answer
up vote 2 down vote

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.

share|improve this answer
    
I apologise for changing the accepted answer, but @pozs' answer seems, well, cleaner. –  9lo May 29 '14 at 14:50
    
@9lo No worries, pozs's is a good answer. –  mu is too short May 29 '14 at 15:11
up vote 1 down vote

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
;
share|improve this answer
    
That gives me {""} instead of {}. I believe they're not the same. –  9lo May 29 '14 at 2:32
    
@9lo -- I don't know of any other values besides NULL and empty strings. Seems you might be talking about presentation logic instead of sql. This is the result of the above query -- sqlfiddle.com/#!15/59b18/1 –  sgeddes May 29 '14 at 2:40
1  
Check the result in psql, via terminal. I did say that the result shown in the fiddle doesn't print the NULL value. The result gotten from the query will be parsed to JSON, where [] != [null] as well as [] != [""]. –  9lo May 29 '14 at 2:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.