Npgsql pls leave me alone!

Hi there, I am Mine, today’s problem (a bug) with npgsql was little bit annoying for me, want to share with you, and I hope you will be as mad as I am!

There was a task on me that involved changing a variable type string to integer on db. As easy as pie, right? In my case db is PostgreSql, and I’m using Ef core for ORM. And then, here comes our hero, NpgSql.

So let’s start the task from the very first step.

The task was to change the payment_term column's data type from string to int on the client table (Postgres calls string a 'character varying').

You wanted to add a migration for your changes, obviously. You opened the package manager console and wrote the command add-migration, and it built perfectly. Here is our migration file.

Basically, it says, on the down, remove character varying payment_term, and on the up, add integer payment_term.

Cool, looks like everything is on the right way. Let’s go and happily update the db.

What do you mean you can cast it? I don’t want you to do so. Just drop and add a new one.

So I searched a bit and here is the issue for this case : https://github.com/npgsql/efcore.pg/issues/304

So basicly PostgreSql won’t do it because there can be a values with non-numeric chars. There can be, yes, but not has to be, or I even don’t want them already. But Npgsql worries about me and tries to save my data. But maybe I don’t need it or I’m already aware of losing it. For changing the data type to a completely different type, you actually have to take that risk for me.

k, lets continue to on refactor, so roji replayed and said use USING statment on postgres, also on efcore side:

The Npgsql EF Core provider simply follows this logic. If your conversion always works (e.g. int4 -> text), then the generated migration will silently work, but if not, you must manually write the ALTER TABLE statement in the migration using raw SQL.

If you do this change on pgAdmin or any tool you use for PostgreSql, it will basically drop the column for payment_term and create the new one with the new type. (So it can do it because your query runs one at a time, with commands drop/create orderly.)

Here is the annoying point;

It’s not my choice to save data or remove it. Just drop the column and give me the new one.

It can’t , So what is this USING and how to solve this?

Postgres says:

The optional USING clause specifies how to compute the new column value from the old; if omitted, the default conversion is the same as an assignment cast from old data type to new. A USING clause must be provided if there is no implicit or assignment cast from old to new type.

(Learn more : https://www.postgresql.org/docs/current/sql-altertable.html)

Lets look at our migration file again:

With USING (payment_term :: integer), I am saying to cast all the data to integer, please, so I can change the column type to integer. (!!!?!!!)

You may have whitespace in your text fields, use: trim() too.

Okey lets update database!

AHHH! Can you please try not the save my data and drop the columm.

It was reported that there were values like 3674836583475, and their range was too big for integer type casting ...

Again lets look back our migration file:

To solve this, I added raw sql to update all payment_term values to ‘1’ (on a string type, of course, since it will execute earlier than the cast).

Finally, lets update database

Thanks, Npgsql, for your help.

In conclusion, I lost my data anyway and had to explain everything step by step to Npgsql. It feels like a waste of energy.

But why can’t Npgsql do it? Let me explain,

Basic migration function explanation is:

• The Up method contains C# code that applies any changes made to the model to the schema of the database since the last migration was generated.
• The Down method reverses those changes, restoring the database to the state of the previous migration.
• A ModelSnapshot file is also created or updated, depending on whether one previously existed.

In the beginning I only tried to change the column type using the Up method without modifying the existing data in the database. However, I encountered an error because some of the data could not be converted, (aka I would lose some of my data). So, npgsql could mark them as a null or whatever the default value is, not try to save them for me, because even when I try to make changes, I manipulate my data, I still lost it and do everything manually.

If pgAdmin could do it, npgSql, you should able to do it too. (and you have type conversion problems and not see nullable flags during scaffold too)