top

Package pgx is a PostgreSQL database driver.

pgx provides lower level access to PostgreSQL than the standard database/sql It remains as similar to the database/sql interface as possible while providing better speed and access to PostgreSQL specific features. Import github.com/jack/pgx/stdlib to use pgx as a database/sql compatible driver.

Query Interface

pgx implements Query and Scan in the familiar database/sql style.

var sum int32

// Send the query to the server. The returned rows MUST be closed
// before conn can be used again.
rows, err := conn.Query("select generate_series(1,$1)", 10)
if err != nil {
    return err
}

// rows.Close is called by rows.Next when all rows are read
// or an error occurs in Next or Scan. So it may optionally be
// omitted if nothing in the rows.Next loop can panic. It is
// safe to close rows multiple times.
defer rows.Close()

// Iterate through the result set
for rows.Next() {
    var n int32
    err = rows.Scan(&n)
    if err != nil {
        return err
    }
    sum += n
}

// Any errors encountered by rows.Next or rows.Scan will be returned here
if rows.Err() != nil {
    return err
}

// No errors found - do something with sum

pgx also implements QueryRow in the same style as database/sql.

var name string
var weight int64
err := conn.QueryRow("select name, weight from widgets where id=$1", 42).Scan(&name, &weight)
if err != nil {
    return err
}

Use Exec to execute a query that does not return a result set.

commandTag, err := conn.Exec("delete from widgets where id=$1", 42)
if err != nil {
    return err
}
if commandTag.RowsAffected() != 1 {
    return errors.New("No row found to delete")
}

Connection Pool

Connection pool usage is explicit and configurable. In pgx, a connection can be created and managed directly, or a connection pool with a configurable maximum connections can be used. Also, the connection pool offers an after connect hook that allows every connection to be automatically setup before being made available in the connection pool. This is especially useful to ensure all connections have the same prepared statements available or to change any other connection settings.

It delegates Query, QueryRow, Exec, and Begin functions to an automatically checked out and released connection so you can avoid manually acquiring and releasing connections when you do not need that level of control.

var name string
var weight int64
err := pool.QueryRow("select name, weight from widgets where id=$1", 42).Scan(&name, &weight)
if err != nil {
    return err
}

Transactions

Transactions are started by calling Begin or BeginIso. The BeginIso variant creates a transaction with a specified isolation level.

tx, err := conn.Begin()
if err != nil {
    return err
}
// Rollback is safe to call even if the tx is already closed, so if
// the tx commits successfully, this is a no-op
defer tx.Rollback()

_, err = tx.Exec("insert into foo(id) values (1)")
if err != nil {
    return err
}

err = tx.Commit()
if err != nil {
    return err
}

Copy Protocol

Use CopyTo to efficiently insert multiple rows at a time using the PostgreSQL copy protocol. CopyTo accepts a CopyToSource interface. If the data is already in a [][]interface{} use CopyToRows to wrap it in a CopyToSource interface. Or implement CopyToSource to avoid buffering the entire data set in memory.

rows := [][]interface{}{
    {"John", "Smith", int32(36)},
    {"Jane", "Doe", int32(29)},
}

copyCount, err := conn.CopyTo(
    "people",
    []string{"first_name", "last_name", "age"},
    pgx.CopyToRows(rows),
)

CopyTo can be faster than an insert with as few as 5 rows.

Listen and Notify

pgx can listen to the PostgreSQL notification system with the WaitForNotification function. It takes a maximum time to wait for a notification.

err := conn.Listen("channelname")
if err != nil {
    return nil
}

if notification, err := conn.WaitForNotification(time.Second); err != nil {
    // do something with notification
}

Null Mapping

pgx can map nulls in two ways. The first is Null* types that have a data field and a valid field. They work in a similar fashion to database/sql. The second is to use a pointer to a pointer.

var foo pgx.NullString
var bar *string
err := conn.QueryRow("select foo, bar from widgets where id=$1", 42).Scan(&a, &b)
if err != nil {
    return err
}

Array Mapping

pgx maps between int16, int32, int64, float32, float64, and string Go slices and the equivalent PostgreSQL array type. Go slices of native types do not support nulls, so if a PostgreSQL array that contains a null is read into a native Go slice an error will occur.

Hstore Mapping

pgx includes an Hstore type and a NullHstore type. Hstore is simply a map[string]string and is preferred when the hstore contains no nulls. NullHstore follows the Null* pattern and supports null values.

JSON and JSONB Mapping

pgx includes built-in support to marshal and unmarshal between Go types and the PostgreSQL JSON and JSONB.

Inet and Cidr Mapping

pgx encodes from net.IPNet to and from inet and cidr PostgreSQL types. In addition, as a convenience pgx will encode from a net.IP; it will assume a /32 netmask for IPv4 and a /128 for IPv6.

Custom Type Support

pgx includes support for the common data types like integers, floats, strings, dates, and times that have direct mappings between Go and SQL. Support can be added for additional types like point, hstore, numeric, etc. that do not have direct mappings in Go by the types implementing Scanner and Encoder.

Custom types can support text or binary formats. Binary format can provide a large performance increase. The natural place for deciding the format for a value would be in Scanner as it is responsible for decoding the returned data. However, that is impossible as the query has already been sent by the time the Scanner is invoked. The solution to this is the global DefaultTypeFormats. If a custom type prefers binary format it should register it there.

pgx.DefaultTypeFormats["point"] = pgx.BinaryFormatCode

Note that the type is referred to by name, not by OID. This is because custom PostgreSQL types like hstore will have different OIDs on different servers. When pgx establishes a connection it queries the pg_type table for all types. It then matches the names in DefaultTypeFormats with the returned OIDs and stores it in Conn.PgTypes.

See example_custom_type_test.go for an example of a custom type for the PostgreSQL point type.

pgx also includes support for custom types implementing the database/sql.Scanner and database/sql/driver.Valuer interfaces.

Raw Bytes Mapping

[]byte passed as arguments to Query, QueryRow, and Exec are passed unmodified to PostgreSQL. In like manner, a *[]byte passed to Scan will be filled with the raw bytes returned by PostgreSQL. This can be especially useful for reading varchar, text, json, and jsonb values directly into a []byte and avoiding the type conversion from string.

TLS

The pgx ConnConfig struct has a TLSConfig field. If this field is nil, then TLS will be disabled. If it is present, then it will be used to configure the TLS connection. This allows total configuration of the TLS connection.

Logging

pgx defines a simple logger interface. Connections optionally accept a logger that satisfies this interface. The log15 package (http://gopkg.in/inconshreveable/log15.v2) satisfies this interface and it is simple to define adapters for other loggers. Set LogLevel to control logging verbosity.

Imported by 179 package(s)

  1. bitbucket.org/jatone/genieql/internal/postgresql/internal
  2. github.com/ARolek/pgx/examples/chat
  3. github.com/ARolek/pgx/examples/todo
  4. github.com/ARolek/pgx/examples/url_shortener
  5. github.com/ARolek/pgx/stdlib
  6. github.com/CRVV/pgclimb/formats
  7. github.com/CRVV/pgclimb/pg
  8. github.com/CRVV/pgy
  9. github.com/Everlag/pgx/stdlib
  10. github.com/FidelityInternational/atc/atccmd
  11. github.com/FidelityInternational/atc/db
  12. github.com/FidelityInternational/atc/postgresrunner
  13. github.com/JackC/doublecheck
  14. github.com/JackC/go_database_sql_retry_bug
  15. github.com/JackC/go_db_bench
  16. github.com/achiku/pgx/stdlib
  17. github.com/achiku/qg
  18. github.com/achiku/que-go
  19. github.com/achiku/sample-golang-pgx
  20. github.com/achiku/sample-golang-postgres-que
  21. github.com/achiku/webque
  22. github.com/alaska/pgx/stdlib
  23. github.com/alternaDev/georenting-server/models
  24. github.com/apaxa-io/pgxhelper
  25. github.com/apaxa-io/uuid
  26. github.com/apaxa-io/uuid/nulluuid
  27. github.com/archSeer/pgx/stdlib
  28. github.com/arolek/pgx/stdlib
  29. github.com/asyoume/paas_srv/pkg/models
  30. github.com/balanceit/accounting_service
  31. github.com/bbuck/futura/adapters
  32. github.com/bgentry/que-go
  33. github.com/bpina/go-tracker/data
  34. github.com/bpina/shortened/data
  35. github.com/bx2/pgx/examples/chat
  36. github.com/bx2/pgx/examples/todo
  37. github.com/bx2/pgx/examples/url_shortener
  38. github.com/bx2/pgx/stdlib
  39. github.com/cgt212/cloudie
  40. github.com/chubais/pgx/stdlib
  41. github.com/concourse/atc/atccmd
  42. github.com/concourse/atc/db
  43. github.com/concourse/atc/db/dbfakes
  44. github.com/concourse/atc/postgresrunner
  45. github.com/crvv/pgclimb/formats
  46. github.com/crvv/pgclimb/pg
  47. github.com/crvv/pgy
  48. github.com/davidamitchell/accounting_service
  49. github.com/davidamitchell/benchmarks/go
  50. github.com/dbenson24/iris-pgx
  51. github.com/dbenson24/iris-pgx/example
  52. github.com/debackerl/pgasus
  53. github.com/debackerl/pgturtle
  54. github.com/debackerl/pgx/stdlib
  55. github.com/deoxxa/jishaku-web
  56. github.com/ezoic/kinesis-connectors
  57. github.com/flynn/flynn-cli-redirect
  58. github.com/flynn/flynn/appliance/postgresql
  59. github.com/flynn/flynn/blobstore/backend
  60. github.com/flynn/flynn/blobstore/data
  61. github.com/flynn/flynn/controller
  62. github.com/flynn/flynn/controller/schema
  63. github.com/flynn/flynn/pkg/httphelper
  64. github.com/flynn/flynn/pkg/postgres
  65. github.com/flynn/flynn/pkg/testutils/postgres
  66. github.com/flynn/flynn/router
  67. github.com/flynn/flynn/router/schema
  68. github.com/flynn/postgres-external-provider
  69. github.com/flynn/que-go
  70. github.com/gernest/apinovo
  71. github.com/go-on/pj/example
  72. github.com/gophergala2016/dbcom
  73. github.com/grafov/dbcom
  74. github.com/grengojbo/ads/services
  75. github.com/grengojbo/adscore
  76. github.com/harikb/pgx/stdlib
  77. github.com/heroku-examples/go-queue-example
  78. github.com/heroku-examples/go_queue_example
  79. github.com/hlandau/degoutils/dbutil
  80. github.com/hlandau/degoutils/web/authn
  81. github.com/hlandau/degoutils/web/tpl
  82. github.com/homemade/jgforce
  83. github.com/homemade/jgforce/cmd/worker/justgiving
  84. github.com/homemade/jgforce/cmd/worker/salesforce
  85. github.com/ije/gox/database/pgx
  86. github.com/j1436go/pgx/examples/chat
  87. github.com/j1436go/pgx/examples/todo
  88. github.com/j1436go/pgx/examples/url_shortener
  89. github.com/j1436go/pgx/stdlib
  90. github.com/jackc/doublecheck
  91. github.com/jackc/go_database_sql_retry_bug
  92. github.com/jackc/go_db_bench
  93. github.com/jackc/pgx/stdlib
  94. github.com/jackc/pgx_notify_lost_connection
  95. github.com/jackc/pgxdata
  96. github.com/jackc/tern
  97. github.com/jackc/tern-deb
  98. github.com/jackc/tern-deb/migrate
  99. github.com/jackc/tern/migrate
  100. github.com/jackc/tpr/backend
  101. github.com/jackc/tpr/backend/data
  102. github.com/janmentzel/que-go
  103. github.com/jbranchaud/dat
  104. github.com/jigish/pgx/stdlib
  105. github.com/jonasi/pg
  106. github.com/jonasi/pgx/stdlib
  107. github.com/jsimnz/flynn/appliance/postgresql
  108. github.com/jsimnz/flynn/blobstore/backend
  109. github.com/jsimnz/flynn/blobstore/data
  110. github.com/jsimnz/flynn/controller
  111. github.com/jsimnz/flynn/pkg/httphelper
  112. github.com/jsimnz/flynn/pkg/postgres
  113. github.com/jsimnz/flynn/router
  114. github.com/jsimnz/flynn/router/schema
  115. github.com/kinghrothgar/shortened/data
  116. github.com/konjoot/benches/kami
  117. github.com/liu-rui/FrameworkBenchmarks/frameworks/Go/fasthttp-postgresql
  118. github.com/meowgorithm/pgx/stdlib
  119. github.com/mmb/atc/atccmd
  120. github.com/mmb/atc/db
  121. github.com/mmb/atc/db/dbfakes
  122. github.com/mmb/atc/postgresrunner
  123. github.com/mohanarpit/flynn/appliance/postgresql
  124. github.com/mohanarpit/flynn/blobstore/backend
  125. github.com/mohanarpit/flynn/blobstore/data
  126. github.com/mohanarpit/flynn/controller
  127. github.com/mohanarpit/flynn/controller/schema
  128. github.com/mohanarpit/flynn/pkg/httphelper
  129. github.com/mohanarpit/flynn/pkg/postgres
  130. github.com/mohanarpit/flynn/router
  131. github.com/mostafah/pgx/examples/chat
  132. github.com/mostafah/pgx/examples/todo
  133. github.com/mostafah/pgx/examples/url_shortener
  134. github.com/mostafah/pgx/stdlib
  135. github.com/nerfmiester/postgres-chat
  136. github.com/nutmegdevelopment/nutcracker/postgres
  137. github.com/ociule/flynn/appliance/postgresql
  138. github.com/ociule/flynn/blobstore/backend
  139. github.com/ociule/flynn/blobstore/data
  140. github.com/ociule/flynn/controller
  141. github.com/ociule/flynn/controller/schema
  142. github.com/ociule/flynn/pkg/httphelper
  143. github.com/ociule/flynn/pkg/postgres
  144. github.com/ociule/flynn/pkg/testutils/postgres
  145. github.com/ociule/flynn/router
  146. github.com/rsto/pgx/stdlib
  147. github.com/russellchadwick/messagebroker
  148. github.com/sctlee/hazel
  149. github.com/sctlee/hazel/db
  150. github.com/sctlee/tcpx
  151. github.com/sctlee/tcpx/db
  152. github.com/shopsmart/pgx/stdlib
  153. github.com/siavashs/flynn/appliance/postgresql
  154. github.com/siavashs/flynn/blobstore/backend
  155. github.com/siavashs/flynn/blobstore/data
  156. github.com/siavashs/flynn/controller
  157. github.com/siavashs/flynn/controller/schema
  158. github.com/siavashs/flynn/pkg/httphelper
  159. github.com/siavashs/flynn/pkg/postgres
  160. github.com/siavashs/flynn/router
  161. github.com/siavashs/flynn/router/schema
  162. github.com/silviucm/pgx/examples/chat
  163. github.com/silviucm/pgx/examples/todo
  164. github.com/silviucm/pgx/examples/url_shortener
  165. github.com/silviucm/pgx/stdlib
  166. github.com/theSuess/openspotmap
  167. github.com/tvmaly/go-json-rest-postgresql-example
  168. github.com/utrack/pgx/stdlib
  169. github.com/vycb/gotol/QueryPq
  170. github.com/wdamron/pgx/stdlib
  171. github.com/yml/degoutils/dbutil
  172. github.com/yml/degoutils/web/authn
  173. github.com/yml/degoutils/web/tpl
  174. github.com/zamotivator/pgx/examples/chat
  175. github.com/zamotivator/pgx/examples/todo
  176. github.com/zamotivator/pgx/examples/url_shortener
  177. github.com/zamotivator/pgx/stdlib
  178. github.com/zouchao2010/FrameworkBenchmarks/frameworks/Go/fasthttp-postgresql
  179. gopkg.in/jackc/pgx.v2/stdlib

Imported only in test by 29 package(s)

  1. github.com/ARolek/pgx
  2. github.com/Everlag/pgx
  3. github.com/achiku/pgx
  4. github.com/alaska/pgx
  5. github.com/archSeer/pgx
  6. github.com/arolek/pgx
  7. github.com/bx2/pgx
  8. github.com/chubais/pgx
  9. github.com/debackerl/pgx
  10. github.com/flynn/flynn/blobstore
  11. github.com/harikb/pgx
  12. github.com/j1436go/pgx
  13. github.com/jigish/pgx
  14. github.com/jonasi/pgx
  15. github.com/jsimnz/flynn/blobstore
  16. github.com/lmars/flynn-webhook-deploy
  17. github.com/mattyr/flynn-pgbackups
  18. github.com/meowgorithm/pgx
  19. github.com/mohanarpit/flynn/blobstore
  20. github.com/mostafah/pgx
  21. github.com/ociule/flynn/blobstore
  22. github.com/rsto/pgx
  23. github.com/shopsmart/pgx
  24. github.com/siavashs/flynn/blobstore
  25. github.com/silviucm/pgx
  26. github.com/utrack/pgx
  27. github.com/wdamron/pgx
  28. github.com/zamotivator/pgx
  29. gopkg.in/jackc/pgx.v2

Test imports 3 package(s)

  1. github.com/jackc/fake
  2. gopkg.in/inconshreveable/log15.v2
  3. github.com/shopspring/decimal