Обсуждение: pg_dump and boolean format
Hi all,
Is it possible to control the representation of boolean data in a
pg_dump?
The pg docs say that booleans can be stored as 't', 'true', 'y',
'yes', or '1'. My db is storing them as 't' and pg_dump is outputing
them as 'true'. Can I coerce pg_dump to output 't' as 1?
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.
I'm currently using the following command:
pg_dump -aDFp -v mydatabase > mydatabase_BAK.sql
Thanks!
Scott
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 booleanvalues. > 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
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 >
On Aug 11, 2010, at 18:21 , Scott Frankel wrote: > > On Aug 11, 2010, at 10:12 AM, Michael Glaesemann wrote: > >> 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. Query the database Information Schema or system tables to provide a list of the table names. By using COPY WITH CSV HEADERand the names of the tables, you should be able to generate INSERT statements programatically. Michael Glaesemann grzm seespotcode net
On Aug 11, 2010, at 3:57 PM, Michael Glaesemann wrote: > > On Aug 11, 2010, at 18:21 , Scott Frankel wrote: > >> >> On Aug 11, 2010, at 10:12 AM, Michael Glaesemann wrote: >> >>> 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. > > Query the database Information Schema or system tables to provide a > list of the table names. By using COPY WITH CSV HEADER and the names > of the tables, you should be able to generate INSERT statements > programatically. Good point. Thanks for the suggestions! 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 >