Do empty arrays equal NULL in PostgreSQL or is it an array with NULL inside?

Question

Let's say I want to have an array of following and followers for the user, so I create a users table like so:

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  email VARCHAR(64) NOT NULL UNIQUE,
  username VARCHAR(64) NOT NULL,
  password TEXT NOT NULL,
  following INT[],
  followers INT[]
);

Now, I want the user to be able to have no followers or to not follow anyone, so should I allow NULL values for those fields or should I just have an empty array? If second option is better, how do I implement it? Does an empty array automatically equals NULL or not? I'd rather have it as an empty array and not have any NULL values at all, so how should I go about that?

Answer 1

You should use an empty array.

A NULL value means "unknown", so it won't work like expected with a condition like this:

WHERE NOT ARRAY[42] <@ followers

Here <@ is the "contains" operator, and the result will not be TRUE if followers IS NULL.

I also recommend to avoid storing NULL values as array elements. An array that contains NULL is different from an empty array, and again the semantics of NULL may lead to confusion.