Re: trimming a column

On 05/03/2012 06:01 PM, Michael P. Soulier wrote:
> Hi,
>
> I need to trim whitespace off of a whole column and replace the existing
> values with the trimmed ones.
>
> This isn't working
>
> update mytable set id = trim(id);
>
> I'm not sure of the correct syntax. Help appreciated.

Works here:
test=> SELECT version();
version

---------------------------------------------------------------------------------------
PostgreSQL 9.0.7 on i686-pc-linux-gnu, compiled by GCC gcc (SUSE
Linux) 4.6.2, 32-bit

test=> create table trim_test(fld_1 varchar);
CREATE TABLE
test=> INSERT INTO trim_test VALUES (' test ');
INSERT 0 1
test=> INSERT INTO trim_test VALUES (' test1 ');
INSERT 0 1
test=> INSERT INTO trim_test VALUES (' test2 ');
INSERT 0 1
test=> SELECT length(fld_1), fld_1 from trim_test ;
length | fld_1
--------+---------
6 | test
7 | test1
7 | test2
(3 rows)

test=> UPDATE trim_test set fld_1 = trim(fld_1);
UPDATE 3
test=> SELECT length(fld_1), fld_1 from trim_test ;
length | fld_1
--------+-------
4 | test
5 | test1
5 | test2

Sure you do not have an open transaction?
Say did the the UPDATE in one session inside a transaction without
issuing a COMMIT and looking at data in another session that will not
see changes until COMMIT is done.

>
> Mike

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com