Search Postgresql Archives

Re: Automatic truncation of character values & casting to the type of a column type

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wednesday 16 December 2009 5:05:19 pm Justin Bailey wrote:
> 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

My solution would be to declare the varchar without a length restriction and not 
worry. Right off the top I see two potential problems with the truncation 
procedure you are proposing. One, is if you go and reduce the field width for 
the table column you will have the same truncate error. Two, what happens to 
the 20 characters you are losing? They where important once are they not now?

-- 
Adrian Klaver
aklaver@xxxxxxxxxxx

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux