What difference between NULL and NULL::character varying in PostgreSQL

Question

Is there a difference between default values of column in PostgreSQL? Whether this is important?

state character varying(255) DEFAULT NULL

and

state character varying(255) DEFAULT NULL::character varying

Answer 1

There is no effective difference in the presented example in a standard installation.

Without explicit cast, NULL of data type unknown will be coerced to varchar in an assignment cast automatically. See:

• Generate series of dates - using date type as input

In other situations, where the type cannot be derived from context, you may need to cast explicitly - to tell Postgres the intended type of the value. This is rarely the case, though.