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 {}
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 .
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.
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.
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[];
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 '{}';
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[];
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With