Support ignored for PostgreSQL

[AllanWang]

As of now, I don't believe it is added in the VendorDialect subclass. I am still doing my research on this, but here is what I've noticed:

• With just ignoring, you simply append ON CONFLICT DO NOTHING.
• For upsert, you do ON CONFLICT (id) DO UPDATE SET ...; Where id must be specified (could be primary key by default?) and set must be specified. For set, you can map the column names to "${name} = EXCLUDED.${name}", which will set everything to the new value

[AllanWang]

The following works. Based around #167

class BatchInsertOrIgnore(table: Table) : BatchInsertStatement(table, false) {
    override fun prepareSQL(transaction: Transaction): String {
        return "${super.prepareSQL(transaction)} ON CONFLICT DO NOTHING"
    }
}

fun <T : Table, E> T.batchInsertOrIgnore(data: List<E>, body: BatchInsertOrIgnore.(E) -> Unit): List<Int> {
    return data.takeIf { it.isNotEmpty() }?.let {
        val insert = BatchInsertOrIgnore(this)
        data.forEach {
            insert.addBatch()
            body(insert, it)
        }
        TransactionManager.current().exec(insert)
        columns.firstOrNull { it.columnType.isAutoInc }?.let { idCol ->
            insert.generatedKey?.mapNotNull {
                val value = it[idCol]
                when (value) {
                    is Long -> value.toInt()
                    is Int -> value
                    null -> null
                    else -> error("can't find primary key of type Int or Long; map['$idCol']='$value' (where map='$it')")
                }
            }
        }
    }.orEmpty()
}

[Rubyj]

Hey @AllanWang @Tapac I am trying to use this functionality in batchInsert for postgres aka batchInsert(ignore=True,...) and it does not seem to be respecting the ignore for postgres. The insert statement it is sending to the DB has no on conflict clause. Is this not intended to work for batchInsert?

[bog-walk]

Hi @Rubyj The tests I'm running using PostgreSQL with batchInsert(ignore = true) are generating insert statements with the ON CONFLICT DO NOTHING clause and correctly ignoring insertions that have a conflict with a pre-existing record.

Could you please confirm if this issue persists on your end with current version 0.44.0?

If it does, please consider opening a new issue on YouTrack, so that we can attempt to investigate further.
Please also consider including your PostgreSQL setup details, as well as a sample of your table object and how you're calling batchInsert() and/or confirming that it works.