Jackc/pgx

External

• https://github.com/jackc/pgx
• https://github.com/jackc/pgx/wiki/Getting-started-with-pgx
• https://www.youtube.com/watch?v=sXMSWhcHCf8

Internal

• PostgreSQL Support in Go

Overview

jackc/pgx is a PostgreSQL low-level, high performance driver and toolkit that exposes PostgreSQL-specific features such as LISTEN/NOTIFY and COPY. It also includes an adapter for the standard database/sql package, but using database/sql is optional. It understands PostgreSQL data types. jackc/pgx is recommended when the application only targets PostgreSQL.

Installation

go get github.com/jackc/pgx/v5

Database Operations

Open a Connection

import (
	"context"
	"fmt"
	"os"

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

...

ctx := context.Background()
connectionString := "postgres://postgres:@localhost:5432"
conn, err := pgx.Connect(ctx, connectionString)
if err != nil {
  ...
}
defer func() {
  if err = conn.Close(ctx); err != nil {
    ...
  }
}()

if err = conn.Ping(ctx); err != nil {
  ...
}
fmt.Printf("success\n")

Create a Schema

sql := `CREATE SCHEMA IF NOT EXISTS blue`
commandTag, err := conn.Exec(ctx, sql)
if err != nil {
  ...
} else {
  fmt.Printf("%s\n", commandTag)
}

Create a Table

sql := `CREATE TABLE IF NOT EXISTS BLUE.TEST ("ID" int, "NAME" varchar(10))`
commandTag, err = db.Exec(sql)
if err != nil {
 ...
} else {
  fmt.Printf("%s\n", commandTag)
}

Insert a Row

commandTag, err := conn.Exec(context.B, `INSERT INTO someschema.sometable ("id", "color", "size") VALUES ($1, $2, $3)`, 1, "red", 15)
if err != nil {
    ...
} else {
    fmt.Printf("%s\n", commandTag)
}

Query

rows, err := conn.Query(ctx, `SELECT "id", "color", "lab_id", "size" FROM someschema.sometable`)
if err != nil {
    ...
}
for rows.Next() {
    var (
	      id    string
		  color string
		  size  string
		)
    err = rows.Scan(&id, &color, &size)
    if err != nil {
        ...
	} else {
   	   fmt.Printf("%s, %s, %s, %s, %v\n", id, fleetID, labID, category, keys)
    }
}