### Setup Test Database

Source: https://github.com/jackc/pgx/blob/master/CONTRIBUTING.md

Create the database and run the setup SQL script.

```bash
createdb
psql --no-psqlrc -f testsetup/postgresql_setup.sql
```

--------------------------------

### Install pgfortune

Source: https://github.com/jackc/pgx/blob/master/pgproto3/example/pgfortune/README.md

Use the go get command to install the pgfortune package.

```bash
go get -u github.com/jackc/pgproto3/example/pgfortune
```

--------------------------------

### Full Example Execution Session

Source: https://github.com/jackc/pgx/blob/master/examples/todo/README.md

A complete walkthrough of setting up, building, and interacting with the todo application.

```bash
jack@hk-47~/dev/go/src/github.com/jackc/pgx/examples/todo$ createdb todo
jack@hk-47~/dev/go/src/github.com/jackc/pgx/examples/todo$ psql todo < structure.sql
Expanded display is used automatically.
Timing is on.
CREATE TABLE
Time: 6.363 ms
jack@hk-47~/dev/go/src/github.com/jackc/pgx/examples/todo$ go build
jack@hk-47~/dev/go/src/github.com/jackc/pgx/examples/todo$ export PGDATABASE=todo
jack@hk-47~/dev/go/src/github.com/jackc/pgx/examples/todo$ ./todo list
jack@hk-47~/dev/go/src/github.com/jackc/pgx/examples/todo$ ./todo add 'Learn Go'
jack@hk-47~/dev/go/src/github.com/jackc/pgx/examples/todo$ ./todo list
1. Learn Go
jack@hk-47~/dev/go/src/github.com/jackc/pgx/examples/todo$ ./todo update 1 'Learn more Go'
jack@hk-47~/dev/go/src/github.com/jackc/pgx/examples/todo$ ./todo list
1. Learn more Go
jack@hk-47~/dev/go/src/github.com/jackc/pgx/examples/todo$ ./todo remove 1
jack@hk-47~/dev/go/src/github.com/jackc/pgx/examples/todo$ ./todo list
```

--------------------------------

### Complete PostgreSQL Connection String Example

Source: https://github.com/jackc/pgx/blob/master/_autodocs/configuration.md

A comprehensive example combining multiple connection parameters including SSL, application name, search path, and statement cache.

```url
postgresql://username:password@db.example.com:5432/dbname?sslmode=require&application_name=myapp&search_path=myschema&statement_cache_capacity=256
```

--------------------------------

### Run pgfortune

Source: https://github.com/jackc/pgx/blob/master/pgproto3/example/pgfortune/README.md

Start the mock server using the default configuration.

```bash
$ pgfortune
```

--------------------------------

### Start a Transaction

Source: https://github.com/jackc/pgx/blob/master/_autodocs/conn.md

Use Begin to start a new transaction with default options. Remember to defer Rollback and commit the transaction when done.

```go
func (c *Conn) Begin(ctx context.Context) (Tx, error)
```

```go
tx, err := conn.Begin(context.Background())
if err != nil {
    return err
}
deferr tx.Rollback(context.Background())

_, err = tx.Exec(context.Background(), "insert into users(name) values($1)", "Alice")
if err != nil {
    return err
}

return tx.Commit(context.Background())
```

--------------------------------

### Start a Transaction with Options

Source: https://github.com/jackc/pgx/blob/master/_autodocs/conn.md

Use BeginTx to start a transaction with specific isolation levels and access modes. This provides more control over transaction behavior.

```go
func (c *Conn) BeginTx(ctx context.Context, txOptions TxOptions) (Tx, error)
```

```go
tx, err := conn.BeginTx(context.Background(), pgx.TxOptions{
    IsoLevel:   pgx.Serializable,
    AccessMode: pgx.ReadOnly,
})
```

--------------------------------

### Connect and query with database/sql

Source: https://github.com/jackc/pgx/wiki/Getting-started-with-pgx-through-database-sql

Example implementation using the pgx stdlib driver to connect to a database and execute a query.

```go
package main

import (
	"database/sql"
	"fmt"
	"os"

	_ "github.com/jackc/pgx/v5/stdlib"
)

func main() {
	db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
		os.Exit(1)
	}
	defer db.Close()

	var greeting string
	err = db.QueryRow("select 'Hello, world!'").Scan(&greeting)
	if err != nil {
		fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
		os.Exit(1)
	}

	fmt.Println(greeting)
}
```

--------------------------------

### Start a New Transaction with Default Options

Source: https://github.com/jackc/pgx/blob/master/_autodocs/transactions.md

Use Conn.Begin to start a new transaction with default settings (ReadCommitted, ReadWrite, NotDeferrable). Ensure to rollback or commit the transaction.

```go
tx, err := conn.Begin(context.Background())
if err != nil {
    return err
}
deferr tx.Rollback(context.Background())

// Execute queries on tx...

return tx.Commit(context.Background())
```

--------------------------------

### Start a Transaction with Custom Options

Source: https://github.com/jackc/pgx/blob/master/_autodocs/transactions.md

Use Conn.BeginTx to start a transaction with specific isolation level, access mode, and deferrable mode. Remember to handle rollback or commit.

```go
tx, err := conn.BeginTx(context.Background(), pgx.TxOptions{
    IsoLevel:   pgx.Serializable,
    AccessMode: pgx.ReadOnly,
})
if err != nil {
    return err
}
deferr tx.Rollback(context.Background())

// Query operations...

return tx.Commit(context.Background())
```

--------------------------------

### Conn.Begin

Source: https://github.com/jackc/pgx/blob/master/_autodocs/transactions.md

Starts a new transaction with default options (ReadCommitted, ReadWrite, NotDeferrable).

```APIDOC
## Conn.Begin

### Description
Starts a new transaction with default options (ReadCommitted, ReadWrite, NotDeferrable).

### Method
func (c *Conn) Begin(ctx context.Context) (Tx, error)

### Parameters
#### Path Parameters
- **ctx** (context.Context) - Required - Context (only affects BEGIN command)

### Response
#### Success Response
- **Tx** - The transaction object.
- **error** - Error if transaction cannot be started.

### Request Example
```go
tx, err := conn.Begin(context.Background())
if err != nil {
    return err
}
deferr tx.Rollback(context.Background())

// Execute queries on tx...

return tx.Commit(context.Background())
```
```

--------------------------------

### Start PostgreSQL Cluster

Source: https://github.com/jackc/pgx/blob/master/CONTRIBUTING.md

Launch the PostgreSQL server instance using the initialized data directory.

```bash
postgres -D .testdb/$POSTGRESQL_DATA_DIR
```

--------------------------------

### Nested Transactions with Savepoints Example

Source: https://github.com/jackc/pgx/blob/master/_autodocs/transactions.md

This example demonstrates nested transactions using savepoints. An outer transaction is started, and then an inner transaction (savepoint) is attempted. If the inner operation fails, only the savepoint is rolled back, allowing the outer transaction to continue with an alternative approach. If successful, the savepoint is committed.

```go
tx, err := conn.Begin(context.Background())
if err != nil {
    return err
}
defer tx.Rollback(context.Background())

// Outer transaction work
_, err = tx.Exec(context.Background(), "insert into log(msg) values($1)", "Start")
if err != nil {
    return err
}

// Try risky operation in savepoint
innerTx, err := tx.Begin(context.Background())
if err != nil {
    return err
}

err = riskyOperation(innerTx)
if err != nil {
    // Only rolls back savepoint, outer transaction continues
    innerTx.Rollback(context.Background())
    
    // Try alternative approach
    _, err = tx.Exec(context.Background(), "insert into log(msg) values($1)", "Fallback")
    if err != nil {
        return err
    }
} else {
    err = innerTx.Commit(context.Background())
    if err != nil {
        return err
    }
}

_, err = tx.Exec(context.Background(), "insert into log(msg) values($1)", "Complete")
if err != nil {
    return err
}

return tx.Commit(context.Background())
```

--------------------------------

### CopyFromRows Example

Source: https://github.com/jackc/pgx/blob/master/_autodocs/copy_and_bulk.md

Creates a CopyFromSource from a slice of rows. Requires all data to be in memory.

```go
data := [][]any{
    {1, "Alice"},
    {2, "Bob"},
    {3, "Charlie"},
}

count, err := conn.CopyFrom(ctx, pgx.Identifier{"users"}, []string{"id", "name"}, pgx.CopyFromRows(data))
```

--------------------------------

### Setup Test Database and Extensions

Source: https://github.com/jackc/pgx/blob/master/CONTRIBUTING.md

Run these commands to create a test database, enable necessary PostgreSQL extensions (hstore, ltree), and define a custom domain (uint64). Ensure your PGX_TEST_DATABASE environment variable points to this database.

```bash
export PGDATABASE=pgx_test
createdb
psql -c 'create extension hstore;'
psql -c 'create extension ltree;'
psql -c 'create domain uint64 as numeric(20,0);'
```

--------------------------------

### Start and Manage Nested Transactions

Source: https://github.com/jackc/pgx/blob/master/_autodocs/transactions.md

Illustrates starting a pseudo-nested transaction using savepoints. If an operation within the inner transaction fails, only the inner work is rolled back, allowing the outer transaction to continue.

```go
tx, _ := conn.Begin(ctx)
defer tx.Rollback(ctx)

// Do outer work...

// Start nested transaction
innerTx, _ := tx.Begin(ctx)

// If this fails, outer transaction continues
if err := someRiskyOperation(innerTx); err != nil {
    innerTx.Rollback(ctx) // Only rolls back inner work
    // Outer tx continues
}

return tx.Commit(ctx)
```

--------------------------------

### Hello World with Connection Pool

Source: https://github.com/jackc/pgx/wiki/Getting-started-with-pgx

Connect to a PostgreSQL database using a connection pool for concurrent applications. This example replaces `pgx.Connect` with `pgxpool.New` and imports `pgxpool`.

```go
package main

import (
	"context"
	"fmt"
	"os"

	"github.com/jackc/pgx/v5/pgxpool"
)

func main() {
	dbp, err := pgxpool.New(context.Background(), os.Getenv("DATABASE_URL"))
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to create connection pool: %v\n", err)
		os.Exit(1)
	}
	defer dbp.Close()

	var greeting string
	err = dbp.QueryRow(context.Background(), "select 'Hello, world!'").Scan(&greeting)
	if err != nil {
		fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
		os.Exit(1)
	}

	fmt.Println(greeting)
}
```

--------------------------------

### CopyFromFunc Example

Source: https://github.com/jackc/pgx/blob/master/_autodocs/copy_and_bulk.md

Creates a CopyFromSource from a callback that returns rows until it signals completion (row=nil, err=nil).

```go
rowGen := func() ([]any, error) {
    // Generate or fetch rows dynamically
    // Return nil, nil when done
    row, err := getNextRowFromDatabase()
    if err != nil {
        return nil, err
    }
    if row == nil {
        return nil, nil // Signal end of data
    }
    return []any{row.ID, row.Name}, nil
}

count, err := conn.CopyFrom(ctx, pgx.Identifier{"users"}, []string{"id", "name"}, pgx.CopyFromFunc(rowGen))
```

--------------------------------

### Install pgx dependency

Source: https://github.com/jackc/pgx/wiki/Getting-started-with-pgx-through-database-sql

Add the pgx v5 package to the project modules.

```bash
$ go get github.com/jackc/pgx/v5
```

--------------------------------

### CopyFromSlice Example

Source: https://github.com/jackc/pgx/blob/master/_autodocs/copy_and_bulk.md

Creates a CopyFromSource from a callback function. Useful for typed data without buffering.

```go
type User struct {
    ID   int32
    Name string
    Age  int32
}

users := []User{
    {1, "Alice", 30},
    {2, "Bob", 25},
}

count, err := conn.CopyFrom(
    context.Background(),
    pgx.Identifier{"users"},
    []string{"id", "name", "age"},
    pgx.CopyFromSlice(len(users), func(i int) ([]any, error) {
        return []any{users[i].ID, users[i].Name, users[i].Age}, nil
    }),
)
```

--------------------------------

### Verify Go version

Source: https://github.com/jackc/pgx/wiki/Getting-started-with-pgx-through-database-sql

Check that the installed Go version meets the minimum requirement of 1.18.

```bash
$ go version
go version go1.19.1 darwin/amd64
```

--------------------------------

### Complete Batch Example with Callbacks

Source: https://github.com/jackc/pgx/blob/master/_autodocs/batch.md

Demonstrates sending a batch with insert, select, and delete operations, processing results via callbacks. Useful for complex transactions requiring structured result handling for each statement.

```go
batch := &pgx.Batch{}

// Insert with result handling
insertQuery := batch.Queue("insert into users(name, email) values($1, $2) returning id", "Alice", "alice@example.com")
var userID int32
insertQuery.QueryRow(func(row Row) error {
    return row.Scan(&userID)
})

// Select with result handling
selectQuery := batch.Queue("select count(*) from users")
var totalUsers int32
selectQuery.QueryRow(func(row Row) error {
    return row.Scan(&totalUsers)
})

// Delete with result handling
deleteQuery := batch.Queue("delete from users where name=$1", "Bob")
deleteQuery.Exec(func(ct pgconn.CommandTag) error {
    if ct.RowsAffected() == 0 {
        return fmt.Errorf("no user named Bob")
    }
    return nil
})

results := conn.SendBatch(context.Background(), batch)
defer results.Close()

if err := results.Close(); err != nil {
    return err
}

fmt.Printf("Created user %d\n", userID)
fmt.Printf("Total users: %d\n", totalUsers)
```

--------------------------------

### Add pgxpool to Go Modules

Source: https://github.com/jackc/pgx/wiki/Getting-started-with-pgx

Install the `pgxpool` package, which provides a connection pool for concurrent database access.

```bash
$ go get github.com/jackc/pgx/v5/pgxpool
```

--------------------------------

### Development Configuration

Source: https://github.com/jackc/pgx/blob/master/_autodocs/configuration.md

Use this minimal configuration for schema development, disabling caching and SSL to simplify the setup.

```url
postgres://user@localhost/db?statement_cache_capacity=0&sslmode=disable
```

--------------------------------

### Basic Transaction Example

Source: https://github.com/jackc/pgx/blob/master/_autodocs/transactions.md

Demonstrates a simple transaction using the defer pattern for rollback. Ensures operations are executed within a transaction context.

```go
// Simple transaction with defer pattern
tx, err := conn.Begin(context.Background())
if err != nil {
    return err
}
deferr tx.Rollback(context.Background())

_, err = tx.Exec(context.Background(), "insert into users(name) values($1)", "Alice")
if err != nil {
    return err
}

_, err = tx.Exec(context.Background(), "insert into users(name) values($1)", "Bob")
if err != nil {
    return err
}

return tx.Commit(context.Background())
```

--------------------------------

### Basic Local PostgreSQL Connection String

Source: https://github.com/jackc/pgx/blob/master/_autodocs/configuration.md

Provides examples of basic connection strings for local PostgreSQL databases, including authentication.

```url
postgres://localhost/mydb
```

```url
postgres://user:password@localhost/mydb
```

```url
postgresql://localhost/mydb
```

--------------------------------

### Batch Example with Manual Result Reading

Source: https://github.com/jackc/pgx/blob/master/_autodocs/batch.md

Shows how to send a batch and manually read results for query, exec, and queryRow operations. This approach provides fine-grained control over result consumption.

```go
batch := &pgx.Batch{}
batch.Queue("select id from users limit 10")
batch.Queue("insert into logs(msg) values($1)", "test")
batch.Queue("select count(*) from logs")

results := conn.SendBatch(context.Background(), batch)
defer results.Close()

// Read first result (Query)
rows, err := results.Query()
if err != nil {
    return err
}
defer rows.Close()

var ids []int32
for rows.Next() {
    var id int32
    if err := rows.Scan(&id); err != nil {
        return err
    }
    ids = append(ids, id)
}
if err := rows.Err(); err != nil {
    return err
}

// Read second result (Exec)
tag, err := results.Exec()
if err != nil {
    return err
}
fmt.Println("Inserted rows:", tag.RowsAffected())

// Read third result (QueryRow)
var count int32
if err := results.QueryRow().Scan(&count); err != nil {
    return err
}
fmt.Println("Log count:", count)

// Close the batch
if err := results.Close(); err != nil {
    return err
}
```

--------------------------------

### Conn.BeginTx

Source: https://github.com/jackc/pgx/blob/master/_autodocs/transactions.md

Starts a transaction with specified options (isolation level, access mode, deferrable mode).

```APIDOC
## Conn.BeginTx

### Description
Starts a transaction with specified options (isolation level, access mode, deferrable mode).

### Method
func (c *Conn) BeginTx(ctx context.Context, txOptions TxOptions) (Tx, error)

### Parameters
#### Path Parameters
- **ctx** (context.Context) - Required - Context (only affects BEGIN command)
- **txOptions** (TxOptions) - Required - Transaction options

### Response
#### Success Response
- **Tx** - The transaction object.
- **error** - Error if transaction cannot be started.

### Request Example
```go
tx, err := conn.BeginTx(context.Background(), pgx.TxOptions{
    IsoLevel:   pgx.Serializable,
    AccessMode: pgx.ReadOnly,
})
if err != nil {
    return err
}
deferr tx.Rollback(context.Background())

// Query operations...

return tx.Commit(context.Background())
```
```

--------------------------------

### Key=Value Connection String Format

Source: https://github.com/jackc/pgx/blob/master/_autodocs/configuration.md

Example of a PostgreSQL connection string using the key=value format.

```sql
host=localhost port=5432 user=username password=password dbname=dbname option1=value1
```

--------------------------------

### URL Connection String Formats

Source: https://github.com/jackc/pgx/blob/master/_autodocs/configuration.md

Examples of PostgreSQL connection strings in URL format, including Unix socket paths.

```sql
postgres://username:password@host:port/dbname?option1=value1&option2=value2
```

```sql
postgresql://username:password@host:port/dbname?option1=value1
```

```sql
postgres+unix:///path/to/socket/file
```

--------------------------------

### Example: Using Conn.CopyFrom with pgx.CopyFromRows

Source: https://github.com/jackc/pgx/blob/master/_autodocs/copy_and_bulk.md

Demonstrates how to use `Conn.CopyFrom` to copy a slice of slices of any type into a table. Ensure the data types in the slice match the table's column types.

```go
rows := [][]any{
    {"Alice", int32(30), "alice@example.com"},
    {"Bob", int32(25), "bob@example.com"},
}

count, err := conn.CopyFrom(
    context.Background(),
    pgx.Identifier{"users"},
    []string{"name", "age", "email"},
    pgx.CopyFromRows(rows),
)
if err != nil {
    return err
}
fmt.Printf("Copied %d rows\n", count)
```

--------------------------------

### Import pgx-shopspring-decimal Package

Source: https://github.com/jackc/pgx/wiki/Numeric-and-decimal-support

Add this import statement to use the shopspring/decimal integration with pgx. Ensure you have the shopspring/decimal package installed.

```go
pgxdecimal "github.com/jackc/pgx-shopspring-decimal"
```

--------------------------------

### Transaction Handling in Go

Source: https://github.com/jackc/pgx/blob/master/_autodocs/README.md

Illustrates how to manage database transactions using pgx, including starting a transaction, executing statements within it, and committing or rolling back.

```Go
tx, err := conn.Begin(context.Background())
if err != nil {
    return err
}
deferr tx.Rollback(context.Background())

_, err = tx.Exec(context.Background(), "insert into users(name) values($1)", "Alice")
if err != nil {
    return err
}

return tx.Commit(context.Background())
```

--------------------------------

### Begin Transaction

Source: https://github.com/jackc/pgx/blob/master/_autodocs/conn.md

Starts a new transaction with default options. The transaction can be rolled back or committed using the returned Tx handle.

```APIDOC
## Begin

### Description
Starts a new transaction with default options.

### Method
`Begin`

### Parameters
#### Path Parameters
- **ctx** (context.Context) - Required - Context (only affects the BEGIN command)

### Returns
- **Tx** (transaction handle)
- **error**

### Example
```go
tx, err := conn.Begin(context.Background())
if err != nil {
    return err
}
defer tx.Rollback(context.Background())

_, err = tx.Exec(context.Background(), "insert into users(name) values($1)", "Alice")
if err != nil {
    return err
}

return tx.Commit(context.Background())
```
```

--------------------------------

### Store and Retrieve File with pgx

Source: https://github.com/jackc/pgx/blob/master/_autodocs/large_objects.md

This example shows how to store a file into a PostgreSQL large object and then retrieve it. It includes functions for storing, retrieving, and cleaning up large objects within a transaction.

```go
func storeFile(ctx context.Context, tx pgx.Tx, filename string) (uint32, error) {
    // Create new large object
    lo := tx.LargeObjects()
    oid, err := lo.Create(ctx, 0)
    if err != nil {
        return 0, err
    }
    
    // Open for writing
    loObject, err := lo.Open(ctx, oid, pgx.LargeObjectModeWrite)
    if err != nil {
        return 0, err
    }
    defer loObject.Close()
    
    // Open and copy file
    file, err := os.Open(filename)
    if err != nil {
        return 0, err
    }
    defer file.Close()
    
    _, err = io.Copy(loObject, file)
    if err != nil {
        return 0, err
    }
    
    return oid, nil
}

func retrieveFile(ctx context.Context, tx pgx.Tx, oid uint32, outputFile string) error {
    // Open for reading
    lo := tx.LargeObjects()
    loObject, err := lo.Open(ctx, oid, pgx.LargeObjectModeRead)
    if err != nil {
        return err
    }
    defer loObject.Close()
    
    // Write to file
    file, err := os.Create(outputFile)
    if err != nil {
        return err
    }
    defer file.Close()
    
    _, err = io.Copy(file, loObject)
    return err
}

func main() {
    conn, _ := pgx.Connect(context.Background(), "postgres://...")
    defer conn.Close(context.Background())
    
    tx, _ := conn.Begin(context.Background())
    defer tx.Rollback(context.Background())
    
    // Store file
    oid, err := storeFile(context.Background(), tx, "myfile.bin")
    if err != nil {
        log.Fatal(err)
    }
    
    // Later: retrieve file
    err = retrieveFile(context.Background(), tx, oid, "retrieved.bin")
    if err != nil {
        log.Fatal(err)
    }
    
    // Clean up
    lo := tx.LargeObjects()
    lo.Unlink(context.Background(), oid)
    
    tx.Commit(context.Background())
}
```

--------------------------------

### TxOptions

Source: https://github.com/jackc/pgx/blob/master/_autodocs/types.md

Configuration options for starting a new transaction, including isolation level, access mode, and deferrable mode.

```APIDOC
## TxOptions

```go
type TxOptions struct {
    IsoLevel       TxIsoLevel
    AccessMode     TxAccessMode
    DeferrableMode TxDeferrableMode
    BeginQuery     string
    CommitQuery    string
}
```

Transaction options for `BeginTx`. See `transactions.md` for full documentation.
```

--------------------------------

### Customize Connection Before Creation

Source: https://github.com/jackc/pgx/blob/master/_autodocs/pgxpool.md

Use the BeforeConnect hook to modify connection configuration before a new connection is established. This allows for pre-connection setup.

```go
BeforeConnect: func(ctx context.Context, config *pgx.ConnConfig) error {
    // Customize config before creating connection
    return nil
}
```

--------------------------------

### StructArgs Example with pgx.Query

Source: https://github.com/jackc/pgx/blob/master/_autodocs/named_args.md

Shows how to use `pgx.StructArgs` to automatically convert struct fields into named arguments for a query. Exported fields are used, and struct tags can customize the argument names.

```go
type QueryParams struct {
    Name string `db:"name"`
    Age  int32  `db:"age"`
}

params := QueryParams{Name: "Alice", Age: 30}

rows, err := conn.Query(ctx,
    "select * from users where name = @name and age = @age",
    pgx.StructArgs(params),
)
```

--------------------------------

### Serializable Transaction Example

Source: https://github.com/jackc/pgx/blob/master/_autodocs/transactions.md

This snippet shows how to begin a transaction with a Serializable isolation level and ReadWrite access mode. It performs a read, updates a balance, and then commits the transaction. Ensure to handle errors and use defer tx.Rollback() for safety.

```go
tx, err := conn.BeginTx(context.Background(), pgx.TxOptions{
    IsoLevel:   pgx.Serializable,
    AccessMode: pgx.ReadWrite,
})
if err != nil {
    return err
}
defer tx.Rollback(context.Background())

var balance int64
err = tx.QueryRow(context.Background(), "select balance from accounts where id=$1", 1).
    Scan(&balance)
if err != nil {
    return err
}

newBalance := balance - 100
_, err = tx.Exec(context.Background(), "update accounts set balance=$1 where id=$2", newBalance, 1)
if err != nil {
    return err
}

return tx.Commit(context.Background())
```

--------------------------------

### ConnectTracer.TraceConnectStart Method Signature

Source: https://github.com/jackc/pgx/blob/master/_autodocs/tracing.md

Signature for the TraceConnectStart method, called when a connection or ConnectConfig begins. It accepts a context and connection start data, returning a context to be passed to TraceConnectEnd.

```go
func (ct ConnectTracer) TraceConnectStart(ctx context.Context, data TraceConnectStartData) context.Context
```

--------------------------------

### SimpleLogger Tracer Implementation

Source: https://github.com/jackc/pgx/blob/master/_autodocs/tracing.md

A basic tracer implementation that logs query start and end events using a provided logger. Requires implementing all tracer interfaces.

```go
type SimpleTracer struct {
    logger *log.Logger
}

func (st *SimpleTracer) TraceQueryStart(ctx context.Context, conn *pgx.Conn, data pgx.TraceQueryStartData) context.Context {
    st.logger.Printf("Query: %s Args: %v", data.SQL, data.Args)
    return ctx
}

func (st *SimpleTracer) TraceQueryEnd(ctx context.Context, conn *pgx.Conn, data pgx.TraceQueryEndData) {
    if data.Err != nil {
        st.logger.Printf("Error: %v", data.Err)
    } else {
        st.logger.Printf("Result: %d rows", data.CommandTag.RowsAffected())
    }
}

// ... implement other tracer interfaces ...
```

--------------------------------

### Tx.Begin

Source: https://github.com/jackc/pgx/blob/master/_autodocs/transactions.md

Starts a pseudo-nested transaction using PostgreSQL savepoints, returning a new transaction handle. This is useful for implementing error recovery without rolling back the outer transaction.

```APIDOC
## Tx.Begin

### Description
Starts a pseudo-nested transaction using PostgreSQL savepoints. Returns a new transaction handle. Useful for implementing error recovery without rolling back outer transaction.

### Method
(Implicitly POST or similar, as it modifies state)

### Endpoint
(Not applicable, this is an SDK method)

### Parameters
#### Path Parameters
(None)

#### Query Parameters
(None)

#### Request Body
(None)

### Request Example
```go
tx, _ := conn.Begin(ctx)
deferr tx.Rollback(ctx)

// Do outer work...

// Start nested transaction
innerTx, _ := tx.Begin(ctx)

// If this fails, outer transaction continues
if err := someRiskyOperation(innerTx); err != nil {
    innerTx.Rollback(ctx) // Only rolls back inner work
    // Outer tx continues
}

return tx.Commit(ctx)
```

### Response
#### Success Response
- **Tx** (Tx) - A new transaction handle for the nested transaction.
- **error** (error) - An error if the nested transaction could not be started.

#### Response Example
(See Request Example for usage)
```

--------------------------------

### Get Connection Configuration

Source: https://github.com/jackc/pgx/blob/master/_autodocs/conn.md

Config returns a copy of the current connection configuration, including settings like timeouts and database name.

```go
func (c *Conn) Config() *ConnConfig
```

--------------------------------

### Create and Initialize Project Directory

Source: https://github.com/jackc/pgx/wiki/Getting-started-with-pgx

Set up a new directory for your project and initialize Go modules.

```bash
$ mkdir hello
$ cd hello
$ go mod init hello
go: creating new go.mod: module hello
```

--------------------------------

### Query Rewriting with Named Placeholders

Source: https://github.com/jackc/pgx/blob/master/_autodocs/named_args.md

Named placeholders starting with '@' are replaced with ordinal placeholders ($1, $2, etc.) based on their first occurrence in the SQL query. This example shows how a query with repeated named placeholders is transformed.

```go
// Input
"select * from users where name = @name and city = @city and name = @name"

// Becomes (NamedArgs{"name": "Alice", "city": "NYC"})
"select * from users where name = $1 and city = $2 and name = $1"

// With arguments: ["Alice", "NYC"]
```

--------------------------------

### NamedArgs Example with pgx.Query

Source: https://github.com/jackc/pgx/blob/master/_autodocs/named_args.md

Demonstrates using `pgx.NamedArgs` to pass named parameters to a query. Extra arguments in the map are ignored, and missing arguments do not cause an error.

```go
rows, err := conn.Query(ctx, 
    "select * from users where name = @name and age > @age",
    pgx.NamedArgs{
        "name": "Alice",
        "age": 18,
    },
)
```

```go
rows, err := conn.Query(ctx,
    "select * from users where name = $1 and age > $2",
    "Alice",
    18,
)
```

--------------------------------

### Initialize Go modules

Source: https://github.com/jackc/pgx/wiki/Getting-started-with-pgx-through-database-sql

Set up Go modules for the project.

```bash
$ go mod init hello
go: creating new go.mod: module hello
```

--------------------------------

### Initialize project directory

Source: https://github.com/jackc/pgx/wiki/Getting-started-with-pgx-through-database-sql

Create a new directory for the project.

```bash
$ mkdir hello
$ cd hello
```

--------------------------------

### StrictNamedArgs Example with pgx.Query

Source: https://github.com/jackc/pgx/blob/master/_autodocs/named_args.md

Illustrates using `pgx.StrictNamedArgs` which enforces that all arguments in the map must be used in the query and all query arguments must be defined in the map. An error is returned if these conditions are not met.

```go
rows, err := conn.Query(ctx,
    "select * from users where name = @name",
    pgx.StrictNamedArgs{
        "name": "Alice",
        "age": 18, // Error: unused argument
    },
)
// Returns: "argument age of StrictNamedArgs not found in sql query"
```

--------------------------------

### Initialize Database Schema

Source: https://github.com/jackc/pgx/blob/master/examples/todo/README.md

Create the database and apply the schema from structure.sql.

```bash
createdb todo
psql todo < structure.sql
```

--------------------------------

### Run the application

Source: https://github.com/jackc/pgx/wiki/Getting-started-with-pgx-through-database-sql

Execute the main.go file to verify the database connection and query result.

```bash
$ go run main.go
Hello, world!
```

--------------------------------

### Build and Configure Application

Source: https://github.com/jackc/pgx/blob/master/examples/todo/README.md

Compile the Go application and set the database connection via environment variables.

```bash
go build
```

```bash
export PGDATABASE=todo
./todo list
```

```bash
PGDATABASE=todo ./todo list
```

--------------------------------

### Stream Data from HTTP Response to PostgreSQL

Source: https://github.com/jackc/pgx/blob/master/_autodocs/copy_and_bulk.md

This example demonstrates streaming data directly from an HTTP response body into a PostgreSQL table using `pgx.CopyFromFunc`. It assumes each line in the response is a comma-separated record.

```go
func copyFromHTTP(ctx context.Context, conn *pgx.Conn, url string) error {
    resp, err := http.Get(url)
    if err != nil {
        return err
    }
    defer resp.Body.Close()
    
    scanner := bufio.NewScanner(resp.Body)
    
    source := pgx.CopyFromFunc(func() ([]any, error) {
        if !scanner.Scan() {
            if scanner.Err() != nil {
                return nil, scanner.Err()
            }
            return nil, nil
        }
        
        fields := strings.Split(scanner.Text(), ",")
        id, _ := strconv.ParseInt(strings.TrimSpace(fields[0]), 10, 32)
        
        return []any{int32(id), strings.TrimSpace(fields[1])}, nil
    })
    
    count, err := conn.CopyFrom(ctx, pgx.Identifier{"users"}, 
        []string{"id", "name"}, source)
    if err != nil {
        return err
    }
    
    return nil
}
```

--------------------------------

### Create Superuser Role for PostgreSQL

Source: https://github.com/jackc/pgx/blob/master/CONTRIBUTING.md

This command creates a superuser role named 'postgres'. This is often necessary for PostgreSQL installations, especially those that do not create this role by default (e.g., Homebrew installations).

```bash
createuser -s postgres
```

--------------------------------

### Run the service

Source: https://github.com/jackc/pgx/blob/master/examples/url_shortener/README.md

Execute the main application entry point.

```bash
go run main.go
```

--------------------------------

### Get a Shortened URL

Source: https://github.com/jackc/pgx/blob/master/examples/url_shortener/README.md

Retrieve the original URL associated with a key.

```bash
curl http://localhost:8080/google
```

--------------------------------

### Initialize PostgreSQL Cluster and Certificates

Source: https://github.com/jackc/pgx/blob/master/CONTRIBUTING.md

Create the database cluster, configure network settings, and generate necessary SSL certificates.

```bash
initdb --locale=en_US -E UTF-8 --username=postgres .testdb/$POSTGRESQL_DATA_DIR

echo "listen_addresses = '127.0.0.1'" >> .testdb/$POSTGRESQL_DATA_DIR/postgresql.conf
echo "port = $PGPORT" >> .testdb/$POSTGRESQL_DATA_DIR/postgresql.conf
cat testsetup/postgresql_ssl.conf >> .testdb/$POSTGRESQL_DATA_DIR/postgresql.conf
cp testsetup/pg_hba.conf .testdb/$POSTGRESQL_DATA_DIR/pg_hba.conf

cd .testdb

# Generate CA, server, and encrypted client certificates.
go run ../testsetup/generate_certs.go

# Copy certificates to server directory and set permissions.
cp ca.pem $POSTGRESQL_DATA_DIR/root.crt
cp localhost.key $POSTGRESQL_DATA_DIR/server.key
chmod 600 $POSTGRESQL_DATA_DIR/server.key
cp localhost.crt $POSTGRESQL_DATA_DIR/server.crt

cd ..
```

--------------------------------

### Begin a Transaction with Options

Source: https://github.com/jackc/pgx/blob/master/_autodocs/pgxpool.md

Acquires a connection and begins a transaction with specified options. This allows for custom transaction isolation levels and other settings.

```go
tx, err := p.BeginTx(ctx, txOptions)
```

--------------------------------

### Setting up a pgxpool Pool

Source: https://github.com/jackc/pgx/blob/master/_autodocs/pgxpool.md

Demonstrates how to configure and create a new pgxpool.Pool with custom settings like max connections, connection lifetime, and connection initialization callbacks. Ensure the DATABASE_URL environment variable is set.

```go
func setupPool(ctx context.Context) (*pgxpool.Pool, error) {
    config, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
    if err != nil {
        return nil, err
    }
    
    config.MaxConns = 25
    config.MinConns = 5
    config.MaxConnLifetime = time.Hour
    config.MaxConnIdleTime = time.Minute * 5
    
    config.AfterConnect = func(ctx context.Context, conn *pgx.Conn) error {
        // Custom initialization per connection
        _, err := conn.Exec(ctx, "set search_path = 'public'")
        return err
    }
    
    config.PrepareConn = func(ctx context.Context, conn *pgx.Conn) (bool, error) {
        // Validate connection before use
        return !conn.IsClosed(), nil
    }
    
    pool, err := pgxpool.NewWithConfig(ctx, config)
    if err != nil {
        return nil, err
    }
    
    // Verify pool can connect
    if err := pool.Ping(ctx); err != nil {
        pool.Close()
        return nil, err
    }
    
    return pool, nil
}
```

--------------------------------

### Get Batch Size

Source: https://github.com/jackc/pgx/blob/master/_autodocs/batch.md

Batch.Len returns the number of queries currently queued in the batch.

```go
func (b *Batch) Len() int
```

--------------------------------

### Basic Connection and Query in Go

Source: https://github.com/jackc/pgx/blob/master/_autodocs/README.md

Demonstrates how to establish a basic connection to a PostgreSQL database, execute a single row query, and iterate over multiple rows using pgx.

```Go
import "github.com/jackc/pgx/v5"

// Connect
conn, err := pgx.Connect(context.Background(), "postgres://user:password@localhost/dbname")
if err != nil {
    return err
}
deferr conn.Close(context.Background())

// Query
var name string
err = conn.QueryRow(context.Background(), "select name from users where id=$1", 42).Scan(&name)

// Rows
rows, err := conn.Query(context.Background(), "select id, name from users")
if err != nil {
    return err
}
deferr rows.Close()

for rows.Next() {
    var id int32
    var name string
    if err := rows.Scan(&id, &name); err != nil {
        return err
    }
}
return rows.Err()
```

--------------------------------

### Hello World Database Connection

Source: https://github.com/jackc/pgx/wiki/Getting-started-with-pgx

Connect to a PostgreSQL database using a connection string from the DATABASE_URL environment variable and execute a simple query.

```go
package main

import (
	"context"
	"fmt"
	"os"

	"github.com/jackc/pgx/v5"
)

func main() {
	conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
		os.Exit(1)
	}
	defer conn.Close(context.Background())

	var greeting string
	err = conn.QueryRow(context.Background(), "select 'Hello, world!'").Scan(&greeting)
	if err != nil {
		fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
		os.Exit(1)
	}

	fmt.Println(greeting)
}
```

--------------------------------

### Connect and Execute Query with pgconn

Source: https://github.com/jackc/pgx/blob/master/pgconn/README.md

Demonstrates connecting to a PostgreSQL database using a connection string and executing a parameterized query. Ensure the DATABASE_URL environment variable is set.

```go
pgConn, err := pgconn.Connect(context.Background(), os.Getenv("DATABASE_URL"))
if err != nil {
	log.Fatalln("pgconn failed to connect:", err)
}
deffer pgConn.Close(context.Background())

result := pgConn.ExecParams(context.Background(), "SELECT email FROM users WHERE id=$1", [][]byte{[]byte("123")}, nil, nil, nil)
for result.NextRow() {
	fmt.Println("User 123 has email:", string(result.Values()[0]))
}
_, err = result.Close()
if err != nil {
	log.Fatalln("failed reading result:", err)
}
```

--------------------------------

### Remote PostgreSQL Connection with SSL

Source: https://github.com/jackc/pgx/blob/master/_autodocs/configuration.md

Example of a connection string for a remote PostgreSQL server requiring SSL/TLS encryption.

```url
postgres://user:pass@db.example.com:5432/mydb?sslmode=require
```

--------------------------------

### Get Current Position in Large Object

Source: https://github.com/jackc/pgx/blob/master/_autodocs/large_objects.md

Retrieves the current read/write position of a large object without altering it.

```go
pos, err := loObject.Tell()
if err != nil {
    return err
}
fmt.Printf("Current position: %d\n", pos)
```

--------------------------------

### Begin a Transaction

Source: https://github.com/jackc/pgx/blob/master/_autodocs/pgxpool.md

Acquires a connection and begins a transaction on it. The transaction owns the connection and must be committed or rolled back.

```go
tx, err := p.Begin(ctx)
```

--------------------------------

### QueryTracer Interface

Source: https://github.com/jackc/pgx/blob/master/_autodocs/tracing.md

The QueryTracer interface allows for tracing of query execution. It includes methods to mark the start and end of a query.

```APIDOC
## QueryTracer Interface

### Description
Traces `Query`, `QueryRow`, and `Exec` operations.

### Methods

#### TraceQueryStart

##### Description
Called at the beginning of query execution. Returns a context that will be passed to `TraceQueryEnd`.

##### Signature
```go
func (qt QueryTracer) TraceQueryStart(ctx context.Context, conn *Conn, data TraceQueryStartData) context.Context
```

##### Parameters
- **ctx** (context.Context) - Current context
- **conn** (*Conn) - Connection executing query
- **data** (TraceQueryStartData) - Query SQL and arguments

##### Returns
- **context.Context** (can be modified)

#### TraceQueryEnd

##### Description
Called after query execution completes (success or error).

##### Signature
```go
func (qt QueryTracer) TraceQueryEnd(ctx context.Context, conn *Conn, data TraceQueryEndData)
```

##### Parameters
- **ctx** (context.Context) - Context from TraceQueryStart
- **conn** (*Conn) - Connection that executed query
- **data** (TraceQueryEndData) - Command tag and error
```

--------------------------------

### Connection String with Query Execution Mode

Source: https://github.com/jackc/pgx/blob/master/_autodocs/configuration.md

Demonstrates setting the query execution mode (e.g., simple_protocol) via the connection string.

```url
postgres://user@localhost/db?default_query_exec_mode=simple_protocol
```

--------------------------------

### Querying with pgxpool using Acquire

Source: https://github.com/jackc/pgx/blob/master/_autodocs/pgxpool.md

Shows how to acquire a connection from the pool, execute a query, and then release the connection back to the pool using defer. This is useful for performing multiple operations on a single connection.

```go
func queryWithPool(ctx context.Context, pool *pgxpool.Pool) error {
    // Method 1: Acquire and defer release
    conn, err := pool.Acquire(ctx)
    if err != nil {
        return err
    }
    defer conn.Release()
    
    var count int32
    err = conn.QueryRow(ctx, "select count(*) from users").Scan(&count)
    return err
}
```

--------------------------------

### BeginTx Transaction

Source: https://github.com/jackc/pgx/blob/master/_autodocs/conn.md

Starts a transaction with specified isolation level, access mode, and other options. Allows fine-grained control over transaction behavior.

```APIDOC
## BeginTx

### Description
Starts a transaction with specified isolation level, access mode, and other options.

### Method
`BeginTx`

### Parameters
#### Path Parameters
- **ctx** (context.Context) - Required - Context (only affects the BEGIN command)
- **txOptions** (TxOptions) - Required - Transaction options

### Returns
- **Tx**
- **error**

### Example
```go
tx, err := conn.BeginTx(context.Background(), pgx.TxOptions{
    IsoLevel:   pgx.Serializable,
    AccessMode: pgx.ReadOnly,
})
```
```

--------------------------------

### Create, Open, Write, and Commit Large Object

Source: https://github.com/jackc/pgx/blob/master/_autodocs/large_objects.md

Demonstrates the complete lifecycle of creating a new large object, opening it for writing, writing data, and committing the transaction. Ensure to handle errors and defer rollback/close operations.

```go
tx, _ := conn.Begin(ctx)
defer tx.Rollback(ctx)

lo := tx.LargeObjects()

// Create new large object
oid, err := lo.Create(ctx, 0) // 0 = let server assign OID
if err != nil {
    return err
}

// Open for writing
loObject, err := lo.Open(ctx, oid, pgx.LargeObjectModeWrite)
if err != nil {
    return err
}
deffer loObject.Close()

// Write data
_, err = loObject.Write([]byte("Hello, World!"))
if err != nil {
    return err
}

return tx.Commit(ctx)
```

--------------------------------

### Connect to PostgreSQL and Query Data in Go

Source: https://github.com/jackc/pgx/blob/master/README.md

Connect to a PostgreSQL database using a connection URL and execute a query to retrieve data. Ensure the DATABASE_URL environment variable is set.

```go
package main

import (
	"context"
	"fmt"
	"os"

	"github.com/jackc/pgx/v5"
)

func main() {
	// urlExample := "postgres://username:password@localhost:5432/database_name"
	conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
		os.Exit(1)
	}
	defer conn.Close(context.Background())

	var name string
	var weight int64
	err = conn.QueryRow(context.Background(), "select name, weight from widgets where id=$1", 42).Scan(&name, &weight)
	if err != nil {
		fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
		os.Exit(1)
	}

	fmt.Println(name, weight)
}
```

--------------------------------

### BatchTracer.TraceBatchStart

Source: https://github.com/jackc/pgx/blob/master/_autodocs/tracing.md

Called when SendBatch is executed. It receives batch start data and returns a context that will be passed to subsequent tracing calls for this batch.

```APIDOC
## BatchTracer.TraceBatchStart

### Description
Called when SendBatch is executed.

### Signature
```go
func (bt BatchTracer) TraceBatchStart(ctx context.Context, conn *Conn, data TraceBatchStartData) context.Context
```

### Parameters
#### Path Parameters
- **ctx** (context.Context) - Required - Query context
- **conn** (*Conn) - Required - Connection
- **data** (TraceBatchStartData) - Required - Batch reference

### Returns
- **context.Context** - Context passed to TraceBatchQuery and TraceBatchEnd
```

--------------------------------

### Establish PostgreSQL Connection with Connect

Source: https://github.com/jackc/pgx/blob/master/_autodocs/conn.md

Use Connect to establish a connection to a PostgreSQL server using a connection string. Ensure to close the connection when done.

```go
conn, err := pgx.Connect(context.Background(), "postgres://user:password@localhost/dbname")
if err != nil {
    return err
}
deffer conn.Close(context.Background())
```

--------------------------------

### ConnectWithOptions

Source: https://github.com/jackc/pgx/blob/master/_autodocs/conn.md

Establishes a connection with additional options such as GetSSLPasswordFunc.

```APIDOC
## ConnectWithOptions

### Description
Establishes a connection with additional options.

### Method
POST

### Endpoint
/connectWithOptions

### Parameters
#### Query Parameters
- **ctx** (context.Context) - Required - Context for connection establishment
- **connString** (string) - Required - PostgreSQL connection string
- **options** (ParseConfigOptions) - Required - Additional parsing options

### Response
#### Success Response (200)
- ***Conn** (pointer to Conn)
- **error**
```

--------------------------------

### Connect with psql

Source: https://github.com/jackc/pgx/blob/master/pgproto3/example/pgfortune/README.md

Connect to the running pgfortune instance using the psql command-line client.

```bash
$ psql -h 127.0.0.1 -p 15432
Timing is on.
Null display is "∅".
Line style is unicode.
psql (11.5, server 0.0.0)
Type "help" for help.

jack@127.0.0.1:15432 jack=# select foo;
                   fortune
─────────────────────────────────────────────
  _________________________________________
 / Ships are safe in harbor, but they were \
 \ never meant to stay there.              /
  -----------------------------------------
  \     /\  ___  /\
   \   // \/   \/ \\
      ((    O O    ))
       \\ /     \ //
        \/  | |  \/
         |  | |  |
         |  | |  |
         |   o   |
         | |   | |
         |m|   |m|

(1 row)

Time: 28.161 ms
```

--------------------------------

### Get Underlying pgconn.PgConn

Source: https://github.com/jackc/pgx/blob/master/_autodocs/conn.md

PgConn returns the low-level pgconn.PgConn handle, allowing for advanced operations not directly exposed by the pgx connection.

```go
func (c *Conn) PgConn() *pgconn.PgConn
```

--------------------------------

### Prepare a SQL Statement

Source: https://github.com/jackc/pgx/blob/master/_autodocs/conn.md

Creates or retrieves a prepared statement on the server. Prepare is idempotent and can be called multiple times with the same name and SQL. Use this for queries that will be executed multiple times.

```go
func (c *Conn) Prepare(ctx context.Context, name, sql string) (*pgconn.StatementDescription, error)
```

```go
sd, err := conn.Prepare(context.Background(), "getUserStmt", "select name, age from users where id=$1")
if err != nil {
    return err
}

// Reuse the prepared statement
var name string
var age int32
err = conn.QueryRow(context.Background(), "getUserStmt", 42).Scan(&name, &age)
```

--------------------------------

### Seek within Large Object

Source: https://github.com/jackc/pgx/blob/master/_autodocs/large_objects.md

Moves the read/write position within a large object. Supports seeking from the start, current position, or end.

```go
// Seek to start
loObject.Seek(0, 0)

// Seek to end
loObject.Seek(0, 2)

// Seek 100 bytes from current position
loObject.Seek(100, 1)
```

--------------------------------

### PostgreSQL Unix Socket Connection String

Source: https://github.com/jackc/pgx/blob/master/_autodocs/configuration.md

Examples of connecting to a PostgreSQL server via Unix domain sockets, using different URI formats.

```url
postgres+unix:///var/run/postgresql/mydb
```

```url
postgres://%2Fvar%2Frun%2Fpostgresql@/mydb
```

--------------------------------

### Get Type Map

Source: https://github.com/jackc/pgx/blob/master/_autodocs/conn.md

TypeMap returns the pgtype.Map used by the connection for encoding and decoding PostgreSQL data types. This can be useful for custom type handling.

```go
func (c *Conn) TypeMap() *pgtype.Map
```