Search code examples
sqlpostgresqlnullheidisql

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.

Solution

  • 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).