How to use window function over calculated column?

Question

Table definition:

                                                      Table "public.transactions"
┌────────────────────────────┬──────────────────────────┬───────────┬──────────┬───────────────────────────────────────────────────────┐
│           Column           │           Type           │ Collation │ Nullable │                        Default                        │
├────────────────────────────┼──────────────────────────┼───────────┼──────────┼───────────────────────────────────────────────────────┤
│ transactions_id            │ integer                  │           │ not null │ nextval('transactions_transactions_id_seq'::regclass) │
│ transactions_datecreated   │ timestamp with time zone │           │ not null │ CURRENT_TIMESTAMP                                     │
│ transactions_datemodified  │ timestamp with time zone │           │          │                                                       │
│ transactions_usercreated   │ text                     │           │ not null │ CURRENT_USER                                          │
│ transactions_usermodified  │ text                     │           │ not null │ CURRENT_USER                                          │
│ transactions_date          │ timestamp with time zone │           │ not null │ CURRENT_TIMESTAMP                                     │
│ transactions_name          │ text                     │           │ not null │                                                       │
│ transactions_description   │ text                     │           │          │                                                       │
│ transactions_amount        │ numeric                  │           │ not null │                                                       │
│ transactions_currencies    │ integer                  │           │ not null │                                                       │
│ transactions_fromaccount   │ integer                  │           │ not null │                                                       │
│ transactions_toaccount     │ integer                  │           │ not null │                                                       │
│ transactions_uuid          │ uuid                     │           │ not null │ gen_random_uuid()                                     │
│ transactions_hyobjects     │ integer                  │           │          │                                                       │
│ transactions_receiver      │ integer                  │           │          │                                                       │
│ transactions_reporter      │ integer                  │           │          │                                                       │
│ transactions_payer         │ integer                  │           │          │                                                       │
│ transactions_receipt       │ integer                  │           │          │                                                       │
│ transactions_locations     │ integer                  │           │          │                                                       │
│ transactions_exchangerates │ integer                  │           │          │                                                       │
└────────────────────────────┴──────────────────────────┴───────────┴──────────┴───────────────────────────────────────────────────────┘
Indexes:
    "transactions_pkey" PRIMARY KEY, btree (transactions_id)
    "transactions_transactions_uuid_key" UNIQUE CONSTRAINT, btree (transactions_uuid)
Foreign-key constraints:
    "transactions_transactions_currencies_fkey" FOREIGN KEY (transactions_currencies) REFERENCES currencies(currencies_id)
    "transactions_transactions_exchangerates_fkey" FOREIGN KEY (transactions_exchangerates) REFERENCES exchangerates(exchangerates_id)
    "transactions_transactions_fromaccount_fkey" FOREIGN KEY (transactions_fromaccount) REFERENCES accounts(accounts_id)
    "transactions_transactions_hyobjects_fkey" FOREIGN KEY (transactions_hyobjects) REFERENCES hyobjects(hyobjects_id)
    "transactions_transactions_locations_fkey" FOREIGN KEY (transactions_locations) REFERENCES locations(locations_id)
    "transactions_transactions_payer_fkey" FOREIGN KEY (transactions_payer) REFERENCES people(people_id)
    "transactions_transactions_receipt_fkey" FOREIGN KEY (transactions_receipt) REFERENCES hyobjects(hyobjects_id)
    "transactions_transactions_receiver_fkey" FOREIGN KEY (transactions_receiver) REFERENCES people(people_id)
    "transactions_transactions_reporter_fkey" FOREIGN KEY (transactions_reporter) REFERENCES people(people_id)
    "transactions_transactions_toaccount_fkey" FOREIGN KEY (transactions_toaccount) REFERENCES accounts(accounts_id)
Triggers:
    account_update_balance AFTER INSERT OR UPDATE ON transactions FOR EACH STATEMENT EXECUTE FUNCTION account_update_balance()
    insert_username_transactions BEFORE INSERT OR UPDATE ON transactions FOR EACH ROW EXECUTE FUNCTION insert_username('transactions_usermodified')
    transactions_moddatetime BEFORE UPDATE ON transactions FOR EACH ROW EXECUTE FUNCTION moddatetime('transactions_datemodified')

    transactions_id integer NOT NULL,
    transactions_datecreated timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    transactions_datemodified timestamp with time zone,
    transactions_usercreated text DEFAULT CURRENT_USER NOT NULL,
    transactions_usermodified text DEFAULT CURRENT_USER NOT NULL,
    transactions_date timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    transactions_name text NOT NULL,
    transactions_description text,
    transactions_amount numeric NOT NULL,
    transactions_currencies integer NOT NULL,
    transactions_fromaccount integer NOT NULL,
    transactions_toaccount integer NOT NULL,
    transactions_uuid uuid DEFAULT gen_random_uuid() NOT NULL,
    transactions_hyobjects integer,
    transactions_receiver integer,
    transactions_reporter integer,
    transactions_payer integer,
    transactions_receipt integer,
    transactions_locations integer,
    transactions_exchangerates integer
);

In the above table definition I have only one entry with the amount which represent the amount of money moved from one account to other account.

I would still like to use a window function to get the balance at the end of the day.

Here is the SQL that does not work yet, my attempt:

SELECT transactions_id AS "ID", 
       transactions_date AS "Date", 
       CASE 
       WHEN transactions_fromaccount = accounts_id
     THEN - transactions_amount
       WHEN transactions_toaccount = accounts_id
     THEN transactions_amount
       END AS "Amount",
       account_name(transactions_fromaccount) AS "From account",
       account_name(transactions_toaccount) AS "To account",
       CASE WHEN transactions_fromaccount = accounts_id
     THEN '' 
       ELSE transactions_amount::numeric::text END AS "Debit", 
       CASE WHEN transactions_toaccount = accounts_id
        THEN ''
       ELSE transactions_amount::numeric::text END AS "Credit",
       sum("Amount")
     OVER (PARTITITON BY transactions_id 
           ORDER BY transactions_date)
  FROM accounts, transactions 
 WHERE (transactions_fromaccount = accounts_id
    OR transactions_toaccount = accounts_id) 
   AND accounts_id = 65
 ORDER BY transactions_date;

with PostgreSQL 14.6

I am trying to generate the column "Amount" and then to use sum("Amount"), though, I am doing something wrong. I get this error:

ERROR:  column "Amount" does not exist
LINE 17:        sum("Amount")

Is there way to solve it to get sum("Amount")?

Answer 1

When you have a calculated column in your SELECT, like this:

SELECT
  ...
  some_expression AS "alias",
  ...
FROM
  ...

and you want to re-use its result in that same SELECT, the most generic solution is to rewrite the query as a derived table, so that you can reference the "alias" at the outer level:

SELECT
  ...
  "alias",
  ...
  some_expression_involving_"alias" AS "another_alias"
FROM
  ( /* the original query */
    SELECT
      ...
      some_expression AS "alias",
      ...
    FROM
      ...
  ) AS derived /* assign an appropriate alias here */

Now "alias" becomes a valid reference, allowing you both to return the column as is and re-use it in expressions to produce other columns based on it (in your case, an aggregate result).

A similar option would be to rewrite the original as a CTE (common table expression):

WITH
  my_cte AS /* change "my_cte" to whatever name seems more appropriate */
  ( /* the original query */
    SELECT
      ...
      some_expression AS "alias",
      ...
    FROM
      ...
  )
SELECT
  ...
  "alias",
  ...
  some_expression_involving_"alias" AS "another_alias"
FROM
  my_cte

Those two methods could be considered equivalent, and the only difference (and an important one at that) is the CTEs are materialisable in PostgreSQL, which means that choosing one way over the other may have performance implications.

There is also a third approach, which is not as universal as the first two but is applicable in your case. PostgreSQL supports LATERAL joins. You can define "alias" in a separate FROM-less SELECT which you can then LATERAL-join to your other tables. More specifically, it would look like this:

SELECT
  ...
  x."Amount",  /* just the reference here */
  ...
  SUM(x."Amount") OVER (PARTITITON BY transactions_id 
                        ORDER BY transactions_date)
FROM
  accounts,
  transactions,
  LATERAL
  ( /* the definition of "Amount" is moved here from the main SELECT */
    SELECT
      CASE 
        WHEN transactions_fromaccount = accounts_id
        THEN -transactions_amount
        WHEN transactions_toaccount = accounts_id
        THEN transactions_amount
      END AS "Amount"
  ) AS x ("Amount")
WHERE
  ...
ORDER BY
  ...
;

Those are the only options I know of that do not involve creation of new objects in the database. If creating a new object is not an issue, then you might also consider the possibility of defining the original query as a view:

CREATE VIEW my_view  /* again, rename as appropriate */
AS
  /* your original query */
SELECT
  ...

Then your main query would be selecting from the view, enabling you to reference the already defined "Amount" column in whatever way necessary:

SELECT
  ...
  "Amount",
  ...
  SUM("Amount") OVER (PARTITITON BY transactions_id 
                      ORDER BY transactions_date)  
FROM
  my_view

This method is very similar to the first two options, except this way your original query is persisted in the database. Other queries could now read from that view as well, allowing you to re-use your code on a greater scale than just a single column definition (which is, of course, one of the principal purposes of views).

Answer 2

With the following SQL I have managed to get proper running balance for debit and credit of each specific account. Thanks to contributors.

WITH DATA AS ( SELECT transactions_id, transactions_date::date as "Date", transactions_name, account_name(transactions_fromaccount) AS "From account", account_name(transactions_toaccount) AS "To account",
             CASE WHEN transactions_fromaccount = 65 THEN '' 
             ELSE transactions_amount::integer::text END AS "Debit", 
             CASE WHEN transactions_toaccount = 65 THEN ''
             ELSE transactions_amount::integer::text END AS "Credit",
             CASE WHEN transactions_fromaccount = 65 THEN - transactions_amount
             WHEN transactions_toaccount = 65 THEN transactions_amount
             END AS amount
         FROM transactions, accounts
             WHERE (accounts_id = transactions_fromaccount
              OR accounts_id = transactions_toaccount) 
            AND transactions_toaccount= 65 or transactions_fromaccount = 65
            GROUP BY transactions_id )
        SELECT transactions_id, "Date", transactions_name, "From account", "To account", "Debit", "Credit", 
        cast( cast (sum(amount) OVER (ORDER BY "Date", transactions_id  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as integer) as text) AS "Balance"
        FROM DATA
        WHERE "Date" <= current_date