Hacker News new | comments | show | ask | jobs | submit login

>> cool things you possibly don't know about

A hugely useful feature of postgres, that nearly no ORM/ActiveRecord library makes use of, is the RETURNING clause of INSERT statements[1] (also supported on UPDATE and DELETE statements, though those are less useful).

A write statement with a RETURNING clause returns a resultset just like SELECT, with one row per row inserted, updated, or deleted. In this way, you can request the values of columns whose data was dynamically determined by the postgres server. This is especially useful for sequence (ie: SERIAL, autoincrement) values, as well as other columns with default values (such as an insert datetime, or a column with a value based on a complex expression).

This functionality is amazing, yet is so underused. Try to do a multi-row insert statement with MySQL with an auto-increment primary key, and know which IDs were assigned to your rows. You can't, because there is no guarantee your auto-increment values will be sequential.

Example query, which will return the auto-increment sequence id and default insertion timestamp of the two inserted rows:

    INSERT INTO members (name) VALUES ('Foo'), ('Bar') RETURNING id, created_at;
[1] https://www.postgresql.org/docs/current/static/sql-insert.ht...



Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | DMCA | Apply to YC | Contact

Search: