INSERT (original) (raw)

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.AllColumnsso 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

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:

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) );