I am revising and editing tables of a database that was imported into PostgreSQL. I have found that some data type conversions throw errors so I am deleting the field and adding it again with the desired data type. When doing so, and also when adding new fields, I am unable to move up or down the field to it's proper place when using the schema editor. I have found how to sort (alphabetically) and unsort (field position). Is there a place to change the field position?
Thank you for the quick response and the clear instructions. I have tried both and have the same problem on other software so it must have to do with the conversion of the tables which came from MySQL. The tables are empty so there is no data to worry about. Reordering of the fields would be a great feature to have.
Hi Sergey, Thank you again for the quick response. Yes I sometimes need to delete a field, specially when needing the type to be boolean instead of smallint or some other. There is no data in the tables. I have tried the same on other software and the same happens, so it must be something to do with the converstion of the tables structures which came from MySQL.
The reordering feature I have used in Microsoft SQL Server Management Studio and it is actually a drag and drop. It is very handy ad you can organize the order of fields as you would like them to be completed/read when developing the user interface.
1) Please notice that PostgreSQL should support in ALTER TABLE changing of the field type.
You should use this way to save the position of the original field.
ALTER TABLE should be able to work on a Table with records.
This is a slower operation if exists records.
Don't delete the field. Then more that we will lose the data of the column if you delete it.
2) Even simpler to use GUI of Valentina Studio
right click on the Table in Schema Editor and choose Design Table,
change the type of field here.
In the background, it will execute that ALTER TABLE for DB if it supports it.
As far as I remember only SQLite do not support such ALTER, and for it Vstudio copy the whole table to execute this task.
But this feature is not supported by PostgreSQL, have you seen it in some other application?
Anyway, we'll consider adding it, and for other engines with the ability to specify order on altering a table.
Yes, there is no position editing for fields of existing tables, only for new ones (and it is done in the Design Table dialog).
It's a limitation of the model, I think we'll revisit it and try to improve the model to support reordering.
Meanwhile, do you have to delete a field because the type can't be switched? I suppose due to the field content that can't be converted to the new type, right?
If so, have you tried to set a NULL value (or other compatible value) to all records of the problematic field and change the type after that?