timetz does not recognize localized timezone names (tested on v14 to v18)
Dear PostgreSQL developers,
I think I stumbled upon a minor bug during a course I took this week.
This is on Debian 13.7 Trixie with up-to-date PostgreSQL versions 14 to 18.
The timetz data type does not recognize the geographical descriptions of time zones.
#### snip ####
postgres@test:~$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
14 main 5436 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
15 main 5435 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
16 main 5434 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
17 main 5433 online postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log
18 main 5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log
postgres@test:~$ for port in $(pg_lsclusters | awk '/main/ {print $3}'); do echo "create database
test;" | psql -p $port; echo "create table mytest (testtime timetz); insert into mytest values
('8:00 CET'); insert into mytest values ('9:00 Europe/Berlin');" | psql -p $port; done
CREATE DATABASE
CREATE TABLE
INSERT 0 1
ERROR: invalid input syntax for type time with time zone: "9:00 Europe/Berlin"
LINE 1: insert into mytest values ('9:00 Europe/Berlin');
^
CREATE DATABASE
CREATE TABLE
INSERT 0 1
ERROR: invalid input syntax for type time with time zone: "9:00 Europe/Berlin"
LINE 1: insert into mytest values ('9:00 Europe/Berlin');
^
CREATE DATABASE
CREATE TABLE
INSERT 0 1
ERROR: invalid input syntax for type time with time zone: "9:00 Europe/Berlin"
LINE 1: insert into mytest values ('9:00 Europe/Berlin');
^
CREATE DATABASE
CREATE TABLE
INSERT 0 1
ERROR: invalid input syntax for type time with time zone: "9:00 Europe/Berlin"
LINE 1: insert into mytest values ('9:00 Europe/Berlin');
^
CREATE DATABASE
CREATE TABLE
INSERT 0 1
ERROR: invalid input syntax for type time with time zone: "9:00 Europe/Berlin"
LINE 1: insert into mytest values ('9:00 Europe/Berlin');
^
postgres@test:~$ dpkg -l | grep -e 'postgresql-1. '
ii postgresql-14 14.25-1.pgdg13+1 amd64 The
World's Most Advanced Open Source Relational Database
ii postgresql-15 15.20-1.pgdg13+1 amd64 The
World's Most Advanced Open Source Relational Database
ii postgresql-16 16.16-1.pgdg13+1 amd64 The
World's Most Advanced Open Source Relational Database
ii postgresql-17 17.12-1.pgdg13+1 amd64 The
World's Most Advanced Open Source Relational Database
ii postgresql-18 18.7-1.pgdg13+2 amd64 The
World's Most Advanced Open Source Relational Database
#### snap ####
Regards,
Uwe Sauter
Uwe Sauter <uwe.sauter.de@gmail.com> writes:
The timetz data type does not recognize the geographical descriptions of time zones.
See this thread from yesterday:
/messages/by-id/B6F7E197-70D6-4D09-9BB5-D2A576B0C1B2@miracee.de
regards, tom lane