Question on querying by comparing column values

[noobmaster19]

Environment

• PostgreSQL version: 14
• PostgREST version: Latest image from postgrest/postgrest
• Operating system: MacOS Sonoma 14.5

Description of issue

Take for example the following query:

http://localhost:6060/transaction?created_at=eq.updated_at

What I'm trying to do is to query out all the transactions where the created_at timestamp is the same as the updated_at timestamp.

Ofcourse here's the error:

"message": "invalid input syntax for type timestamp with time zone: \"updated_at\""

Because it's treating the updated_at as a string.

My question is to check if there's a way to achieve what i want purely using the querying syntax and not via views.

[laurenceisla]

My question is to check if there's a way to achieve what i want purely using the querying syntax and not via views.

This is not possible right now and it's also not planned for implementation. The solutions are using views as you mentioned, or creating a computed column that returns the boolean value of created_at=eq.updated_at and then using that column as filter.

Here's the related info: #1105

[wolfgangwalther]

What I'm trying to do is to query out all the transactions where the created_at timestamp is the same as the updated_at timestamp.

In a case like that, it's highly likely that you can give those "kind of transactions" a name. Maybe "new transactions"? Or "unmodified transactions"?

Use this name as another endpoint and create a view for it. Also makes your API nicer to use, imho.