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

[David Fetter <david@xxxxxxxxxx>]

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@xxxxxxxxxx> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!