"AT TIME ZONE" with zone name PostgreSQL bug?

Actually, the documentation says clearly that the time zone name and abbreviation will behave differently.

In short, this is the difference between abbreviations and full names: abbreviations always represent a fixed offset from UTC, whereas most of the full names imply a local daylight-savings time rule, and so have two possible UTC offsets. Reference

FWIW, that same reference also says

We do not recommend using the type time with time zone (though it is supported by PostgreSQL for legacy applications and for compliance with the SQL standard).

And that's still not the gist of it! I ran into a very similar problem some time ago.

The major cons of time zone abbreviations have been presented here already: they do not take DST (daylight saving time) into account. The major pro: simplicity resulting in superior performance. Taking DST rules into account makes time zone names slow in comparison. Time zone abbreviations are simple, symbolic time offsets, time zone names are subject to a constantly changing set of rules. I ran benchmarks in this related answer on SO, the difference is remarkable. But when applied to a set, it's typically necessary to use time zone names to cover possibly different DST status per row (and also historic differences).

We are talking about CET. The really tricky part is that "CET" is not only (obviously) a time zone abbreviation, it is also a time zone name, at least according to my installation (PostgreSQL 9.1.6 on Debian Squeeze with locale "de_AT.UTF-8") and all others I have seen so far. I mention these details, because Postgres uses the locale information of the underlying OS if available.

See for yourself:

SELECT * FROM pg_timezone_names WHERE name = 'CET';

SELECT * FROM pg_timezone_abbrevs WHERE abbrev = 'CET';

SQL Fiddle.

Postgres picks the abbreviation over the full name. So, even though I found CET in the time zone names, the expression '2012-01-18 01:00 CET'::timestamptz is interpreted according to the subtly different rules for time zone abbreviations.

If that's not a loaded footgun I don't know what is.

To avoid ambiguities, go with the time zone name 'Europe/Berlin' (or 'Europe/Vienna' in my case - which is effectively the same, except for historic differences). Find more details on the topic under the closely related question I mentioned above.

In closing, I would like to voice my deeply felt contempt for the moronic concept of DST. It should be removed from existence and never spoken of again.

Check this:

select  
    '2012-10-28 02:30:00+02'::timestamp with time zone at time zone 'Europe/Berlin' as berlin,
    '2012-10-28 02:30:00+02'::timestamp with time zone at time zone 'CET' as cet,
    '2012-10-28 02:30:00+02'::timestamp with time zone at time zone 'CEST' as cest

+02 is CEST in Berlin, not CET.