Today I Learned

A Hashrocket project

PostgreSQL "RETURNING"

Today I learned that PostgreSQL INSERT/UPDATE/DELETE has a RETURNING clause that returns the computed values.

We can use it to get values that are generated by database such as sequences or defaults.

Check this out:

DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id    SERIAL PRIMARY KEY,
  email VARCHAR NOT NULL UNIQUE
);

INSERT INTO users (email) VALUES ('user1@example.com');
-- INSERT 0 1

INSERT INTO users (email) VALUES ('user2@example.com') RETURNING *;
--  id |       email
-- ----+-------------------
--   2 | user2@example.com
-- (1 row)

h/t @joshuadavey

Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.