Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem with too short column

Problem with too short column

From
Thomas.AUBRY@limagrain.com
Date:
Hi,

I actually try to insert varchar which length is higher than 32 in a column
which type is varchar(32). I can't change the type of the column, and I
want to trunc the data. I know it's possible ! I have seen this !

Example : "j'aime les saucisses avec du beurre dedans" will be "j'aime les
saucisses avec du beu". For the moment it produces a SQL Error !

Is it an option in the config files ?

I'm using Postgresql 7.2.2.

Thanks in advance.

TOm

Re: Problem with too short column

From
"scott.marlowe"
Date:
On Wed, 4 Feb 2004 Thomas.AUBRY@limagrain.com wrote:

> Hi,
>
> I actually try to insert varchar which length is higher than 32 in a column
> which type is varchar(32). I can't change the type of the column, and I
> want to trunc the data. I know it's possible ! I have seen this !
>
> Example : "j'aime les saucisses avec du beurre dedans" will be "j'aime les
> saucisses avec du beu". For the moment it produces a SQL Error !
>
> Is it an option in the config files ?

No, it's your job to truncate it.  You could use substring in your insert
statement like this:

insert into tablename (fieldname) values
(substring('inputdatathatstoolonghere',0 for x);

where x is the length you want it chopped off at.

Further, you could create an insert (i.e. before) trigger to do the same
thing.

Note that the SQL spec is very clear, truncating automatically is an
error, and if you want things truncated, it's not the databases job to do
it by any kind of "default" setting.  Luckily, it's pretty easy to do it
either "by hand" in SQL with substring, or by trigger.

> I'm using Postgresql 7.2.2.

Upgrade.  There are bugs in the early 7.2 series you don't want to have to
deal with.  I'd recommend going to 7.4.1 if you can, but if not, at least
upgrade to the latest version of 7.2.  As long as you compile it the same
as your earlier 7.2 install, it's an in place (i.e. no dump restore
required) upgrade.

Re: Problem with too short column

From
Sam Barnett-Cormack
Date:
Truncate it in the applications? Create an SQL function with pg-perl or
something?

On Wed, 4 Feb 2004 Thomas.AUBRY@limagrain.com wrote:

> Hi,
>
> I actually try to insert varchar which length is higher than 32 in a column
> which type is varchar(32). I can't change the type of the column, and I
> want to trunc the data. I know it's possible ! I have seen this !
>
> Example : "j'aime les saucisses avec du beurre dedans" will be "j'aime les
> saucisses avec du beu". For the moment it produces a SQL Error !
>
> Is it an option in the config files ?
>
> I'm using Postgresql 7.2.2.
>
> Thanks in advance.
>
> TOm
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

--

Sam Barnett-Cormack
Software Developer                           |  Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk)  |  Lancaster University

Re: Problem with too short column

From
"scott.marlowe"
Date:
On Wed, 4 Feb 2004 Thomas.AUBRY@limagrain.com wrote:

> Hi,
>
> I actually try to insert varchar which length is higher than 32 in a column
> which type is varchar(32). I can't change the type of the column, and I
> want to trunc the data. I know it's possible ! I have seen this !
>
> Example : "j'aime les saucisses avec du beurre dedans" will be "j'aime les
> saucisses avec du beu". For the moment it produces a SQL Error !
>
> Is it an option in the config files ?

Note that in my previous posting I think I used substring with a 0 start,
it should start at 1:

select substring('abcdefgh',1,4);
SELECT
substring
----
abcd