Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rmDMc-006gCc-QO for pgsql-hackers@arkaria.postgresql.org; Mon, 18 Mar 2024 13:49:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rmDMb-001yBt-4B for pgsql-hackers@arkaria.postgresql.org; Mon, 18 Mar 2024 13:49:17 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rlVgO-00GGqp-0p for pgsql-hackers@lists.postgresql.org; Sat, 16 Mar 2024 15:10:48 +0000 Received: from mail-pf1-x42e.google.com ([2607:f8b0:4864:20::42e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rlVgK-004rlI-Ez for pgsql-hackers@lists.postgresql.org; Sat, 16 Mar 2024 15:10:47 +0000 Received: by mail-pf1-x42e.google.com with SMTP id d2e1a72fcca58-6e6fb9a494aso1036434b3a.0 for ; Sat, 16 Mar 2024 08:10:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1710601842; x=1711206642; darn=lists.postgresql.org; h=to:message-id:subject:date:mime-version:from :content-transfer-encoding:from:to:cc:subject:date:message-id :reply-to; bh=6kS7msn9QpREuidiCruRhEIUOJEPXbo3kWusk7O4irM=; b=EUU6BUeSPxrfGAFzufTSAJvKLMx+ZAqkM7v2hW07rVvpjSRh2fPwT6AsLlPXeorEK9 vm28YrzwEUKZqVAaCqKCriyR5IthF16f63kaMstpiwEalMwIzYTr25HuxzFBZHba08kN /FbZzzL5wekgjCAZn16Z6DvDh+Rzx/DUnAA2NNFhJi0KgzqSo10AANkumnlMnoKP9IIl rZlFb3qJ8J3MjVROWyYK5DrOnhVmeRfJLIgDlRBSlBDe3WtkpsVpTMi4tvy2UkIwTe/b ovfqxgSdm5sUPCri4cTSwj/olkX+G3BgHOFETib9NmpkWE0RYYjxdQSiSvEE+bvDub4Y B1dA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1710601842; x=1711206642; h=to:message-id:subject:date:mime-version:from :content-transfer-encoding:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=6kS7msn9QpREuidiCruRhEIUOJEPXbo3kWusk7O4irM=; b=iyVs9SvhkLyrIvqFkrQ3Rp5Wc1LRmoA6ryMA9EVhKXX7RpZw3KCOAxz3II7xdbAeDJ IWMcQNRxf0G2/38ZEqIWCNsP4dGG3YIzT3rVVPvOL9dCBo+4kgXuNvv9n1Ipth8otUYh 2iKFBCq+L64GcnMqriv3ovQa4NTEbjlyY2/IkBTzb9Eo+fQa4CgUsr19YpXn4vpS+nh0 2E1bG1uxuS0Y1wX+o8ub4L2GGeAEQz4IHb16XSEKceuSF2U7fPqs3eBJ3LlNkGUIqBu3 9N2XJGD2s+tE67rv45TNsWqBd24n1MhBe8xzJvq6Upeo5nqlUWcDHZ6F6NvIzHuRGxMi EVjw== X-Gm-Message-State: AOJu0Yxyv2eZOv9bdGmmpKQOBhCwn12xCVYZIMZWpuyRop5L9DfrqHbF XY1465wzmifKCfei5B6uab30lIiPGOeAP7cEzpSMxpIzpruKcMP5WyGib0PdW8g= X-Google-Smtp-Source: AGHT+IGiPhchzLn1iyFuYGK05BXU/6DLiUrQNwyuyVOJ6fouJSO8YXBmyVRWv8+f3QRwk98cu2YuZg== X-Received: by 2002:a05:6a20:5091:b0:1a1:6b78:d70 with SMTP id l17-20020a056a20509100b001a16b780d70mr4479021pza.61.1710601841616; Sat, 16 Mar 2024 08:10:41 -0700 (PDT) Received: from smtpclient.apple ([2405:201:6823:687b:30e5:5dc6:7181:acfc]) by smtp.gmail.com with ESMTPSA id v1-20020a62c301000000b006e6f9e13a8csm3079612pfg.192.2024.03.16.08.10.40 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 16 Mar 2024 08:10:41 -0700 (PDT) Content-Type: multipart/alternative; boundary=Apple-Mail-11492FF7-4B50-4E08-92CA-96B112F07981 Content-Transfer-Encoding: 7bit From: Rahul Uniyal Mime-Version: 1.0 (1.0) Date: Sat, 16 Mar 2024 20:40:29 +0530 Subject: Java : Postgres double precession issue with different data format text and binary Message-Id: <8DAAADB3-47C6-42BA-B7BF-23C34855D720@gmail.com> To: pgsql-hackers@lists.postgresql.org X-Mailer: iPhone Mail (21D61) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail-11492FF7-4B50-4E08-92CA-96B112F07981 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable Hello Team, Hope everyone is doing well here. I am writing this email to understand an issue I'm facing when fetching data= in our Java application. We are using PostgreSQL JDBC Driver version 42.6.0= . Issue: We are encountering an issue where the double precision data type in Postgre= SQL is giving some intermittent results when fetching data. For example, in t= he database the value is 40, but sometimes this value is fetched as 40.0. Si= milarly, for a value of 0.0005, it is being fetched as 0.00050, resulting in= extra trailing zeros. While debugging, it seems like this issue is caused by the different data fo= rmats, such as Text and Binary. There is some logic in the PgResultSet class= that converts values based on this data format. Example: Below is an example where we are getting different data formats for the same= table: Text Format: [Field(id,FLOAT8,8,T), Field(client_id,FLOAT8,8,T), Field(creat= e_ts,TIMESTAMP,8,T), ...] Binary Format: [Field(id,FLOAT8,8,B), Field(client_id,FLOAT8,8,B), ...] (not= ice some format changes) We are not sure why different formats are coming for the same table. Schema: Below is the schema for the table used: SQL =20 CREATE TABLE IF NOT EXISTS SUBMISSION_QUEUE( ID DOUBLE PRECISION, CLIENT_ID DOUBLE PRECISION, OCODE VARCHAR(20) NOT NULL, PAYLOAD_TYPE VARCHAR(20), REPOSITORY VARCHAR(16), SUB_REPOSITORY VARCHAR(20), FORCE_GENERATION_FLAG BOOLEAN, IS_JMX_CALL BOOLEAN, INSTANCE_ID DOUBLE PRECISION, CREATE_TS TIMESTAMP(6) NOT NULL, ); Request: Team, would it be possible to give some insight on this issue? Any help woul= d be greatly appreciated. Thanks,= --Apple-Mail-11492FF7-4B50-4E08-92CA-96B112F07981 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit

Hello Team,

Hope everyone is doing well here.

I am writing this email to understand an issue I'm facing when fetching data in our Java application. We are using PostgreSQL JDBC Driver version 42.6.0.

Issue:

We are encountering an issue where the double precision data type in PostgreSQL is giving some intermittent results when fetching data. For example, in the database the value is 40, but sometimes this value is fetched as 40.0. Similarly, for a value of 0.0005, it is being fetched as 0.00050, resulting in extra trailing zeros.

While debugging, it seems like this issue is caused by the different data formats, such as Text and Binary. There is some logic in the PgResultSet class that converts values based on this data format.

Example:

Below is an example where we are getting different data formats for the same table:

Text Format: [Field(id,FLOAT8,8,T), Field(client_id,FLOAT8,8,T), Field(create_ts,TIMESTAMP,8,T), ...]

Binary Format: [Field(id,FLOAT8,8,B), Field(client_id,FLOAT8,8,B), ...] (notice some format changes)

We are not sure why different formats are coming for the same table.

Schema:

Below is the schema for the table used:

SQL

 

 CREATE TABLE IF NOT EXISTS SUBMISSION_QUEUE(
  ID               DOUBLE PRECISION,
  CLIENT_ID           DOUBLE PRECISION,
  OCODE VARCHAR(20) NOT NULL,
  PAYLOAD_TYPE        VARCHAR(20),
  REPOSITORY VARCHAR(16),
  SUB_REPOSITORY          VARCHAR(20),
  FORCE_GENERATION_FLAG   BOOLEAN,
IS_JMX_CALL BOOLEAN,
INSTANCE_ID           DOUBLE PRECISION,
CREATE_TS         TIMESTAMP(6) NOT NULL,
);


Request:

Team, would it be possible to give some insight on this issue? Any help would be greatly appreciated.

Thanks,

--Apple-Mail-11492FF7-4B50-4E08-92CA-96B112F07981--