More than one table in a database transaction in PostgreSQL

Here you'll learn how to use a database transaction to insert/update into multiple tables; so that if any of those fail, the entire transaction is cancelled (rollbacked), and if they all succeed, the transaction is committed as a whole, atomically.

What if you wanted to record temperatures in any given zip code, and compute averages as you go along, so both historical temperatures and averages are available at will, without computation? Here's how to do it. Setting up the database is probably the most of it, so that can't be avoided. But the actual application to do the job is super simple.

First, create a new directory for your application, and create a new application "climate":
mkdir temps
cd temps
gg -k climate

Here's the database setup. We'll use a little "cat" trick with EOF so we can write directly to files. So in this case, all the lines between "cat << EOF..." and "EOF" are written to file "setup.sql", which is the database setup. We use MariaDB here; you can use other databases too:
cat << EOF > setup.sql
create user $(whoami)
create database weather_pdb with owner=$(whoami)
grant all on database weather_pdb to $(whoami)
\c weather_pdb
create table if not exists temperature_history (zip varchar(10), temp int, curr_date date, curr_time time);
create table if not exists climate_avg (zip varchar(10) primary key, average_temp int, count int);
EOF

Run the database setup:
sudo -u postgres psql < setup.sql

This file ("weather_pdb") is the database-config-file. It describes your database the way your database needs it, i.e. natively. So if you'd use MariaDB you'd use MariaDB format. If you'd use SQLite, you'd use SQLite format. This makes it easy, since there's no new database config file format to learn:
cat << EOF > weather_pdb
user=bear dbname=weather_pdb
EOF

Here's the actual code. It's a Golf file "weather.golf", which contains two services, "/weather/insert" (to insert new temperature data) and  "/weather/get-agv" (to get historical data and averages). The code is really quite self-explanatory. In Golf, every statements always starts with the new line (watch out for "\" at end of the line, which continues the line). So you can get documentation for get-param (to get input parameter), or run-query (to run a database query), etc.

In this case, for instance, for "/weather/insert" service, you need to provide "zip_code" and "temperature" parameters. The URL path to do that might be for instance "/weather/insert/zip_code=11111/temperature=75", or "/weather/insert?zip_code=11111&temperature=75", depending on what looks better to you, or to what practices you subscribe. If this is a web service, you can pass those parameters as a part of HTTP request body in a POST request; the point is, there's ways to supply them.

Here's the code:
cat << EOF > weather-postgres.golf
%% /weather-postgres/insert public
    get-param zip_code
    get-param temperature
    begin-transaction @weather_pdb
        run-query @weather_pdb = "insert into temperature_history (zip, temp, curr_date, curr_time) \
            values ('%s', '%s', CURRENT_DATE, CURRENT_TIMESTAMP)" \
            input zip_code, temperature no-loop error-text et
        if-true et not-equal ""
            @Error in inserting temperature history [<<print-out et>>]
            rollback-transaction @weather_pdb
            exit-handler
        else-if
            run-query @weather_pdb = "insert into climate_avg (zip, average_temp, count) values ('%s', '%s', 1) \
                on conflict(zip) do update set count=climate_avg.count+1, \
                average_temp=(climate_avg.average_temp*(climate_avg.count)+'%s')/(climate_avg.count+1)" \
                input zip_code, temperature, temperature no-loop error-text et
            if-true et not-equal ""
                @Error in updating temperature average [<<print-out et>>]
                rollback-transaction @weather_pdb
                exit-handler
            end-if
        end-if
    commit-transaction @weather_pdb
    @Data stored and average updated.
%%

%% /weather-postgres/get-avg public
    get-param zip_code
    run-query @weather_pdb = "select temp, curr_date, curr_time from temperature_history \
        where zip='%s' order by curr_date, curr_time asc" input zip_code output temp, date, time
        print-format "Temp [%4ld] Date [%s %s]\n", #temp, date, time
    end-query
    run-query @weather_pdb = "select average_temp, count from climate_avg where zip='%s'" input zip_code output avg, count
        @Average is [<<print-out avg>>] from the total of [<<print-out count>>] samples
    end-query
%%
EOF

Make the application:
gg -q --db="postgres:weather_pdb"

Insert the data. Note we're using gg utility. You can skip the "--exec" here, and see the bash code to run your native application directly from command line. 
gg -r --req="/weather-postgres/insert/zip_code=11111/temperature=82" --exec --silent-header
gg -r --req="/weather-postgres/insert/zip_code=11111/temperature=102" --exec --silent-header
gg -r --req="/weather-postgres/insert/zip_code=11111/temperature=91" --exec --silent-header

The result is:
Data stored and average updated.
Data stored and average updated.
Data stored and average updated.

And now you can query the data:
gg -r --req="/weather-postgres/get-avg/zip_code=11111" --exec --silent-header

With the result like:
Temp [  82] Date [2025-09-07 09:47:42]
Temp [ 102] Date [2025-09-07 09:47:42]
Temp [  91] Date [2025-09-07 09:47:42]
Average is [91] from the total of [3] sample

That's the application! You can run it from command-line or as a web service (see article-tree-web for an example of a setup).

Copyright (c) 2019-2025 Gliim LLC. All contents on this web site is "AS IS" without warranties or guarantees of any kind.