作为 PostgreSQL 数组列默认值的空数组

我在 postgreql 9.4数据库中定义了一个数组字段:

character varying(64)[]

我可以有一个空数组，例如{}作为该字段的默认值吗？这样设置的语法是什么？

在设置方括号{}的情况下，我得到了以下错误:

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 {}

70393 次浏览

小开

最佳答案

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[];