Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Postgresql uniqueIndex error with sheme. #803

Closed
DuchGhast opened this issue Feb 20, 2020 · 8 comments
Closed

Postgresql uniqueIndex error with sheme. #803

DuchGhast opened this issue Feb 20, 2020 · 8 comments
Labels
bug postgresql

Comments

@DuchGhast
Copy link

This is happening only with scheme.

Code:

fun main()
{
	Database.connect(url = "jdbc:postgresql://localhost:5432/test_database",
					 driver = "org.postgresql.Driver",
					 user = "test_user",
					 password = "test_password")

	transaction {
		createSchema("user")
		SchemaUtils.createMissingTablesAndColumns(UsersTable)
	}
}

fun Transaction.createSchema(name: String) { exec("CREATE SCHEMA IF NOT EXISTS \"$name\"") }

object UsersTable : IntIdTable("user.users")
{
	val name = varchar("name", 200)
	val email = varchar("email", 100).uniqueIndex("users_email_unique")
	val password = varchar("password", 100)
}

Error:

15:53:49.891 [main] DEBUG Exposed - CREATE SCHEMA IF NOT EXISTS "users"
15:53:54.781 [main] INFO Exposed - Preparing create tables statements took 172ms
15:53:55.106 [main] DEBUG Exposed - CREATE TABLE IF NOT EXISTS users.users (id SERIAL PRIMARY KEY, "name" VARCHAR(200) NOT NULL, email VARCHAR(100) NOT NULL, "password" VARCHAR(100) NOT NULL)
15:53:55.231 [main] DEBUG Exposed - ALTER TABLE users.users ADD CONSTRAINT users_email_unique UNIQUE (email)
15:53:55.267 [main] INFO Exposed - Executing create tables statements took 486ms
15:53:55.674 [main] INFO Exposed - Extracting table columns took 407ms
15:53:55.722 [main] INFO Exposed - Extracting column constraints took 42ms
15:53:55.722 [main] INFO Exposed - Preparing alter table statements took 455ms
15:53:55.747 [main] DEBUG Exposed - ALTER TABLE users.users ADD id SERIAL PRIMARY KEY
15:53:55.761 [main] WARN Exposed - Transaction attempt #0 failed: org.postgresql.util.PSQLException: ERROR: column "id" of relation "users" already exists. Statement(s): ALTER TABLE users.users ADD id SERIAL PRIMARY KEY
org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: column "id" of relation "users" already exists
	at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:63)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:126)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:112)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:88)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:79)
	at org.jetbrains.exposed.sql.SchemaUtils.execStatements(SchemaUtils.kt:170)
	at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns(SchemaUtils.kt:239)
	at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns$default(SchemaUtils.kt:224)
	at MainKt$main$1.invoke(Main.kt:14)
	at MainKt$main$1.invoke(Main.kt)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$1.invoke(ThreadLocalTransactionManager.kt:156)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$2.invoke(ThreadLocalTransactionManager.kt:197)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:205)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:196)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$transaction$1.invoke(ThreadLocalTransactionManager.kt:134)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:205)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:106)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:104)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$default(ThreadLocalTransactionManager.kt:103)
	at MainKt.main(Main.kt:12)
	at MainKt.main(Main.kt)
Caused by: org.postgresql.util.PSQLException: ERROR: column "id" of relation "users" already exists
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:132)
	at org.jetbrains.exposed.sql.statements.jdbc.JdbcPreparedStatementImpl.executeUpdate(JdbcPreparedStatementImpl.kt:23)
	at org.jetbrains.exposed.sql.Transaction$exec$2.executeInternal(Transaction.kt:93)
	at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:61)
	... 20 common frames omitted
15:53:56.051 [main] DEBUG Exposed - CREATE SCHEMA IF NOT EXISTS "users"
15:53:56.079 [main] INFO Exposed - Preparing create tables statements took 27ms
15:53:56.081 [main] DEBUG Exposed - CREATE TABLE IF NOT EXISTS users.users (id SERIAL PRIMARY KEY, "name" VARCHAR(200) NOT NULL, email VARCHAR(100) NOT NULL, "password" VARCHAR(100) NOT NULL)
15:53:56.084 [main] DEBUG Exposed - ALTER TABLE users.users ADD CONSTRAINT users_email_unique UNIQUE (email)
15:53:56.084 [main] WARN Exposed - Transaction attempt #1 failed: org.postgresql.util.PSQLException: ERROR: relation "users_email_unique" already exists. Statement(s): ALTER TABLE users.users ADD CONSTRAINT users_email_unique UNIQUE (email)
org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: relation "users_email_unique" already exists
	at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:63)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:126)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:112)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:88)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:79)
	at org.jetbrains.exposed.sql.SchemaUtils.execStatements(SchemaUtils.kt:170)
	at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns(SchemaUtils.kt:231)
	at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns$default(SchemaUtils.kt:224)
	at MainKt$main$1.invoke(Main.kt:14)
	at MainKt$main$1.invoke(Main.kt)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$1.invoke(ThreadLocalTransactionManager.kt:156)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$2.invoke(ThreadLocalTransactionManager.kt:197)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:205)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:196)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$transaction$1.invoke(ThreadLocalTransactionManager.kt:134)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:205)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:106)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:104)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$default(ThreadLocalTransactionManager.kt:103)
	at MainKt.main(Main.kt:12)
	at MainKt.main(Main.kt)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "users_email_unique" already exists
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:132)
	at org.jetbrains.exposed.sql.statements.jdbc.JdbcPreparedStatementImpl.executeUpdate(JdbcPreparedStatementImpl.kt:23)
	at org.jetbrains.exposed.sql.Transaction$exec$2.executeInternal(Transaction.kt:93)
	at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:61)
	... 20 common frames omitted
15:53:56.130 [main] DEBUG Exposed - CREATE SCHEMA IF NOT EXISTS "users"
15:53:56.183 [main] INFO Exposed - Preparing create tables statements took 25ms
15:53:56.187 [main] DEBUG Exposed - CREATE TABLE IF NOT EXISTS users.users (id SERIAL PRIMARY KEY, "name" VARCHAR(200) NOT NULL, email VARCHAR(100) NOT NULL, "password" VARCHAR(100) NOT NULL)
15:53:56.189 [main] DEBUG Exposed - ALTER TABLE users.users ADD CONSTRAINT users_email_unique UNIQUE (email)
15:53:56.190 [main] WARN Exposed - Transaction attempt #2 failed: org.postgresql.util.PSQLException: ERROR: relation "users_email_unique" already exists. Statement(s): ALTER TABLE users.users ADD CONSTRAINT users_email_unique UNIQUE (email)
org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: relation "users_email_unique" already exists
	at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:63)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:126)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:112)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:88)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:79)
	at org.jetbrains.exposed.sql.SchemaUtils.execStatements(SchemaUtils.kt:170)
	at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns(SchemaUtils.kt:231)
	at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns$default(SchemaUtils.kt:224)
	at MainKt$main$1.invoke(Main.kt:14)
	at MainKt$main$1.invoke(Main.kt)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$1.invoke(ThreadLocalTransactionManager.kt:156)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$2.invoke(ThreadLocalTransactionManager.kt:197)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:205)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:196)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$transaction$1.invoke(ThreadLocalTransactionManager.kt:134)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:205)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:106)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:104)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$default(ThreadLocalTransactionManager.kt:103)
	at MainKt.main(Main.kt:12)
	at MainKt.main(Main.kt)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "users_email_unique" already exists
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:132)
	at org.jetbrains.exposed.sql.statements.jdbc.JdbcPreparedStatementImpl.executeUpdate(JdbcPreparedStatementImpl.kt:23)
	at org.jetbrains.exposed.sql.Transaction$exec$2.executeInternal(Transaction.kt:93)
	at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:61)
	... 20 common frames omitted
Exception in thread "main" org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: relation "users_email_unique" already exists
SQL: [ALTER TABLE users.users ADD CONSTRAINT users_email_unique UNIQUE (email)]
	at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:63)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:126)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:112)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:88)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:79)
	at org.jetbrains.exposed.sql.SchemaUtils.execStatements(SchemaUtils.kt:170)
	at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns(SchemaUtils.kt:231)
	at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns$default(SchemaUtils.kt:224)
	at MainKt$main$1.invoke(Main.kt:14)
	at MainKt$main$1.invoke(Main.kt)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$1.invoke(ThreadLocalTransactionManager.kt:156)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$2.invoke(ThreadLocalTransactionManager.kt:197)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:205)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:196)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$transaction$1.invoke(ThreadLocalTransactionManager.kt:134)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:205)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:106)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:104)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$default(ThreadLocalTransactionManager.kt:103)
	at MainKt.main(Main.kt:12)
	at MainKt.main(Main.kt)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "users_email_unique" already exists
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:132)
	at org.jetbrains.exposed.sql.statements.jdbc.JdbcPreparedStatementImpl.executeUpdate(JdbcPreparedStatementImpl.kt:23)
	at org.jetbrains.exposed.sql.Transaction$exec$2.executeInternal(Transaction.kt:93)
	at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:61)
	... 20 more
@hfazai hfazai mentioned this issue Feb 22, 2020
Closed
@Suntrie Suntrie mentioned this issue Apr 11, 2020
Open
@DRSchlaubi
Copy link
Contributor

Any updates on this? also, why do we have to put the unique index there in the first place shouldn't exposed do it automatically when making a reference

@jnfeinstein
Copy link
Contributor

jnfeinstein commented Jul 27, 2020 •
edited
Loading

I think I am receiving a related error when using SchemaUtils.createMissingTablesAndColumns.

SQL: [ALTER TABLE domainevententry ADD CONSTRAINT domainevententry_aggregateidentifier_sequencenumber_unique UNIQUE (aggregateidentifier, sequencenumber)]

results in:

org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: relation "domainevententry_aggregateidentifier_sequencenumber_unique" already exists

Kotlin: 1.3.71
Exposed: 0.26.1
PostgreSQL: 10.7 via AWS Aurora Serverless

/* schema.XXX returns string names for 3rd party table */

object AxonDomainEventEntryTable : LongIdTable(schema.domainEventTable(), schema.globalIndexColumn().toLowerCase()) {
    val aggregateIdentifier = varchar(schema.aggregateIdentifierColumn().toLowerCase(), 255)
    val sequenceNumber = long(schema.sequenceNumberColumn().toLowerCase())
    val type = varchar(schema.typeColumn().toLowerCase(), 255).nullable()
    val eventIdentifier = varchar(schema.eventIdentifierColumn().toLowerCase(), 255)
    val metadata = binary(schema.metaDataColumn().toLowerCase()).nullable()
    val payload = binary(schema.payloadColumn().toLowerCase())
    val payloadRevision = varchar(schema.payloadRevisionColumn().toLowerCase(), 255).nullable()
    val payloadType = varchar(schema.payloadTypeColumn().toLowerCase(), 255)
    val timestamp = varchar(schema.timestampColumn().toLowerCase(), 255)

    init {
        uniqueIndex(aggregateIdentifier, sequenceNumber)
        uniqueIndex(eventIdentifier)
    }
}

@jnfeinstein
Copy link
Contributor

Further data point: it appears to occur when there is another schema with the same collection of tables and indices.

Tapac added a commit that referenced this issue Jul 31, 2020
@Tapac
Postgresql uniqueIndex error with sheme. #803
9ecbcdf 
SchemeUtils.setScheme doesn't reset current scheme in ExposedDatabaseMetadata
@Tapac
Copy link
Contributor

Tapac commented Aug 1, 2020

Should be fixed in the next release. Please report if issue will still exist.

Tapac added a commit that referenced this issue Aug 2, 2020
@Tapac
Postgresql uniqueIndex error with sheme. #803 / exclude SQL Server fr…
8b8f756 
…om test
@Tapac Tapac closed this as completed Aug 3, 2020
@du-it
Copy link

du-it commented Dec 20, 2020

The error still exists in 0.28.1 when I try to create a table with a string id:

import com.forhims.ganesha.wired.EnvironmentConfiguration
import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource
import org.jetbrains.exposed.dao.id.EntityID
import org.jetbrains.exposed.dao.id.IdTable
import org.jetbrains.exposed.sql.Column
import org.jetbrains.exposed.sql.Database
import org.jetbrains.exposed.sql.Schema
import org.jetbrains.exposed.sql.SchemaUtils
import org.jetbrains.exposed.sql.StdOutSqlLogger
import org.jetbrains.exposed.sql.addLogger
import org.jetbrains.exposed.sql.transactions.transaction
import org.slf4j.LoggerFactory
import java.sql.Statement


fun main(args: Array<String>) {
    val db = DatabaseExposed
}

open class StringIdTable(
    schema: String,
    name: String,
    columnName: String = "id",
    columnLength: Int = 10,
) : IdTable<String>("$schema.$name") {
    override val id: Column<EntityID<String>> =
        varchar(columnName, columnLength).entityId()
    override val primaryKey by lazy { super.primaryKey ?: PrimaryKey(id) }
}

const val DB_NAME = "db"
const val SCHEMA_NAME = "schema"
val SCHEMA = Schema(name = SCHEMA_NAME, authorization = "postgres")

object Xxx : StringIdTable(SCHEMA_NAME, "xxx") {
    // object Xxx : Table("xxx") {
    //    val id = varchar(name = "id", length = 10)
    val label = varchar(name = "title", length = 255)
    val order = text("order")
}

object DatabaseExposed {
        private val logger = LoggerFactory.getLogger(javaClass)

        //    url = "jdbc:postgresql://localhost:5432/db",
        //    driver = "org.postgresql.Driver",
        //    user = "postgres",
        //    password = ""
        private val hikariConfig by lazy {
            HikariConfig().apply {
                this.jdbcUrl = EnvironmentConfiguration.credentials.databaseUrl
                this.username = EnvironmentConfiguration.credentials.databaseUser
                this.password = EnvironmentConfiguration.credentials.databasePassword
            }
        }

    private fun createDatabase(statement: Statement) {
        // Postgres doesn't support `CREATE DATABASE IF NOT EXISTS [...]`
        val resultSet = statement.executeQuery(
            "SELECT FROM pg_database WHERE datname = '$DB_NAME'"
        )

        // Returns `true` if a row exists
        if (!resultSet.next()) {
            statement.execute("CREATE DATABASE $DB_NAME")
        }
    }

    init {
        val dataSource = HikariDataSource(hikariConfig)
        Database.connect(dataSource)

        val statement = dataSource.getConnection().createStatement()

        createDatabase(statement)

        transaction {
            addLogger(StdOutSqlLogger)

            SchemaUtils.createSchema(SCHEMA)
            SchemaUtils.createMissingTablesAndColumns(Xxx)
        }
    }
}

@AntonAb
Copy link

AntonAb commented Feb 1, 2021 •
edited
Loading

0.29.1 has the same error too. It is because Queries.kt line 307

val existingTableIndices = existingIndices[table].orEmpty().filterFKeys().filterInternalIndices()

existingTableIndices contains Primary key as EntityIDColumnType instead of Primary index type. That's why in this line

missingIndices.addAll(mappedIndices.subtract(existingTableIndices))

Primary key index is marked as missing.
My simple work around:

fun List<Index>.fixEntityIdColumns(): List<Index> {
        return this.map {
            val columns = it.columns.map { col ->
                when (col.columnType.javaClass) {
                    EntityIDColumnType::class.java -> {
                        (col.columnType as EntityIDColumnType<*>).idColumn
                    }
                    else -> col
                }
            }
            it.copy(columns = columns)
        }
    }

and

val existingTableIndices = existingIndices[table].orEmpty().filterFKeys().filterInternalIndices().fixEntityIdColumns()

@miladheydari
Copy link

this error still exists in version 0.37.3
error:

ALTER TABLE product ADD CONSTRAINT aaaa UNIQUE (column_name)
 e: org.postgresql.util.PSQLException: ERROR: relation "aaaa" already exists

when call

 SchemaUtils.create( ProductTable)

@kelposun
Copy link

kelposun commented Aug 23, 2022 •
edited
Loading

0.39.2 error still exists :(

org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: relation "field_id_unique" already exists
SQL: [ALTER TABLE sampletable ADD CONSTRAINT field_id_unique UNIQUE (id)]

when call

val tables = listOf(SampleTable)
SchemaUtils.createMissingTablesAndColumns(*tables.toTypedArray())

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug postgresql
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
8 participants
@Tapac @jnfeinstein @du-it @AntonAb @miladheydari @DRSchlaubi @DuchGhast @kelposun