binary_arrays.go

package main

import (
	"fmt"

	"github.com/jackc/pgx"
)

func main() {
	if err := run(); err != nil {
		panic(err)
	}
}

func run() error {
	db, err := pgx.Connect(pgx.ConnConfig{
		Host: "localhost",
		Port: 26257,
		User: "root",
	})
	if err != nil {
		return err
	}

	_, err = db.Exec(`DROP DATABASE IF EXISTS test`)
	_, err = db.Exec(`CREATE DATABASE test`)
	_, err = db.Exec(`USE test`)

	_, err = db.Exec(`CREATE TABLE activity_categories (
		id INT NOT NULL DEFAULT unique_rowid(),
		ref_name STRING NOT NULL,
		parent_ids INT[] NULL,
		CONSTRAINT "primary" PRIMARY KEY (id ASC),
		FAMILY "primary" (id, ref_name, parent_ids)
	)`)
	if err != nil {
		return err
	}

	_, err = db.Exec(`INSERT INTO activity_categories VALUES (0, 'yolo', ARRAY[1,2,3])`)
	if err != nil {
		return err
	}

	_, err = db.Exec(`CREATE TABLE activity_category_strings (
		id INT NOT NULL DEFAULT unique_rowid(),
		lang STRING NOT NULL,
		"name" STRING NOT NULL,
		abbr STRING NULL,
		activity_category_id INT NOT NULL,
		CONSTRAINT "primary" PRIMARY KEY (id ASC),
		CONSTRAINT activity_category_strings_activity_category_id_fkey FOREIGN KEY (activity_category_id) REFERENCES activity_categories (id),
		INDEX activity_category_strings_auto_index_activity_category_strings_activity_category_id_fkey (activity_category_id ASC),
		FAMILY "primary" (id, lang, "name", abbr, activity_category_id)
	)`)
	if err != nil {
		return err
	}

	_, err = db.Exec(`INSERT INTO activity_category_strings VALUES (0, 'en', 'justin', 'abbr', 0)`)
	if err != nil {
		return err
	}

	var n []int64
	err = db.QueryRow(`SELECT a0."parent_ids" FROM "activity_categories" AS a0 INNER JOIN "activity_category_strings" AS a1 ON a1."activity_category_id" = a0."id"`).Scan(&n)

	fmt.Println(n)

	return nil
}