duangutian1426
duangutian1426
2017-12-03 18:01
浏览 639

将自定义类型数组插入postgres

I'm trying to insert a row with a column that is an array of a custom type (ingredient). My tables are:

CREATE TYPE ingredient AS (
    name text,
    quantity text,
    unit text
);

CREATE TABLE IF NOT EXISTS recipes (
    recipe_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name text,
    ingredients ingredient[],
    // ...
);

Using raw sql, I can insert a row by:

INSERT INTO recipes (name, ingredients) VALUES ('some_name', ARRAY[ROW('aa', 'bb', 'cc'), ROW('xx', 'yy', 'zz')]::ingredient[] );

But I'm struggling to do this in go with the pq lib. I've created a pq.Array interface:

type Ingredient struct {
    Name string
    Quantity string
    Unit string
}

type Ingredients []*Ingredient

func (ings *Ingredients) ConvertValue(v interface{}) (driver.Value, error) {
    return "something", nil
}
func (ings *Ingredients) Value() (driver.Value, error) {
    val := `ARRAY[]`
    for i, ing := range ings {
        if i != 0 {
            val += ","
        }
        val += fmt.Printf(`ROW('%v','%v','%v')`, ing.Name, ing.Quantity, ing.Unit)
    }
    val += `::ingredient[]`
    return val, nil
}


// and then trying to insert via:
stmt := `INSERT INTO recipes (
        name,
        ingredients
    )
    VALUES ($1, $2)
`
_, err := db.Exec(stmt,
    "some_name",
    &Ingredients{
        &Ingredient{"flour", "3", "cups"},
    },
)

But pg keeps throwing the error:

  Error insertingpq: malformed array literal: "ARRAY[ROW('flour','3','cups')]::ingredient[]"

Am I returning an incorrect driver.Value?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dongyu9263
    dongyu9263 2017-12-03 19:51
    已采纳

    You can either use this approach outlined here: https://github.com/lib/pq/issues/544

    type Ingredient struct {
        Name string
        Quantity string
        Unit string
    }
    
    func (i *Ingredient) Value() (driver.Value, error) {
        return fmt.Sprintf("('%s','%s','%s')", i.Name, i.Quantity, i.Unit), nil
    }
    
    stmt := `INSERT INTO recipes (name, ingredients) VALUES ($1, $2::ingredient[])`
    
    db.Exec(stmt, "some_name", pq.Array([]*Ingredient{{"flour", "3", "cups"}}))
    

    Or if you have records in the table and you query it, you will probably see the ingredient array in its literal form, which you can than mimic during insert.

    func (ings *Ingredients) Value() (driver.Value, error) {
        val := `{`
        for i, ing := range ings {
            if i != 0 {
                val += ","
            }
            val += fmt.Sprintf(`"('%s','%s','%s')"`, ing.Name, ing.Quantity, ing.Unit)
        }
        val += `}`
        return val, nil
    }
    
    // e.g. `{"('flour','3','cups')"}`
    
    stmt := `INSERT INTO recipes (name, ingredients) VALUES ($1, $2::ingredient[])`
    
    // ...
    
    点赞 评论
  • duanhong8839
    duanhong8839 2017-12-03 19:03

    It seems your database design is very complicated and not taking into account the strengths (and weaknesses) of SQL.

    May I suggest you split the ingredients into their own table with a reference to the recipe. Then finding out a full recipe is a JOIN operation.

    Creating the DB:

    CREATE TABLE ingredients (
        recipe_id uuid,
        name text,
        quantity int,
        unit text
    );
    
    CREATE TABLE recipes (
        recipe_id uuid PRIMARY KEY,
        name text
    );
    

    Inserting a recipe and querying to read it out:

    INSERT INTO recipes VALUES (
      '5d1cb631-37bd-46cc-a278-4c8558ed8964', 'cake1'
    );
    
    INSERT INTO ingredients (recipe_id, name, quantity, unit) VALUES
      ('5d1cb631-37bd-46cc-a278-4c8558ed8964', 'flour', 3, 'cups'),
      ('5d1cb631-37bd-46cc-a278-4c8558ed8964', 'water', 1, 'cups')
    ;
    
    SELECT r.name, i.name, i.quantity, i.unit
    FROM ingredients AS i
    INNER JOIN recipes AS r
    ON r.recipe_id=i.recipe_id;
    

    SQLFiddle link: http://sqlfiddle.com/#!17/262ad/14

    点赞 评论

相关推荐