doc.go (4949B)
1 /* 2 Package pq is a Go PostgreSQL driver for database/sql. 3 4 Most clients will use the database/sql package instead of using this package 5 directly. For example: 6 7 import ( 8 "database/sql" 9 10 _ "github.com/lib/pq" 11 ) 12 13 func main() { 14 dsn := "user=pqgo dbname=pqgo sslmode=verify-full" 15 db, err := sql.Open("postgres", dsn) 16 if err != nil { 17 log.Fatal(err) 18 } 19 20 age := 21 21 rows, err := db.Query("select name from users where age = $1", age) 22 // … 23 } 24 25 You can also connect with an URL: 26 27 dsn := "postgres://pqgo:password@localhost/pqgo?sslmode=verify-full" 28 db, err := sql.Open("postgres", dsn) 29 30 # Connection String Parameters 31 32 See [NewConfig]. 33 34 # Queries 35 36 database/sql does not dictate any specific format for parameter placeholders, 37 and pq uses the PostgreSQL-native ordinal markers ($1, $2, etc.). The same 38 placeholder can be used more than once: 39 40 rows, err := db.Query( 41 `select * from users where name = $1 or age between $2 and $2 + 3`, 42 "Duck", 64) 43 44 pq does not support [sql.Result.LastInsertId]. Use the RETURNING clause with a 45 Query or QueryRow call instead to return the identifier: 46 47 row := db.QueryRow(`insert into users(name, age) values('Scrooge McDuck', 93) returning id`) 48 49 var userid int 50 err := row.Scan(&userid) 51 52 # Data Types 53 54 Parameters pass through [driver.DefaultParameterConverter] before they are handled 55 by this package. When the binary_parameters connection option is enabled, []byte 56 values are sent directly to the backend as data in binary format. 57 58 This package returns the following types for values from the PostgreSQL backend: 59 60 - integer types smallint, integer, and bigint are returned as int64 61 - floating-point types real and double precision are returned as float64 62 - character types char, varchar, and text are returned as string 63 - temporal types date, time, timetz, timestamp, and timestamptz are 64 returned as time.Time 65 - the boolean type is returned as bool 66 - the bytea type is returned as []byte 67 68 All other types are returned directly from the backend as []byte values in text format. 69 70 # Errors 71 72 pq may return errors of type [*pq.Error] which contain error details: 73 74 pqErr := new(pq.Error) 75 if errors.As(err, &pqErr) { 76 fmt.Println("pq error:", pqErr.Code.Name()) 77 } 78 79 # Bulk imports 80 81 You can perform bulk imports by preparing a "COPY [..] FROM STDIN" statement in 82 a transaction ([sql.Tx]). The returned [sql.Stmt] handle can then be repeatedly 83 "executed" to copy data into the target table. After all data has been processed 84 you should call Exec() once with no arguments to flush all buffered data. Any 85 call to Exec() might return an error which should be handled appropriately, but 86 because of the internal buffering an error returned by Exec() might not be 87 related to the data passed in the call that failed. 88 89 It is not possible to COPY outside of an explicit transaction in pq. 90 91 Use nil for NULL, or explicitly add WITH NULL 'SOME STRING' (the default of \N 92 doesn't work). 93 94 # Notifications 95 96 PostgreSQL supports a simple publish/subscribe model using PostgreSQL's [NOTIFY] mechanism. 97 98 To start listening for notifications, you first have to open a new connection to 99 the database by calling [NewListener]. This connection can not be used for 100 anything other than LISTEN / NOTIFY. Calling Listen will open a "notification 101 channel"; once a notification channel is open, a notification generated on that 102 channel will effect a send on the Listener.Notify channel. A notification 103 channel will remain open until Unlisten is called, though connection loss might 104 result in some notifications being lost. To solve this problem, Listener sends a 105 nil pointer over the Notify channel any time the connection is re-established 106 following a connection loss. The application can get information about the state 107 of the underlying connection by setting an event callback in the call to 108 NewListener. 109 110 A single [Listener] can safely be used from concurrent goroutines, which means 111 that there is often no need to create more than one Listener in your 112 application. However, a Listener is always connected to a single database, so 113 you will need to create a new Listener instance for every database you want to 114 receive notifications in. 115 116 The channel name in both Listen and Unlisten is case sensitive, and can contain 117 any characters legal in an [identifier]. Note that the channel name will be 118 truncated to 63 bytes by the PostgreSQL server. 119 120 # Kerberos Support 121 122 If you need support for Kerberos authentication, add the following to your main 123 package: 124 125 import "github.com/lib/pq/auth/kerberos" 126 127 func init() { 128 pq.RegisterGSSProvider(func() (pq.Gss, error) { return kerberos.NewGSS() }) 129 } 130 131 This package is in a separate module so that users who don't need Kerberos don't 132 have to add unnecessary dependencies. 133 134 [identifier]: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS 135 [NOTIFY]: http://www.postgresql.org/docs/current/static/sql-notify.html 136 */ 137 package pq