Three little tricks for PostgreSQL

Cover image

Importing data from json, reading and writing to array columns, querying custom data types

by Aleksander Piotrowski · 3 min read PostgreSQL TIL Go

We were working recently on a lot of things related to PostgreSQL and we found some stuff worth sharing. This blog post will shed some light on using JSON as a data source and how to work with PostgreSQL arrays.

Initialize database data from JSON filepermalink

Using JSON as your initial data source for the table is more straight forward than you may think. All you have to do is listed below:

BEGIN TRANSACTION;

-- create table for data
CREATE TABLE "recipes" (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
categories INTEGER[],
ingredients TEXT[]
)

-- create temporary table for storing json data
-- this table will be removed automatically on commit
CREATE TEMPORARY TABLE temp_import (doc JSON) ON COMMIT DROP;

-- copy file content to newly created table
COPY temp_import from 'server/migrations/recipes.json';

-- insert data from temporary table to real table
insert into "recipes" (id, name)
select p.*
from temp_import l
cross join lateral json_populate_recordset(null::recipes, doc) as p;
-- cross join lateral json_populate_record(null::recipes, doc) as p;

COMMIT TRANSACTION;

There is one catch - the JSON file should have JSON written in one line. If it doesn't, you will see an error similar to ERROR 22P02: invalid input syntax for type JSON. Make sure to remove all new lines from your JSON file before copying the file.

[{"name": "quick recipe", "categories": [1,2,3], "ingredients": ["cornflakes", "milk"]}, {"name": "cool recipe", "categories": [1,2,3], "ingredients": ["bread", "butter"]}, {"name": "lavish meal", "categories": [1,2,3], "ingredients": ["meat", "veggies"]}]

Other way around is to use json_populate_record. This way your file should contain one json object each line:

// No array brackets, nor commas after each line
{"name": "quick recipe", "categories": [1,2,3], "ingredients": ["cornflakes", "milk"]}
{"name": "cool recipe", "categories": [1,2,3], "ingredients": ["bread", "butter"]}
{"name": "lavish meal", "categories": [1,2,3], "ingredients": ["meat", "veggies"]}

The JSON object is scanned for fields whose names match column names of the output row type (...) (Fields that do not correspond to any output column name are ignored.) ~ Postgres documentation

Read data from Postgres array fieldpermalink

Postgres can store data as an array. This is really handy with storing categories, tags, and any other grouping information. To fetch multiple rows when dealing with the Postgres array, you have to Query() all data and add it to your array one by one with a little help of pq.Array().

// Select recipes based on categories
categories := []int{1,2,3,4,5}
selectRecipes := `select * FROM recipes WHERE categories @> $1;`

rows, err := s.db.QueryContext(ctx, selectRecipes, pg.Array(categories))

var recipes []*model.Recipe
for rows.Next() {
recipe := &model.Recipe{}

var dbCategories []int
if err := rows.Scan(
&recipe.ID,
&recipe.Name,
// Every array field in Postgres have to have corresponding pg.Array here
pq.Array(dbCategories),
); err != nil {
return nil, err
}

// append Categories to recipe
recipe.Categories = append(Categories{}, dbCategories...)
recipes = append(recipes, recipe)
}

If Categories or Ingredients are not built in Go type, they will not be automatically converted, however, there is a way to do it. More about this in the next section.

Fetch data from Postgres when using custom typespermalink

When you are using custom data types in your application it may be necessary to implement Value() and Scan() for fetching that data from database.

Implementing Value() and Scan() for custom data types is required when used together with pg.Array() or JSON fields.

type Category int

// Value - Implement the database/sql valuer interface
func (c Category) Value() (driver.Value, error) {
//Allowed types here are: int64, float64, string, bool, []byte, time.Time.
return int64(c), nil
}

// Scan - Implement the database/sql scanner interface
func (c *Category) Scan(value interface{}) error {
if value == nil {
// Specify default value
*c = Category(0)
return nil
}
if v, ok := value.([]byte); ok {
// this part is really important
// you have to parse []byte to int
return json.Unmarshal(v, c)
}
return fmt.Errorf("wrong type, got: %T, expected: []byte", value)
}

With those two interfaces implemented you can select (or insert) data from the database without introducing intermediate variable:

// Select recipes based on categories
categories := []int{1,2,3,4,5}
selectRecipes := `select * FROM recipes WHERE categories @> $1;`

rows, err := s.db.QueryContext(ctx, selectRecipes, pg.Array(categories))

var recipes []*model.Recipe
for rows.Next() {
recipe := &model.Recipe{}
if err := rows.Scan(
&recipe.ID,
&recipe.Name,
pq.Array(&recipe.Categories),
); err != nil {
return nil, err
}
recipes = append(recipes, recipe)
}

In summarypermalink

With a few simple tricks you learned how to use JSON files to load data into your tables and how to read this data in your Go app. I hope you found it useful.

Does Ingrid sound like an interesting place to work at? We are always looking for good people! Check out our open positions