CODEWITHSUNDEEP
sqlpostgresqlnullheidisql
Thor
Thor

Reputation: 1202

Postgres alter table defaults to 'NULL::character varying' instead of NULL

I am using psql to alter a database table. Currently running the following alter-statement:

ALTER TABLE "devices" ADD "device_id" VARCHAR(255) NULL DEFAULT NULL;

but I end up with the following in the create-table-statement:

"device_id" VARCHAR(255) NULL DEFAULT 'NULL::character varying'

Why is the default set to 'NULL::character varying' ?

I am bit confused, since the table already have multiple varchar fields, where the default is correct.. ex from CREATE-statement:

"external_id" VARCHAR(50) NULL DEFAULT NULL,

FYI: This column, external_id, was created multiple years ago before I started to touch the table.

Upvotes: 2

Views: 5823

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247475

Since you explicitly set the default value to NULL, PostgreSQL has added a column default. Default values are not stored in string form, but they are parsed and stored as a parse tree (in binary form). When you display the table definition, PostgreSQL “deparses” this information, which results in the (equivalent) NULL::character varying (the :: is a type cast).

That is just fine, but if you find it optically displeasing, you can simply drop the default value:

ALTER TABLE devices ALTER device_id DROP DEFAULT;

That will get rid of the default value, which won't change the behavior (the “default default value” is NULL).

Upvotes: 3

Related Questions