Postgres: Inserting into an array

If you make an array column in Postgres, it’s not completely obvious how you’d insert static data into it.

Say you create a table like so (two int array fields):

create table groups (
  id int unique,
  name varchar unique,
  member_users int[],
  member_groups int[]
);

You can insert using quoted strings. Note that the quotes are apostrophes – otherwise Postgres interprets this as a column name (as if you were doing an “INSERT INTO table SELECT … FROM …”).

Also note this uses “{}” braces (not [] like you’d expect on an array).

insert into groups values (1, 'admins', '{1,2}', '{}');

If you’d like, you can also cast the text to an array (this doesn’t change the behavior, but you might find it clearer)

insert into groups values (1, 'admins', '{1,2}'::int, '{}'::int[]);

You can also use spaces if you wish:

insert into groups values (2, 'users', '{1, 2}', '{}');