I have a problem with my custom ColumnType which are GENERAT

Question

I have a problem with my custom ColumnType which are GENERATED columns for Postgres In this minimal example while running `SchemaUtils drop ExampleTable SchemaUtils createMissingTablesAndColumns ExampleTable ` so recreating the table completely I get the following log output ```17 02 26 029 DefaultDispatcher worker 1 coroutine 3 DEBUG Exposed DROP TABLE IF EXISTS example 17 02 26 066 DefaultDispatcher worker 1 coroutine 3 INFO Exposed Preparing create tables statements took 36ms 17 02 26 073 DefaultDispatcher worker 1 coroutine 3 DEBUG Exposed CREATE TABLE IF NOT EXISTS example id BIGSERIAL PRIMARY KEY name VARCHAR 64 NOT NULL isNamedAlice BOOLEAN GENERATED ALWAYS AS example name = Alice STORED NOT NULL 17 02 26 084 DefaultDispatcher worker 1 coroutine 3 INFO Exposed Executing create tables statements took 17ms 17 02 26 097 DefaultDispatcher worker 1 coroutine 3 INFO Exposed Extracting table columns took 13ms 17 02 26 113 DefaultDispatcher worker 1 coroutine 3 INFO Exposed Extracting column constraints took 14ms 17 02 26 113 DefaultDispatcher worker 1 coroutine 3 INFO Exposed Preparing alter table statements took 29ms 17 02 26 124 DefaultDispatcher worker 1 coroutine 3 DEBUG Exposed ALTER TABLE example ALTER COLUMN isNamedAlice TYPE BOOLEAN GENERATED ALWAYS AS example name = Alice STORED 17 02 26 127 DefaultDispatcher worker 1 coroutine 3 WARN Exposed Transaction attempt 1 failed org postgresql util PSQLException ERROR syntax error at or near GENERATED ``` As you can see the table is originally created with the column isNamedAlice having NOT NULL then immediately altered without that NOT NULL part When looking into `createMissingTablesAndColumns` there seems to be a ColumnDiff triggered between the ColumnMetadata and Column I could not find out which of the 4 comparisons triggered the change detection but when looking into `org jetbrains exposed sql vendors PostgreSQL modifyColumn` there only nullability and defaults are checked and since both do not appear in the modify statement I assume it is one of the other 2 auto inc or case sensitive name Please look at the snippet for reference even though I would say that an ALTER COLUMN statement should never trigger in this case In addition the problem here is that GENERATED columns do not support ALTER COLUMN at all only DROP ADD column resulting in the syntax error There is currently no possibility to override modifyStatement from Column I think but I guess the real problem remains that this modify statement is even executed with nothing actually modified Postgres 14 4 Exposed 0 38 2 Kotlin 1 6 21 also tested with Exposed 0 37 3 0 36 2

maxmello · Answer

I found the reason incorrectDefaults is triggered `existingCol defaultDbValue` is ` name text = Alice text ` but `col dbDefaultValue let dataTypeProvider dbDefaultToString it ` is null From what I can see the `JdbcDatabaseMetadataImpl columns` is able to receives the generated expression as the columns default via `val defaultDbValue = it getString COLUMN DEF let sanitizedDefault it ` but I think it is not possible for me to tell my Column that this generated value is actually its default value because I just set the `sqlType` to `GENERATED ALWAYS AS STORED` it would make no sense to set the columns `defaultValueFun` in this context For testing purposes I tried setting the `dbDefaultValue` via reflection but this results in the following ```CREATE TABLE IF NOT EXISTS example id BIGSERIAL PRIMARY KEY name VARCHAR 64 NOT NULL isnamedalice BOOLEAN GENERATED ALWAYS AS example name = Alice STORED DEFAULT example name = Alice NOT NULL ``` with the corresponding error `ERROR both default and generation expression specified for column isnamedalice of table example ` Finally I think the solution would be to add `val isGenerated = it getString IS GENERATEDCOLUMN == YES ` and then `defaultDbValue takeIf autoIncrement amp amp isGenerated ` as long as that has no unintended side effects