I have a problem with my custom ColumnType which a...

# exposed

[maxmello]

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)

MinimalGeneratedColumnTest.kt

[maxmello]

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 && !isGenerated } as long as that has no unintended side effects