PostreSQL - Enabling Implicit Cast From Integer To Boolean

I’ve been using MySQL for a while with my pet projects, but recently I’ve been writing some more complicated queries, and I’m running into a few obnoxious limitations, mostly involving known view performance problems. I’ve decided to start testing the waters in PostgreSQL, so I tried importing my data into PG by running a MySQL-generated insert statement, only to run into a PostgreSQL newbie gotcha; “Column is of type boolean but expression is of type integer”.

By default, PG does not automatically interpret “1″ or “0″ as a boolean. The arguments in favor of this limitation are that it prevents “unintended” conversions. However, I’ve been developing in Python for years, which does do this automatic conversion, and its never bitten me.

The code to disable this feature is relatively simple, although I can’t find anyone who’s mentioned it, so I post it here:

UPDATE pg_cast SET castcontext = 'i' WHERE oid IN (
 SELECT c.oid
 FROM pg_cast c
 inner join pg_type src ON src.oid = c.castsource
 inner join pg_type tgt ON tgt.oid = c.casttarget
 WHERE src.typname LIKE 'int%' AND tgt.typname LIKE 'bool%'
)

This SQL updates PG’s int-to-bool cast path to “implicit” mode, meaning PG will now automatically cast an integer to boolean if the target type is a boolean. Otherwise, in the default “emplicit” mode, you’d have to use the CAST() syntax.

After the update, the insert statement runs perfectly.

PostgreSQL - Comandi utili per amministrare il db

Verificare l'utilizzo degli indici

select * from pg_stat_user_indexes

Verificare lo stato delle tabelle

select * from pg_stat_user_tables

Verificare le query in esecuzione

select * from pg_stat_activity

Verificare l'utilizzo dei checkpoint

select * from pg_stat_bgwriter