Insert string array into postgres text array field

Question

I'm trying to make an article taggable.

Article table:

type Article struct {
  ID int64
  Body string
  Tags string
}

Preparing values:

tags := r.FormValue("tags")
tagArray := fmt.Sprintf("%q", strings.Split(tags, ", ")) // How do I make use of this?

t := Article{
    Body: "this is a post",
    Tags: `{"apple", "orange"}`,    // I have to hard code this for this to work.
}
if err := t.Insert(Db); err != nil {
   // Error handling
}

Database query:

func (t *Article) Insert(db *sqlx.DB) error {
    nstmt, err := db.PrepareNamed(`INSERT INTO articles
    (body, tags)
    VALUES (:body, :tags)
    RETURNING *;
    `)
    if err != nil {
        return err
    }
    defer nstmt.Close()

    err = nstmt.QueryRow(t).StructScan(t)
    if err, ok := err.(*pq.Error); ok {
        return err
    }
    return err
}

Postgres setup for tags field:

tags character varying(255)[] DEFAULT '{}',

It seems like I have to hard code the value for tags for it to work. Otherwise I would get errors like these:

pq: missing dimension value
OR
pq: array value must start with "{" or dimension information

How do I make use of tagArray?

Helpful reference: https://gist.github.com/adharris/4163702

I meant the fully qualified insert statement i.e insert into table (col1,col2) values(1,'Some_Data') — Vivek S.
– Vivek S., Commented Apr 20, 2015 at 10:39
You mean this? INSERT INTO articles (body, tags) VALUES (:body, :tags) RETURNING *; — user3918985
– user3918985, Commented Apr 20, 2015 at 10:41
possible duplicate of Inserting an array into a Postgresql database in Golang — Ainar-G
– Ainar-G, Commented Apr 20, 2015 at 12:08

andybalholm · Accepted Answer · 2015-04-20 17:00:20Z

0

You need to format your tags with the proper array input format, the same as your hard-coded value.

The following code will work if the tags don't contain any special characters that need to be escaped differently from how strconv.Quote would escape them:

tags := "apple, orange"
tagArray := strings.Split(tags, ", ")
for i, s := range tagArray {
    tagArray[i] = strconv.Quote(s)
}
final := "{" + strings.Join(tagArray, ",") + "}"

answered Apr 20, 2015 at 17:00

andybalholm

16.3k4 gold badges42 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert string array into postgres text array field

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related