2

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.

Thor A. Pedersen
  • 1,122
  • 4
  • 18
  • 32

1 Answers1

2

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

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • But `'NULL::character varying'` (which is what Thor claims to see) is something different than `NULL::character varying` (which is what he expects) –  May 26 '21 at 10:27
  • 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'` ? – Thor A. Pedersen May 26 '21 at 10:43
  • How do these old default values look in `psql` when you display the table definition with `\d`? – Laurenz Albe May 26 '21 at 10:44
  • shouldn't they all be `DEFAULT 'NULL::character varying'` ?.. even if the old/prev. columns was created on an older version of the DB – Thor A. Pedersen May 26 '21 at 10:45
  • This has nothing to do with the version - the difference is if you set a default value or not. – Laurenz Albe May 26 '21 at 10:46
  • psql shows the following for the device_id: https://i.imgur.com/OojvKqI.png - and for the old/prev. columns: https://i.imgur.com/Xeh6rxA.png - and HeidiSQL displays them as following: https://i.imgur.com/6csFB5S.png – Thor A. Pedersen May 26 '21 at 10:54
  • 1
    That confirms that you defined the "old columns" without specifying a default value, while you used `DEFAULT NULL` for the "new columns". – Laurenz Albe May 26 '21 at 10:58
  • cool.. i did not create these columns, which was why i was curious.. they where also created with django's ORM.. where it is specified the column is allowed to be null.. but no default was set (specified in python-code). – Thor A. Pedersen May 26 '21 at 11:01