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.klaver@gmail.com