Re: Stripping empty space from all fields in a table?

On Fri, Oct 27, 2006 at 03:39:21PM -0400, J B wrote:
> Guys,
> I have a table that has various fields that have whitespace in the
> values. I'd like to roll through and strip the left and right
> whitespace out of all fields that contain strings. Is there any
> easy way to do this?

If you're really and want to hit all your tables, run the output of
the following:

SELECT
'UPDATE
' ||
quote_ident(table_schema) ||
'.' ||
quote_ident(table_name) ||
'
SET
' || array_to_string(ARRAY(
SELECT
quote_ident(column_name) ||
' = trim(' ||
quote_ident(column_name) ||
')'
FROM
information_schema.columns
WHERE
table_name = 'person'
AND
data_type = 'character varying'
),
',
') ||
';
'
FROM
information_schema.tables
WHERE
table_schema NOT IN ('pg_catalog','information_schema')
;

through psql.

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!