Thread: trimming a column
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. Mike
Attachment
Michael -- >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. > >Mike > The trim function needs to be told what sort of trim to do -- Following the 9.1 manual (you did not specify which version of postgres you are using) try: UPDATE mytable SET id = trim(both ' ' from id). (untested) See <http://www.postgresql.org/docs/9.1/static/functions-string.html> HTH, Greg Williamson
On 05/03/2012 06:55 PM, Greg Williamson wrote:
> Michael --
>
>> 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.
>>
>> Mike
>>
>
> The trim function needs to be told what sort of trim to do --
>
> Following the 9.1 manual (you did not specify which version of postgres you are using) try:
>
> UPDATE mytable SET id = trim(both ' ' from id).
Actually not:)
test=> SELECT length(trim(' test '));
length
--------
4
>
> (untested)
>
> See<http://www.postgresql.org/docs/9.1/static/functions-string.html>
>
> HTH,
>
> Greg Williamson
>
--
Adrian Klaver
adrian.klaver@gmail.com
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
On 03/05/12 Adrian Klaver said:
> 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
eventsdb=# select version();
version
--------------------------------------------------------------------------------
----------------------------
PostgreSQL 8.4.10 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.4.5
2011021
4 (Red Hat 4.4.5-6), 32-bit
(1 row)
Perhaps it how the fields are displayed...
eventsdb=# select length(id), id from application_events;
length | id
--------+-----------------------------------------------------------------------
-------------------------------
48 | vmbg-msoulier3.nssg.mitel.com_1-1335549662-26268
44 | vmbg-msoulier3.nssg.mitel.com_1-1335549687-0
27 | tug-manage-ssl-1335549693-0
46 | vmbg-msoulier3.nssg.mitel.com_1-1335553128-344
44 | vmbg-msoulier3.nssg.mitel.com_1-1335553152-0
27 | tug-manage-ssl-1335553155-0
44 | vmbg-msoulier3.nssg.mitel.com_1-1336056097-0
46 | vmbg-msoulier3.nssg.mitel.com_1-1336058939-285
44 | vmbg-msoulier3.nssg.mitel.com_1-1336058940-0
(9 rows)
eventsdb=# select trim(id) from application_events;
btrim
--------------------------------------------------
vmbg-msoulier3.nssg.mitel.com_1-1335549662-26268
vmbg-msoulier3.nssg.mitel.com_1-1335549687-0
tug-manage-ssl-1335549693-0
vmbg-msoulier3.nssg.mitel.com_1-1335553128-344
vmbg-msoulier3.nssg.mitel.com_1-1335553152-0
tug-manage-ssl-1335553155-0
vmbg-msoulier3.nssg.mitel.com_1-1336056097-0
vmbg-msoulier3.nssg.mitel.com_1-1336058939-285
vmbg-msoulier3.nssg.mitel.com_1-1336058940-0
(9 rows)
still, in my app I see a lot of trailing whitespace after reading from the
db...
Mike
Attachment
On 03/05/12 Michael P. Soulier said: > still, in my app I see a lot of trailing whitespace after reading from the > db... [root@vmbg-msoulier3 eventviewer]# PYTHONPATH=.. python manage.py shell Python 2.6.6 (r266:84292, Dec 7 2011, 20:38:36) [GCC 4.4.6 20110731 (Red Hat 4.4.6-3)] on linux2 Type "help", "copyright", "credits" or "license" for more information. (InteractiveConsole) >>> from main.models import * >>> dir() ['ApplicationEvent', '__builtins__', 'datetime', 'models'] >>> events = ApplicationEvent.objects.all() >>> for event in events: ... print "'%s'" % event.id ... 'vmbg-msoulier3.nssg.mitel.com_1-1335549662-26268 ' 'vmbg-msoulier3.nssg.mitel.com_1-1335549687-0 ' 'tug-manage-ssl-1335549693-0 ' 'vmbg-msoulier3.nssg.mitel.com_1-1335553128-344 ' 'vmbg-msoulier3.nssg.mitel.com_1-1335553152-0 ' 'tug-manage-ssl-1335553155-0 ' 'vmbg-msoulier3.nssg.mitel.com_1-1336056097-0 ' 'vmbg-msoulier3.nssg.mitel.com_1-1336058939-285 ' 'vmbg-msoulier3.nssg.mitel.com_1-1336058940-0 ' lots and lots of whitespace. I think it's in the db. Mike
Attachment
On 03/05/12 Michael P. Soulier said: > lots and lots of whitespace. I think it's in the db. Ah, they're not varchars, they're character columns. That explains it...