Repository navigation
Handle Postgres 'NULL' types #1097
Description
Hey!
For example, I have a schema
CREATE TYPE tag AS ENUM ('new', 'stock', 'top');
CREATE TABLE some_table (
...
mainTag tag
...
)You can see that the mainTag field in the table can be null.
And for the corresponding table, 'sqlc' generates me the following code
type TagType string
const (
TagTypeNew TagType = "new"
TagTypeStock TagType = "stock"
TagTypeTop TagType = "top"
)
func (e *TagType) Scan(src interface{}) error {
switch s := src.(type) {
case []byte:
*e = TagType(s)
case string:
*e = TagType(s)
default:
return fmt.Errorf("unsupported scan type for TagType: %T", src)
}
return nil
}After I try to translate data from the database, I always get an error in the default case: "unsupported scan type for ...."
Question.
SQLC can track that this field can have a NULL value in the schema?
Reacted by Jille Timmermans, Tobias Gessler, StealWonders, Sjors Gielen and Jerome Quere
Activity
kyleconroy commentedon Aug 24, 2021
I think the best option here to generate a second type that can handle NULL values. This will also be backwards compatible with the existing type.
fresonn commentedon Aug 27, 2021
But it's internal error, how can I handle it? I only got an error from sqlc query method.
sbres commentedon Mar 14, 2022
I'm looking at this problem and I think I can try at least scope out the work needed.
In order to replicate the Nullable values form the SQL library, wee need the equivalent of
convertAssignRowsAfter looking at some PR golang/go#24258 for example it is clear the method will stay private.
It is not possible to only copy-paste the method as I found it not that easy to extract it.
This library has the equivalent function but is easy to copy paste:
https://github.com/denisenkom/go-mssqldb/blob/master/convert.go
1- We need an equivalent to convertAssign.
Should we add a convert.go file on the generation with convertAssign from go-mssqldb ?
Should we add convertAssign to sqlc as a public method ?
Or we can just use the Scan method of the type that is already generated ?
2- Once we have convertAssign we can create the NullXXX type needed with it's equivalent Scan and Value methods
Should we generate this type even when the type is never used?
If we can just generate it no matter what becomes very straightforward.
3- Use the Nullable value instead of the raw type on the needed queries
I actually have not vision on this part at all.
@kyleconroy If you can tell me what you prefer for the point N-1 I can try to draft a PR with the changes, at least the points 1, 2
feat: Nullable enums
Jille commentedon May 2, 2022
@sbres I'm not sure why we need a copy of convertAssign at all? Shouldn't implementing driver.Valuer be enough?
I've drafted up a PR, #1582. It seemed fairly simple, but I haven't even tried to execute the code yet.
sbres commentedon May 2, 2022
I had forgotten about this comment. Yeah, we don't need the convertAssign.
I've also made a PR for this issue
#1485
kyleconroy commentedon Sep 22, 2023
sqlc now generates the correct Null* types for enums.