README.md (9844B)
1 pq is a Go PostgreSQL driver for database/sql. 2 3 All [maintained versions of PostgreSQL] are supported. Older versions may work, 4 but this is not tested. [API docs]. 5 6 [maintained versions of PostgreSQL]: https://www.postgresql.org/support/versioning 7 [API docs]: https://pkg.go.dev/github.com/lib/pq 8 9 Connecting 10 ---------- 11 Use the `postgres` driver name in the `sql.Open()` call: 12 13 ```go 14 package main 15 16 import ( 17 "database/sql" 18 "log" 19 20 _ "github.com/lib/pq" // To register the driver. 21 ) 22 23 func main() { 24 // Or as URL: postgresql://localhost/pqgo 25 db, err := sql.Open("postgres", "host=localhost dbname=pqgo") 26 if err != nil { 27 log.Fatal(err) 28 } 29 defer db.Close() 30 31 // db.Open() only creates a connection pool, and doesn't actually establish 32 // a connection. To ensure the connection works you need to do *something* 33 // with a connection. 34 err = db.Ping() 35 if err != nil { 36 log.Fatal(err) 37 } 38 } 39 ``` 40 41 You can also use the `pq.Config` struct: 42 43 ```go 44 cfg := pq.Config{ 45 Host: "localhost", 46 Port: 5432, 47 User: "pqgo", 48 } 49 // Or: create a new Config from the defaults, environment, and DSN. 50 // cfg, err := pq.NewConfig("host=postgres dbname=pqgo") 51 // if err != nil { 52 // log.Fatal(err) 53 // } 54 55 c, err := pq.NewConnectorConfig(cfg) 56 if err != nil { 57 log.Fatal(err) 58 } 59 60 // Create connection pool. 61 db := sql.OpenDB(c) 62 defer db.Close() 63 64 // Make sure it works. 65 err = db.Ping() 66 if err != nil { 67 log.Fatal(err) 68 } 69 ``` 70 71 The DSN is identical to PostgreSQL's libpq; most parameters are supported and 72 should behave identical. Both key=value and postgres:// URL-style connection 73 strings are supported. See the doc comments on the [Config struct] for the full 74 list and documentation. 75 76 The most notable difference is that you can use any [run-time parameter] such as 77 `search_path` or `work_mem` in the connection string. This is different from 78 libpq, which uses the `options` parameter for this (which also works in pq). 79 80 For example: 81 82 sql.Open("postgres", "dbname=pqgo work_mem=100kB search_path=xyz") 83 84 The libpq way (which also works in pq) is to use `options='-c k=v'` like so: 85 86 sql.Open("postgres", "dbname=pqgo options='-c work_mem=100kB -c search_path=xyz'") 87 88 [Config struct]: https://pkg.go.dev/github.com/lib/pq#Config 89 [run-time parameter]: http://www.postgresql.org/docs/current/static/runtime-config.html 90 91 Errors 92 ------ 93 Errors from PostgreSQL are returned as [pq.Error]; [pq.As] can be used to 94 convert an error to `pq.Error`: 95 96 ```go 97 pqErr := pq.As(err, pqerror.UniqueViolation) 98 if pqErr != nil { 99 return fmt.Errorf("email %q already exsts", email) 100 } 101 ``` 102 103 the Error() string contains the error message and code: 104 105 pq: duplicate key value violates unique constraint "users_lower_idx" (23505) 106 107 The ErrorWithDetail() string also contains the DETAIL and CONTEXT fields, if 108 present. For example for the above error this helpfully contains the duplicate 109 value: 110 111 ERROR: duplicate key value violates unique constraint "users_lower_idx" (23505) 112 DETAIL: Key (lower(email))=(a@example.com) already exists. 113 114 Or for an invalid syntax error like this: 115 116 pq: invalid input syntax for type json (22P02) 117 118 It contains the context where this error occurred: 119 120 ERROR: invalid input syntax for type json (22P02) 121 DETAIL: Token "asd" is invalid. 122 CONTEXT: line 5, column 8: 123 124 3 | 'def', 125 4 | 123, 126 5 | 'foo', 'asd'::jsonb 127 ^ 128 129 [pq.Error]: https://pkg.go.dev/github.com/lib/pq#Error 130 [pq.As]: https://pkg.go.dev/github.com/lib/pq#As 131 132 PostgreSQL features 133 ------------------- 134 135 ### Authentication 136 pq supports PASSWORD, MD5, and SCRAM-SHA256 authentication out of the box. If 137 you need GSS/Kerberos authentication you'll need to import the `auth/kerberos` 138 module: package: 139 140 import "github.com/lib/pq/auth/kerberos" 141 142 func init() { 143 pq.RegisterGSSProvider(func() (pq.Gss, error) { return kerberos.NewGSS() }) 144 } 145 146 This is in a separate module so that users who don't need Kerberos (i.e. most 147 users) don't have to add unnecessary dependencies. 148 149 Reading a [password file] (pgpass) is also supported. 150 151 [password file]: http://www.postgresql.org/docs/current/static/libpq-pgpass.html 152 153 ### Bulk imports with `COPY [..] FROM STDIN` 154 You can perform bulk imports by preparing a `COPY [..] FROM STDIN` statement 155 inside a transaction. The returned `sql.Stmt` can then be repeatedly executed to 156 copy data. After all data has been processed you should call Exec() once with no 157 arguments to flush all buffered data. 158 159 [Further documentation][copy-doc] and [example][copy-ex]. 160 161 [copy-doc]: https://pkg.go.dev/github.com/lib/pq#hdr-Bulk_imports 162 [copy-ex]: https://pkg.go.dev/github.com/lib/pq#example-package-CopyFromStdin 163 164 ### NOTICE errors 165 PostgreSQL has "NOTICE" errors for informational messages. For example from the 166 psql CLI: 167 168 pqgo=# drop table if exists doesnotexist; 169 NOTICE: table "doesnotexist" does not exist, skipping 170 DROP TABLE 171 172 These errors are not returned because they're not really errors but, well, 173 notices. 174 175 You can register a callback for these notices with [ConnectorWithNoticeHandler] 176 177 [ConnectorWithNoticeHandler]: https://pkg.go.dev/github.com/lib/pq#ConnectorWithNoticeHandler 178 179 ### Using `LISTEN`/`NOTIFY` 180 With [pq.Listener] notifications are send on a channel. For example: 181 182 ```go 183 l := pq.NewListener("dbname=pqgo", time.Second, time.Minute, nil) 184 defer l.Close() 185 186 err := l.Listen("coconut") 187 if err != nil { 188 log.Fatal(err) 189 } 190 191 for { 192 n := <-l.Notify: 193 if n == nil { 194 fmt.Println("nil notify: closing Listener") 195 return 196 } 197 fmt.Printf("notification on %q with data %q\n", n.Channel, n.Extra) 198 } 199 ``` 200 201 And you'll get a notification for every `notify coconut`. 202 203 See the API docs for a more complete example. 204 205 [pq.Listener]: https://pkg.go.dev/github.com/lib/pq#Listener 206 207 208 Caveats 209 ------- 210 ### LastInsertId 211 sql.Result.LastInsertId() is not supported, because the PostgreSQL protocol does 212 not have this facility. Use `insert [..] returning [cols]` instead: 213 214 db.QueryRow(`insert into tbl [..] returning id_col`).Scan(..) 215 // Or multiple rows: 216 db.Query(`insert into tbl (row1), (row2) returning id_col`) 217 218 This will also work in SQLite and MariaDB with the same syntax. MS-SQL and 219 Oracle have a similar facility (with a different syntax). 220 221 ### timestamps 222 For timestamps with a timezone (`timestamptz`/`timestamp with time zone`), pq 223 uses the timezone configured in the server, as libpq. You can change this with 224 `timestamp=[..]` in the connection string. It's generally recommended to use 225 UTC. 226 227 For timestamps without a timezone (`timestamp`/`timestamp without time zone`), 228 pq always uses `time.FixedZone("", 0)` as the timezone; the timestamp parameter 229 has no effect here. This is intentionally not equal to time.UTC, as it's not a 230 UTC time: it's a time without a timezone. Go's time package does not really 231 support this concept, so this is the best we can do This will print `+0000` 232 twice (e.g. `2026-03-15 17:45:47 +0000 +0000`; having a clearer name would have 233 been better, but is not compatible change). See [this comment][ts] for some 234 options on how to deal with this. 235 236 Also see the examples for [timestamptz] and [timestamp] 237 238 [ts]: https://github.com/lib/pq/issues/329#issuecomment-4025733506 239 [timestamptz]: https://pkg.go.dev/github.com/lib/pq#example-package-TimestampWithTimezone 240 [timestamp]: https://pkg.go.dev/github.com/lib/pq#example-package-TimestampWithoutTimezone 241 242 ### bytea with copy 243 All `[]byte` parameters are encoded as `bytea` when using `copy [..] from 244 stdin`, which may result in errors for e.g. `jsonb` columns. The solution is to 245 use a string instead of []byte. See #1023 246 247 Development 248 ----------- 249 ### Running tests 250 Tests need to be run against a PostgreSQL database; you can use Docker compose 251 to start one: 252 253 docker compose up -d 254 255 This starts the latest PostgreSQL; use `docker compose up -d pg«v»` to start a 256 different version. 257 258 In addition, your `/etc/hosts` needs an entry: 259 260 127.0.0.1 postgres postgres-invalid 261 262 Or you can use any other PostgreSQL instance; see 263 `testdata/postgres/docker-entrypoint-initdb.d` for the required setup. You can use 264 the standard `PG*` environment variables to control the connection details; it 265 uses the following defaults: 266 267 PGHOST=localhost 268 PGDATABASE=pqgo 269 PGUSER=pqgo 270 PGSSLMODE=disable 271 PGCONNECT_TIMEOUT=20 272 273 `PQTEST_BINARY_PARAMETERS` can be used to add `binary_parameters=yes` to all 274 connection strings: 275 276 PQTEST_BINARY_PARAMETERS=1 go test 277 278 Tests can be run against pgbouncer with: 279 280 docker compose up -d pgbouncer pg18 281 PGPORT=6432 go test ./... 282 283 and pgpool with: 284 285 docker compose up -d pgpool pg18 286 PGPORT=7432 go test ./... 287 288 ### Protocol debug output 289 You can use PQGO_DEBUG=1 to make the driver print the communication with 290 PostgreSQL to stderr; this works anywhere (test or applications) and can be 291 useful to debug protocol problems. 292 293 For example: 294 295 % PQGO_DEBUG=1 go test -run TestSimpleQuery 296 CLIENT → Startup 69 "\x00\x03\x00\x00database\x00pqgo\x00user [..]" 297 SERVER ← (R) AuthRequest 4 "\x00\x00\x00\x00" 298 SERVER ← (S) ParamStatus 19 "in_hot_standby\x00off\x00" 299 [..] 300 SERVER ← (Z) ReadyForQuery 1 "I" 301 START conn.query 302 START conn.simpleQuery 303 CLIENT → (Q) Query 9 "select 1\x00" 304 SERVER ← (T) RowDescription 29 "\x00\x01?column?\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x17\x00\x04\xff\xff\xff\xff\x00\x00" 305 SERVER ← (D) DataRow 7 "\x00\x01\x00\x00\x00\x011" 306 END conn.simpleQuery 307 END conn.query 308 SERVER ← (C) CommandComplete 9 "SELECT 1\x00" 309 SERVER ← (Z) ReadyForQuery 1 "I" 310 CLIENT → (X) Terminate 0 "" 311 PASS 312 ok github.com/lib/pq 0.010s