Recently I have been working with SQLite files part of a stand-alone application my company uses. We are getting ready to move the functionality from the stand-alone app to our web system. The app was designed with this eventual move in mind and it’s database file had been designed in such a way that we could simply do an export/import to move the data.
We used DB Browser for SQLite to export the database to a sql query we could import into our postgres database. There were some simple issues we needed to fix first (int(11) to int, tinyint(4) to boolean, etc). A simple find and replace for the file fixed those. But one issue we couldn’t fix was that PostgreSQL wouldn’t cast 0 and 1 to the boolean type. Due to the amount of data, a manual fix was impossible and we couldn’t think of a scripted way that wouldn’t cause unintended side effects. I googled the issue and there were some solutions, but it was an answer on a StackExchange question that had the fix (With a giant warning). This was an old answer and it was unclear what version of postgres the asker was using, so we tried it and it worked for us on Postgres 9.3.
Start by running the following:
|
|
update pg_cast set castcontext='a' where casttarget = 'boolean'::regtype; |
Then we can run the offending SQL query. Of course we wouldn’t want to keep it like this for production, so we can reverse it with the following:
|
|
update pg_cast set castcontext='e' where casttarget = 'boolean'::regtype; |
This is interesting, but I wanted to know more, all the query does is change a single field between ‘e’ and ‘a’, why does this work?
First off, pg_cast is a table that stores type conversion information, and it has a documentation page. We are changing the castcontext field of this table from ‘e’ to ‘a’ and back. Per the documentation: “e means only as an explicit cast (using CAST or :: syntax). a means implicitly in assignment to a target column” So we are simply removing the need for explicit casting.
If we do a SELECT on pg_cast and see the rows the query affects, we find it only changes a single row. Looking up the oids of the cast source and target shows that it is the cast from int4 (an alias of int) to bool. Since we aren’t changing the function used to do the casting, Postgres is obviously capable of the cast already, we just needed to give it a poke.
Software and versions used or mentioned:
PostgreSQL 9.3
DB Browser for SQLite
Resources:
StackExchange question
PostgreSQL pg_cast documentation
