Question 89891 in PostgreSQL Question posted in
The official documentation can be found here.

Explain behavior of ADD COLUMN xxx varchar(255) DEFAULT FALSE; – Postgresql

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:

SQL query result

Looks like some weird type coercion to me

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search