Fields View — Change data type of existing NULL field when table has rows

[Bud]

In an empty table in Pro 2.6.8, we can change the datatype of a field, such as from TEXT to LONG. But that only works if the table is empty. If the table has rows, then the datatype is greyed out in the Fields View, so it can't be changed using Pro.

In the back-end of a database, it's possible to change the datatype, even if the table has rows, as long as all the rows are null in that field. (It might even be possible for non-null fields too; I haven't checked.)

ALTER TABLE - ALTER/MODIFY DATATYPE

Oracle example:

--change a text field to an integer datatype
ALTER TABLE a_test_fc
MODIFY a_test_field INT;

But if I do that, the change is not reflected in the Fields View in ArcGIS Pro. I guess there must be geodatabase system table info that stores the original datatype; Pro isn't referencing the actual datatype from the database. (Interestingly, I'm still able to edit the data/field in the attribute table in Pro without issue; I can enter integers into the field. And of course, text values are no longer allowed.)

While changing the datatype through the back-end technically ran without errors, I wouldn't want to do that because now the geodatabase system tables are wrong. At best, that'll cause confusion for users, since the Fields View is displaying the incorrect datatype. At worst, it could cause errors in ArcGIS Pro. 

Could ArcGIS Pro be enhanced so that we can change the datatype of a null field, even if the table has rows?

[Bud]

Example use case — change datatype without losing field order:

I want to change the datatype of an existing field (table has rows) without losing the field order of my existing fields. In this particular case, the field isn't completely null. So I'll add a new field (or a related table) to temporarily store the existing values, populating the temporary field using the field calculator. Then I'll use the field calculator to set the original field in question to null. And change the datatype. Then I'll re-populate the field using the field calculator and delete the temporary field or temporary table.

That workflow would allow me to change the datatype of an existing populated field, without losing the field order, as long as the values in the field are compatible with the new datatype.

I suppose the holy grail would be: Change the datatype of a non-null field. But I'm not sure how many database vendors support that. 

[SSWoodward]

Thanks for the idea @Bud,

You're correct, changing the datatype on the backend is not a recommended workflow. Any schema manipulation inside of an enterprise geodatabase that does not occur in ESRI software risks complications with your data.

You are also correct that the datatypes in ArcGIS Pro are not read from the database itself, but are read from a table in the geodatabase.  This is because ArcGIS datatypes do not match the native datatypes of every RDBMS.  In order for these fields to be consistent within ArcGIS Pro and across platforms, the displayed datatypes are defined by the geodatabase system tables, and not the underlying RDBMS data types. 

You've definitely hit on a key point here in your last comment:

"I suppose the holy grail would be: Change the datatype of a non-null field. But I'm not sure how many database vendors support that."

Many RDBMS do not support in-place modification at all, whether the field is null or non-null.  For instance, changing the data type of a column in an Oracle or PostgreSQL database requires a rewrite of the table in its entirety. 

[Bud]

Thanks @SSWoodward 

Many RDBMS do not support in-place modification at all, whether the field is null or non-null. For instance, changing the data type of a column in an Oracle or PostgreSQL database requires a rewrite of the table in its entirety.

If I'm understanding correctly, that doesn't sound quite right to me.

As mentioned in the original idea, when it comes to Oracle, I was able to change the datatype of a EGDB FC column if all the values were null (the FC had rows).

This Oracle fiddle demonstrates it: https://dbfiddle.uk/f0gTR6Fi?highlight=8 

Likewise, it seems to work in Postgress too: https://dbfiddle.uk/GZzh2PSE?highlight=4 

[SSWoodward]

Yes those results make sense, those tables are new, and have been re-written in their entirety. Not modified in-place.

[Bud]

@SSWoodward 

Are you saying that ALTER TABLE actually tells the database to DROP the entire table and re-CREATE it?

That seems strange to me. My limited understanding is ALTER TABLE modifies the table in-place.

[SSWoodward]

Its unexpected, isn't it! It really surprised me as well when I learned it.  

From the PostgreSQL Docs:

"The fact that ALTER TYPE requires rewriting the whole table is sometimes an advantage...."

The same is true for Oracle.  I'm not sure that its exactly the same as DROP under the hood, but the whole table is recreated from scratch. 

[Bud]

Yeah, that's surprising.

When we change the datatype of a field in an empty table using the current ArcGIS Pro functionality, does that behave the same way? Does it replace the table?