This repo does not have a license -- as a practical matter this means I'm not able to use it.

I assume you intend this to be MIT, same as pgx. I'll put together a PR with a copy of that on that assumption

if I import pgtype:
"github.com/jackc/pgx/pgtype"

in my source code

with go 1.14
I get the following modules in my go.mod file;

require (
github.com/jackc/pgx v3.6.2+incompatible
github.com/jackc/pgx/v4 v4.4.1

if I do

go get github.com/jackc/pgx/pgtype

the output is:

go: found github.com/jackc/pgx/pgtype in github.com/jackc/pgx v3.6.2+incompatible

Minimal example:

import "github.com/google/uuid"

var t []uuid.UUID
tx.QueryRow(ctx, `select '{}'::uuid[]`).Scan(&t)  // panic: runtime error: index out of range [0] with length 0

It looks like assignToRecursive is always called with index = 0, dimension = 0, which then:

1. Breaks from the reflect.Slice case,
2. Indexes src.Elements[0] and panics.

I don't know what the best special case handling should be here: maybe an explicit check for an empty array? Do multidimensional empty arrays exist?

This is also likely not restricted to UUIDArray, it's just the type I tested with.

Currently there is no support for range array datatypes. This adds support for the tstzrange_array datatype.

#5

The built in method AssignTo() for converting a postgresql numeric such as Numeric(5,2) to float64 does not work.

I'm getting the following error trying to insert into a integer[] column in Postgres:
ERROR: wrong element type (SQLSTATE 42804)

I'm hardcoding the value now just to see what's going on:

pgtype.Int2Array{
			Elements: []pgtype.Int2{
				{Int: 1, Status: pgtype.Present},
			},
			Dimensions: []pgtype.ArrayDimension{{Length: 1, LowerBound: 1}},
			Status:     pgtype.Present,
		}

Any ideas?

Would be nice if pgx supported CITEXT extension which is often used for email fields.

I believe we just need to alias them to TEXT field.

I've currently added it like this:

conn.ConnInfo.RegisterDataType(pgtype.DataType{
	Value: &pgtype.Text{},
	Name:  "citext",
	OID:   16393,
})

And it seems to work, but I haven't done extensive testing.

Hello.
I'm trying select value from table where value = ANY($1) and parameter pgtype.VarcharArray
value has type - character varying
with Int4Array - construction with ANY - has no errors

Hi. There is a regression in v1.5.0 compared to v1.4.2
Here is the case:

var dst *[]string
err = db.QueryRow(ctx, `SELECT ARRAY('foo val', 'foo val 2', 'foo val 3')`).Scan(&dst)

This returns an error:

can't scan into dest[0]: incorrect dimensions, expected 1, found 0

In v1.4.2 the above operation was successful.
I guess it might be related to this change: #51

Hi, first of all thank you for providing suite of libraries and drivers for the Postgres in Golang.

I am working on a very big integer field that could not fits in Golang int64 nor Postgres bigint. So I use the Postgres numeric field. I noticed the Set method on pgtype.Numeric can accepts a string so I have made the insertion operation works by using

value := big.NewInt(int64(1))
new(pgtype.Numeric).Set(value.String())

However, I realize in the implementation of AssignTo there is no way back to string but only existing Golang primitive numeric types only. I notice the Numeric struct does declare the Int (big.Int) as public, but it doesn't seem right to use it directly, I assume this is implementation details and I should rely on public methods to do it.

Maybe I have missed something in this process. Is this design on purpose or do I missed the way to convert it back to string or big.Int? Thank you.

attempting to scan into a pointer-to slice, in this case *[]string, results in the confusing error
can't scan into dest[0]: incorrect dimensions, expected 1, found 0

Because unlike the prior code, it does not re-attempt after a dereference of the destination.

Should this case work? scany depends on it in its tests to do so. There is, however, a distinction of nil vs. empty slices, but pointers to maps are still working.

For the integer types, would you accept a PR that allowed float64 to be used where float64(int(v)) = v? We are currently trying to switch from lib/pq to pgx and we have a number of use cases where the bind parameters are coming from JSON, decoded to an interface{}, and used in a dynamically generated query.

Hi, so I'm trying to accomplish the above, and this is what I'm trying at the moment:

I have a struct declaration

import (
	"github.com/jackc/pgx/pgtype"
)

type ABC struct {
        ...
	Address     pgtype.Text        `db:"address"`
        ...
}

And I'm trying to check if the field is null

import (
	"strings"

	"github.com/jackc/pgtype"
)

if abc.Address.Status == pgtype.Null { }

However, it gives me the following error

invalid operation: abc.Address.Status == "github.com/jackc/pgtype".Null (mismatched types "github.com/jackc/pgx/pgtype".Status and "github.com/jackc/pgtype".Status)

At no point is pgx/pgtype imported, so how would I check if something is null?

Speaking of, would a function for each data type, IsNull() or IsPresent() be of value? I was surprised to see nothing of the sort, though I'm not sure if it would be implementable without major pains.

Cheers!

We are investigating switching from lib/pq to pgx to see if it improves performance. I'm encountering an issue where scanning into a type results in an unecessary call to Value and Scan. In this specific case it is the uuid type in https://github.com/pborman/uuid. This library defines a uuid that implements the sql.Scanner interface. However, I think this would happen for any type that implements the sql.Scanner interface, but not the pgtype.TextDecoder or pgtype.BinaryDecoder interface.

The problem happens in

I can't make the pboreman/uuid type implement the BinaryDecoder interface, nor can I stop it from implementing the Scanner interface. I could make a new type that wraps the uuid, but we have hundreds of functions (and their tests), that would have to be changed. In addition to the cost involved, failing to convert a variable wouldn't be caught by the compiler, it would silently be more expensive than necessary.

I'm wondering if I missed something, and there is a way to avoid this spurious work? If not, can the logic of Scan be altered to avoid this? For example, could AssignTo be tried first, and on an error it tries to value and scan if possible. Alternatively, maybe the DataType could contain a flag of whether to try this path or not.

Thanks.

I tried to pass the args returned by the squirrel and it says it expects n but got only 1. The type of returned arg is []interface{}.

Given a mytype:

ct, err := pgtype.NewCompositeType("mytype", []pgtype.CompositeTypeField{
	{"a", pgtype.Int4OID},
	{"b", pgtype.TextOID},
}, conn.ConnInfo())

How to define array of mytype?

Hi, i would to think it a ltree in pgx avaible?
It's an "Additional Supplied Modules", https://www.postgresql.org/docs/12/ltree.html

I've been playing null values in text[] columns. Say you have a text array containing one element, which is nil. pg text encodes that as {NULL}. A text array containing one element, which is the literal string "NULL" will be encoded with quotes by pg: {"NULL"}.

But it seems that TextArray.DecodeText() will treat both cases as actual nulls:

a := pgtype.TextArray{}
a.DecodeText(nil, []byte(`{NULL}`))
fmt.Printf("%+v\n", a.Elements[0])      // {String: Status:1}

a.DecodeText(nil, []byte(`{"NULL"}`))
fmt.Printf("%+v\n", a.Elements[0])      // {String: Status:1}

The decode path hits arrayParseQuotedValue which strips off the quotes, but back up in DecodeText, it looks for values that equal NULL, but at that point, it can't tell if the value had been quoted or not. The tests don't catch this because they use variations of the word "null" with different cases, but not the exact value of NULL.

Hello, I am having trouble implementing time with time zone without date postgres type. I could only see time or timestamp or timestampz implementation and think it is not what i need. Can anyone help me with this ?
I have a time of day in a for a certain time zone, that I need to store in postgres. This is what I am trying to insert inside db,

But, db throws an error stating date/field out of range.

What correction should I make ?

Why structs don't implement Stringer interface ? It would be nice to have .String() string method for easy use in fmt.Println and in logger.

Hi, I ended up with this problem::

import (
    "fmt"
    "github.com/jackc/pgtype"
)

func main() {
    var buf []byte
    t := pgtype.Text{"", pgtype.Present}

    res, _ := t.EncodeText(nil, buf)

    if res == nil {
        fmt.Print("This should not happen")
    }
}

We found that when we were storing empty strings as values to the hstore. They ended up to be stored as nils. Basically, the problem is in return append(buf, src.String...), nil the src.String... when it is ""... which evaluates to nothing, e.g. return append(buf) which is in that case nil.

Encode pgtype.Tstzrange with [eeee,)cause a panic.
=> runtime error: index out of range [-5]

shouldn't be reported as invalid tstzrange instead ?

Error message

pgtype.JSON ERROR: invalid input syntax for type json (SQLSTATE 22P02)

Description

When use pgx/v4/stdlib and sqlx to store pgtype.JSON to database, will get an error like above(SQLSTATE 22P02).
I also tried database/sql and got the same errors.
However, with pgx.ConnectConfig(ctx, connConfig), pgtype.JSON worked but []byte not.

Code to reproduce

package main

import (
    "log"

    "github.com/jackc/pgtype"
    "github.com/jackc/pgx/v4"
    "github.com/jackc/pgx/v4/stdlib"
    "github.com/jmoiron/sqlx"
)

func main() {
    dns := "user=tester host=localhost dbname=tester sslmode=disable"
    connConfig, _ := pgx.ParseConfig(dns)
    connConfig.PreferSimpleProtocol = true
    conn, err := sqlx.Open("pgx", stdlib.RegisterConnConfig(connConfig))
    //conn, err := pgx.ConnectConfig(ctx, connConfig)
    if err != nil {
        log.Fatal(err)
    }

    _, err = conn.Exec(`drop table if exists pgx514;`)
    if err != nil {
        log.Fatal(err)
    }

    _, err = conn.Exec(`create table pgx514 (id serial primary key, data jsonb not null);`)
    if err != nil {
        log.Fatal(err)
    }

    dataJSON := &pgtype.JSON{Bytes: []byte(`{"foo": "bar"}`), Status: pgtype.Present}
    commandTag, err := conn.Exec("insert into pgx514(data) values($1)", dataJSON)
    if err == nil {
        log.Println("pgtype.JSON", commandTag)
    } else {
        log.Println("pgtype.JSON", err)
    }

    dataBytes := []byte(`{"foo": "bar"}`)
    commandTag, err = conn.Exec("insert into pgx514(data) values($1)", dataBytes)
    if err == nil {
        log.Println("[]byte", commandTag)
    } else {
        log.Println("[]byte", err)
    }
}

I'm using LISTEN to get some streaming updates from Postgres in json format. The struct I'm unmarshalling into has two pgtype.Int4 fields. The source JSON has one value set to null and the other value set to 2. The result looked like this:

TaskID:{Int:2 Status:2}
TestID:{Int:0 Status:2}

Status of 2 is Present and it looks like Present is hardcoded in UnmarshalJSON.

I expected the result to look like this instead:

TaskID:{Int:2 Status:2}
TestID:{Int:0 Status:1}

where 1 is Null.

I changed the UnmarshallJSON function to the following and it achieves what I needed:

func (dst *Int4) UnmarshalJSON(b []byte) error {
	var n *int32
	err := json.Unmarshal(b, &n)
	if err != nil {
		return err
	}

	if n == nil {
		*dst = Int4{Status: Null}
	} else {
		*dst = Int4{Int: *n, Status: Present}
	}

	return nil
}

Does this change make sense to you, and if so would you accept a PR for this? The change would cover all types of course, not just Int4.

I have a uuid[] column in a postgres table and trying to do a simple scan into pgtype.UUIDArray but I get the error: "can't scan into dest[1]: unable to assign to pgtype.UUIDArray"

My code is really simple:

var v pgtype.UUIDArray
err = conn.QueryRow(ctx, `SELECT uuids FROM my_table LIMIT 1;`).Scan(&v)

Running a query like SELECT B'1001111101101110001101100010110' is panicing:

panic: interface conversion: *pgtype.Bit is not pgtype.TextDecoder: missing method DecodeText

goroutine 44631 [running]:
main.compareConns.func1.1(0xc00018eda5, 0x8, 0x0, 0x0, 0xc000792200, 0x63)
	/home/mjibson/go/src/github.com/cockroachdb/cockroach/pkg/cmd/smithcmp/main.go:246 +0x195
panic(0x136d620, 0xc000476240)
	/usr/local/go/src/runtime/panic.go:522 +0x1b5
github.com/cockroachdb/cockroach/vendor/github.com/jackc/pgx.(*Rows).Values(0xc000f026e0, 0x1595d01, 0xc000f026e0, 0xc000792200, 0x63, 0x265dde0)
	/home/mjibson/go/src/github.com/cockroachdb/cockroach/vendor/github.com/jackc/pgx/query.go:296 +0x64e
main.(*Conn).Values(0xc0002da100, 0x1942a60, 0xc000bd9180, 0x0, 0x0, 0xc000792200, 0x63, 0x0, 0x0, 0x0, ...)
	/home/mjibson/go/src/github.com/cockroachdb/cockroach/pkg/cmd/smithcmp/conn.go:101 +0x157
main.compareConns.func1(0x0, 0x0)
	/home/mjibson/go/src/github.com/cockroachdb/cockroach/pkg/cmd/smithcmp/main.go:251 +0x1b7
github.com/cockroachdb/cockroach/vendor/golang.org/x/sync/errgroup.(*Group).Go.func1(0xc0008de960, 0xc000851f80)
	/home/mjibson/go/src/github.com/cockroachdb/cockroach/vendor/golang.org/x/sync/errgroup/errgroup.go:57 +0x57
created by github.com/cockroachdb/cockroach/vendor/golang.org/x/sync/errgroup.(*Group).Go
	/home/mjibson/go/src/github.com/cockroachdb/cockroach/vendor/golang.org/x/sync/errgroup/errgroup.go:54 +0x66

There are misleading error messages in Set(src interface{}) in polygon.go, circle.go etc...
The error message cannot convert %v to T implies that other types can be converted. It should say that the function is not yet implemented.

While reading the source code I encountered the following line

I'd like to understand what is the purpose of it, as it smells a bit buggy to me. Is this correct? The returned value ends up being, for nil values, a byte slice containing the following, which is not the same for the json type for example

0 = {uint8} 1
1 = {uint8} 110
2 = {uint8} 117
3 = {uint8} 108
4 = {uint8} 108

I'm having problems scanning into custom types, that can have null returned for them. Instead of scan invoking the object's DecodeText method, scan return an error such as can't scan into dest[1]: unknown oid 16494 cannot be scanned into &{%!t(string=) %!t(*time.Duration=<nil>) []}).

Here is a psuedocode example of what I'm trying to do.

-- Postgres record type
CREATE TYPE foo_t AS (
    field1    TEXT, 
    // more fields...
);

// Define a go type to match a postgres record type
type Foo struct { 
     Field1 string
     // more fields...
}

func (f *Foo) DecodeText(ci *pgtype.ConnInfo, buf []byte) error {
   // parse the composite record like ('a',,'b') into this object's fields
}

// Retrieve some data into this custom type
var when *time.Time
var f *Foo
row := conn.QueryRow(ctx, "SELECT NULL::timestamp, NULL::foo_t")
err := row.Scan(&when, &f)

I believe an error is returned because the type passed to ConnInfo Scan is a pointer to a pointer; which means it does not implement the BinaryEncoder or BinaryDecoder interface. The custom type's oid isn't registered with ci (and there is nothing implementing the DataType interface to register). Is there another way to accomplish what I'm trying to do?

While experimenting to try and understand the problem, I was able to make Scan work in this case, by adding a bit of reflection. I can make a pr to add this code if that's the right solution. Just prior to falling back to scanUnknownType:

    // We might be given a pointer to something that implements the decoder interface(s),
    // even though the pointer itself doesn't.
    refVal := reflect.ValueOf(dest)
    if refVal.Kind() == reflect.Ptr && refVal.Type().Elem().Kind() == reflect.Ptr {
        // If the database returned NULL, then we leave dest as nil to indicate that.
        if buf == nil {
            return nil
        }

        // We need to allocate an element, and set the destination to it
        // Then we can retry as that element.
        // This is roughly equivelent to the following go code:
        //      var dest **Foo
        //      elemPtr := new(Foo)
        //      *dest = elemPtr
        elemPtr := reflect.New(refVal.Type().Elem().Elem())
        refVal.Elem().Set(elemPtr)
        return ci.Scan(oid, formatCode, buf, elemPtr.Interface())
    }

given the type
type MyString string
and an object of

struct {
  Thing MyString
  Things []MyString
}

Thing will scan fine, as there's a conversion from *string to *MyString. Things will not. Value panics because it cannot come up with a conversion from *[]string to *[]MyString

When trying to pass nil net.IP into the database, we have encountered error during the encoding:

+   $ cat main.go
package main

import (
        "fmt"
        "net"

        "github.com/jackc/pgtype"
)

func main() {
        var ip net.IP
        var inet pgtype.Inet

        inet.Set(ip)
        fmt.Printf("Present: %v\n", inet.Status == pgtype.Present)

        _, err := inet.EncodeBinary(nil, nil)
        fmt.Println(err)
}
:   $ go run .
Present: true
Unexpected IP length: 0

That causes the db insert to fail. Pull request incomming in a bit.

Support for so many different types and automatic conversions doesn't come for free. This package appears to have a significant impact on program binary size.

Removing hstore and array types reduce the size of a binary by ~700k. It is unlikely that significant improvements can be made without breaking backwards compatibility. But consider the following changes if and when a major release is made (possibly when Go gets generics).

1. One array type such as the existing ArrayType instead of a specific array type for each underlying type. This would save binary size at the possible expense of runtime performance.
2. Array types and other less common types could be separated into other packages.

See also jackc/pgx#822.

Hi,

within our company we have many SPs that return time with timezones. This is because users located in different time zones and require to see date/time in their local time zone.

We faced issue that pgx ignores timezones returned by the Postgres in format "2006-01-02 15:04:05+02". As the result we see that timezones are defaulted to the server's timezone.

Currently PGX supports the following formats (taken from sources):
const pgTimestamptzHourFormat = "2006-01-02 15:04:05.999999999Z07"
const pgTimestamptzMinuteFormat = "2006-01-02 15:04:05.999999999Z07:00"
const pgTimestamptzSecondFormat = "2006-01-02 15:04:05.999999999Z07:00:00"

Is it possible to customize the default format?

It looks this was extracted from a mono-repo and the testing infra was lost along the way.

i use gorm , code like follow:

       a := float64(1)
	demo := Demo{}
	if err := db.Where("id = ?", a).Find(&demo); err != nil {
		t.Fatal(err)
	}

gorm print SQL log:

SELECT * FROM "demos" WHERE id = 1.000000 AND "demos"."deleted_at" IS NULL

error: cannot convert 1 to Int4

is a bug or not?, please forgive my poor english!!!

pgtype.Int4{
	Status: pgtype.Null,
},
pgtype.Int4{
	Int: int32(14812872),
	Status: pgtype.Present,
}

Both columns are integers in the table. The null works but actually trying to insert an integer is throwing that error.
I tried this variation as well.

pgtype.Int4{
	Int: 14812872,
	Status: pgtype.Present,
}

Any ideas?

Right now, many types have a "Set" method that allows conversion from a plain Go type to a pgtype. However, in practice it could be awkward to use. It would be nice to have constructor functions for each type, so that:

ts := pgtype.Timestamptz{}
ts.Set(t)
return myStruct{
    ts: ts,
}

could be replaced by just

return myStruct{
    ts: pgtype.NewTimestamptz(t),
}

This is not without precedent. Protobuf, for instance, has timestamppb.New and friends that allows this exact use case.

I have this query, where validity is of tstzrange type:

SELECT
    id
FROM
    my_list
WHERE
    validity @> $1
LIMIT 1
;

tDate := time.Now()
var id int
err = tx.QueryRow(
    p.ctx,
tDate,
).Scan(
    &id,
)

The following code throws an error: cannot convert 2020-04-04 16:03:06.679 +0000 UTC to Tstzrange

The validity column is a tstzrange, but the sql query above works fine when I test it directly in Postgresql. validity @> $1 accepts a timestamptz like validity @> '2020-04-04 00:00:00.000+00'::timestamptz

When I try to set a time.Time for $1, pgtype wants to convert it to a tstzrange, even though it shouldn't.

I am facing an issue, where (*Pool).Exec is failing in text.go:47 in Set method. It seems to fail to convert string to Text. At the moment I am using masterminds/squirrel sqlbuilder and both sqlString and args seem to have correct values.
Error looks like the following:
Error: Received unexpected error: cannot convert [a] to Text: github.com/jackc/pgtype.(*Text).Set github.com/jackc/[email protected]/text.go:47

I am wondering if square brackets actually somehow ended up there, as a part of the value

If I have aTEXT column in my database table with null values in it, I'm unable to load that into a *string variable.

Here's a really simple example. Suppose the avatar column is a TEXT column with a null value:

type User struct {
    ID uuid.UUID
    Email string
    Avatar *string
}

row := conn.QueryRow(ctx, "select id, email, avatar from users where id = $1", userID)
if err := row.Scan(&user.ID, &user.Email, &user.Avatar); err != nil {
    fmt.Printf("Unable to get record: %s\n", err)
}

When I run that code, I get an error something like this:

can't scan into dest[3]: illegal base64 data at input byte 5

I can use string pointers if I change the column type to a VARCHAR. Or I can use a pgtype.Text variable directly instead:

var user User
var avatar pgtype.Text

row := conn.QueryRow(ctx, "select id, email, avatar from users where id = $1", userID)
if err := row.Scan(&user.ID, &user.Email, &avatar); err != nil {
	fmt.Printf("Unable to get record: %s\n", err)
} 

if avatar.Status == pgtype.Present {
	user.Avatar = avatar.String
} else {
	user.Avatar = nil
}

I noticed that there's no implementation of MarshalJSON() for pgtype.VarcharArray.

Was this intentionally excluded or would this be a good target for contribution?

Hello. How can I convert from []int32 to Int4Array?

I'm having a column "start_at time not null" which SQLBoiler converts to go structure as "StartAt time.Time"

Query looks like:
INSERT INTO "my_table" ("id","start_at") VALUES ($1,$2)

where $1 = '74f6924b-98cb-4871-bf8d-962d7c8d0191' and $2 is time.Time (time.Now())

Running the query (via SQLBoiler) returns error:
cannot convert 2019-12-09 02:00:00 +0200 EET to Text

I've followed the code and find out that the error is returned inside method:

pgtype/text.go: Set(src interface{}) error
...
return errors.Errorf("cannot convert %v to Text", value)

It seems that for this OID=1083 (time) there is no appropriate function. I would expect the time.Time object for this OID type to look something like:

case time.Time:
	if value.IsZero() {
		*dst = Text{Status: Null}
	} else {
		*dst = Text{String: value.Format("15:04:05.00000"), Status: Present}
	}

If I hack the generic implementation in the pgtype/text.go, then the Insert statement succeed.

The only place where I saw time Format method is in pgtype/date.go, where we have:

s = src.Time.Format("2006-01-02")

Am I doing something wrong here or time type is not supported?

Right now I've got code like:

ids := []string{}
for obj := range objs {
    ids = append(ids, obj.id)
}

idsArray := &pgtype.TextArray{}
idsArray.Set(ids)
tx.Query(ctx, `SELECT * FROM TABLE WHERE id = ANY($1)`, idsArray)

The TextArray API here is relatively inefficient because it requires allocating a new slice of []Text. I can manually create an instance, but this requires knowing how to populate the Dimensions field. Worse, there's no easy way to reuse the allocated memory, if I'm doing multiple queries.

I think the right solution here would be if TextArray.Set() supported intialization from []pgtype.Text.

I believe this would be a straightforward addition to the API, and I'd be happy to send a PR if you're interested. The code probably looks something like:

case []Text:
    if value == nil {
        *dst = TextArray{Status: Null}
    } else if len(value) == 0 {
        *dst = TextArray{Status: Present}
    } else {
        *dst = TextArray{Elements: value, Dimensions: []ArrayDimension{{Length: int32(len(value)), LowerBound: 1}}, Status: Present}
    }

inserted at https://github.com/jackc/pgtype/blob/master/text_array.go#L44

Application JSON data coming in (or going out) should not know (or reveal) anything about our underlying implementation model. For a given struct like:

type My struct {
  IP pgtype.Inet
  MAC pgtype.Macaddr
}

Populated accordingly, it would be reasonable for json.Marshall to deliver bytes equivalent to (for example):

{"IP":"192.0.2.1/32","MAC":"00:0a:95:9d:68:16"}

Symmetrically, json.Unmarshal should be able reconstitute the struct from the serialized JSON. Can probably submit a PR for some of this if you concur. Currently that would give an error like:

json: cannot unmarshal string into Go struct field My.IP of type pgtype.Inet

We had a similar problem to pgx#242, however in our case we needed a []*time.Time.

We have to call a Postgres function to insert data which takes a couple arrays as input parameters. One of which is a an ETA defined as: eta_ TIMESTAMP WITH TIME ZONE[]. In go we have a var etas []*time.Time where some of the elements are nil (as the ETA is not yet known) and those should be stored as NULL in our database.

I propose a solution to solve this particular issue with []*time.Time which works with pgx v4 out of the box, see #41. If you find this good quality I'd be pleased to make the fix for every array types. I'd also appreciate if you had a smarter way of dealing with this issue since there's a bit of code repetition.

The zero type for pgtype.Numeric is {Int:<nil> Exp:0 Status:0} which seems like a reasonable representation of a NaN Numeric value. It may be beneficial to treat this zero type as NaN.

Hi I'm found that I can't assign interval with days or months to time.Durartion

interval with months or days cannot be decoded into *time.Duration

What were the reasons for doing this?

Hi, first of all, thanks for your awesome libraries!
I didn't know where exactly should I post this question, so I apologize in advance, feel free to close this topic.

My question is: are there any benefits from using pgtype primitive types such as Varchar, Bool, Timestamp etc. compared to standard Go types like string, bool, Time? Any performance gains?

It's much more cumbersome to use pgtype types in a struct, for example:

type Item {
	MetaCreatedAt           pgtype.Timestamptz     `db:"meta_created_at"`
        CurrencyCode             pgtype.Varchar              `db:"currency_code"`
...}

especially when trying to create structs with fields inline. Also, from now on you must use .Get() to interact with the rest of your Go codebase.
pgtype types are very useful when you need some postgres specific types like hstore, jsonb etc, but is it worth the hussle for primitive types?