1

I have a column with an array of composite type (text, decimal, timestamp) as data type. I want to create a query to sum the total of the double column of the composite type. Also I want to perform a group by on the date(day-month-year) of the date time.

Can anyone show me an example of explain how this an be done?

Definition of table and type:

create type stage as (
   Stage_Name        text,
   Stage_Distance    decimal,
   Stage_Start_Time  timestamp
);

CREATE TABLE "Event" (
  "Id" serial NOT NULL,
  "Location" text,
  "Date_Range" daterange,
  "Surface" text,
  "Stage_Information" stage[],
  CONSTRAINT "PK_Event" PRIMARY KEY ("Id")
);

Example data

{"(Newtownards,1.5,\"2015-04-03 18:28:00\")"
,"(\"Bulls Brook\",13.4,\"2015-04-04 09:04:00\")"}

Expected results:

Sum(1.5 + 13.4) = 14.9

Group by 2015-04-03, 2015-04-04

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Alan Mulligan
  • 1,107
  • 2
  • 16
  • 35

1 Answers1

3

Assuming current Postgres version 9.4 for lack of information.

Proper design

First of all, consider database normalization. An additional table instead of the column "Stage_Information" is typically the superior solution:

CREATE TABLE stage (
  stage_id  serial PRIMARY KEY
, event_id  int NOT NULL REFERENCES event
, name      text        -- possibly NOT NULL
, distance  numeric     -- possibly NOT NULL
, starttime timestamp   -- possibly NOT NULL
);

It doesn't occupy much more disk space either, array overhead is similar to table overhead. Only the additional index needs some more space. But many queries on the base table will be faster, and updates will be much cheaper, and everything will be cleaner and simpler.

Don't mix quoted and unquoted capitalization with your identifiers. That is very error prone. Use unquoted, legal, lower-case names exclusively if you can.

The query would then be:

SELECT e.id, s.starttime::date AS day
     , sum(s.distance) AS sum_distance
FROM   "Event" e
LEFT   JOIN stage s ON s.event_id = e.id
WHERE  e.id = 1
GROUP  BY 1, 2;

Solution for the problem at hand

While stuck with your current design, you need to unnest() the array to apply aggregate functions to its elements. An then you need to decompose the composite values. Use a LATERAL join:

SELECT e.id, (s.st).stage_start_time::date AS day
     , sum((s.st).stage_distance) AS sum_distance
FROM   "Event" e
LEFT   JOIN LATERAL unnest(e."Stage_Information") s(st) ON true
WHERE  e.id = 1
GROUP  BY 1, 2;

Note the parentheses around (s.st) (the column alias for the unnested column). You need those to access elements of a composite type (row type).

Why LEFT JOIN LATERAL ... ON true?

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I now get the ERROR: column notation .stage_distance applied to type stage[], which is not a composite type LINE 1: SELECT ("Stage_Information").Stage_Distance::date AS day, Updated question – Alan Mulligan Mar 27 '15 at 22:27
  • @AlanMulligan: Okay, that is an array of composite type. Explains why you had "array" in the title. Requires a more sophisticated approach. What exactly do you want to calculate? The sum for an individual column or over the whole table? Please add your version of Postgres to the question. – Erwin Brandstetter Mar 27 '15 at 22:31
  • I need to sum Stage_Distance where id = 1 and select Stage_Start_Time where id = 1 group by Stage_Start_Time (day-month--year) if they make sence – Alan Mulligan Mar 27 '15 at 22:38
  • @AlanMulligan: You cannot select the time *and* group by the day at once. Logically contradictory. Please add example values and the desired result to your question if you find it hard to explain. – Erwin Brandstetter Mar 27 '15 at 22:46