Обсуждение: Fixing a too long column value in a before insert trigger or rule

Hi all,

We have a situation where a varchar column was limited to a too small
maximum length in the design phase... shit happens, we will fix our db
schema in our next database downtime or software version deployment,
whichever comes first (we are using 7.4 and on the fly column type
change is only from 8.0 on I guess).
But in the meantime it would be nice to have a trigger or something in
place which could intercept inserts and fix the offending value by
truncating it, so that the insert doesn't fail because of this.

I've tried with a before insert trigger, but the column size seems to be
checked before the trigger ever gets a chance to be called, I suppose
it's a parse time check.

A rule will probably also not work, as I can't replace an insert with
another one to the same table, at least that's how I understand from the
docs, otherwise it causes recursive rule processing...

Is there any way to fix the length of a row to fit in the table in any
way, be it a rule or trigger ? Or the length check will kick in even for
a rule before it gets a chance to fix it ?

TIA,
Csaba.

Csaba Nagy <nagy@ecircle-ag.com> writes:

> Hi all,
>
> We have a situation where a varchar column was limited to a too small
> maximum length in the design phase... shit happens, we will fix our db
> schema in our next database downtime or software version deployment,
> whichever comes first (we are using 7.4 and on the fly column type
> change is only from 8.0 on I guess).

You can do surgery on the system catalogs in 7.4 if you need to change
the length of a varchar.  8.0 just put a nice ALTER TABLE interface on
top of it.  Read the system catalog docs and definitely try it on a
test database first.  :)

-Doug

Csaba Nagy <nagy@ecircle-ag.com> writes:
> We have a situation where a varchar column was limited to a too small
> maximum length in the design phase... shit happens, we will fix our db
> schema in our next database downtime or software version deployment,
> whichever comes first (we are using 7.4 and on the fly column type
> change is only from 8.0 on I guess).

Why don't you just hack the column's atttypmod?  See the archives
(and practice on a test database ;-))

            regards, tom lane

Searching the archives lead me to this link:
http://archives.postgresql.org/pgsql-general/2001-10/msg01208.php
(I also searched before asking but for the wrong keywords...)

Is that advice still valid ?

I will try in any case :-)

Thanks,
Csaba.


On Wed, 2005-05-11 at 15:44, Tom Lane wrote:
> Csaba Nagy <nagy@ecircle-ag.com> writes:
> > We have a situation where a varchar column was limited to a too small
> > maximum length in the design phase... shit happens, we will fix our db
> > schema in our next database downtime or software version deployment,
> > whichever comes first (we are using 7.4 and on the fly column type
> > change is only from 8.0 on I guess).
>
> Why don't you just hack the column's atttypmod?  See the archives
> (and practice on a test database ;-))
>
>             regards, tom lane