When to use SELECT * in PostgreSQL

In LedgerSMB we use a lot of queries which involve SELECT *.  Many people consider SELECT * harmful but there are several cases where it is useful.  Keep in mind we encapsulate the database behind an API, so SELECT * has different implications than it does from applications selecting directly from tables.

The Fundamental Design Questions

It all comes down to software interface contracts and types.  Poorly thought-out contracts, loosely applied, lead to unmaintainable code.  Clear contracts, carefully applied, lead to maintainable code because the expectations are easily enforced.

PostgreSQL comes with a complex type system where every table, view, or composite type is an object class.  In the right contexts, SELECT * provides you a result of a guaranteed type.  This is important when doing object relational work because it means you get a series of objects back in a defined class.  This allows you to then pass those on to other functions to get derived data.

Select * therefore helps you when working with objects, because you can ensure that the result types are in fact valid objects of a specified class defined in the relation clause of the query. 

Where SELECT * can't be helpful

SELECT * is never helpful (and can have significant problems) in specific areas, such as anywhere you have a join.  There are specific reasons for these problems.

Consider the following:

chris=# create table typetest (test text);
CREATE TABLE
                                              ^
chris=# insert into typetest values ('test1'), ('test2');
INSERT 0 2
chris=# CREATE VIEW typetestview AS select * from typetest;
CREATE VIEW
chris=# select * from typetestview;
 test 
-------
 test1
 test2
(2 rows)

chris=# alter table typetest add newfield bool default false;
ALTER TABLE
chris=# select * from typetestview;
 test 
-------
 test1
 test2
(2 rows)

(Edited, see note [1]).  Interestingly this is fixed in the view definition so dump and reload won't change it.  However without setting up casts, you can't take advantage of the fact you are passing up the data structure.

The key problem here is that views are their own types, and consequently you cannot guarantee that the view type will be the same as the underlying table type.  This makes castes more complex to maintain and probably not worth the trouble.

Once joins are used in a query, however, SELECT * loses any potential benefit.  Joins do not return a defined type, and so SELECT * should never be used in queries utilizing joins (aside possibly from ad hoc queries run by the dba to explore the data).

SELECT * and Stored Procedures

Consider for example the following CRUD stored procedure:

CREATE OR REPLACE FUNCTION accounts__list_all()
RETURNS SETOF accounts
LANGUAGE SQL AS
$$
    SELECT * FROM accounts ORDER BY account_no;
$$;

This query is relatively simple, but the stored procedure returns a type that is defined by the underlying table.  We all run into cases where application data can't be much further normalized and we may want to have stored procedures delivering that data to the application.  In this case, we are likely to use a function like this, and that enables us to do other object-relational things outside it.

Now, if we need to change the underlying accounts table, we can always make a decision as to whether to make accounts a view with a stable representation, a complex type with a hand-coded query returning it, or just propagate the changes upwards.  Because the application is not directly selecting from the underlying storage, we have options to ensure that the contract can be maintained.  In essence this injects a dependency that allows us to maintain contracts more easily through schema changes.

Consequently although it leads to the same execution plan in this example, there is a tremendous difference, software engineering-wise, between an application calling:

SELECT * FROM accounts ORDER BY account_no;

and

SELECT * FROM accounts__list_all();

In the first case, you have only one contract, between the high level application code and the low-level storage.  In the second case, you have two contracts, one between the storage and the procedure (which can be centrally adjusted), and a more important one between the application code and the stored procedure.

Conclusions

In PostgreSQL, the choice of whether to use SELECT * in a query is a relatively clear one.  If you want to return objects of a type of an underlying construct, and the return type is closely tied over time to the output type, then SELECT * is fine.  On the other hand, if these things aren't true then either you should find ways to make them true, or avoid using SELECT * altogether.

This makes a lot more sense when you realize that things like table methods can be passed up when select * is used (or methods applied to views, or the like).

In general you are going to find two areas where select * is most helpful.  The first is in object-relational design.   The second case is where you want PostgreSQL to define an object model for you.  In reality the first case is a special case of the second.

This way of doing things is very different than the way most applications work today.  The database is encapsulated behind an object model and the application consumes that object model.  In those cases, select * is very helpful.

[1]  Corrected as per comment. I was under the mistaken impression that select * would be a part of the view definition.  This still leads to annoying and unexpected changes in view definition, such as when you drop and recreate the view and so I would still discourage it here however.