Обсуждение: Replacing all CHAR colums with VARCHAR

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.
This makes application code complicated. Application must remove trailing
spaces from incoming data for C# string manipulation to work.

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

Is it reasonable do convert all CHAR columns in database to VARCHAR columns
in whole database.
Where to find such script ?
Shoult I make a loop over all columns and execute ALTER TABLE statements
in transaction making all constraints deferred?

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

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

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

Andrus.

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

On Tue, Jan 02, 2007 at 02:37:59 +0200,
  Andrus <kobruleht2@hot.ee> wrote:
>
> Also Tom Lane strongly recommends using VARCHAR always and never use CHAR
> columns.

If you just want to use Postgres, you might want to use 'text' instead of
'varchar', since you don't need to specify arbitrarylimits for that type.
This isn't portable though.