upgrading from 7.3.5 to 8.1.5

From: Frank Bax <fbax(at)sympatico(dot)ca>
To: pgsql-novice(at)postgresql(dot)org
Subject: upgrading from 7.3.5 to 8.1.5
Date: 2006-12-30 23:04:56
Message-ID: 5.2.1.1.0.20061230172957.0303cec0@pop6.sympatico.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I've got a couple of problems/questions - all in one table. These commands
came from pg_dump on 7.3.5:

CREATE TABLE payperiod (
pay real NOT NULL,
lo_date timestamp with time zone,
hi_date timestamp with time zone,
"input" timestamp with time zone,
payday date,
notes text
);
INSERT INTO payperiod VALUES (2006.01, '2005-12-22 00:00:00-05',
'2006-01-04 23:59:00-05', '2006-01-06 09:30:00-05', '2006-01-12', '');
INSERT INTO payperiod VALUES (2006.02, '2006-01-05 00:00:00-05',
'2006-01-18 23:59:00-05', '2006-01-20 09:30:00-05', '2006-01-26', '');
INSERT INTO payperiod VALUES (2006.03, '2006-01-19 00:00:00-05',
'2006-02-01 23:59:00-05', '2006-02-03 09:30:00-05', '2006-02-09', '');
INSERT INTO payperiod VALUES (2006.04, '2006-02-02 00:00:00-05',
'2006-02-15 23:59:00-05', '2006-02-17 09:30:00-05', '2006-02-23', '');
INSERT INTO payperiod VALUES (2006.05, '2006-02-16 00:00:00-05',
'2006-03-01 23:59:00-05', '2006-03-03 09:45:00-05', '2006-03-09', '');
INSERT INTO payperiod VALUES (2006.06, '2006-03-02 00:00:00-05',
'2006-03-15 23:59:00-05', '2006-03-17 10:00:00-05', '2006-03-23', '');
INSERT INTO payperiod VALUES (2006.07, '2006-03-16 00:00:00-05',
'2006-03-29 23:59:00-05', '2006-03-31 10:00:00-05', '2006-04-06', '');
INSERT INTO payperiod VALUES (2006.08, '2006-03-30 00:00:00-05',
'2006-04-12 23:59:00-04', '2006-04-13 09:40:00-04', '2006-04-20', 'Early
Cut-off');
INSERT INTO payperiod VALUES (2006.09, '2006-04-13 00:00:00-04',
'2006-04-26 23:59:00-04', '2006-04-28 09:30:00-04', '2006-05-04', '');
INSERT INTO payperiod VALUES (2006.1, '2006-04-27 00:00:00-04', '2006-05-10
23:59:00-04', '2006-05-12 10:10:00-04', '2006-05-18', '');
INSERT INTO payperiod VALUES (2006.11, '2006-05-11 00:00:00-04',
'2006-05-24 23:59:00-04', '2006-05-26 10:45:00-04', '2006-06-01', '');
INSERT INTO payperiod VALUES (2006.12, '2006-05-25 00:00:00-04',
'2006-06-07 23:59:00-04', '2006-06-09 09:30:00-04', '2006-06-15', '');
INSERT INTO payperiod VALUES (2006.13, '2006-06-08 00:00:00-04',
'2006-06-21 23:59:00-04', '2006-06-23 09:30:00-04', '2006-06-29', '');
INSERT INTO payperiod VALUES (2006.14, '2006-06-22 00:00:00-04',
'2006-07-05 23:59:00-04', '2006-07-07 09:30:00-04', '2006-07-13', '');
INSERT INTO payperiod VALUES (2006.15, '2006-07-06 00:00:00-04',
'2006-07-19 23:59:00-04', '2006-07-21 09:30:00-04', '2006-07-27', '');
INSERT INTO payperiod VALUES (2006.16, '2006-07-20 00:00:00-04',
'2006-08-02 23:59:00-04', '2006-08-03 16:30:00-04', '2006-08-10', 'Early
Cut-off');
INSERT INTO payperiod VALUES (2006.17, '2006-08-03 00:00:00-04',
'2006-08-16 23:59:00-04', '2006-08-18 09:30:00-04', '2006-08-24', '');
INSERT INTO payperiod VALUES (2006.18, '2006-08-17 00:00:00-04',
'2006-08-30 23:59:00-04', '2006-09-01 08:30:00-04', '2006-09-07', 'Early
Cut-off');
INSERT INTO payperiod VALUES (2006.19, '2006-08-31 00:00:00-04',
'2006-09-13 23:59:00-04', '2006-09-15 10:15:00-04', '2006-09-21', 'Stat');
INSERT INTO payperiod VALUES (2006.2, '2006-09-14 00:00:00-04', '2006-09-27
23:59:00-04', '2006-09-29 10:00:00-04', '2006-10-05', '');
INSERT INTO payperiod VALUES (2006.21, '2006-09-28 00:00:00-04',
'2006-10-11 23:59:00-04', '2006-10-13 10:00:00-04', '2006-10-19', 'Stat');
INSERT INTO payperiod VALUES (2006.22, '2006-10-12 00:00:00-04',
'2006-10-25 23:59:00-04', '2006-10-27 10:00:00-04', '2006-11-02', '');
INSERT INTO payperiod VALUES (2006.23, '2006-10-26 00:00:00-04',
'2006-11-08 23:59:00-05', '2006-11-10 11:02:00-05', '2006-11-16', '');
INSERT INTO payperiod VALUES (2006.25, '2006-11-23 00:00:00-05',
'2006-12-06 23:59:00-05', '2006-12-08 10:00:00-05', '2006-12-14', '');
INSERT INTO payperiod VALUES (2006.24, '2006-11-09 00:00:00-05',
'2006-11-22 23:59:00-05', '2006-11-24 10:00:00-05', '2006-11-30', '');
INSERT INTO payperiod VALUES (2006.26, '2006-12-07 00:00:00-05',
'2006-12-20 23:59:00-05', '2006-12-21 10:25:00-05', '2006-12-28', 'Early
Cut-off');

CREATE UNIQUE INDEX payperiod_pkey ON payperiod USING btree (pay);

All output in these examples is from 8.1.5:

1) select and pg_dump produce different results. Why does .06 change to
.0601 and .07 to .0699?
$ pg_dump -t payperiod -d | grep 01,
INSERT INTO payperiod VALUES (2006.01, '2005-12-22 00:00:00-05',
'2006-01-04 23:59:00-05', '2006-01-06 09:30:00-05', '2006-01-12', '');
INSERT INTO payperiod VALUES (2006.0601, '2006-03-02 00:00:00-05',
'2006-03-15 23:59:00-05', '2006-03-17 10:00:00-05', '2006-03-23', '');
INSERT INTO payperiod VALUES (2006.1801, '2006-08-17 00:00:00-04',
'2006-08-30 23:59:00-04', '2006-09-01 08:30:00-04', '2006-09-07', 'Early
Cut-off');

$ pg_dump -t payperiod -d | grep 99,
INSERT INTO payperiod VALUES (2006.0699, '2006-03-16 00:00:00-05',
'2006-03-29 23:59:00-05', '2006-03-31 10:00:00-05', '2006-04-06', '');
INSERT INTO payperiod VALUES (2006.1899, '2006-08-31 00:00:00-04',
'2006-09-13 23:59:00-04', '2006-09-15 10:15:00-04', '2006-09-21', 'Stat');

2) age() changed from 7.3.5 to 8.1.5?

select lo_date,hi_date,age(hi_date,lo_date)+'1 min' as d14 from payperiod
where age(hi_date,lo_date)+'1 min' <> '14 days';
lo_date | hi_date | d14
------------------------+------------------------+---------------
2006-03-30 00:00:00-05 | 2006-04-12 23:59:00-04 | 13 days 23:00
2006-10-26 00:00:00-04 | 2006-11-08 23:59:00-05 | 14 days 01:00
(2 rows)

On 8.1.5, the query returns no results. How do I now identify the
payperiods where there is a time change?

3) I was surprised to see that d14 contains '13 days 24:00:00' instead of
'14 days' (which is what 7.3.5 returned), but my query works anyway; so I
don't much care about this detail.

Frank

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2006-12-31 01:09:44 Re: upgrading from 7.3.5 to 8.1.5
Previous Message Alvaro Herrera 2006-12-29 03:09:35 Re: Performances