postgres

README

postgres

install

go get github.com/lib/pq

• Create an Azure Database for PostgreSQL (e.g. via the Azure Portal).
• If you did not record it at deployment time, reset the password via the "Reset password" button on the "Overview" blade.
• Copy "PostgreSQL connection URL" from the "Connection strings" blade. It will look similar to: postgres://username:{your_password}@pg210600.postgres.database.azure.com/postgres?sslmode=require
• Set an environment variable POSTGRES_URL='postgres://username:{your_password}@pg210600.postgres.database.azure.com/postgres?sslmode=require' in _/AUTH_postgres.sh, and replace {your_password} with the correct password.

citus

check out columnar compression:

https://www.citusdata.com/blog/2021/03/06/citus-10-columnar-compression-for-postgres/

Documentation

Overview

Package postgres provides a collection of functions that are primarily geared towards use by a simple CLI tool, and secondarily serve as example code for how to use the Azure Database for PostgreSQL (Flexible Server).

Index

• func CreateTable(name, valueType string) error
• func DbFromEnv() (*sql.DB, error)
• func Delete(table, key string) error
• func DeleteTable(name string) error
• func Exec(sql string, args ...interface{}) (int64, error)
• func InsertKeyValue(table, key, value string) error
• func InsertStdin(table string) error
• func InsertStdinBulk(table string, batchSize int) error
• func ListTables() error
• func QueryJSON(query string) error
• func QueryKeyValue(query string) error
• func QueryString(query string) error
• func Test() error
• type KeyValue

Functions

func CreateTable

func CreateTable(name, valueType string) error

CreateTable creates a table in the database with columns key of type varchar(256) and value of type valueType which defaults to jsonb.

func DbFromEnv

func DbFromEnv() (*sql.DB, error)

DbFromEnv creates a *sql.DB authenticated by the environment variable POSTGRES_SQL

func Delete

func Delete(table, key string) error

Delete deletes from table where the key column matches the key parameter.

func DeleteTable

func DeleteTable(name string) error

DeleteTable deletes a table from the database

func Exec

func Exec(sql string, args ...interface{}) (int64, error)

Exec excutes a command and returns the rows affected, or an error. This is primarily to show RowsAffected() which we have ommitted in other places where Exec is used.

func InsertKeyValue

func InsertKeyValue(table, key, value string) error

InsertKeyValue inserts a key/value pair into a table. The name of the table defaults to kv.

func InsertStdin

func InsertStdin(table string) error

InsertStdin takes one or more records from the standard input and inserts them individually using insertJSON which is similar to InsertJSON but reuses the database connection so we avoid exhausting them in a loop.

func InsertStdinBulk

func InsertStdinBulk(table string, batchSize int) error

InsertStdinBulk takes one or more records from the standard input and inserts them individually using insertBulkJSON rather than InsertStdin's insertJSON. This uses the bulk import approach outlined in the pq docs: https://pkg.go.dev/github.com/lib/pq#hdr-Bulk_imports We set a batchSize, which defaults to 100 if batchSize == 0

func ListTables

func ListTables() error

ListTables selects all tables from the current database and outputs them in JSON format.

func QueryJSON

func QueryJSON(query string) error

QueryJSON selects performs a select from the database (with a default) and builds a JSON map which we write to the standard output.

func QueryKeyValue

func QueryKeyValue(query string) error

QueryKeyValue is a function that is designed to return a Key/Value pair which we marshal to JSON and write to the standard output. It is partially designed to be an example, and to guarantee output shape when we pair with InsertKeyValue. We also default the query to: select key, value from kv

func QueryString

func QueryString(query string) error

QueryString performs a query (with a default) which returns a single string, which we then print to the standard output. This function is designed for queries that have a single return value (e.g. a json/jsonb column)

func Test

func Test() error

Types

type KeyValue

type KeyValue struct {
	Key   string
	Value string
}

KeyValue is a simple struct to represent a Key/Value pair