2

I am using libpq to connect to Postgres on Windows 10 using Visual Studio 2015, PostgreSQL9.4

I have a table like this:

CREATE TABLE real_data (
  plane_id integer NOT NULL PRIMARY KEY,
  timestamp integer NOT NULL,
  data1 double precision
);

INSERT INTO real_data VALUES ('100001', '14987654', 635.28938)

And when I get it out char* data1 = PQgetvalue(res, row,2);, data1 is "635.28938" in the watch, and if I convert it to double with atof, double d_value = atof(data1), d_value is 635.28938000000000005. It's not exactly the same.

I found this question on SO: How to get the double value using libpq? , but I don't think I can follow it since I have a ascii string representing the original double in data1.

How can I get an accurate double number from Postgres?

Thanks a lot.

Community
  • 1
  • 1
larryzhao
  • 3,173
  • 2
  • 40
  • 62
  • Use exact datatype `DECIMAL/NUMERIC` – Lukasz Szozda Oct 28 '15 at 16:16
  • @lad2025 I tried `NUMERIC (15, 8)`, it's the same. I haven't try `DECIMAL` yet. – larryzhao Oct 28 '15 at 16:17
  • The result of `atof` followed by `printf` is independant of libpq or postgres. What you see is just an expected imprecision in a decimal representation of IEEE double. See for example [decimal number and exact representation](http://stackoverflow.com/questions/9068434) – Daniel Vérité Oct 28 '15 at 16:57
  • @DanielVérité Yes, I understand it has nothing to do with libpq/postgres. What I'd like to know is how to get an exact double value from postgres. What I could find is to use PQgetValue, but it gives me an ascii string of the double value ( which I thought it would be a binary value ). So I had to convert it with atof, from which I get a double value with a tiny difference. Is there a way I could get an exact double number out of Postgres directly? – larryzhao Oct 30 '15 at 17:12
  • @larryzhao: there's no way because the imprecision comes from the `double` datatype itself, both in C/C++ and in SQL. If you requested a binary representation (it's possible, see `PQexecParams`) it wouldn't change that fact. – Daniel Vérité Oct 30 '15 at 18:15

0 Answers0