In PostgreSQL 9.5, I have a table named
CREATE TABLE public.reports ( id BIGSERIAL PRIMARY KEY, id_station character(11) NOT NULL, date date NOT NULL, element character(4) NOT NULL, value smallint NOT NULL );
For each station (
id_station column) and each day (
date column), I may have multiple value types (element column) :
TAVG (sometimes these values don't exist: I may have only a
TMIN and a
TMAX for a given day).
Here is a (fake) sample:
22;"FR069029001";"1925-01-01";"TMAX";130 23;"FR069029001";"1925-01-01";"TMIN";-25 24;"FR069029001";"1925-01-01";"TAVG";0
I would like to consolidate these values in one single row for each station and each day using this table:
CREATE TABLE public.reports_con ( id SERIAL PRIMARY KEY, id_station character(11) NOT NULL, date date NOT NULL, tmin smallint, tmax smallint, tavg smallint );
I would like to achieve this result:
How to consolidate the data this way in PostgreSQL? With
CREATE TABLE AS?
I know it has to be a recursive query like this (human language) :
For each day: For each station: Find values for TMIN, TMAX, TAVG Insert the results in reports_con in a single row with day and station
I begin learning SQL and would like to achieve this within PostgreSQL (not with Python or a programming language). Can you please help me?