Re: Replacing all CHAR colums with VARCHAR

Andrus wrote:
> I have CHAR columns in database in 500 tables. There are currently no
> VARCHAR columns.
>
> My C# application receives character data with trailing spaces from npgsql.

Yep - that's what CHAR is for.

> This makes application code complicated. Application must remove trailing
> spaces from incoming data for C# string manipulation to work.

It does indeed

> Also Tom Lane strongly recommends using VARCHAR always and never use CHAR
> columns.

Because it maintains trailing spaces which you usually don't want.

> Is it reasonable do convert all CHAR columns in database to VARCHAR columns
> in whole database.

Nobody else can tell you. Do you know what you are using these columns
for? Names and descriptions should be fine. You'll need to think when
you're dealing with e.g. product-codes.

> Where to find such script ?
> Shoult I make a loop over all columns and execute ALTER TABLE statements
> in transaction making all constraints deferred?

That's the obvious way - check the information_schema.columns view.
You'll want to dump and restore the database after all this too.

> I have also some triggers, indexes and sequences in database and execute
> statements into database which contain a lot of operations with CHAR
> columns.

hmm...

>
> Will database work OK after such conversion?
> What things may be broken ?

Anything that was relying on trailing spaces. You'll probably find you
expose some bugs in your code. Make sure you allow the time to do it
properly.

> Or is it better to hack npgsql code so that it removes trailing spaces
> itself ?

No. Fix the database.

--
Richard Huxton
Archonet Ltd