Scan from Postgres into struct with pointers

Question

What would be the benefit of using a struct like below with pointers instead of a struct without pointers when scanning from database?

  type User struct {
      ID           *UserID    `json:"id,omitempty"`
      Email        *string    `json:"email"`
      Username     *string    `json:"username"`
      PasswordHash *[]byte    `json:"password_hash"`
      CreatedAt    *time.Time `json:"created_at"`
    }

Counter-question: how would you scan a nullable-string value from your database into the string type? — Luke Joshua Park
– Luke Joshua Park, Commented Dec 3, 2019 at 3:26
@LukeJoshuaPark with sql.NullString in Go or with COALESCE in SQL. — mkopriva
– mkopriva, Commented Dec 3, 2019 at 6:48

colm.anseo · Accepted Answer · 2019-12-03 11:50:10Z

2

As @Luke hinted, if you try to scan a row with null DB values into non-pointer types, the row-scan operation will result in an error.

Best practice is to examine the schema of the DB table. If columns cannot have null-values, use a non-pointer. If a null is possible (however remote a possibility you may think) use a pointer.

If you find pointers tedious to work with (i.e. boilerplate nil checks) use, say a, sql.NullString for a column field. If a NULL is encountered during a row-scan, a NullString value will default to an empty string.

Go 1.13 also added NullTime etc. to handle other Nullable column types.

edited Dec 3, 2019 at 11:50

answered Dec 3, 2019 at 4:25

colm.anseo

22.7k5 gold badges50 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Burak Serdar · Accepted Answer · 2019-12-03 04:18:57Z

1

Using a struct with pointers when scanning an unmarshaling is necessary if you need to distinguish between zero-values and null values. A string/int/struct will fail (or initialized to zero-value when unmarshaling) when the corresponding value is null, whereas a pointer to string/int/struct will be set to nil.

answered Dec 3, 2019 at 4:18

Burak Serdar

52.5k4 gold badges54 silver badges74 bronze badges

Comments

Big_Boulard · Accepted Answer · 2022-04-12 15:52:17Z

0

I suggest to use pgx as mentioned at the end of the lib/pg README file on Github:

For users that require new features or reliable resolution of reported bugs, we recommend using pgx which is under active development.

Then, you could use custom pgtype instead of Go built-in ones.

answered Apr 12, 2022 at 15:52

Big_Boulard

1,3851 gold badge19 silver badges42 bronze badges

Collectives™ on Stack Overflow

Scan from Postgres into struct with pointers

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related