Empty array as PostgreSQL array column default value

Question

I have a defined an array field in postgresql 9.4 database:

character varying(64)[]

Can I have an empty array e.g. {} for default value of that field? What will be the syntax for setting so?

I'm getting following error in case of setting just brackets {}:

SQL error:

ERROR:  syntax error at or near "{"
LINE 1: ...public"."accounts" ALTER COLUMN "pwd_history" SET DEFAULT {}
                                                                     ^

In statement:
ALTER TABLE "public"."accounts" ALTER COLUMN "pwd_history" SET DEFAULT {}

user330315 · Accepted Answer · 2015-06-19 08:21:20Z

165

You need to use the explicit array initializer and cast that to the correct type:

ALTER TABLE public.accounts 
    ALTER COLUMN pwd_history SET DEFAULT array[]::varchar[];

answered Jun 19, 2015 at 8:21

user330315

Sign up to request clarification or add additional context in comments.

4 Comments

Hett Over a year ago

Thank you very much! I've found another solution too, although don't know which is one more "correct" pwd_history SET DEFAULT '{}'. But as I understand both solutions are resulting the same casting for postgresql (explicit/implicit).

K Raphael Over a year ago

Just as a note for anyone trying to create a default value via SqlAlchemy (for python): you have to use {} for default value in the ORM mapping, e.g. sizes = Column(postgresql.ARRAY(postgresql.TEXT), server_default="{}") and let it do the implicit conversion, as mentioned in @Hett comment above

John Bachir Over a year ago

I've done this with success ALTER TABLE foo ALTER COLUMN bar SET DEFAULT '{}';

saza Over a year ago

I realise the op asked specifically about Pg 9.4, but this might be useful for users of newer versions. Just set the default as the following constant: {}. This would be the DDL: my_array_column text[] DEFAULT '{}'::text[]

GollyJer · Accepted Answer · 2018-11-27 07:26:56Z

79

I tested both the accepted answer and the one from the comments. They both work.
I'll graduate the comments to an answer as it's my preferred syntax. 🙂

ALTER TABLE public.accounts 
    ALTER COLUMN pwd_history SET DEFAULT '{}';

answered Nov 27, 2018 at 7:26

GollyJer

27.2k18 gold badges126 silver badges190 bronze badges

Comments

Burak · Accepted Answer · 2021-05-12 20:57:08Z

7

It threw an error where it can not find SET. This worked for me.

ALTER TABLE public.accounts 
    ALTER COLUMN pwd_history DEFAULT array[]::varchar[];

answered May 12, 2021 at 20:57

Burak

2,2063 gold badges16 silver badges32 bronze badges

Collectives™ on Stack Overflow

Empty array as PostgreSQL array column default value

3 Answers 3

4 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Linked

Related