Use computed columns more than once in same query

Question

I have this recurring issue where I want to use a value I computed in a select query more than once. Here's an example

SELECT
  complicated_function(x) as foo,
  another_complicated_function(y) as bar,
  complicated_function(x)/another_complicated_function(y) as foo_bar_rate
FROM my_table;

What's the simplest way to write this query? Ideally, I'd like to write

SELECT
  complicated_function(x) as foo
  another_complicated_function(y) as bar,
  foo/bar as foo_bar_rate
FROM my_table;

This question isn't about the specific values being computing, it's about how to write this query in a simpler way that can be more easily maintained.

Please provide a table definition (`CREATE TABLE` statement) showing data types and constraints and your version of Postgres. And explan what you are counting *exactly*. There might be a simpler / faster solution. And there is at least one lingering bug. — Erwin Brandstetter, Jun 02 '17 at 23:12
This question isn't about the specific values I'm computing. I rewrote the query to reflect that. I'm trying to write better more maintainable SQL. I'm using PostgreSQL 9.6.2 — wesdotcool, Jun 05 '17 at 15:55

wesdotcool · Answer 1 · 2017-06-05T15:51:22.520

1

You can use WITH

WITH computed_functions as (
  SELECT
    complicated_function(x) as foo,
    another_complicated_function(y) as bar,
  FROM my_table;
)
SELECT foo, bar, foo/bar as foo_bar_rate FROM computed_functions;

However this is still clunky. If you want to select more columns, you need to add it to the WITH query and the main query.

edited Jun 05 '17 at 15:51

answered Jun 02 '17 at 18:49

wesdotcool

475
4
14

Erwin Brandstetter · Answer 2 · 2017-06-05T21:37:06.347

Your answer using a CTE is generally good. A couple of suggestions, though:

SELECT *, foo / NULLIF(bar, 0) AS foo_bar_rate  -- defend against div0
FROM  (
   SELECT complicated_function(x)         AS foo
        , another_complicated_function(y) AS bar
   FROM   my_table
   --  OFFSET 0  -- see below
   ) sub;

A CTE (though easier to read to some) is typically more expensive than a plain subquery in Postgres. If performance is relevant, only use CTEs where you actually need them. In this case, you don't.

Postgres might flatten the subquery in its query plan, and if cost settings and server config are good, that's typically also the best course of action. If you know better (you sure?) you can use the undocumented "query hint" OFFSET 0, which is logical noise, but forces separate execution of the subquery.
- How can I prevent Postgres from inlining a subquery?
To keep the code short, you can use SELECT * in the outer SELECT. (Since you complained you'd have to add all columns to the main query.)
Use NULLIF(bar, 0) to defend against possible division by 0 exceptions - unless another_complicated_function(y) can never return 0.

Use computed columns more than once in same query

2 Answers2