View columns made by EXTRACT cannot be used in Query Tool.

[DaniWe]

Have a view with Year, Month and/or Day as numeric view columns defined as:
EXTRACT(year FROM f.recorddate::timestamp without time zone) AS "Year",
EXTRACT(month FROM f.recorddate::timestamp without time zone) AS "Month",
EXTRACT(day FROM f.recorddate::timestamp without time zone) AS "Day",

Execute a simple query:
SELECT * FROM service.yxney_fuelrecord WHERE Year>2022

Result:

ERROR: column "year" does not exist
LINE 7: SELECT * FROM service.yxney_fuelrecord WHERE Year>2022
^
HINT: Perhaps you meant to reference the column "yxney_fuelrecord.Year".

SQL state: 42703
Character: 407

No other alternatives work either, like:
SELECT * FROM service.yxney_fuelrecord WHERE yxney_fuelrecord .Year>2022
SELECT * FROM service.yxney_fuelrecord WHERE service.yxney_fuelrecord.Month=6
SELECT * FROM service.yxney_fuelrecord ORDER BY Day;

The Query Tool colors Year, Month and Day as if they are reserved keywords and refuse to acknowledge them as columns no matter what. Cannot be used anywhere in a query, even if prefixed.

pgAdmin 7.8 on Windows 11.

[adityatoshniwal]

Hi @DaniWe,
What is the behaviour when you run it from psql tool?

[DaniWe]

Hi, Same behavior with psql apparently: => SELECT recorddate FROM service.yxney_fuelrecord WHERE yxney_fuelrecord.Year=2024 AND Day=31; ERROR: column yxney_fuelrecord.year does not exist LINE 1: ...CT recorddate FROM service.yxney_fuelrecord WHERE yxney_fuel... ^ HINT: Perhaps you meant to reference the column "yxney_fuelrecord.Year". => (the hint gets kind of silly…) In code using a ResultSet to collect values from these columns, using rs.getInt(“Year”), works. The code doesn’t query on these so don’t know if that works or not. Regards, Daniel From: Aditya Toshniwal ***@***.***> Sent: Monday, May 6, 2024 7:29 AM To: pgadmin-org/pgadmin4 ***@***.***> Cc: Westerberg, Daniel ***@***.***>; Mention ***@***.***> Subject: Re: [pgadmin-org/pgadmin4] View columns made by EXTRACT cannot be used in Query Tool. (Issue #7446) Hi @DaniWe<https://github.com/DaniWe>, What is the behaviour when you run it from psql tool? — Reply to this email directly, view it on GitHub<#7446 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ACPZ6NDS6NLNYUZZEQCSHKLZA4ILDAVCNFSM6AAAAABHFMPOBSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDAOJVGIZDENZXGY>. You are receiving this because you were mentioned.Message ID: ***@***.***>

[adityatoshniwal]

Hi @DaniWe,
If its the same behaviour with PSQL then Query tool is working fine. You'll probably need to fix the SQL you're using.
You can take help from pgsql-admin@postgresql.org
I'm closing this issue.
Thanks.