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

Question

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!

Answer 1

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.

Answer 2

If you want to accept 0 and 1 as boolean, your best bet is probably to create the table with the column defined as smallint with a CHECK (colname >= 0 AND colname <= 1) condition.

After import you can ALTER TABLE to change the type to boolean and provide a USING term to do the type conversion.

Alternately, use a more flexible data-loader, like pgloader, Pentaho Kettle, Talend Studio, etc, rather than trying to get COPY to accept the data.

I do think that accepting 0/1 as false/true would be desirable at least for the CSV mode of COPY. If you submit a patch I expect it'll probably get accepted.

Answer 3

I had some CSV files exported from Microsoft Access, which outputs True as 1 and False as 0. I imported them to Postgres using DBeaver but it converted all the 1's to false. So wound up using pgloader to import those tables - it handles the 1's and 0's automatically.

I'm on Windows, so had to use the Windows Subsystem for Linux to run pgloader.

Make a file called csv.load:

LOAD CSV
    FROM 'entity.csv'
    INTO postgresql://USER:PASSWORD@HOST/DATABASE?TABLENAME
    WITH
        skip header = 1,
        fields optionally enclosed by '"',
        fields escaped by double-quote,
        fields terminated by ','
;

Then run pgloader -

pgloader csv.load

Answer 4

I had the exact same problem and after reading the docs saw that it does accept 0's and 1's but only as strings! So I ran ALTER TABLE [TABLE NAME] ALTER COLUMN [COLUMN NAME] TYPE TEXT and from there cast to Boolean! (In my case I also had to drop the default first and add it back in after).

Answer 5

alter table test alter done type integer using case when done=true then 0 else 1 end;