Automatic truncation of character values & casting to the type of a column type
От | Justin Bailey |
---|---|
Тема | Automatic truncation of character values & casting to the type of a column type |
Дата | |
Msg-id | a45dff840912161705r1688f74at60901174745b7c4b@mail.gmail.com обсуждение исходный текст |
Ответ на | Automatic truncation of character values & casting to the type of a column type (Justin Bailey <jgbailey@gmail.com>) |
Ответы |
Re: Automatic truncation of character values & casting to the type of a column type
|
Список | 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
В списке pgsql-general по дате отправления: