Thread: pgadmin error
Is there a way to configure the JDBC driver that pgadmin uses to access the db's? I'm using pg's postgresql-8.4-701.jdbc4.jar in my code and I get the correct values from the db. But pgadmin does not get the correct values back. pgadmin is rounding real's to 6 significant digits, independent of the magnitude of the value. real's obviously have an issue with resolution as the magnitude of the number increases. But this pgadmin issue is not magnitude dependent. Second the real datatype issue loses resolution in power of 2's. pgadmin is losing in the power of 10's. It seems like a string parsing issue in the jdbc that only parses the first 6 digits.
So I'd like to configure pgadmin to use the same jdbc driver as my code. Which works. Where can I set the driver for pgadmin?
Charlie Holleran
On Wed, May 16, 2012 at 10:04 AM, Charlie Holleran <charles.holleran@shiftenergy.com> wrote: > Is there a way to configure the JDBC driver that pgadmin uses to access the > db's? I'm using pg's postgresql-8.4-701.jdbc4.jar in my code and I get the > correct values from the db. But pgadmin does not get the correct values > back. pgadmin is rounding real's to 6 significant digits, independent of > the magnitude of the value. real's obviously have an issue with resolution > as the magnitude of the number increases. But this pgadmin issue is not > magnitude dependent. Second the real datatype issue loses resolution in > power of 2's. pgadmin is losing in the power of 10's. It seems like a > string parsing issue in the jdbc that only parses the first 6 digits. > > So I'd like to configure pgadmin to use the same jdbc driver as my code. > Which works. Where can I set the driver for pgadmin? pgAdmin isn't a Java app. It's written in C++ and uses libpq to access the server. Where in pgAdmin do you see the rounding? (query tool, edit grid or...). Can you provide a self contained test case? -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Hi Dave Tried some more queries to qualify the rounding problem accessing data in pgadmin. Not quite what was expected. Hmmm... Edit view brings back the values such as 8.53639e+06. What has been stored in the table are numbers in increasing value. <test_query> SELECT col1, col2, date_observed, rt_value v0, rt_value::TEXT v1, to_char(rt_value, '999999999') v2, rt_value::NUMERIC v3, rt_value::NUMERIC::TEXT v4, to_char(rt_value::NUMERIC, '999999999') v5, rt_value::double precision v6, rt_value::double precision::TEXT v7, to_char(rt_value::double precision, '999999999') v8 FROM tbla WHERE (col1,col2) = ( (-1465867942,-3248196) ) AND date_observed >= '9-May-2012 23:57' AND date_observed < '10-May-2012' ORDER BY col1, col2, date_observed ; </test_query> <results> -1465867942;-3248196;"2012-05-09 23:57:00-03";8.53639e+06;"8.53639e+06";" 8536388";8536390;"8536390";" 8536390";8536388;"8536388";" 8536388" -1465867942;-3248196;"2012-05-09 23:58:00-03";8.5364e+06;"8.5364e+06";" 8536396";8536400;"8536400";" 8536400";8536396;"8536396";" 8536396" -1465867942;-3248196;"2012-05-09 23:59:00-03";8.5364e+06;"8.5364e+06";" 8536404";8536400;"8536400";" 8536400";8536404;"8536404";" 8536404" </results> <table_snippet> CREATE TABLE tbldaq_adb ( id bigserial NOT NULL, col1 integer NOT NULL, col2 integer NOT NULL, date_observed timestamp with time zone NOT NULL, rt_value real NOT NULL, ); </table_snippet> Charlie -------------------------------------------------- From: "Dave Page" <dpage@pgadmin.org> Sent: Wednesday, May 16, 2012 2:51 PM To: "Charlie Holleran" <charles.holleran@shiftenergy.com> Cc: <pgadmin-hackers@postgresql.org> Subject: Re: [pgadmin-hackers] pgadmin error > On Wed, May 16, 2012 at 10:04 AM, Charlie Holleran > <charles.holleran@shiftenergy.com> wrote: >> Is there a way to configure the JDBC driver that pgadmin uses to access >> the >> db's? I'm using pg's postgresql-8.4-701.jdbc4.jar in my code and I get >> the >> correct values from the db. But pgadmin does not get the correct values >> back. pgadmin is rounding real's to 6 significant digits, independent of >> the magnitude of the value. real's obviously have an issue with >> resolution >> as the magnitude of the number increases. But this pgadmin issue is not >> magnitude dependent. Second the real datatype issue loses resolution in >> power of 2's. pgadmin is losing in the power of 10's. It seems like a >> string parsing issue in the jdbc that only parses the first 6 digits. >> >> So I'd like to configure pgadmin to use the same jdbc driver as my code. >> Which works. Where can I set the driver for pgadmin? > > pgAdmin isn't a Java app. It's written in C++ and uses libpq to access > the server. > > Where in pgAdmin do you see the rounding? (query tool, edit grid > or...). Can you provide a self contained test case? > > > -- > Dave Page > Blog: http://pgsnake.blogspot.com > Twitter: @pgsnake > > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
HI Can I get the raw data for the test case please (ideally as a set of INSERT statements)? Also, what happens if you INSERT/SELECT the same data using psql? On Wed, May 16, 2012 at 10:24 PM, Charlie Holleran <charles.holleran@shiftenergy.com> wrote: > Hi Dave > > Tried some more queries to qualify the rounding problem accessing data in > pgadmin. Not quite what was expected. Hmmm... > > Edit view brings back the values such as 8.53639e+06. > > What has been stored in the table are numbers in increasing value. > > <test_query> > > SELECT col1, col2, date_observed, > rt_value v0, rt_value::TEXT v1, to_char(rt_value, '999999999') v2, > rt_value::NUMERIC v3, rt_value::NUMERIC::TEXT v4, > to_char(rt_value::NUMERIC, '999999999') v5, > rt_value::double precision v6, rt_value::double precision::TEXT v7, > to_char(rt_value::double precision, '999999999') v8 > FROM tbla > WHERE (col1,col2) = ( (-1465867942,-3248196) ) > AND date_observed >= '9-May-2012 23:57' > AND date_observed < '10-May-2012' > ORDER BY col1, col2, date_observed > ; > > </test_query> > > <results> > > -1465867942;-3248196;"2012-05-09 23:57:00-03";8.53639e+06;"8.53639e+06";" > 8536388";8536390;"8536390";" 8536390";8536388;"8536388";" 8536388" > -1465867942;-3248196;"2012-05-09 23:58:00-03";8.5364e+06;"8.5364e+06";" > 8536396";8536400;"8536400";" 8536400";8536396;"8536396";" 8536396" > -1465867942;-3248196;"2012-05-09 23:59:00-03";8.5364e+06;"8.5364e+06";" > 8536404";8536400;"8536400";" 8536400";8536404;"8536404";" 8536404" > > </results> > > <table_snippet> > > CREATE TABLE tbldaq_adb > ( > id bigserial NOT NULL, > col1 integer NOT NULL, > col2 integer NOT NULL, > date_observed timestamp with time zone NOT NULL, > rt_value real NOT NULL, > ); > > </table_snippet> > > Charlie > > > > -------------------------------------------------- > From: "Dave Page" <dpage@pgadmin.org> > Sent: Wednesday, May 16, 2012 2:51 PM > To: "Charlie Holleran" <charles.holleran@shiftenergy.com> > Cc: <pgadmin-hackers@postgresql.org> > Subject: Re: [pgadmin-hackers] pgadmin error > > >> On Wed, May 16, 2012 at 10:04 AM, Charlie Holleran >> <charles.holleran@shiftenergy.com> wrote: >>> >>> Is there a way to configure the JDBC driver that pgadmin uses to access >>> the >>> db's? I'm using pg's postgresql-8.4-701.jdbc4.jar in my code and I get >>> the >>> correct values from the db. But pgadmin does not get the correct values >>> back. pgadmin is rounding real's to 6 significant digits, independent of >>> the magnitude of the value. real's obviously have an issue with >>> resolution >>> as the magnitude of the number increases. But this pgadmin issue is not >>> magnitude dependent. Second the real datatype issue loses resolution in >>> power of 2's. pgadmin is losing in the power of 10's. It seems like a >>> string parsing issue in the jdbc that only parses the first 6 digits. >>> >>> So I'd like to configure pgadmin to use the same jdbc driver as my code. >>> Which works. Where can I set the driver for pgadmin? >> >> >> pgAdmin isn't a Java app. It's written in C++ and uses libpq to access >> the server. >> >> Where in pgAdmin do you see the rounding? (query tool, edit grid >> or...). Can you provide a self contained test case? >> >> >> -- >> Dave Page >> Blog: http://pgsnake.blogspot.com >> Twitter: @pgsnake >> >> EnterpriseDB UK: http://www.enterprisedb.com >> The Enterprise PostgreSQL Company >> > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Thanks Charlie. What happens with psql, per my question below? On Thu, May 17, 2012 at 10:24 PM, Charlie Holleran <charles.holleran@shiftenergy.com> wrote: > INSERT INTO tbla(col1, col2, date_observed, rt_value) > VALUES > ( -1465867942,-3248196,'2012-05-09 23:57:00-03',8536388), > ( -1465867942,-3248196,'2012-05-09 23:58:00-03',8536396), > ( -1465867942,-3248196,'2012-05-09 23:59:00-03',8536404); > > > > > -------------------------------------------------- > From: "Dave Page" <dpage@pgadmin.org> > Sent: Thursday, May 17, 2012 5:45 PM > > To: "Charlie Holleran" <charles.holleran@shiftenergy.com> > Cc: <pgadmin-hackers@postgresql.org> > Subject: Re: [pgadmin-hackers] pgadmin error > >> HI >> >> Can I get the raw data for the test case please (ideally as a set of >> INSERT statements)? >> >> Also, what happens if you INSERT/SELECT the same data using psql? >> >> On Wed, May 16, 2012 at 10:24 PM, Charlie Holleran >> <charles.holleran@shiftenergy.com> wrote: >>> >>> Hi Dave >>> >>> Tried some more queries to qualify the rounding problem accessing data in >>> pgadmin. Not quite what was expected. Hmmm... >>> >>> Edit view brings back the values such as 8.53639e+06. >>> >>> What has been stored in the table are numbers in increasing value. >>> >>> <test_query> >>> >>> SELECT col1, col2, date_observed, >>> rt_value v0, rt_value::TEXT v1, to_char(rt_value, '999999999') v2, >>> rt_value::NUMERIC v3, rt_value::NUMERIC::TEXT v4, >>> to_char(rt_value::NUMERIC, '999999999') v5, >>> rt_value::double precision v6, rt_value::double precision::TEXT v7, >>> to_char(rt_value::double precision, '999999999') v8 >>> FROM tbla >>> WHERE (col1,col2) = ( (-1465867942,-3248196) ) >>> AND date_observed >= '9-May-2012 23:57' >>> AND date_observed < '10-May-2012' >>> ORDER BY col1, col2, date_observed >>> ; >>> >>> </test_query> >>> >>> <results> >>> >>> -1465867942;-3248196;"2012-05-09 23:57:00-03";8.53639e+06;"8.53639e+06";" >>> 8536388";8536390;"8536390";" 8536390";8536388;"8536388";" 8536388" >>> -1465867942;-3248196;"2012-05-09 23:58:00-03";8.5364e+06;"8.5364e+06";" >>> 8536396";8536400;"8536400";" 8536400";8536396;"8536396";" 8536396" >>> -1465867942;-3248196;"2012-05-09 23:59:00-03";8.5364e+06;"8.5364e+06";" >>> 8536404";8536400;"8536400";" 8536400";8536404;"8536404";" 8536404" >>> >>> </results> >>> >>> <table_snippet> >>> >>> CREATE TABLE tbldaq_adb >>> ( >>> id bigserial NOT NULL, >>> col1 integer NOT NULL, >>> col2 integer NOT NULL, >>> date_observed timestamp with time zone NOT NULL, >>> rt_value real NOT NULL, >>> ); >>> >>> </table_snippet> >>> >>> Charlie >>> >>> >>> >>> -------------------------------------------------- >>> From: "Dave Page" <dpage@pgadmin.org> >>> Sent: Wednesday, May 16, 2012 2:51 PM >>> To: "Charlie Holleran" <charles.holleran@shiftenergy.com> >>> Cc: <pgadmin-hackers@postgresql.org> >>> Subject: Re: [pgadmin-hackers] pgadmin error >>> >>> >>>> On Wed, May 16, 2012 at 10:04 AM, Charlie Holleran >>>> <charles.holleran@shiftenergy.com> wrote: >>>>> >>>>> >>>>> Is there a way to configure the JDBC driver that pgadmin uses to access >>>>> the >>>>> db's? I'm using pg's postgresql-8.4-701.jdbc4.jar in my code and I get >>>>> the >>>>> correct values from the db. But pgadmin does not get the correct >>>>> values >>>>> back. pgadmin is rounding real's to 6 significant digits, independent >>>>> of >>>>> the magnitude of the value. real's obviously have an issue with >>>>> resolution >>>>> as the magnitude of the number increases. But this pgadmin issue is >>>>> not >>>>> magnitude dependent. Second the real datatype issue loses resolution >>>>> in >>>>> power of 2's. pgadmin is losing in the power of 10's. It seems like a >>>>> string parsing issue in the jdbc that only parses the first 6 digits. >>>>> >>>>> So I'd like to configure pgadmin to use the same jdbc driver as my >>>>> code. >>>>> Which works. Where can I set the driver for pgadmin? >>>> >>>> >>>> >>>> pgAdmin isn't a Java app. It's written in C++ and uses libpq to access >>>> the server. >>>> >>>> Where in pgAdmin do you see the rounding? (query tool, edit grid >>>> or...). Can you provide a self contained test case? >>>> >>>> >>>> -- >>>> Dave Page >>>> Blog: http://pgsnake.blogspot.com >>>> Twitter: @pgsnake >>>> >>>> EnterpriseDB UK: http://www.enterprisedb.com >>>> The Enterprise PostgreSQL Company >>>> >>> >> >> >> >> -- >> Dave Page >> Blog: http://pgsnake.blogspot.com >> Twitter: @pgsnake >> >> EnterpriseDB UK: http://www.enterprisedb.com >> The Enterprise PostgreSQL Company >> > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company