Open dandee opened 10 months ago
Thanks for not losing track of this. From https://www.postgresql.org/docs/current/datatype-datetime.html: "timestamptz is accepted as an abbreviation for timestamp with time zone; this is a PostgreSQL extension."
With the new database analyzer this is still an issue (note that I simplified the original query in your playground link to work around the fact that it doesn't PREPARE ...
): https://play.sqlc.dev/p/d1bda5ac81677118390d371f287e5dde62ecdf30ee049352fb3597f34fa015cb
You're going to get the same error with the new database analyzer when you replace timestamp with time zone
with timestamptz
:
https://play.sqlc.dev/p/0ba4f8fe56f9a93c9b3298a11e68595ec3d35c084e8179e3387594b5a1d0a5f9
I tried the new analyzer yesterday and got a false positive in another query. I don't think it's ready for production use yet.
Yes the issue is not with sqlc's analysis step. I was just trying out the new analyzer to confirm.
The new analyzer will have bugs for sure. The error in your last playground link ("column "first_login" is of type timestamp with time zone but expression is of type text") comes from the fact that the query fails to PREPARE ...
against a PostgreSQL database. If you are successfully running that query somewhere that would be interesting to know and I'd say it's worth opening a new issue, since it would imply that there's a problem with the assumptions the new analyzer makes about how PostgreSQL handles queries in PREPARE ...
statements.
All queries in my examples are working without any issues in my project's Postgres database. I even try to run them with EXPLAIN ANALYZE and there are no issues indicated.
We upgraded pgx from v4 without using any override. A lot of our legacy code in our repo have SomeColumn time.Time
. It seems currently if sql_package
is set to pgx/v5
, there is no way to keep the original mapping.
I'm not sure if keeping the original mapping is possible while using pgx v5. If so, please allow the override, otherwise we have to do a major refactor while upgrading to pgx v5.
Actually is there a reason why sqlc is generating pgtype by default?
For instance generating pgtype.Timestamp for create/update params is really not a good idea imo. Say we have a field updated_at, we’ll have to create pgtype.Timestamp ourselves just for the generated update function. Which doesn’t really make sense when we should simple pass time.Time to the update function.
To fix this, we have to apply override on pg_catalog.timestamp, so it’s using time.Time instead of pgtype.Timestamp.
Same issue for uuid, where we can’t just use the default generated code and need to apply override.
Version
1.23.0
What happened?
A SQL type
timestamp with time zone
is not recognized astimestamptz
whentimestamptz
is overridden withtime.Time
.as mentioned by @andrewmbenton here: https://github.com/sqlc-dev/sqlc/issues/2630#issuecomment-1729891052
Relevant log output
Database schema
SQL queries
Configuration
Playground URL
https://play.sqlc.dev/p/d1b3dd0e8105ed7efdbc421c5cf2c6e0896d6ed57c7a17b4eec27b0ce28ca1b1
What operating system are you using?
macOS
What database engines are you using?
PostgreSQL
What type of code are you generating?
Go