JPA and PostgreSQL with GenerationType.IDENTITY

If you have a column of type SERIAL, it will be sufficient to annotate your id field with: @Id @GeneratedValue(strategy=GenerationType.IDENTITY) This is telling Hibernate that the database will be looking after the generation of the id column. How the database implements the auto-generation is vendor specific and can be considered “transparent” to Hibernate. Hibernate just … Read more

Syntax error at end of input in PostgreSQL

You haven’t provided any details about the language/environment, but I’ll try a wild guess anyway: MySQL’s prepared statements natively use ? as the parameter placeholder, but PostgreSQL uses $1, $2 etc. Try replacing the ? with $1 and see if it works: WHERE address = $1 The error messages in PostgreSQL are very cryptic. In … Read more

Selecting rows ordered by some column and distinct on another

Quite a clear question 🙂 SELECT t1.* FROM purchases t1 LEFT JOIN purchases t2 ON t1.address_id = t2.address_id AND t1.purchased_at < t2.purchased_at WHERE t2.purchased_at IS NULL ORDER BY t1.purchased_at DESC And most likely a faster approach: SELECT t1.* FROM purchases t1 JOIN ( SELECT address_id, max(purchased_at) max_purchased_at FROM purchases GROUP BY address_id ) t2 ON … Read more

What’s the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)

For a random table: # select pg_relation_size(20306, ‘main’) as main, pg_relation_size(20306, ‘fsm’) as fsm, pg_relation_size(20306, ‘vm’) as vm, pg_relation_size(20306, ‘init’) as init, pg_table_size(20306), pg_indexes_size(20306) as indexes, pg_total_relation_size(20306) as total; main | fsm | vm | init | pg_table_size | indexes | total ——–+——-+——+——+—————+———+——– 253952 | 24576 | 8192 | 0 | 286720 | 196608 | … Read more

Are PostgreSQL functions transactional?

PostgreSQL 12 update: there is limited support for top-level PROCEDUREs that can do transaction control. You still cannot manage transactions in regular SQL-callable functions, so the below remains true except when using the new top-level procedures. Functions are part of the transaction they’re called from. Their effects are rolled back if the transaction rolls back. … Read more