PostgreSQL type boolean

[NikkyAI]

PostgreSQL type boolean

[NikkyAI]

having a different runtime could also allow proper wrapping for the TIMESTAMP types correct ?
i assume the postgres driver exposes function to send and retrieve those without the need to blindly throw strings at the server.. i hope

and i bet there is more PG specific data types i just did not try to use yet

[kevincianfarini]

Another related datatype could be UUID. Postgres supports uuids as a first class type, but currently sql-psi binds them as a string type. #289 This is awkward when querying, as UUIDs returned from a query will be a String, but using UUID as a query parameters requires a manual cast to VARCHAR. I'm unsure of the query performance impact this has.

It looks like pgsql's implementation of JDBC ResultSet allows getting UUID's natively. Looks like it also supports getting Date objects as well.

[veyndan]

This is very similar to the issue with SERIAL (#150), where BOOLEAN has been added to sql-psi (#141), but the plumbing in SQLDelight hasn't been done to get this type to work. I'll try and get a fix out within the next couple of days.

[veyndan]

@AlecStrong It looks like we finally reached the limits of tunneling the PostgreSQL types into SQLite types by way of IntermediateType.SqliteType, as I'd want to access ResultSet#getBoolean(Int) to support this change, but SqliteJdbcCursor doesn't support it.

This looks like it could be a big change (e.g., introducing PostgreSqlJdbcCursor, reassessing the API of IntermediateType). Before I go ham on refactoring, I'm interested in what API design choices you have in mind, considering you've thought about this much more than I have (#155).

[AlecKazakova]

able to push a branch? interested in playing around with it for a bit.

i think what this will probably result in is separate runtimes as well likeruntime-postgres which extends SqlCursor to have a getBoolean() or whatever function.

[AlecKazakova]

is it okay to call getInt() on that column? would it return 0/1? If thats the case you should be able to do the same thing we do for MySQL's boolean type: https://github.com/cashapp/sqldelight/blob/master/sqldelight-compiler/src/main/kotlin/com/squareup/sqldelight/core/lang/psi/sqlTypeName.kt#L41

that will use getInt() == 1 and so the kotlin side will be a boolean. If calling getInt() on the column throws (because it only works with getBoolean()) then yea we'll have to iterate on the runtime API

[veyndan]

In MySQL, BOOLEAN is just a synonym for TINYINT(1), so it's just a glorified integer. In PostgreSQL, BOOLEAN is its own separate type, and to retrieve it as 0 or 1 it'd need to be casted to an integer.

I verified this behavior by doing what you said, and added the following case to this when expression:

booleanDataType != null -> IntermediateType(IntermediateType.SqliteType.INTEGER, BOOLEAN)

And changed the dog table in dog.sq to:

CREATE TABLE dog (
  name VARCHAR(8) NOT NULL,
  breed TEXT NOT NULL,
  is_good BOOLEAN NOT NULL
);

Running the integration tests results in the following exception as expected:

org.postgresql.util.PSQLException: ERROR: column "is_good" is of type boolean but expression is of type bigint
  Hint: You will need to rewrite or cast the expression.
  Position: 33
    at …

[veyndan]

Literally just a two line change, but here ya go: https://github.com/veyndan/sqldelight/tree/sql-psi-156/boolean

[AlecKazakova]

yea I would expect the timestamp stuff is also going to have the same problem

[AlecKazakova]

fixed on sqldelight master