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

Question

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.

Currently i am using HeidiSQL to view the data and CREATE-statement — Thor
– Thor, Commented May 26, 2021 at 8:53
just checked, and this is also what \d+ is telling me in psql.. so no its not HeidiSQL — Thor
– Thor, Commented May 26, 2021 at 8:57
The you probably used default 'NULL' not what you put in the question. — user330315
– user330315, Commented May 26, 2021 at 8:58

Laurenz Albe · Accepted Answer · 2021-05-26 10:45:30Z

4

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

edited May 26, 2021 at 10:45

answered May 26, 2021 at 10:20

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user330315 Over a year ago

But 'NULL::character varying' (which is what Thor claims to see) is something different than NULL::character varying (which is what he expects)

Thor Over a year ago

The CREATE-statement in HeidiSQL displays this default value as a string yes.. and i just copy pasted it to here.. but the psql statement displays it as just NULL::character varying without the single-quotes, so everything is fine.. i was just wondering why some of my columns in the CREATE-statement was: .. DEFAULT NULL, ... and some was DEFAULT 'NULL::character varying', .... But it looks like it is "just" type casting. The only question that remains for me is.. how come the old/prev. columns be defined as DEFAULT NULL and not DEFAULT 'NULL::character varying' ?

Laurenz Albe Over a year ago

How do these old default values look in psql when you display the table definition with \d?

Thor Over a year ago

shouldn't they all be DEFAULT 'NULL::character varying' ?.. even if the old/prev. columns was created on an older version of the DB

Laurenz Albe Over a year ago

That confirms that you defined the "old columns" without specifying a default value, while you used DEFAULT NULL for the "new columns".

|

Collectives™ on Stack Overflow

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

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related