Re: Postgres 7.3 migrate to 8.0 date problems.

On Wed, Mar 30, 2005 at 04:23:34PM -0500, Robert Treat wrote:
>
> Would it be possible to use a BEFORE trigger to reformat the YYYY-DD-MM
> date to YYYY-MM-DD ?  The error I see on 7.4 is ERROR:  date/time field
> value out of range: "2005-14-01" so ISTM you could do some data
> manipulation if you wanted.

I don't think that would work if the target column has type DATE,
presumably because NEW has the same type as a row of the table, so
NEW.datefield would be a DATE and the YYYY-DD-MM value would raise
an exception before the trigger was ever called.
 CREATE TABLE foo (     id         serial PRIMARY KEY,     datefield  date NOT NULL );  CREATE FUNCTION datefix()
RETURNStrigger AS $$ BEGIN     RAISE INFO 'datefix';     NEW.datefield := current_date;  -- for testing     RETURN NEW;
END;$$ LANGUAGE plpgsql;  CREATE TRIGGER footrig BEFORE INSERT OR UPDATE ON foo   FOR EACH ROW EXECUTE PROCEDURE
datefix(); INSERT INTO foo (datefield) VALUES ('2005-03-30');  -- valid INFO:  datefix INSERT 0 1  INSERT INTO foo
(datefield)VALUES ('2005-30-03');  -- not valid ERROR:  date/time field value out of range: "2005-30-03" HINT:  Perhaps
youneed a different "datestyle" setting.
 

Notice that the trigger wasn't called for the second INSERT.

Just brainstorming now, but if you want to keep datefield as a DATE,
then maybe you could create a view with datefield cast to TEXT and
create an appropriate rule so you can insert into the view and have
YYYY-DD-MM converted to YYYY-MM-DD:
 DROP TABLE foo; DROP FUNCTION datefix();
 CREATE TABLE foo (     id         serial PRIMARY KEY,     datefield  date NOT NULL );
 CREATE VIEW fooview AS SELECT id, datefield::text FROM foo;
 CREATE RULE datefix AS ON INSERT TO fooview  DO INSTEAD  INSERT INTO foo (datefield) VALUES (to_date(NEW.datefield,
'YYYY-DD-MM'));
 INSERT INTO fooview (datefield) VALUES ('2005-30-03'); INSERT 0 1  SELECT * FROM foo;  id | datefield
----+------------  1 | 2005-03-30 (1 row)
 

I admittedly haven't thought this through very far so it could have
problems, but it might be a starting point.  On the other hand, I'm
inclined to agree with Scott Marlowe's advice: fix the data.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/