I've faced a strange SQL query like

ALTER TABLE some_db.some_table
    ADD COLUMN metadata_labels varchar(255) DEFAULT FALSE;

I'd expect it to fail because I'm adding a Boolean default value for the varchar column. But at least at Postgres, it is executed successfully and I see the following:

Looks like some weird type coercion to me

Why this query does not fail due to a type mismatch?

CodePudding user response：

Postgres does implicit type conversion. It's documented here:

https://www.postgresql.org/docs/current/typeconv.html

So your SQL Statement is perfectly valid, as false::bool can be perfectly converted into 'bool'::text.

CodePudding user response：

There is an assignment cast from boolean to text, so it must be that DEFAULT values are acceptable if there is an assignment cast to the target data type.

Looking at the adbin column in the pg_attrdef catalog, I can see the the actual default expression that is stored is

"varchar"(text(FALSE), 259, FALSE)

where the outer function is the conversion to varchar(255).