Selecting numeric named columns in PostgreSQL returns ?column?

Question

I am using PostgreSQL 13.7 (Debian 13.7-0+deb11u1) and have the following table:

CREATE TABLE public.meterdata (
  "0.0.0" numeric(8,0) NOT NULL,
  "0.9.1" numeric(7,0) NOT NULL,
  "0.9.2" numeric(7,0) NOT NULL,
  "1.2.0" real,
  "1.6.0" real NOT NULL,
  "1.8.0" real NOT NULL,
  "1.8.1" real,
  "1.8.2" real,
  "3.8.0" real NOT NULL,
  "3.8.1" real,
  "3.8.2" real,
  "F.F" numeric(8,0) NOT NULL,
  factor numeric(4,0) NOT NULL
);

Now when I try to get some data:

SELECT '0.0.0','1.8.0',factor FROM meterdata

The returned data from column 'factor' is right, but the column with the numeric names appears as name ?column? and the value of each row in this column is the column name (like 1.8.0).

I tried this with PHP, phppgadmin and even psql from the command line. All return ?column? for each column starting with a number.

I know it is not optimal to use numbers as column names, but is it somehow possible to use this way or am I forced to use letters as column names? Or is that some bug I am running into? Thanks in advance for explaining!

Patrick · Accepted Answer · 2022-08-14T01:00:59.323

In PostgreSQL, if you use non-standard column names you must enclose them in double-quotes. There is no issue with you using column names that start with a number or having a period, but you need to consistently double-quote them.

Single-quotes indicate literal strings, so in your query the query processor sees a constant string, not a column name. In cases where the column name is not clear (could also be for computed columns, for instance), PostgreSQL will print ?column? as a placeholder.

SELECT "0.0.0", "1.8.0", factor FROM meterdata

Selecting numeric named columns in PostgreSQL returns ?column?

1 Answers1