1 // Package pgtype converts between Go and PostgreSQL values. 2 /* 3 The primary type is the Map type. It is a map of PostgreSQL types identified by OID (object ID) to a Codec. A Codec is 4 responsible for converting between Go and PostgreSQL values. NewMap creates a Map with all supported standard PostgreSQL 5 types already registered. Additional types can be registered with Map.RegisterType. 6 7 Use Map.Scan and Map.Encode to decode PostgreSQL values to Go and encode Go values to PostgreSQL respectively. 8 9 Base Type Mapping 10 11 pgtype maps between all common base types directly between Go and PostgreSQL. In particular: 12 13 Go PostgreSQL 14 ----------------------- 15 string varchar 16 text 17 18 // Integers are automatically be converted to any other integer type if 19 // it can be done without overflow or underflow. 20 int8 21 int16 smallint 22 int32 int 23 int64 bigint 24 int 25 uint8 26 uint16 27 uint32 28 uint64 29 uint 30 31 // Floats are strict and do not automatically convert like integers. 32 float32 float4 33 float64 float8 34 35 time.Time date 36 timestamp 37 timestamptz 38 39 netip.Addr inet 40 netip.Prefix cidr 41 42 []byte bytea 43 44 Null Values 45 46 pgtype can map NULLs in two ways. The first is types that can directly represent NULL such as Int4. They work in a 47 similar fashion to database/sql. The second is to use a pointer to a pointer. 48 49 var foo pgtype.Text 50 var bar *string 51 err := conn.QueryRow("select foo, bar from widgets where id=$1", 42).Scan(&foo, &bar) 52 if err != nil { 53 return err 54 } 55 56 JSON Support 57 58 pgtype automatically marshals and unmarshals data from json and jsonb PostgreSQL types. 59 60 Extending Existing PostgreSQL Type Support 61 62 Generally, all Codecs will support interfaces that can be implemented to enable scanning and encoding. For example, 63 PointCodec can use any Go type that implements the PointScanner and PointValuer interfaces. So rather than use 64 pgtype.Point and application can directly use its own point type with pgtype as long as it implements those interfaces. 65 66 See example_custom_type_test.go for an example of a custom type for the PostgreSQL point type. 67 68 Sometimes pgx supports a PostgreSQL type such as numeric but the Go type is in an external package that does not have 69 pgx support such as github.com/shopspring/decimal. These types can be registered with pgtype with custom conversion 70 logic. See https://github.com/jackc/pgx-shopspring-decimal and https://github.com/jackc/pgx-gofrs-uuid for example 71 integrations. 72 73 New PostgreSQL Type Support 74 75 pgtype uses the PostgreSQL OID to determine how to encode or decode a value. pgtype supports array, composite, domain, 76 and enum types. However, any type created in PostgreSQL with CREATE TYPE will receive a new OID. This means that the OID 77 of each new PostgreSQL type must be registered for pgtype to handle values of that type with the correct Codec. 78 79 The pgx.Conn LoadType method can return a *Type for array, composite, domain, and enum types by inspecting the database 80 metadata. This *Type can then be registered with Map.RegisterType. 81 82 For example, the following function could be called after a connection is established: 83 84 func RegisterDataTypes(ctx context.Context, conn *pgx.Conn) error { 85 dataTypeNames := []string{ 86 "foo", 87 "_foo", 88 "bar", 89 "_bar", 90 } 91 92 for _, typeName := range dataTypeNames { 93 dataType, err := conn.LoadType(ctx, typeName) 94 if err != nil { 95 return err 96 } 97 conn.TypeMap().RegisterType(dataType) 98 } 99 100 return nil 101 } 102 103 A type cannot be registered unless all types it depends on are already registered. e.g. An array type cannot be 104 registered until its element type is registered. 105 106 ArrayCodec implements support for arrays. If pgtype supports type T then it can easily support []T by registering an 107 ArrayCodec for the appropriate PostgreSQL OID. In addition, Array[T] type can support multi-dimensional arrays. 108 109 CompositeCodec implements support for PostgreSQL composite types. Go structs can be scanned into if the public fields of 110 the struct are in the exact order and type of the PostgreSQL type or by implementing CompositeIndexScanner and 111 CompositeIndexGetter. 112 113 Domain types are treated as their underlying type if the underlying type and the domain type are registered. 114 115 PostgreSQL enums can usually be treated as text. However, EnumCodec implements support for interning strings which can 116 reduce memory usage. 117 118 While pgtype will often still work with unregistered types it is highly recommended that all types be registered due to 119 an improvement in performance and the elimination of certain edge cases. 120 121 If an entirely new PostgreSQL type (e.g. PostGIS types) is used then the application or a library can create a new 122 Codec. Then the OID / Codec mapping can be registered with Map.RegisterType. There is no difference between a Codec 123 defined and registered by the application and a Codec built in to pgtype. See any of the Codecs in pgtype for Codec 124 examples and for examples of type registration. 125 126 Encoding Unknown Types 127 128 pgtype works best when the OID of the PostgreSQL type is known. But in some cases such as using the simple protocol the 129 OID is unknown. In this case Map.RegisterDefaultPgType can be used to register an assumed OID for a particular Go type. 130 131 Renamed Types 132 133 If pgtype does not recognize a type and that type is a renamed simple type simple (e.g. type MyInt32 int32) pgtype acts 134 as if it is the underlying type. It currently cannot automatically detect the underlying type of renamed structs (eg.g. 135 type MyTime time.Time). 136 137 Compatibility with database/sql 138 139 pgtype also includes support for custom types implementing the database/sql.Scanner and database/sql/driver.Valuer 140 interfaces. 141 142 Child Records 143 144 pgtype's support for arrays and composite records can be used to load records and their children in a single query. See 145 example_child_records_test.go for an example. 146 147 Overview of Scanning Implementation 148 149 The first step is to use the OID to lookup the correct Codec. If the OID is unavailable, Map will try to find the OID 150 from previous calls of Map.RegisterDefaultPgType. The Map will call the Codec's PlanScan method to get a plan for 151 scanning into the Go value. A Codec will support scanning into one or more Go types. Oftentime these Go types are 152 interfaces rather than explicit types. For example, PointCodec can use any Go type that implements the PointScanner and 153 PointValuer interfaces. 154 155 If a Go value is not supported directly by a Codec then Map will try wrapping it with additional logic and try again. 156 For example, Int8Codec does not support scanning into a renamed type (e.g. type myInt64 int64). But Map will detect that 157 myInt64 is a renamed type and create a plan that converts the value to the underlying int64 type and then passes that to 158 the Codec (see TryFindUnderlyingTypeScanPlan). 159 160 These plan wrappers are contained in Map.TryWrapScanPlanFuncs. By default these contain shared logic to handle renamed 161 types, pointers to pointers, slices, composite types, etc. Additional plan wrappers can be added to seamlessly integrate 162 types that do not support pgx directly. For example, the before mentioned 163 https://github.com/jackc/pgx-shopspring-decimal package detects decimal.Decimal values, wraps them in something 164 implementing NumericScanner and passes that to the Codec. 165 166 Map.Scan and Map.Encode are convenience methods that wrap Map.PlanScan and Map.PlanEncode. Determining how to scan or 167 encode a particular type may be a time consuming operation. Hence the planning and execution steps of a conversion are 168 internally separated. 169 170 Reducing Compiled Binary Size 171 172 pgx.QueryExecModeExec and pgx.QueryExecModeSimpleProtocol require the default PostgreSQL type to be registered for each 173 Go type used as a query parameter. By default pgx does this for all supported types and their array variants. If an 174 application does not use those query execution modes or manually registers the default PostgreSQL type for the types it 175 uses as query parameters it can use the build tag nopgxregisterdefaulttypes. This omits the default type registration 176 and reduces the compiled binary size by ~2MB. 177 */ 178 package pgtype 179