PostgreSQL data types

timestamptz vs timestamp

TLDR You should prefer using timestamptz over timestamp. None of the types store the provided timezone, but timestamptz at least properly parses the time with a timezone. To save user timezone, create a separate column for it.

Let’s use the following table as an example:

CREATE TABLE test (
  t1 timestamptz,
  t2 timestamp
);

The first difference between timestamptz and timestamp is that timestamp discards/ignores the provided timezone:

INSERT INTO test VALUES ('2021-01-01 02:00:00+02', '2021-01-01 02:00:00+02') RETURNING *;
           t1           |         t2
------------------------+---------------------
 2021-01-01 00:00:00+00 | 2021-01-01 02:00:00

timestamp also ignores the server/session timezone:

SET timezone = 'America/Los_Angeles';

The result:

SELECT * FROM test;
           t1           |         t2
------------------------+---------------------
 2020-12-31 16:00:00-08 | 2021-01-01 02:00:00

JSONB

Bun uses JSONB data type to store maps and slices. To change the default type, use type struct tag option:

type Model struct {
    Data map[string]interface{} `bun:"type:jsonb"`
}

To enable json.Decoder.UseNumber option:

type Model struct {
    Data map[string]interface{} `bun:",json_use_number"`
}

You can also use json.RawMessage to work with raw bytes:

type Model struct {
    Data json.RawMessage `bun:"type:jsonb"`
}

Arrays

See Working with PostgreSQL arrays.

UUID

See Generating UUIDs in PostgreSQL.

See also

See Don’t do thisopen in new window for more tips.