Postgresql – Have Postgresql accept 1 and 0 as true and false for boolean

postgresql

I have a large file of dumped sql data to load in to postgresl (600mb+). In that dump, fields that are boolean true and false are expessed as 1 and 0 without quotes. Trying to load them in to postgresql, results in complains as it doesnt know how to cast them.

Loading like this…

psql --disable-triggers -1 -f /foo/bar/dump.sql

Due to the large amount of data and the sprinkling of boolean fields in the – regexing through to replace them with true/false appropriately has proved impractical.

After much googling i havent been able to find a way to have postgresql accept that = true and 0 = false for the duration of this data load. It seems simple enough, but its apparently not!

Hopefully someone knows exactly how to nail it and can help me out!

Best Answer

Digging in to the dark magic of postgresql, i discovered that the integer to boolean cast can be changed from an exlicit to an automatic coersion. Then changed back when done... 

update pg_cast set castcontext='a' where casttarget = 'boolean'::regtype;

Then load with pg_dump.

When done, set back the casting to its default with... 

update pg_cast set castcontext='e' where casttarget = 'boolean'::regtype;

Important note: don't do this unless you know what it involves. The catalogs aren't guaranteed to be compatible version to version, and something that's safe(ish) to do now could be quite bad to do in a future version, or not work at all.

Related Question