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