unknown column ?column? show

Question

the way I did like this answer, its all fine get the data I want, but in my project real use, sub table level_two_table it shows one unknonw column ?column? so strange, also not just key there is value exist too.

Anyone what is that? How to solve it? I searched google but cant find related answer, tried demo in fiddle didnt show.

This is on my local computer Mac, and install PostreSQL 16 through Homebrew, its not just showing with my code my project, I also did test with pgAdmin it shows too.

...
level_two_table: [
    {
        '?column?': 'email',
        id: 3,
        level_three_table: { ... }
    }
]
 

Answer 1

That is the column name ("alias") that PostgreSQL chooses when you don't specify anything in the query and it cannot make a good guess.

The following statement and its result demonstrates that:

SELECT /* the column name is used for this result column */
       datname,
       /* the function name is used for this column */
       upper(datname),
       /* PostgreSQL has no guud guess and uses "?column?" */
       encoding + 1
FROM pg_database
WHERE datname = current_database();

 datname │ upper │ ?column? 
═════════╪═══════╪══════════
 test    │ TEST  │        7
(1 row)

If you don't like that, specify your own aliases:

SELECT datname AS database,
       upper(datname) AS db_upper_case,
       encoding + 1 AS pointless_number
FROM pg_database
WHERE datname = current_database();

 database │ db_upper_case │ pointless_number 
══════════╪═══════════════╪══════════════════
 test     │ TEST          │                7
(1 row)