Skip to content

INSERT

Jump to bottom
go-jet edited this page Sep 30, 2022 · 11 revisions

INSERT statement is used to insert a single record or multiple records into a table.
More about INSERT statement can be at:
PostgreSQL - https://www.postgresql.org/docs/11/sql-insert.html
MySQL - https://dev.mysql.com/doc/refman/8.0/en/insert.html
MariaDB - https://mariadb.com/kb/en/library/update/

Following clauses are supported:

  • INSERT(columns...) - list of columns for insert
  • VALUES(values...) - list of values
  • MODEL(model) - list of values for columns will be extracted from model object
  • MODELS([]model) - list of values for columns will be extracted from list of model objects
  • QUERY(select) - select statement that supplies the rows to be inserted.
  • ON CONFLICT - specifies an alternative action to raising a unique violation or exclusion constraint violation error (PostgreSQL only).
  • ON DUPLICATE KEY UPDATE - enables existing rows to be updated if a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY(MySQL and MariaDB).
  • RETURNING(output_expression...) - An expressions to be computed and returned by the INSERT statement after each row is inserted. The expressions can use any column names of the table. Use TableName.AllColumns to return all columns. (PostgreSQL only)

This list might be extended with feature Jet releases.

Insert row by row

Using VALUES (not recommended, see bellow)

insertStmt := Link.INSERT(Link.ID, Link.URL, Link.Name, Link.Description).
    VALUES(100, "http://www.postgresqltutorial.com", "PostgreSQL Tutorial", DEFAULT).
    VALUES(101, "http://www.google.com", "Google", DEFAULT).
    VALUES(102, "http://www.yahoo.com", "Yahoo", nil)

Debug SQL of above insert statement:

INSERT INTO test_sample.link (id, url, name, description) VALUES
     (100, 'http://www.postgresqltutorial.com', 'PostgreSQL Tutorial', DEFAULT),
     (101, 'http://www.google.com', 'Google', DEFAULT),
     (102, 'http://www.yahoo.com', 'Yahoo', NULL)

Using MODEL, MODELS (recommended)

This notation is recommended, because model types will add type and pointer safety to insert query.

tutorial := model.Link{
    ID:   100,
    URL:  "http://www.postgresqltutorial.com",
    Name: "PostgreSQL Tutorial",
}

google := model.Link{
    ID:   101,
    URL:  "http://www.google.com",
    Name: "Google",
}

yahoo := model.Link{
    ID:   102,
    URL:  "http://www.yahoo.com",
    Name: "Yahoo",
}

insertStmt := Link.INSERT(Link.ID, Link.URL, Link.Name, Link.Description).
    MODEL(turorial).
    MODEL(google).
    MODEL(yahoo)

Or event shorter if model data is in the slice:

insertStmt := Link.INSERT(Link.ID, Link.URL, Link.Name, Link.Description).
    MODELS([]model.Link{turorial, google, yahoo})

Link.ID, Link.URL, Link.Name, Link.Description - is the same as Link.AllColumns so above statement can be simplified to:

insertStmt := Link.INSERT(Link.AllColumns).
    MODELS([]model.Link{turorial, google, yahoo})

Link.ID is a primary key autoincrement column so it can be omitted in INSERT statement.
Link.MutableColumns - is shorthand notation for list of all columns minus primary key columns.

insertStmt := Link.INSERT(Link.MutableColumns).
    MODELS([]model.Link{turorial, google, yahoo})

ColumnList can be used to pass a custom list of columns to the INSERT query:

columnList := ColumnList{Link.Name, Link.Description}
insertStmt := Link.INSERT(columnList).
    MODEL(turorial)

Inserts using VALUES, MODEL and MODELS can appear as the part of the same insert statement.

insertStmt := Link.INSERT(Link.ID, Link.URL, Link.Name, Link.Description).
    VALUES(101, "http://www.google.com", "Google", DEFAULT, DEFAULT).
    MODEL(turorial).
    MODELS([]model.Link{yahoo})

Insert using query

// duplicate first 10 entries
insertStmt := Link.
    INSERT(Link.URL, Link.Name).
    QUERY(
        SELECT(Link.URL, Link.Name).
            FROM(Link).
            WHERE(Link.ID.GT(Int(0)).AND(Link.ID.LT_EQ(10))),
    )

Upsert

[PostgreSQL, SQLite] Insert with ON CONFLICT update

  • ON CONFLICT DO NOTHING
Employee.INSERT(Employee.AllColumns).
MODEL(employee).
ON_CONFLICT(Employee.EmployeeID).DO_NOTHING()
  • ON CONFLICT DO UPDATE
Link.INSERT(Link.ID, Link.URL, Link.Name, Link.Description).
VALUES(100, "http://www.postgresqltutorial.com", "PostgreSQL Tutorial", DEFAULT).
ON_CONFLICT(Link.ID).DO_UPDATE(
    SET(
        Link.ID.SET(Link.EXCLUDED.ID),
        Link.URL.SET(String("http://www.postgresqltutorial2.com")),
    ),
)
  • ON CONFLICT DO UPDATE WHERE
Link.INSERT(Link.ID, Link.URL, Link.Name, Link.Description).
VALUES(100, "http://www.postgresqltutorial.com", "PostgreSQL Tutorial", DEFAULT).
ON_CONFLICT(Link.ID).
    WHERE(Link.ID.MUL(Int(2)).GT(Int(10))).
    DO_UPDATE(
        SET(
            Link.ID.SET(
                IntExp(SELECT(MAXi(Link.ID).ADD(Int(1))).
                    FROM(Link)),
            ),
            ColumnList{Link.Name, Link.Description}.SET(ROW(Link.EXCLUDED.Name, String("new description"))),
        ).WHERE(Link.Description.IS_NOT_NULL()),
    )

[MySQL] Insert with ON DUPLICATE KEY UPDATE

Link.INSERT().
	VALUES(randId, "http://www.postgresqltutorial.com", "PostgreSQL Tutorial", DEFAULT).
	ON_DUPLICATE_KEY_UPDATE(
    		Link.ID.SET(Link.ID.ADD(Int(11))),
    		Link.Name.SET(String("PostgreSQL Tutorial 2")),
	)
  • New rows aliased
Link.INSERT().
	MODEL(model.Link{
		{
			ID:          randId,
			URL:         "https://www.postgresqltutorial.com",
			Name:        "PostgreSQL Tutorial",
			Description: nil,
		},
	}).AS_NEW().      // Note !!! 
	ON_DUPLICATE_KEY_UPDATE(
		Link.URL.SET(Link.NEW.URL),
		Link.Name.SET(Link.NEW.Name),
	)

Execute statement

To execute insert statement and get sql.Result:

res, err := insertStmt.Exec(db)

To execute insert statement and return records inserted, insert statement has to have RETURNING clause:

insertStmt := Link.INSERT(Link.ID, Link.URL, Link.Name, Link.Description).
    VALUES(100, "http://www.postgresqltutorial.com", "PostgreSQL Tutorial", DEFAULT).
    VALUES(101, "http://www.google.com", "Google", DEFAULT).
    RETURNING(Link.ID, Link.URL, Link.Name, Link.Description)  // or RETURNING(Link.AllColumns)
    
dest := []model.Link{}

err := insertStmt.Query(db, &dest)

Use ExecContext and QueryContext to provide context object to execution.

SQL table used for the example:
CREATE TABLE IF NOT EXISTS link (
    id serial PRIMARY KEY,
    url VARCHAR (255) NOT NULL,
    name VARCHAR (255) NOT NULL,
    description VARCHAR (255)
);
Clone this wiki locally