Why does postgresql differentiate between named and numeric time zone notations?

Question

Why is there a difference between named timezone and numeric timezone offset, both of which yield different UTC timestamps for TIMESTAMPTZ?

Assume Europe/Prague timezone, which is currently +02:00 (DST); time of writing is 2018-08-25.

As you can check e.g. via google:

Also, in my postgres installation I can query it:

SELECT utc_offset FROM pg_timezone_names where name = 'Europe/Prague';

Which also yields 02:00:00.

Then, why on earth, do I get two different UTC timestamps when running following two queries?

The following yields 2018-08-25T20:00:00.000Z (2018-08-25 18:00:00-02):

SET TIME ZONE '+02:00'; -- with or without the plus
SELECT '2018-08-25 18:00:00'::TIMESTAMPTZ;

The following yields 2018-08-25T16:00:00.000Z (2018-08-25 18:00:00+02), which is correct:

SET TIME ZONE 'Europe/Prague';
SELECT '2018-08-25 18:00:00'::TIMESTAMPTZ;

Why does postgres differentiate those two notations?

Answer 1

Because, as documented, one uses POSIX and other uses ISO-8601.

Another issue to keep in mind is that in POSIX time zone names, positive offsets are used for locations west of Greenwich. Everywhere else, PostgreSQL follows the ISO-8601 convention that positive timezone offsets are east of Greenwich.