Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Empty array as PostgreSQL array column default value

Tags:

sql

postgresql

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 {}
like image 630
Hett Avatar asked Jun 19 '15 08:06

Hett


People also ask

How do I find the default value of a column in PostgreSQL?

you can just type " \d table_name" command , then It will displays some information about the table, such as the default value of a column. \d will show the default values of a column .

How do I change the default value of a column in PostgreSQL?

Changing a Column's Default Value. To set a new default for a column, use a command like: ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77; Note that this doesn't affect any existing rows in the table, it just changes the default for future INSERT commands.

What is text [] in Postgres?

PostgreSQL supports a character data type called TEXT. This data type is used to store character of unlimited length. It is represented as text in PostgreSQL. The performance of the varchar (without n) and text are the same.


3 Answers

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[]; 
like image 84
a_horse_with_no_name Avatar answered Sep 20 '22 15:09

a_horse_with_no_name


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 '{}';
like image 42
GollyJer Avatar answered Sep 17 '22 15:09

GollyJer


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[];
like image 25
Burak Avatar answered Sep 18 '22 15:09

Burak