array_length in Postgresql

Postgresql is an amazing database filled with features that help developers, but occasionally I come across something unexpected. This time it was with an array column.

Let's setup some data to work with:

create table posts (id integer, title character varying(255), tags character varying(255)[]);

insert into posts values (1, 'My Post', null);
insert into posts values (2, 'Another Post', '{}');
insert into posts values (3, 'Even more', '{"postgresql"}');
insert into posts values (4, 'One last one', '{"postgresql", "array"}');

If I try to get the length of the tags array for each row:

select id, array_length(tags, 1) from posts

I would expect:

1,<null>
2,0
3,1
4,2

but it actually returns:

1,<null>
2,<null>
3,1
4,2

That's right - array_length returns NULL for an empty array instead of zero.

It took me by surprise, hopefully this tip will help someone else.