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 On Tuesday, April 5, 2022 at 8:13:36 PM UTC-4 [email protected] wrote: > 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 > > On Tue, 2022-04-05 at 16:28 -0700, Tom wrote: > > 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 > > -- > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/03b164cf-e394-4109-9bf5-ebb7d7dcb6edn%40googlegroups.com > > <https://groups.google.com/d/msgid/h2-database/03b164cf-e394-4109-9bf5-ebb7d7dcb6edn%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/341df34a-e696-483a-a994-ce392e9de4a2n%40googlegroups.com.
