From: | Justin Bailey <jgbailey(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Automatic truncation of character values & casting to the type of a column type |
Date: | 2009-12-17 00:49:57 |
Message-ID: | a45dff840912161649v17f7c8b7ra4af624c510ddd4f@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Greetings! I am trying to avoid the old problem of inserting a 40
character string into a 20 character field. However, I'd like to avoid
hard-coding the acceptable length (20). Is there a way to say "cast to
the same type as a given column"? E.g., if I have tables Long and
Short:
CREATE TABLE Long (longCol varchar(40) )
CREATE TABLE Short (shortCol varchar(20) )
And this data:
INSERT INTO Long VALUES ('FOOBAR'), ('BAZ'),
(CAST('2314J1L234J21LK342JKL32J32KL4J123LK4J13L4' AS VARCHAR(40)))
Can make values inserted into shortCol have a maximum length of 20
without hard-coding that value? Something like:
INSERT INTO Short (ShortCol)
(SELECT CAST(Long.longCol as Short.shortCol) FROM LONG)
I am using postgres 8.2.
Clearly this is a toy example. In the real world, I insert or update
values in my target table using a stored procedure. I want to
future-proof my stored procedure against the column lengths on the
target table changing. Otherwise, I have to update my sproc with new
lengths if the table ever changes. I have tried using the PL/PGSQL
feature where types can be "copied" in a declaration:
DECLARE
myVal Short.shortCol%TYPE;
...
But I can still put values which are too long into that variable, so
it doesn't help me. Sadly, using the same syntax in a CAST fails in
various ways:
UPDATE Short SET shortCol = CAST(myVal AS Short.shortCol) -- schema
"Short" does not exist error
UPDATE Short SET shortCol = CAST(myVal AS Short.shortCol%TYPE) -- syntax error
UPDATE Short SET shortCol = CAST(myVal AS (Short).shortCol) -- syntax error
Thanks in advance for any advice
Justin
From | Date | Subject | |
---|---|---|---|
Next Message | Justin Bailey | 2009-12-17 01:05:19 | Automatic truncation of character values & casting to the type of a column type |
Previous Message | Peter Geoghegan | 2009-12-17 00:42:55 | Re: Justifying a PG over MySQL approach to a project |