Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

migrated from stackoverflow.com Jul 11 '13 at 7:53

This question came from our site for professional and enthusiast programmers.

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 the pgdump. When done, set it back with... update pg_cast set castcontext='e' where casttarget = 'boolean'::regtype; –  Dean Jul 11 '13 at 7:01

2 Answers 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.

share|improve this answer
    
the dump is all inserts, so i was hoping to use some sort of one line cast declairation or something so postgresql has a way to convert from int to bool. –  Dean Jul 11 '13 at 6:24
    
@Dean Don't think so, probably not even by messing with the system catalogs directly, since the bool_in function will be consuming the value. Create the table with an integer column then change it after the import. –  Craig Ringer Jul 11 '13 at 6:26
2  
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 the pgdump. When done, set it back with... update pg_cast set castcontext='e' where casttarget = 'boolean'::regtype; –  Dean Jul 11 '13 at 7:00
    
@Dean Exactly so. Please don't put that in production applications, etc, though; 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. I'm a little surprised that making the cast implicit worked, actually, I expected bool_in to be called directly and to fail. –  Craig Ringer Jul 11 '13 at 8:30
2  
My answer would have been to change the cast. Please upgrade your comment to an answer. –  Peter Eisentraut Jul 11 '13 at 13:18

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.