1. Jorge Adamicska
  2. Valentina Studio
  3. Samstag, Mai 27 2023, 12:45 AM
  4.  Abonnieren via E-Mail
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?
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
Hello Jorge,

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?
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
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.
Kommentar
There are no comments made yet.
Ruslan Zasukhin Akzeptierte Antwort
Hi Jorge,

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.
Kommentar
There are no comments made yet.
Jorge Adamicska Akzeptierte Antwort
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.
Kommentar
There are no comments made yet.
Jorge Adamicska Akzeptierte Antwort
Hi Ruslan,
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.
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
Then you can do a manual conversion - convert it to NULL or FALSE (even without any data).
ALTER TABLE "public"."table1" ALTER COLUMN "newField1SmallInt" TYPE Boolean USING NULL;
Kommentar
There are no comments made yet.
  • Seite :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.