Thread: Formatting query output
Hi All, I am using libpq to interface PostgreSQL with Matlab. My question is about formatting query results: If I have a table field which is of type float8 and I insert, for example, a value of 4503599627370496, when I do a select on this table field I get 4.5035996273705e+15. The query result is rounded. When I use libpq's PQgetvalue() this is the value I get. PQftype() correctly indicates a float8. How can I get the actual (503599627370496) value by doing a direct query to this field (maintaining PQftype() float8 result) ? I can do it whit PostgreSQL function to_char() but the field type will be set to some CHAR based type. I woul like to have the actual value along with a PQftype() float8 result. Thanks for any help. Pedro M. Ferreira -- ---------------------------------------------------------------------- Pedro Miguel Frazao Fernandes Ferreira Universidade do Algarve Faculdade de Ciencias e Tecnologia Campus de Gambelas 8000-117 Faro Portugal Tel./Fax: (+351) 289 800950 / 289 819403 http://w3.ualg.pt/~pfrazao
On Mon, Oct 28, 2002 at 02:56:20PM +0000, Pedro Miguel Frazao Fernandes Ferreira wrote: > Hi All, > > I am using libpq to interface PostgreSQL with Matlab. > My question is about formatting query results: > > If I have a table field which is of type float8 and I insert, for > example, a value of 4503599627370496, when I do a select on this table > field I get 4.5035996273705e+15. I think the reason for that is that float8s can only store about 16 significant digits anyway so you're cutting pretty close to the line. Remember, floating point numbers are accurate but not precise. Do you really need that extra digit of precision? Maybe you should be using numeric() type for this. Actually, it looks like a credit card number, perhaps you should use a string if you're not actually doing calculations on it. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
Attachment
Martijn van Oosterhout wrote:
> On Mon, Oct 28, 2002 at 02:56:20PM +0000, Pedro Miguel Frazao Fernandes Ferreira wrote:
>
>>Hi All,
>>
>>I am using libpq to interface PostgreSQL with Matlab.
>>My question is about formatting query results:
>>
>>If I have a table field which is of type float8 and I insert, for
>>example, a value of 4503599627370496, when I do a select on this table
>>field I get 4.5035996273705e+15.
>
>
> I think the reason for that is that float8s can only store about 16
> significant digits anyway so you're cutting pretty close to the line.
> Remember, floating point numbers are accurate but not precise. Do you really
> need that extra digit of precision?
Ok, but PostgreSQL stores the number correctly:
(real8 is the field name)
select to_char(real8,'9999999999999999999.99999') from test;
to_char
----------------------
4503599627370496
(1 row)
Query output formatting for float8 does the following:
select real8 from test;
real8
---------------------
4.5035996273705e+15
(1 row)
I have a simple C interface between PostgreSQL and Matlab which is a
Matrix based computation and simulation software. I want to use
PostgreSQL to store data from my simulations. When I store a float8
number in a database, I am supposed to fetch the number as inserted. It
is not a matter of wanting a determined precision. Matlab data types
match to some PostgreSQL data types in size.
As you can see above the number is stored correctly, its just the output
precision for float8 in querys that cuts the number for output. I can do
it with the to_char() function but then libpq PQftype() function will
not return a float8 type for this field.
I believe there is (should be ?) some runtime parameter to control the
output precision for floats, but I still can not find anything in the
doc's. Do you know anything like this ?
>
> Maybe you should be using numeric() type for this. Actually, it looks like a
> credit card number, perhaps you should use a string if you're not actually
> doing calculations on it.
>
I think now you understand there is no need for numeric in this case,
and that strings are not a correct storage solution.
Thank you very much for your reply.
Best regards,
Pedro M. Ferreira
--
----------------------------------------------------------------------
Pedro Miguel Frazao Fernandes Ferreira
Universidade do Algarve
Faculdade de Ciencias e Tecnologia
Campus de Gambelas
8000-117 Faro
Portugal
Tel./Fax: (+351) 289 800950 / 289 819403
http://w3.ualg.pt/~pfrazao
Pedro Miguel Frazao Fernandes Ferreira wrote: > select real8 from test; > real8 > --------------------- > 4.5035996273705e+15 The conversion from float8 to text occurs in the float8out function, defined in src/backend/utils/adt/float.c, using: sprintf(ascii, "%.*g", DBL_DIG, num) The value of DBL_DIG is defined in the standard include file <float.h> and typically has a value of 15. I would not recommend changing this value in <float.h>, but you could redefine it in float.c, or just change the sprintf to use a hardcoded value of 16, and recompile and reinstall. -- Peter Gibbs EmKel Systems
Hi, I just wanted to know is it possible to improve the performance of a query if the query is calling any stored procedure in it. Because when I run this query it takes 100% CPU usage and other applications are hanging. Please let me nkow the solution is there something we can apply in stored procedure. -- Best Regards - Savita ---------------------------------------------------- Hewlett Packard (India) +91 80 2051288 (Phone) 847 1288 (HP Telnet) ----------------------------------------------------
i have got this error what is it? DEBUG: recycled transaction log file 0000000000000048 DEBUG: recycled transaction log file 0000000000000049 DEBUG: recycled transaction log file 000000000000004A DEBUG: recycled transaction log file 000000000000004B DEBUG: recycled transaction log file 000000000000004C DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES DEBUG: recycled transaction log file 000000000000004D DEBUG: recycled transaction log file 000000000000004E DEBUG: recycled transaction log file 000000000000004F DEBUG: recycled transaction log file 0000000000000050 DEBUG: recycled transaction log file 0000000000000051
On Tue, Oct 29, 2002 at 01:58:29PM +0100, Florian Litot wrote:
> i have got this error
> what is it?
It's not an error. It's your WAL recycling. See the section on WAL
in the manual.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
On 29 Oct 2002 at 13:58, Florian Litot wrote: > i have got this error > what is it? This is not error. It's just a message. > > DEBUG: recycled transaction log file 0000000000000048 > DEBUG: recycled transaction log file 0000000000000049 Postgresql uses some WAL files which are fixed in size and fixed in number for a running instance. Obviously for a running instance there are going to be more transactions than it can hold it in WAL. But transaction in WAL are periodically flushed to main database. After this flush is complete, the WAL file can be used to hold another set of transaction(rather WAL file name, if I understand it correctly..) This message just says that it's reusing WAL files. If this is too frequent, consider increasing wal_files in postgresql.conf, but anything beyond 5 or 10 is overkill, given that each WAL file is 16MB in size.. HTH Bye Shridhar -- design, v.: What you regret not doing later on.
On 29 Oct 2002 at 16:42, Savita wrote: > I just wanted to know is it possible to improve the performance of a query if > the query is calling any stored procedure in it. Generally if you are making 3-4 calls for select/update/insert, a stored procedure works much better compared to separate calls to database because communication overhead is only once and database probably pre-parse-pre-plan the queries in it. So yes, it does improve performance.. > Because when I run this query it takes 100% CPU usage and other applications are > hanging. What's the query? Could you elaborate more on that? And other applications are hanging is rediculous behaviour unless it's windows. On linux, any 100% CPU chewing task should not block any other task. At least 2.4.19 onwards, same goes for other unices.. Windows is a different story.(My mail client, pegasus maxes out CPU, if it can not reach SMTP host for some reason. This is XP/SP1.. Go figure) Bye Shridhar -- Dijkstra probably hates me(Linus Torvalds, in kernel/sched.c)
Pedro Miguel Frazao Fernandes Ferreira <pfrazao@ualg.pt> writes:
> When I store a float8
> number in a database, I am supposed to fetch the number as inserted.
I fear you have a fundamental misconception about the nature of
floating-point representation. There is no such thing as "fetching the
number as inserted". You get only as much accuracy as float8 format
will store, which is about half a digit less than you are asking for in
this case.
The fact that you might be able to store this particular 16-digit value
exactly is no guarantee that you'll be able to store other 16-digit
values exactly, so I recommend choosing another representation if
that's your requirement. Blaming the output formatting is the wrong
way to look at it.
regards, tom lane
Hi, My machine is a windowsNT machine. In my stored procedure I am using some statements like 1.select count(*) into cust_count from os_customer_view; 2.select to_number(substring(os_crm_exchange_unit_id, 4), 999999999999) into id from os_unit_view offset row; SO If I call a query which is using this stored procedure than execution is very slow. Is there any way to fine tune the statements inside the stored procedure.I have created index on os_crm_exchange_unit_id field. Like in ORACLE if you use count(*),sub-string,to-number then indexing won't work is there something like this is there in postgres also.Is yes then what is the appropriate solution for this. Shridhar Daithankar wrote: > On 29 Oct 2002 at 16:42, Savita wrote: > > I just wanted to know is it possible to improve the performance of a query if > > the query is calling any stored procedure in it. > > Generally if you are making 3-4 calls for select/update/insert, a stored > procedure works much better compared to separate calls to database because > communication overhead is only once and database probably pre-parse-pre-plan > the queries in it. So yes, it does improve performance.. > > > > Because when I run this query it takes 100% CPU usage and other applications are > > hanging. > > What's the query? Could you elaborate more on that? > > And other applications are hanging is rediculous behaviour unless it's windows. > On linux, any 100% CPU chewing task should not block any other task. At least > 2.4.19 onwards, same goes for other unices.. Windows is a different story.(My > mail client, pegasus maxes out CPU, if it can not reach SMTP host for some > reason. This is XP/SP1.. Go figure) > > Bye > Shridhar > > -- > Dijkstra probably hates me(Linus Torvalds, in kernel/sched.c) > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Best Regards - Savita ---------------------------------------------------- Hewlett Packard (India) +91 80 2051288 (Phone) 847 1288 (HP Telnet) ----------------------------------------------------
Hi, I am disabling the seqscan by set enable_seqscan=no; I want to know is there any way to disable merge,joins,nested loops also. -- Best Regards - Savita ---------------------------------------------------- Hewlett Packard (India) +91 80 2051288 (Phone) 847 1288 (HP Telnet) ----------------------------------------------------
These options are defined in the postgresql.conf file: #enable_seqscan = true #enable_indexscan = true #enable_tidscan = true #enable_sort = true #enable_nestloop = true #enable_mergejoin = true #enable_hashjoin = true Or you can change them temporarily on the command line using the SET command as you have done Adam > Hi, > > I am disabling the seqscan by > set enable_seqscan=no; > > I want to know is there any way to disable merge,joins,nested loops also. > > -- > Best Regards > - Savita > ---------------------------------------------------- > Hewlett Packard (India) > +91 80 2051288 (Phone) > 847 1288 (HP Telnet) > ---------------------------------------------------- > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
Tom Lane wrote:
> Pedro Miguel Frazao Fernandes Ferreira <pfrazao@ualg.pt> writes:
>
>>When I store a float8
>>number in a database, I am supposed to fetch the number as inserted.
>
>
> I fear you have a fundamental misconception about the nature of
> floating-point representation. There is no such thing as "fetching the
> number as inserted". You get only as much accuracy as float8 format
> will store, which is about half a digit less than you are asking for in
> this case.
Yes, I understand this. I must explain a bit more. All the float numbers
I will insert in PostgreSQL datababases will come from Matlab's double
type which is the same format as PostgreSQL float8 or a C double. When I
say "fetching the number as inserted" I am talking about numbers which
already come from an 8 byte float representation. If the number can be
stored in C or Matlab float 8 it can also be stored in PostgreSQL float8.
>
> The fact that you might be able to store this particular 16-digit value
> exactly is no guarantee that you'll be able to store other 16-digit
> values exactly, so I recommend choosing another representation if
> that's your requirement. Blaming the output formatting is the wrong
> way to look at it.
Its not a matter of blaming. I am not blaming anything neither anyone
work. I find PostgreSQL a very good example of open source high quality
software, which I use for long time for other type of applications, so
there's nothing to blame.
All I am saying is that float8 output in PostgreSQL query's is not
getting maximum precision. You can store a number with more precision
than the precision with which you can get it.
Do you understand that if I have a number stored in C double format and
I insert it in a database float8 field, I am supposed to be able to get
it back as it was stored in C double ?
Due to the way the output is formated currently this is not possible.
I now some people which needed to store double numbers which have
changed the code in src/backend/utils/adt/float.c because of this
problem. I just thought that instead of solving 'my' problem I should
report it so that it would be generally solved in order for PostgreSQL
to be used as storage for number crunching (in our case, distributed)
systems.
Again one example:
(4503599627370496 can be stored by a C double or PostgreSQL float8)
insert into test(real8) values (4503599627370496);
INSERT 21192 1
A select produces,
select real8 from test;
real8
---------------------
4.5035996273705e+15
(1 row)
In fact the number is correctly stored by PostgreSQL float8:
select to_char(real8,'9999999999999999999.99999') from test;
to_char
----------------------
4503599627370496
(1 row)
I hope you understand that all I want is that PostgreSQL can be used for
this type of application instead of other commercial database system's
currently used. A lot of people would start using it and maybe
supporting it.
Sorry if this message is a bit long.
Thanks for your reply and for PostgreSQL itself.
Best regards,
Pedro M. Ferreira
>
> regards, tom lane
>
>
--
----------------------------------------------------------------------
Pedro Miguel Frazao Fernandes Ferreira
Universidade do Algarve
Faculdade de Ciencias e Tecnologia
Campus de Gambelas
8000-117 Faro
Portugal
Tel./Fax: (+351) 289 800950 / 289 819403
http://w3.ualg.pt/~pfrazao