insert with on conflict do nothing not working

[Tom]

hi All,

Is there anything special that I need to do to get ON CONFLICT DO NOTHING working with Postgresql? I'm using H2 2.1.210  (and I know it has been added since H2 1.4.200 in 2019) with PostgreSQL10Dialect (H2Dialect doesn't work either) but what I get is 

on statement:

insert into transaction_type (transaction_type, description) values ('sometx', 'xxxxxx') ON CONFLICT DO NOTHING;

I get:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "insert into transaction_type (transaction_type, description) values ('sometx', 'xxxxxx') [*]ON CONFLICT DO NOTHING"; SQL statement:
insert into transaction_type (transaction_type, description) values ('sometx', 'xxxxxx') ON CONFLICT DO NOTHING [42000-210]

help?

Tom

[Andreas Reichel]

Tom,

from the docs:

PostgreSQL Compatibility Mode

To use the PostgreSQL mode, use the database URL jdbc:h2:~/test;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DEFAULT_NULL_ORDERING=HIGH. Do not change value of DATABASE_TO_LOWER after creation of database.

• For aliased columns, ResultSetMetaData.getColumnName() returns the alias name and getTableName() returns null.
• When converting a floating point number to an integer, the fractional digits are not be truncated, but the value is rounded.
• The system columns ctid and oid are supported.
• LOG(x) is base 10 in this mode.
• REGEXP_REPLACE():
  • uses \ for back-references;
  • does not throw an exception when the flagsString parameter contains a 'g';
  • replaces only the first matched substring in the absence of the 'g' flag in the flagsString parameter.
• LIMIT / OFFSET clauses are supported.
• Legacy SERIAL and BIGSERIAL data types are supported.
• ON CONFLICT DO NOTHING is supported in INSERT statements.
• Spaces are trimmed from the right side of CHAR values, but CHAR values in result sets are right-padded with spaces to the declared length.
• MONEY data type is treated like NUMERIC(19, 2) data type.
• Datetime value functions return the same value within a transaction.
• ARRAY_SLICE() out of bounds parameters are silently corrected.
• EXTRACT function with DOW field returns (0-6), Sunday is 0.
• UPDATE with FROM is supported.
• GROUP BY clause can contain 1-based positions of expressions from the SELECT list.

It should work. What is your connection setting please?

Best regards

Andreas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/03b164cf-e394-4109-9bf5-ebb7d7dcb6edn%40googlegroups.com.

[Tom]

Hi Andreas,

Thank you for your reply. You just saved me hours of frustration.  The connection string I had was:
jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false

and so I was indeed missing something there which was the MODE=PostgreSQL.  Argh, I thought that by setting the hibernate dialect to postresql was going to do the trick but I guess that's for hibernate piece not H2 - significant lesson learned on my part.

Thank you very much!

Tom