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
