Обсуждение: Problem inserting data
Hello everybody. I''ve just converted an access database to postgres. The conversion was ok, but when i'm trying to insert data on some tables with boolean types from a Visual Basic application i receive an error. A sample: table example, with firs field of integer, second of type text and third of type boolean the query: INSERT INTO EXAMPLE VALUES (1, 'text', 0) When i used access this worked fine, in boolean types 0 is false and 1 is true, but in postgres (8.1 and 8.3 version) i receive the next error: column "premarco" is of type boolean but expression is of type integer My question is, can i force postgres to accept integer values as boolean without changin query to INSERT INTO EXAMPLE VALUES (1, 'text', 0::boolean). This is a problem because if I have to do this i will have to test about one hundred queryes Thank you very much
On Friday 20 June 2008 5:20 am, Tk421 wrote: > Hello everybody. > > I''ve just converted an access database to postgres. The conversion > was ok, but when i'm trying to insert data on some tables with boolean > types from a Visual Basic application i receive an error. A sample: > > table example, with firs field of integer, second of type text and > third of type boolean > > the query: INSERT INTO EXAMPLE VALUES (1, 'text', 0) > > When i used access this worked fine, in boolean types 0 is false and > 1 is true, but in postgres (8.1 and 8.3 version) i receive the next error: > > column "premarco" is of type boolean but expression is of type integer > > My question is, can i force postgres to accept integer values as > boolean without changin query to INSERT INTO EXAMPLE VALUES (1, 'text', > 0::boolean). This is a problem because if I have to do this i will have > to test about one hundred queryes > > Thank you very much Two options: 1) Change the column in Postgres to an integer type. 2) Change the assignment in pg_cast from explicit to implied for the int4,bool cast. See for more details: http://www.postgresql.org/docs/8.3/interactive/catalog-pg-cast.html -- Adrian Klaver aklaver@comcast.net
Adrian Klaver <aklaver@comcast.net> writes: > Two options: > 1) Change the column in Postgres to an integer type. > 2) Change the assignment in pg_cast from explicit to implied for the > int4,bool cast. See for more details: > http://www.postgresql.org/docs/8.3/interactive/catalog-pg-cast.html Making it an assignment cast would be sufficient for the particular case shown, and would be less likely to introduce surprising behavior elsewhere. Of course, depending on how thoroughly the application confuses int and bool, you might be stuck with using the implicit cast. But it's a bit scary. I think I'd look at option (1) before trying (2) with an implicit cast. regards, tom lane
Adrian Klaver escribió: <blockquote cite="mid:200806200740.46335.aklaver@comcast.net" type="cite"><pre wrap="">On Friday20 June 2008 5:20 am, Tk421 wrote: </pre><blockquote type="cite"><pre wrap=""> Hello everybody. I''ve just converted an access database to postgres. The conversion was ok, but when i'm trying to insert data on some tables with boolean types from a Visual Basic application i receive an error. A sample: table example, with firs field of integer, second of type text and third of type boolean the query: INSERT INTO EXAMPLE VALUES (1, 'text', 0) When i used access this worked fine, in boolean types 0 is false and 1 is true, but in postgres (8.1 and 8.3 version) i receive the next error: column "premarco" is of type boolean but expression is of type integer My question is, can i force postgres to accept integer values as boolean without changin query to INSERT INTO EXAMPLE VALUES (1, 'text', 0::boolean). This is a problem because if I have to do this i will have to test about one hundred queryes Thank you very much </pre></blockquote><pre wrap=""> Two options: 1) Change the column in Postgres to an integer type. 2) Change the assignment in pg_cast from explicit to implied for the int4,bool cast. See for more details: <a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/8.3/interactive/catalog-pg-cast.html">http://www.postgresql.org/docs/8.3/interactive/catalog-pg-cast.html</a> </pre></blockquote> Thank you very much. I've choosen the second option, using this query:<br /><br /> update pg_castset castcontext='i' where castsource=23 and casttarget=16<br /><br /><br />