Re: pg_dump and boolean format

On Aug 11, 2010, at 10:12 AM, Michael Glaesemann wrote:

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

Right.


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

With the number of tables and insert statements I'll be wrangling, I
was hopeful I could coerce pg_dump to perform the "AS INT" cast.

Thanks for the tip!
Scott




> Michael Glaesemann
> grzm seespotcode net
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>