Thread

Loss of significant digits on pg_dump

PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-04-10T16:11:37Z

Patricia Holben (pholben@greatbridge.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Loss of significant digits on pg_dump

Long Description
This has been noticed on 7.0.3 and all betas/releases of 7.1.  When data is stored as a timestamp(see examples) and then is dumped and reloaded, display and math operations present different results before vice after the dump. .

Sample Code
A more complete example can be generated by running the regression tests, dumping the db, dropping the db, creating the db, loading the dumpfile,use psql to go into the database, set datestyle = 'Postgres,Noneuropean'; select d1 from timestamp_tbl.
-----------------
short example:

test=# create table timestamp_tbl (d1 timestamp);
CREATE
test=# insert into timestamp_tbl values ('Mon Feb 10 17:32:01.000001 1997 PST');
INSERT 1305206 1
test=# select d1 from timestamp_tbl;
            d1             
---------------------------
 1997-02-10 20:32:01.00-05
(1row)

test=# set datestyle = 'Postgres,NonEuropean';

SET VARIABLE
test=# select d1 from timestamp_tbl;
               d1                
---------------------------------
 Mon Feb 10 20:32:01.00 1997 EST
(1 row)

test=# insert into timestamp_tbl values ('Mon Feb 10 17:32:01.999999 1997 PST');
INSERT 1305207 1
test=# select d1 from timestamp_tbl;
               d1                
---------------------------------
 Mon Feb 10 20:32:01.00 1997 EST
 Mon Feb 10 20:32:02.00 1997 EST
(2rows)

----   now dump the db, drop, recreate, reload the data

test=# select d1 from timestamp_tbl;
           d1           
------------------------
 1997-02-10 20:32:01-05
 1997-02-10 20:32:02-05
(2rows)

test=# set datestyle = 'Postgres,NonEuropean';
SET VARIABLE
test=# select d1 from timestamp_tbl;
              d1              
------------------------------
 Mon Feb 10 20:32:01 1997 EST
 Mon Feb 10 20:32:02 1997 EST
(2 rows)

-- now we insert 2 rows using identical inserts as above
test=# insert into timestamp_tbl values ('Mon Feb 10 17:32:01.000001 1997 PST');
INSERT 1797129 1
test=# insert into timestamp_tbl values ('Mon Feb 10 17:32:01.999999 1997 PST');
INSERT 1797130 1

-- now we expect the following math option to work the same on the pre-dump and after-dump entries 
test=# SELECT '' AS "53", date_part( 'year', d1) AS year, date_part( 'month', d1) AS month,
test-# date_part( 'day', d1) AS day, date_part( 'hour', d1) AS hour,
test-# date_part( 'minute', d1) AS minute, date_part( 'second', d1) AS second
test-# FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
 53 | year | month | day | hour | minute |  second  
----+------+-------+-----+------+--------+----------
    | 1997 |     2 |  10 |   20 |     32 |        1
    | 1997 |     2 |  10 |   20 |     32 |        2
    | 1997 |     2 |  10 |   20 |     32 | 1.000001
    | 1997 |     2 |  10 |   20 |     32 | 1.999999
(4 rows)


No file was uploaded with this report