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
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
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