Thread: 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