phup 'n stuff

Another cool feature I've recently discovered in PostgreSQL, although one available and documented for a long time, is table inheritance, which works almost exactly like object inheritance in object oriented programming. This simply means that you can have tables inheriting columns from parent tables.

Continue Reading »

I recently came across an amazing feature in PostgreSQL: the RETURNING clause. This clause allows you to return specific data as the result of an INSERT or UPDATE query.

Often, after doing one of these queries, I'm interested in getting some data, such as an autogenerated primary key field, or maybe some defaulted data (like a created timestamp). Normally, one would have to do an additional query to get this data. Using RETURNING, we can do our data manipulation and retrieval in the same query.

Example:

-- Return just the ID:
 
INSERT INTO books(name, author) VALUES('Awesome Book', 'Smart Author') 
    RETURNING book_id;
 
-- Return more than one column:
 
INSERT INTO books(name, author) VALUES ('Sweet Book', 'Other Author') 
    RETURNING book_id, created_on;
 
-- Return all the columns:
 
INSERT INTO books(name, author) VALUES ('Silly Book', 'Airhead Author') 
    RETURNING *;

In our code, we can simply treat the return value of the INSERT or UPDATE code like we would a SELECT statement.

Some things to keep in mind, though:

1. If the UPDATE query affects multiple rows, multiple rows of data will be returned. If no rows are affected, none will be returned.
2. Normally the database would return the number of rows affected, but obviously we're overriding the return value of the query. Obviously we can check the number of rows returned by the query (like we would with a SELECT) to get around this.