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 247 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/exp_error_stat
  7. github.com/CRVV/pgclimb/formats
  8. github.com/CRVV/pgclimb/pg
  9. github.com/CRVV/pgy
  10. github.com/Everlag/pgx/stdlib
  11. github.com/FidelityInternational/atc/atccmd
  12. github.com/FidelityInternational/atc/db
  13. github.com/FidelityInternational/atc/db/dbfakes
  14. github.com/FidelityInternational/atc/postgresrunner
  15. github.com/JackC/doublecheck
  16. github.com/JackC/go_database_sql_retry_bug
  17. github.com/JackC/go_db_bench
  18. github.com/SpirentOrion/telegraf/plugins/inputs/postgresql
  19. github.com/ably-forks/flynn/blobstore/backend
  20. github.com/ably-forks/flynn/blobstore/data
  21. github.com/ably-forks/flynn/controller
  22. github.com/ably-forks/flynn/controller/schema
  23. github.com/ably-forks/flynn/pkg/httphelper
  24. github.com/ably-forks/flynn/pkg/postgres
  25. github.com/ably-forks/flynn/pkg/testutils/postgres
  26. github.com/ably-forks/flynn/router
  27. github.com/ably-forks/flynn/router/schema
  28. github.com/achiku/gotodoit
  29. github.com/achiku/pgx/stdlib
  30. github.com/achiku/qg
  31. github.com/achiku/que-go
  32. github.com/achiku/sample-golang-pgx
  33. github.com/achiku/sample-golang-postgres-que
  34. github.com/achiku/webque
  35. github.com/acls/migrate/driver/pgx
  36. github.com/acls/migrate/testutil
  37. github.com/alaska/pgx/stdlib
  38. github.com/alicebob/schemaspy
  39. github.com/alicebob/verssion/core
  40. github.com/alternaDev/georenting-server/models
  41. github.com/apaxa-go/pgtypes
  42. github.com/apaxa-go/pgxh
  43. github.com/apaxa-io/pgxhelper
  44. github.com/apaxa-io/uuid
  45. github.com/apaxa-io/uuid/nulluuid
  46. github.com/archSeer/pgx/stdlib
  47. github.com/archsaber/pgx/stdlib
  48. github.com/arolek/pgx/stdlib
  49. github.com/asyou-me/paas-srv/pkg/models
  50. github.com/asyoume/paas_srv/pkg/models
  51. github.com/balanceit/accounting_service
  52. github.com/bbuck/futura/adapters
  53. github.com/bgentry/que-go
  54. github.com/bpina/go-tracker/data
  55. github.com/bpina/shortened/data
  56. github.com/bx2/pgx/examples/chat
  57. github.com/bx2/pgx/examples/todo
  58. github.com/bx2/pgx/examples/url_shortener
  59. github.com/bx2/pgx/stdlib
  60. github.com/cayleygraph/cayley/graph/sql/cockroach
  61. github.com/cgt212/cloudie
  62. github.com/chenaisheng/pgclusteradmin
  63. github.com/chonthu/flynn/controller
  64. github.com/chonthu/flynn/router
  65. github.com/chubais/pgx/stdlib
  66. github.com/codekoala/go-treksum
  67. github.com/codekoala/go-treksum/api
  68. github.com/codekoala/go-treksum/db
  69. github.com/concourse/atc/atccmd
  70. github.com/concourse/atc/db
  71. github.com/concourse/atc/db/dbfakes
  72. github.com/concourse/atc/postgresrunner
  73. github.com/crvv/pgclimb/formats
  74. github.com/crvv/pgclimb/pg
  75. github.com/crvv/pgy
  76. github.com/davidamitchell/accounting_service
  77. github.com/davidamitchell/benchmarks/go
  78. github.com/dbenson24/iris-pgx
  79. github.com/dbenson24/iris-pgx/example
  80. github.com/debackerl/pgasus
  81. github.com/debackerl/pgturtle
  82. github.com/debackerl/pgx/stdlib
  83. github.com/deoxxa/jishaku-web
  84. github.com/dictyBase/modware/commands/server
  85. github.com/dillonhafer/budgetal-graphql
  86. github.com/ds0nt/codeship-dind
  87. github.com/ezoic/kinesis-connectors
  88. github.com/flynn/flynn-cli-redirect
  89. github.com/flynn/flynn/appliance/postgresql
  90. github.com/flynn/flynn/blobstore/backend
  91. github.com/flynn/flynn/blobstore/data
  92. github.com/flynn/flynn/controller
  93. github.com/flynn/flynn/controller/schema
  94. github.com/flynn/flynn/pkg/httphelper
  95. github.com/flynn/flynn/pkg/postgres
  96. github.com/flynn/flynn/pkg/testutils/postgres
  97. github.com/flynn/flynn/router
  98. github.com/flynn/flynn/router/schema
  99. github.com/flynn/postgres-external-provider
  100. github.com/flynn/que-go
  101. github.com/gernest/apinovo
  102. github.com/go-on/pj/example
  103. github.com/go-spatial/tegola/provider/postgis
  104. github.com/google/cayley/graph/sql/cockroach
  105. github.com/gophergala2016/dbcom
  106. github.com/govau/cf-common/jobs
  107. github.com/grafov/dbcom
  108. github.com/grengojbo/ads/services
  109. github.com/grengojbo/adscore
  110. github.com/harikb/pgx/stdlib
  111. github.com/heroku-examples/go-queue-example
  112. github.com/heroku-examples/go_queue_example
  113. github.com/hlandau/degoutils/dbutil
  114. github.com/hlandau/degoutils/web/authn
  115. github.com/hlandau/degoutils/web/tpl
  116. github.com/homemade/jgforce
  117. github.com/homemade/jgforce/cmd/worker/justgiving
  118. github.com/homemade/jgforce/cmd/worker/salesforce
  119. github.com/ije/gox/database/pgx
  120. github.com/j1436go/pgx/examples/chat
  121. github.com/j1436go/pgx/examples/todo
  122. github.com/j1436go/pgx/examples/url_shortener
  123. github.com/j1436go/pgx/stdlib
  124. github.com/jackc/doublecheck
  125. github.com/jackc/go_database_sql_retry_bug
  126. github.com/jackc/go_db_bench
  127. github.com/jackc/pgx/log/log15adapter
  128. github.com/jackc/pgx/log/logrusadapter
  129. github.com/jackc/pgx/log/testingadapter
  130. github.com/jackc/pgx/log/zapadapter
  131. github.com/jackc/pgx/log/zerologadapter
  132. github.com/jackc/pgx/pgtype/testutil
  133. github.com/jackc/pgx/stdlib
  134. github.com/jackc/pgx_notify_lost_connection
  135. github.com/jackc/pgxdata
  136. github.com/jackc/tern
  137. github.com/jackc/tern-deb
  138. github.com/jackc/tern-deb/migrate
  139. github.com/jackc/tern/migrate
  140. github.com/jackc/tpr/backend
  141. github.com/jackc/tpr/backend/data
  142. github.com/jadekler/atc/atccmd
  143. github.com/jadekler/atc/db
  144. github.com/jadekler/atc/postgresrunner
  145. github.com/janmentzel/que-go
  146. github.com/jbranchaud/dat
  147. github.com/jigish/pgx/stdlib
  148. github.com/johngibb/migrate/db
  149. github.com/jonasi/pg
  150. github.com/jonasi/pgx/stdlib
  151. github.com/jsimnz/flynn/appliance/postgresql
  152. github.com/jsimnz/flynn/blobstore/backend
  153. github.com/jsimnz/flynn/blobstore/data
  154. github.com/jsimnz/flynn/controller
  155. github.com/jsimnz/flynn/controller/schema
  156. github.com/jsimnz/flynn/pkg/httphelper
  157. github.com/jsimnz/flynn/pkg/postgres
  158. github.com/jsimnz/flynn/pkg/testutils/postgres
  159. github.com/jsimnz/flynn/router
  160. github.com/jsimnz/flynn/router/schema
  161. github.com/katzenpost/server/internal/sqldb
  162. github.com/kazarus/GoDemo/example00045
  163. github.com/kinghrothgar/shortened/data
  164. github.com/knq/usql/drivers/pgx
  165. github.com/konjoot/benches/kami
  166. github.com/kyleconroy/pgoutput
  167. github.com/kyleconroy/pgoutput/examples
  168. github.com/liu-rui/FrameworkBenchmarks/frameworks/Go/fasthttp-postgresql
  169. github.com/maknahar/heroku-demonstrator
  170. github.com/manniwood/iidy
  171. github.com/meowgorithm/pgx/examples/chat
  172. github.com/meowgorithm/pgx/examples/todo
  173. github.com/meowgorithm/pgx/examples/url_shortener
  174. github.com/meowgorithm/pgx/stdlib
  175. github.com/mjibson/hots.dog
  176. github.com/mmb/atc/atccmd
  177. github.com/mmb/atc/db
  178. github.com/mmb/atc/db/dbfakes
  179. github.com/mmb/atc/postgresrunner
  180. github.com/mohanarpit/flynn/appliance/postgresql
  181. github.com/mohanarpit/flynn/blobstore/backend
  182. github.com/mohanarpit/flynn/blobstore/data
  183. github.com/mohanarpit/flynn/controller
  184. github.com/mohanarpit/flynn/controller/schema
  185. github.com/mohanarpit/flynn/pkg/httphelper
  186. github.com/mohanarpit/flynn/pkg/postgres
  187. github.com/mohanarpit/flynn/pkg/testutils/postgres
  188. github.com/mohanarpit/flynn/router
  189. github.com/mostafah/pgx/examples/chat
  190. github.com/mostafah/pgx/examples/todo
  191. github.com/mostafah/pgx/examples/url_shortener
  192. github.com/mostafah/pgx/stdlib
  193. github.com/motki/core/db
  194. github.com/motki/core/model
  195. github.com/nerfmiester/postgres-chat
  196. github.com/nutmegdevelopment/nutcracker/postgres
  197. github.com/ociule/flynn/appliance/postgresql
  198. github.com/ociule/flynn/blobstore/backend
  199. github.com/ociule/flynn/blobstore/data
  200. github.com/ociule/flynn/controller
  201. github.com/ociule/flynn/controller/schema
  202. github.com/ociule/flynn/pkg/httphelper
  203. github.com/ociule/flynn/pkg/postgres
  204. github.com/ociule/flynn/pkg/testutils/postgres
  205. github.com/ociule/flynn/router
  206. github.com/rsto/pgx/stdlib
  207. github.com/russellchadwick/messagebroker
  208. github.com/sctlee/hazel
  209. github.com/sctlee/hazel/db
  210. github.com/sctlee/tcpx
  211. github.com/sctlee/tcpx/db
  212. github.com/sent-hil/postbites
  213. github.com/shopsmart/pgx/stdlib
  214. github.com/siavashs/flynn/appliance/postgresql
  215. github.com/siavashs/flynn/blobstore/backend
  216. github.com/siavashs/flynn/blobstore/data
  217. github.com/siavashs/flynn/controller
  218. github.com/siavashs/flynn/controller/schema
  219. github.com/siavashs/flynn/pkg/httphelper
  220. github.com/siavashs/flynn/pkg/postgres
  221. github.com/siavashs/flynn/router
  222. github.com/siavashs/flynn/router/schema
  223. github.com/silviucm/pgx/examples/chat
  224. github.com/silviucm/pgx/examples/todo
  225. github.com/silviucm/pgx/examples/url_shortener
  226. github.com/silviucm/pgx/stdlib
  227. github.com/stephane-martin/w3c-extendedlog-parser/cli/cmd
  228. github.com/terranodo/tegola/provider/postgis
  229. github.com/theSuess/openspotmap
  230. github.com/theSuess/openspotmap/api/v0
  231. github.com/tvmaly/go-json-rest-postgresql-example
  232. github.com/utrack/pgx/stdlib
  233. github.com/vycb/gotol/QueryPq
  234. github.com/wal-g/wal-g
  235. github.com/wdamron/pgx/stdlib
  236. github.com/wttw/pgoutput
  237. github.com/wttw/pgoutput/examples
  238. github.com/xo/usql/drivers/pgx
  239. github.com/yml/degoutils/dbutil
  240. github.com/yml/degoutils/web/authn
  241. github.com/yml/degoutils/web/tpl
  242. github.com/zamotivator/pgx/examples/chat
  243. github.com/zamotivator/pgx/examples/todo
  244. github.com/zamotivator/pgx/examples/url_shortener
  245. github.com/zamotivator/pgx/stdlib
  246. github.com/zouchao2010/FrameworkBenchmarks/frameworks/Go/fasthttp-postgresql
  247. gopkg.in/jackc/pgx.v2/stdlib

Imported only in test by 41 package(s)

  1. github.com/ARolek/pgx
  2. github.com/CRVV/pgx
  3. github.com/Everlag/pgx
  4. github.com/Ligustah/pgx
  5. github.com/ably-forks/flynn/blobstore
  6. github.com/achiku/pgx
  7. github.com/alaska/pgx
  8. github.com/archSeer/pgx
  9. github.com/archsaber/pgx
  10. github.com/arolek/pgx
  11. github.com/bigdrum/pgx
  12. github.com/bx2/pgx
  13. github.com/chonthu/flynn/blobstore
  14. github.com/chubais/pgx
  15. github.com/debackerl/pgx
  16. github.com/flynn/flynn/blobstore
  17. github.com/harikb/pgx
  18. github.com/j1436go/pgx
  19. github.com/jackc/pgx/pgtype
  20. github.com/jackc/pgxtype
  21. github.com/jigish/pgx
  22. github.com/johngibb/migrate
  23. github.com/jonasi/pgx
  24. github.com/jsimnz/flynn/blobstore
  25. github.com/ligustah/pgx
  26. github.com/lmars/flynn-webhook-deploy
  27. github.com/mattyr/flynn-pgbackups
  28. github.com/meowgorithm/pgx
  29. github.com/mohanarpit/flynn/blobstore
  30. github.com/mostafah/pgx
  31. github.com/ociule/flynn/blobstore
  32. github.com/rsto/pgx
  33. github.com/segmentio/pgx
  34. github.com/segmentio/pgx/stdlib
  35. github.com/shopsmart/pgx
  36. github.com/siavashs/flynn/blobstore
  37. github.com/silviucm/pgx
  38. github.com/utrack/pgx
  39. github.com/wdamron/pgx
  40. github.com/zamotivator/pgx
  41. 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