PostgreSQL Training,
Consulting & Support
General Bits
By A. Elein Mustain

01-Nov-2004 Issue: 91

Archives | General Tidbits | Google General Bits | Docs | Castellano | PortuguÍs | Subscriptions | Notifications | | Prev

General Bits is a column loosely based on the PostgreSQL mailing list pgsql-general.
To find out more about the pgsql-general list and PostgreSQL, see

Converting a ROW to an ARRAY
[GENERAL] Turning a subselect into an array 28-Oct-2004

If you have a table with a column that you want to have grouped into an array you can use the ARRAY() function with a subselect. For example, we have a table of rain measurements with one measurement for each of 4 hours per row. We'd like to see all of those measurements in an array with one row per location and date.

	CREATE TABLE rain( location TEXT, rain_date DATE, hour INTEGER, rainfall FLOAT);
	insert into rain values ('Mesa', '10/1/04', 0, 0.01);
	insert into rain values ('Mesa', '10/1/04', 3, 0.01);
	insert into rain values ('Mesa', '10/1/04', 6, 0.02);
	insert into rain values ('Mesa', '10/1/04', 9, 0.01);
	insert into rain values ('Seattle', '10/1/04', 0, 4.01);
	insert into rain values ('Seattle', '10/1/04', 3, 6.01);
	insert into rain values ('Seattle', '10/1/04', 6, 3.02);
	insert into rain values ('Seattle', '10/1/04', 9, 3.01);
	insert into rain values ('Tampa', '10/1/04', 0, 0.00);
	insert into rain values ('Tampa', '10/1/04', 3, 5.00);
	insert into rain values ('Tampa', '10/1/04', 6, 3.00);
	insert into rain values ('Tampa', '10/1/04', 9, 1.00);
This gives us one row for each row inserted. In order to group them to have one row per location and date with the rainfall amount aggregated into an array, the rainfall must be in a subselect joined to the parent query. The result must be grouped by location and rain date to eliminate duplicate rows.

	SELECT location, rain_date,
      	SELECT rainfall
      	FROM rain
      	WHERE rain.location=r.location
   	) as rainfall_a
	FROM rain r
	GROUP BY location, rain_date;

 	location | rain_date  |      rainfall_a       
 	Seattle  | 2004-10-01 | {4.01,6.01,3.02,3.01}
 	Mesa     | 2004-10-01 | {0.01,0.01,0.02,0.01}
 	Tampa    | 2004-10-01 | {0,5,3,1}
	(3 rows)

See also the "Array Constructors" section in the PostgreSQL documentation:

Contributors: Jim C. Nasby decibel at, Michael Fuhr mike at
Always give a type for literals in views
[GENERAL] WARNING: column "footype" has type "unknown" 27-Oct-2004

Databases and SQL are very fussy about data types, unlike scripting languages like Perl and Python. This can be both a feature and a bother.

A view created with a uncast literal leaves PostgreSQL without a verifiable type in that column. It will let you create the view with a little warning.

	CREATE VIEW lit_view AS
	SELECT 'literal label', dataname, datavalue
	FROM datatable;

	WARNING:  column "?column?" has type "unknown"
	DETAIL:  Proceeding with relation creation anyway.

However, when you try to sort or compare or do any operation on the column it will fail. Operator functions are searched for by data type and if there is no data type known, the appropriate function will not be found to perform your task. And beware, almost everything is performed by functions.

	select distinct * from lit_view;

	ERROR:  failed to find conversion function from "unknown" to text

Contributors: Ed L. pgsql at, Tom Lane tgl at
Getting Seconds from Interval
[GENERAL] interval to seconds conversion. How? 28-Oct-2004

Everyone rushed to answer the easy question of how do you calculate the number of seconds in an interval. You only need to remember the two handy functions extract() and date_part and choose the correct one.

	SELECT EXTRACT(EPOCH FROM interval_value) from table_of_intervals;

Contributors: Denis Zaitsev zzz at, Tom Lane tgl at, Alvaro Herrera alvherre at, Robby Russell robby at, Michael Fuhr mike at,
Rules vs. Triggers: Inserts with Sequences
[GENERAL] Rule uses wrong value 12-Oct-2004

Rules, also known as rewrite rules, operate on a statement level. They should be used when an entire query should be substituted or rewritten. Triggers operate on a row level. A trigger function is executed for each row in the statement.

If you have an INSERT statement involved in a RULE and that statement includes a SERIAL column, the rewrite rule may not do what you want. Suppose there were a job_queue and job_queue_trace table and job_queue's primary key column was a SERIAL. When the job_queue was updated, a copy of the record is wanted in the trace table for auditing.

	CREATE RULE rule_job_queue_trace AS
	ON INSERT TO job_queue
	DO INSERT INTO job_queue_trace (job_id,...) VALUES (NEW.job_id,...);
This rule inserts jobs from the queue with an id one higher than the original job. The reason for this is that the substitution takes place per statement before the data is finalized. The SERIAL column NEW.job_id is effectively replaced by nextval(...) and the nextval() function is called twice--once for the original row and once for that traced row.

When you are operating on actual row values a TRIGGER does what you want. In this case this trigger would do the trick.

	CREATE FUNCTION job_trace()
	   INSERT INTO job_queue_trace (job_id,...) VALUES (NEW.job_id,...);
	' language 'SQL';

	CREATE TRIGGER job_queue_trace BEFORE INSERT ON job_queue

In this case, the job_id has already be calculated and the value of job_id is properly propagated to the job_trace_queue table.

Contributors: Jeff Boes jboes at Tom Lane at

Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009

Search General Bits & Search WWW