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);

	SELECT location, rain_date,
   	ARRAY(
      	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 decibel.org, Michael Fuhr mike at fuhr.org

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.
	CREATE VIEW

	select distinct * from lit_view;

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

Contributors: Ed L. pgsql at bluepolka.net, Tom Lane tgl at sss.pgh.pa.us

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 anda.ru, Tom Lane tgl at sss.pgh.pa.us, Alvaro Herrera alvherre at dcc.uchile.cl, Robby Russell robby at planetargon.com, Michael Fuhr mike at fuhr.org,

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,...);

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()
	RETURNS TRIGGER AS
	'
	   INSERT INTO job_queue_trace (job_id,...) VALUES (NEW.job_id,...);
	   RETURN NEW;
	' language 'SQL';

	CREATE TRIGGER job_queue_trace BEFORE INSERT ON job_queue
	FOR EACH ROW EXECUTE PROCEDURE job_trace();

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 qtm.net Tom Lane at sss.pgh.pa.us