Re: pg_dump and boolean format

On Aug 11, 2010, at 13:00 , Scott Frankel wrote:

> The pg docs say that booleans can be stored as 't', 'true', 'y', 'yes', or '1'.

Booleans are not "stored" as those literals: those are only acceptable literals (i.e., string representations) for boolean values.

> I'm using pg_dump to create an SQL script for importing into another database. This will happen automatically and repeatedly. Unfortunately, the other database (sqlite) is limited to only integer representation of booleans: 0 or 1.

One option is to use COPY to export the data in a format you like. For example: COPY (SELECT CAST(boolean_column AS INT) FROM my_table) TO STDOUT. Then write a script which reads the exported data files and loads them into your sqlite database. COPY WITH CSV would likely be helpful as well.

Michael Glaesemann
grzm seespotcode net