Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PostgreSQL demo of Array types using Golang
package main
import (
"database/sql"
"errors"
"fmt"
_ "github.com/bmizerany/pq"
"os"
"regexp"
"strings"
)
func main() {
db := dbConnect()
makeTestTables(db)
defer db.Close()
defer cleanup(db)
// Insert Some Data
db.Exec(`INSERT INTO array_test VALUES ('{"String1", "String2"}')`)
// arrays can be selected as strings...
dataString := selectAsString(db)
fmt.Println("SELECT as String:", dataString)
// Or by using array functions...
dataUnnest := selectUsingUnnest(db)
fmt.Println("SELECT using Unnest:", dataUnnest)
// Or by defining a scan type and parsing the return value
dataSlice := selectAsSlice(db)
fmt.Println("SELECT by parsing:", dataSlice)
// Arrays can be updated by replacing the entire array:
newArray := []interface{}{"String1", "String3", "String4", "String5"}
updateArray(db, newArray)
dataSlice = selectAsSlice(db)
fmt.Println("UPDATE entire array", dataSlice)
// or by appending / prepending value(s):
AppendToArray(db, "String6")
dataSlice = selectAsSlice(db)
fmt.Println("UPDATE with append:", dataSlice)
// or by replacing individual values:
ReplaceInArray(db, 2, "NULL")
dataSlice = selectAsSlice(db)
fmt.Println("UPDATE with replace:", dataSlice)
// Deleting by index requires slicing and is inefficient:
DeleteFromArray(db, 3)
dataSlice = selectAsSlice(db)
fmt.Println("UPDATE deleting index:", dataSlice)
}
// Arrays are serialized to strings {value, value...} by the database.
// these strings selected, updated and inserted like any string
func selectAsString(db *sql.DB) string {
row := db.QueryRow("SELECT data FROM array_test")
var asString string
err := row.Scan(&asString)
if err != nil {
panic(err)
}
return asString
}
// The UNNEST function expands an array into multiple rows. Each row
// can then be scanned individually.
func selectUsingUnnest(db *sql.DB) []string {
results := make([]string, 0)
rows, err := db.Query("SELECT UNNEST(data) FROM array_test")
if err != nil {
panic(err)
}
var scanString string
for rows.Next() {
rows.Scan(&scanString)
results = append(results, scanString)
}
return results
}
// By defining a wrapper type around a slice which implements
// sql.Scanner, we can scan the array directly into the type.
func selectAsSlice(db *sql.DB) StringSlice {
row := db.QueryRow("SELECT data FROM array_test")
var asSlice StringSlice
err := row.Scan(&asSlice)
if err != nil {
panic(err)
}
return asSlice
}
// Update an array by replacing the whole array with new values.
// This _could_ be done by serializing the StringSlice type using
// sql.driver.Valuer, but then we would have to validate the type
// of each value manually and format it for insert by hand. Instead,
// the ARRAY[...] format allows us to use query parameters to construct
// the array, ie ARRAY[$1, $2, $3], which then allows the database
// driver to coerce the variables into the right format for us.
func updateArray(db *sql.DB, array []interface{}) {
params := make([]string, 0, len(array))
for i := range array {
params = append(params, fmt.Sprintf("$%v", i+1))
}
query := fmt.Sprintf("UPDATE array_test SET data = ARRAY[%s]", strings.Join(params, ", "))
db.Exec(query, array...)
}
// The ARRAY_APPEND and ARRAY_PREPEND functions can be used to add single
// values to arrays. ARRAY_CAT combines two arrays. The || operator can
// do the same thing:
// SET data = data || <value>
// SET data = data || ARRAY[<value1>, <value2>]
func AppendToArray(db *sql.DB, value string) {
_, err := db.Exec("UPDATE array_test SET data = ARRAY_APPEND(data, $1)", value)
if err != nil {
panic(err)
}
}
// Arrays are 1-indexed. Individual elements can be used in expressions,
// updated, or selected by indexing the array.
func ReplaceInArray(db *sql.DB, index int, newValue string) {
_, err := db.Exec("UPDATE array_test SET data[$1] = $2", index, newValue)
if err != nil {
panic(err)
}
}
// Arrays support slice indexing:
// ARRAY['a', 'b', 'c'][1:2] == ARRAY['a', 'b']
// The ARRAY_UPPER function gets the length of an array for a specified dimension
// Deleting a value from an array amounts to slicing the array into two parts
// and combining them back together.
func DeleteFromArray(db *sql.DB, i int) {
_, err := db.Exec("UPDATE array_test SET data = array_cat(data[0:$1], data[$2:ARRAY_UPPER(data, 1) + 1])", i-1, i+1)
if err != nil {
panic(err)
}
}
type StringSlice []string
// Implements sql.Scanner for the String slice type
// Scanners take the database value (in this case as a byte slice)
// and sets the value of the type. Here we cast to a string and
// do a regexp based parse
func (s *StringSlice) Scan(src interface{}) error {
asBytes, ok := src.([]byte)
if !ok {
return error(errors.New("Scan source was not []bytes"))
}
asString := string(asBytes)
parsed := parseArray(asString)
(*s) = StringSlice(parsed)
return nil
}
// PARSING ARRAYS
// SEE http://www.postgresql.org/docs/9.1/static/arrays.html#ARRAYS-IO
// Arrays are output within {} and a delimiter, which is a comma for most
// postgres types (; for box)
//
// Individual values are surrounded by quotes:
// The array output routine will put double quotes around element values if
// they are empty strings, contain curly braces, delimiter characters,
// double quotes, backslashes, or white space, or match the word NULL.
// Double quotes and backslashes embedded in element values will be
// backslash-escaped. For numeric data types it is safe to assume that double
// quotes will never appear, but for textual data types one should be prepared
// to cope with either the presence or absence of quotes.
// construct a regexp to extract values:
var (
// unquoted array values must not contain: (" , \ { } whitespace NULL)
// and must be at least one char
unquotedChar = `[^",\\{}\s(NULL)]`
unquotedValue = fmt.Sprintf("(%s)+", unquotedChar)
// quoted array values are surrounded by double quotes, can be any
// character except " or \, which must be backslash escaped:
quotedChar = `[^"\\]|\\"|\\\\`
quotedValue = fmt.Sprintf("\"(%s)*\"", quotedChar)
// an array value may be either quoted or unquoted:
arrayValue = fmt.Sprintf("(?P<value>(%s|%s))", unquotedValue, quotedValue)
// Array values are separated with a comma IF there is more than one value:
arrayExp = regexp.MustCompile(fmt.Sprintf("((%s)(,)?)", arrayValue))
valueIndex int
)
// Find the index of the 'value' named expression
func init() {
for i, subexp := range arrayExp.SubexpNames() {
if subexp == "value" {
valueIndex = i
break
}
}
}
// Parse the output string from the array type.
// Regex used: (((?P<value>(([^",\\{}\s(NULL)])+|"([^"\\]|\\"|\\\\)*")))(,)?)
func parseArray(array string) []string {
results := make([]string, 0)
matches := arrayExp.FindAllStringSubmatch(array, -1)
for _, match := range matches {
s := match[valueIndex]
// the string _might_ be wrapped in quotes, so trim them:
s = strings.Trim(s, "\"")
results = append(results, s)
}
return results
}
// DB HELPERs
func dbConnect() *sql.DB {
datname := os.Getenv("PGDATABASE")
sslmode := os.Getenv("PGSSLMODE")
if datname == "" {
os.Setenv("PGDATABASE", "pqgotest")
}
if sslmode == "" {
os.Setenv("PGSSLMODE", "disable")
}
conn, err := sql.Open("postgres", "")
if err != nil {
panic(err)
}
return conn
}
// Create a table with an array type
// Can also use the syntax CREATE TABLE array_test (data varchar ARRAY)
func makeTestTables(db *sql.DB) {
_, err := db.Exec("CREATE TABLE array_test (data varchar[])")
if err != nil {
panic(err)
}
}
func cleanup(db *sql.DB) {
db.Exec("DROP TABLE array_test")
}
@penland365

This comment has been minimized.

Copy link

penland365 commented Sep 13, 2013

Man, nothing but thanks for this gist. It's going to save me almost an entire day.

@jaredfolkins

This comment has been minimized.

Copy link

jaredfolkins commented Oct 27, 2013

Thank you for this!

@vinsonexx

This comment has been minimized.

Copy link

vinsonexx commented Nov 6, 2013

Thank you, it's useful!

@sescobb27

This comment has been minimized.

Copy link

sescobb27 commented Feb 13, 2014

Oh men, you are my hero, i spent few days looking for something like this, i'm going to test this for calling postgres functions from golang, thanks

@marksievers

This comment has been minimized.

Copy link

marksievers commented Mar 19, 2014

+1 Gold.

@m-b-

This comment has been minimized.

Copy link

m-b- commented Apr 10, 2014

indeed, very nice!

@collinglass

This comment has been minimized.

Copy link

collinglass commented May 20, 2014

+1

@satran

This comment has been minimized.

Copy link

satran commented Oct 10, 2014

Line 186 probably needs a fix. It was breaking on characters N, U and L.
Works fine with

unquotedChar  = `[^",\\{}\s]|NULL`
@iostrovok

This comment has been minimized.

Copy link

iostrovok commented Oct 16, 2014

There is bad work any complex case for []text array.

@peterbe

This comment has been minimized.

Copy link

peterbe commented Oct 21, 2014

What about from StringSlice back to a string that can be inserted in SQL?

@kyleboyle

This comment has been minimized.

Copy link

kyleboyle commented Dec 14, 2014

Here is an alternative approach that only supports single dimension string arrays. It uses the csv parser lib to do the parsing after a bit of preprocessing. And saving to postgres is as easy as creating a string of the proper format:

// for replacing escaped quotes except if it is preceded by a literal backslash
//  eg "\\" should translate to a quoted element whose value is \

var quoteEscapeRegex = regexp.MustCompile(`([^\\]([\\]{2})*)\\"`)

// Scan convert to a slice of strings
// http://www.postgresql.org/docs/9.1/static/arrays.html#ARRAYS-IO
func (s *StringSlice) Scan(src interface{}) error {
    asBytes, ok := src.([]byte)
    if !ok {
        return error(errors.New("Scan source was not []bytes"))
    }
    str := string(asBytes)

    // change quote escapes for csv parser
    str = quoteEscapeRegex.ReplaceAllString(str, `$1""`)
    str = strings.Replace(str, `\\`, `\`, -1)
    // remove braces
    str = str[1 : len(str)-1]
    csvReader := csv.NewReader(strings.NewReader(str))

    slice, err := csvReader.Read()

    if err != nil {
        return err
    }

    (*s) = StringSlice(slice)

    return nil
}

func (s StringSlice) Value() (driver.Value, error) {
    // string escapes.
    // \ => \\\
    // " => \"
    for i, elem := range s {
        s[i] = `"` + strings.Replace(strings.Replace(elem, `\`, `\\\`, -1), `"`, `\"`, -1) + `"`
    }
    return "{" + strings.Join(s, ",") + "}", nil
}

Tests:

func TestStringSliceScan(t *testing.T) {
    var slice StringSlice

    err := slice.Scan([]byte(`{"12",45,"abc,\\\"d\\ef\\\\"}`))

    if err != nil {
        t.Errorf("Could not scan array, %v", err)
        return
    }

    if slice[0] != "12" || slice[2] != `abc,\"d\ef\\` {
        t.Errorf("Did not get expected slice contents")
    }
}

func TestStringSliceDbValue(t *testing.T) {
    slice := StringSlice([]string{`as"f\df`, "43", "}adsf"})

    val, err := slice.Value()
    if err != nil {
        t.Errorf("Could not convert to db string")
    }

    if str, ok := val.(string); ok {
        if `{"as\"f\\\df","43","}adsf"}` != str {
            t.Errorf("db value expecting %s got %s", `{"as\"f\\\df","43","}adsf"}`, str)
        }
    } else {
        t.Errorf("Could not convert %v to string for comparison", val)
    }
}
@rafroehlich2

This comment has been minimized.

Copy link

rafroehlich2 commented Feb 12, 2015

Well this was an awesome find. Thanks for saving me a large amount of time!

@tarikjn

This comment has been minimized.

Copy link

tarikjn commented Feb 22, 2015

An easier approach that works with all kind of nested arrays/composite types, if you only need to retrieve values from Postgres, is simply to use native Postgres functions to_json/array_to_json/row_to_json in your SELECT query and then json unmarshall the resulting string.

@nwoolls

This comment has been minimized.

Copy link

nwoolls commented Feb 26, 2015

@peterbe

type StringSlice []string

func (s StringSlice) Value() (driver.Value, error) {
    if len(s) == 0 {
        return nil, nil
    }

    var buffer bytes.Buffer

    buffer.WriteString("{")
    last := len(s) - 1
    for i, val := range s {
        buffer.WriteString(strconv.Quote(val))
        if i != last {
            buffer.WriteString(",")
        }
    }
    buffer.WriteString("}")

    return string(buffer.Bytes()), nil
}
@jsdir

This comment has been minimized.

Copy link

jsdir commented Apr 23, 2015

👍

@davidmz

This comment has been minimized.

Copy link

davidmz commented Jul 6, 2015

@kyleboyle, your Scan needs a branch for a '{}' case.

@teejayvanslyke

This comment has been minimized.

Copy link

teejayvanslyke commented Dec 13, 2016

Thanks! Quite helpful.

@mikeatlas-r7

This comment has been minimized.

Copy link

mikeatlas-r7 commented Feb 28, 2018

@kyleboyle @davidmz here's the Scan supporting the empty array case:

func (s *StringSlice) Scan(src interface{}) error {
	var str string
	if asBytes, ok := src.([]byte); ok {
		str = string(asBytes)
	} else if asStr, ok := src.(string); ok {
		str = string(asStr)
	} else {
		return error(errors.New(
			"StringSlice Scan source was not []bytes or string"))
	}

	// change quote escapes for csv parser
	str = quoteEscapeRegex.ReplaceAllString(str, `$1""`)
	str = strings.Replace(str, `\\`, `\`, -1)

	// remove braces
	str = str[1 : len(str)-1]

	// if the result was empty, just return empty slice
	if len(str) == 0 {
		(*s) = StringSlice([]string{})
		return nil
	}

	// otherwise, parse the list of values.
	csvReader := csv.NewReader(strings.NewReader(str))
	slice, err := csvReader.Read()
	if err != nil {
		return err
	}
	(*s) = StringSlice(slice)

	return nil
}
@Vitucho

This comment has been minimized.

Copy link

Vitucho commented May 29, 2018

Thanks a lot for sharing!! Let me show an example of working with arrays using pg.Array function, that leverages a little bit the mapping beteween go slices and psql arrays.


package main

import (
	"database/sql"
	"fmt"

	"log"

	"github.com/lib/pq"
)

const (
	host     = "localhost"
	port     = 5431
	user     = "user"
	password = "secret :P" // actually would be difficult to guess 
	dbname   = "testdb"
)

type SampleRow struct {
	Id           int
	MultiplesIds []int64
}

func ensureSampleTableExists(db *sql.DB) {
	_, err := db.Exec(`
	CREATE TABLE IF NOT EXISTS sample ( id serial NOT NULL,
	  multiples_ids bigint[],
	  CONSTRAINT sample_pk PRIMARY KEY (id)
	)`)
	if err != nil {
		panic(err)
	}
}

func main() {
	psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
		"password=%s dbname=%s sslmode=disable",
		host, port, user, password, dbname)
	db, err := sql.Open("postgres", psqlInfo)
	if err != nil {
		panic(err)
	}
	defer db.Close()
	ensureSampleTableExists(db)

	/*var x []int64 // must be int64 to work when reading... compatibility issues i guess...
	db.QueryRow("SELECT ARRAY[235, 401]").Scan(pq.Array(&x))
	log.Println(x)*/
	insertRow(db, SampleRow{MultiplesIds: []int64{2, 3}})
	samples, err := getRows(db)
	if err != nil {
		log.Panicln(err)
	} else {
		log.Println(samples)
	}
}

func insertRow(Db *sql.DB, s SampleRow) (SampleRow, error) {
	err := Db.QueryRow(`
		INSERT INTO sample(multiples_ids)
	    VALUES ($1) RETURNING id`, pq.Array(s.MultiplesIds)).Scan(&s.Id)
	return s, err
}

func getRows(Db *sql.DB) ([]SampleRow, error) {
	rows, err := Db.Query(`
		SELECT id, multiples_ids
		FROM sample`)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	defer rows.Close()
	samples := []SampleRow{}
	for rows.Next() {
		var sample SampleRow
		err = rows.Scan(&sample.Id, pq.Array(&sample.MultiplesIds))
		if err != nil {
			log.Panicln("Error while scanning feched results", err)
			return nil, err
		}
		samples = append(samples, sample)
	}

	return samples, nil
}


Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.