Upsert and Replace

<insert statement> ::= INSERT INTO <table name> [ AS <alias> ]
    [  ( <attr name> [, <attr name> ]... ) ]
        <values>
    [ ON CONFLICT [ <conflict target> ] <conflict action> ]

<values> ::= DEFAULT VALUES | <values clause>
      | <bag value> | <sub-select>

<values clause> ::= VALUES <value> [, <value>]...

<value> ::= ( { <value expr> | DEFAULT } [, { <value expr> | DEFAULT } ]...)

<conflict target> ::=
    ( <index target> [, <index target>]... )
    | ( { <primary key> | <composite primary key> } )
    | ON CONSTRAINT <constraint name>

<index target> ::= <index attr name>

<conflict action> ::= DO NOTHING
    | DO UPDATE <do update> [ WHERE <condition> ]
    | DO REPLACE <do replace> [ WHERE <condition> ]

<do update> ::= EXCLUDED
    | SET <attr values> [, <attr values>]...

<do replace> ::= EXCLUDED
    | SET <attr values> [, <attr values>]...
    | VALUE <tuple value>

<attr values> ::=  {
            <attr name> = { <value expr> | DEFAULT }
            | ( <attr name> [, <attr name>]... ) = <value>
            | ( <attr name> [, <attr name>]... ) = ( <sub-select> )
        }

<primary key> ::= <attr name>

<composite primary key> ::= <attr name>, <attr name> [, <attr name> ]...

<value expr> ::= <partiql value>
     | <sql value expr>

<partiql value> ::= NULL
    | <ion value>
    | <tuple value>
    | <collection value>

<sql value expr> ::= <sql expression>

<ion value> ::= <backtick> <ion literal> <backtick>

<tuple value> ::= <l curly brace> <r curly brace>
    | <l curly brace> <string value> : <partiql value> [, <string value> : <partiql value>]... <r curly brace>

<collection value> ::= <array value>
    | <bag value>

<array value> ::= <left bracket> { <partiql value> [, <partiql value>]... } <right bracket>

<bag value> ::= <opening bag> { <partiql value> [, <partiql value>]... } <closing bag>

<sub-select> ::= <sfw query>

(* See sfw_query in PartiQL spec. figure 3: BNF Grammar for PartiQL Queries:
https://partiql.org/assets/PartiQL-Specification.pdf *)
<sfw query>

<index attr name> ::= <attr name>
<attr name> ::= <identifier>
<constraint name> ::= <identifier>

(* Literals *)
<l curly brace> ::= “{”
<r curly brace> ::= “}”
<left bracket> ::= “[”
<right bracket> ::= “]”
<backtick> ::= "`"
<opening bag> ::= "<<"
<closing bag> ::= ">>"
<left paren> ::= “(”
 <right paren> ::=  “)”

-- The following `CREATE TABLE` syntax is arbitrary since the PartiQL DDL is yet to be defined:
CREATE TABLE Films /* SCHEMA CLOSED */
(
   code  VARCHAR(40) PRIMARY KEY DEFAULT '1',
   title VARCHAR(100) DEFAULT 'Default Film',
   did   INTEGER DEFAULT 10,
   date_prod DATE DEFAULT NOW()::TIMESTAMP,
   kind VARCHAR(50) DEFAULT 'Comedy',
   len VARCHAR(50)
);

INSERT INTO Films
VALUES ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');

INSERT INTO Films (code, title, did, date_prod, kind)
VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');

INSERT INTO Films
VALUES ('UA503', 'Bananas', 105, DEFAULT, 'Comedy', DEFAULT);

INSERT INTO Films (code, title, did, date_prod, kind)
VALUES ('T_603', 'Yojimbo', 106, DEFAULT, 'Drama');

INSERT INTO films (title, code, did, date_prod, len)
VALUES ('MyTitle', 'MyCode', 108, '1961-06-16', '180 minutes');

SELECT * FROM Films;
<<
{
   'code': 'UA503',
   'title': 'Bananas',
   'did': 105,
   'date_prod': 2022-08-10T,
   'kind': 'Comedy',
   'len': NULL
},
{
   'code': 'T_603',
   'title': 'Yojimbo',
   'did': 106,
   'date_prod': 2022-08-10T,
   'kind': 'Drama',
   'len': NULL
},
{
   'code': 'MyCode',
   'title': 'MyTitle',
   'did': 108,
   'date_prod': 1961-06-16T,
   'kind': 'Comedy',
   'len': '180 minutes'
}
>>

INSERT INTO Films DEFAULT VALUES;

INSERT INTO Films (code, title, did, date_prod, kind)
VALUES ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
       ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

-- The following `CREATE TABLE` syntax is arbitrary since the PartiQL DDL is yet to be defined:
CREATE TABLE Music /* SCHEMA CLOSED */
(
   Artist     VARCHAR(20) NOT NULL,
   SongTitle  VARCHAR(30) NOT NULL,
   PRIMARY KEY (Artist, SongTitle)
);

INSERT INTO Music
<<
{'Artist' : 'Acme Band', 'SongTitle' : 'PartiQL Rocks'},
{'Artist' : 'Emca Band', 'SongTitle' : 'PartiQL Rocks'}
>>;

-- The following `CREATE TABLE` syntax is arbitrary since the PartiQL DDL is yet to be defined:
CREATE TABLE Person /* SCHEMA OPEN */
(
   LastName    VARCHAR(50) NOT NULL,
   FirstName   VARCHAR(20),
   DOB        DATE NOT NULL,
   PRIMARY KEY (LastName)
);

INSERT INTO Person
<<
{'FirstName' : 'Raul',
'LastName' : 'Lewis',
'DOB' : 1963-08-19T,
'GovId' : 'LEWISR261LL',
'GovIdType' : 'Driver License',
},{'LastName' : 'Logan',
'DOB' : 1967-07-03T,
'Address' : '43 Stockert Hollow Road, Everett, WA, 98203'
},{'LastName' : 'Pena',
'DOB' : 1974-02-10T,
'GovId' : '744 849 301',
'GovIdType' : 'SSN',
'Address' : '4058 Melrose Street, Spokane Valley, WA, 99206'
}
>>;

SELECT * FROM Person;
<<
{'LastName' : 'Lewis',
'FirstName' : 'Raul',
'DOB' : 1963-08-19T,
'GovId' : 'LEWISR261LL',
'GovIdType' : 'Driver License',
},
{'LastName' : 'Logan',
'FirstName' : NULL,
'DOB' : 1967-07-03T,
'Address' : '43 Stockert Hollow Road, Everett, WA, 98203'
},
{'LastName' : 'Pena',
'FirstName' : NULL,
'DOB' : 1974-02-10T,
'GovId' : '744 849 301',
'GovIdType' : 'SSN',
'Address' : '4058 Melrose Street, Spokane Valley, WA, 99206'
}
>>;

-- The following `CREATE TABLE` syntax is arbitrary since the PartiQL DDL is yet to be defined:
CREATE TABLE Foo /* SCHEMA OPEN */
(
   id         INT     NOT NULL PRIMARY KEY,
   is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
   title      VARCHAR(50),
   bar        VARCHAR(10)      DEFAULT 'baz',
);

-- In the following, inserting `{ 'id': 1 }` goes through because the rest of the attributes either have default value or
-- are nullable. In addition, because `Foo` has open-schema we can add `value` as a new attribute to the table.
INSERT INTO Foo
<<
{ 'id': 1 },
{ 'id': 2, 'title': 'some-name' },
{ 'id': 3, 'is_deleted': true, 'bar': '10'},
{ 'id': 4, 'title': 'some-other-name', 'value': '10'}
>>;

SELECT * FROM Foo;
<<
{ 'id': 1, 'is_deleted': false, 'title': NULL, 'bar': 'baz' },
{ 'id': 2, 'is_deleted': false, 'title': 'some-name', 'bar': 'baz' },
{ 'id': 3, 'is_deleted': true, 'title': NULL, 'bar': '10' },
{ 'id': 4, 'is_deleted': false, 'title': 'some-other-name', 'bar': 'baz', 'value': '10'},
>>;

-- The following `CREATE TABLE` is arbitrary since the PartiQL DDL is yet to be defined:
CREATE TABLE Music /* SCHEMA OPEN */
(
   Artist     VARCHAR(20) NOT NULL,
   SongTitle  VARCHAR(30) NOT NULL,
   AlbumTitle VARCHAR(25) NOT NULL,
   Year       INT,
   Price      FLOAT,
   Genre      VARCHAR(10),
   PRIMARY KEY (Artist, SongTitle)
);

-- The following `CREATE TABLE` syntax is arbitrary since the PartiQL DDL is yet to be defined:
CREATE TABLE RockAlbums /* SCHEMA CLOSED */
(
    Artist     VARCHAR(20) NOT NULL,
    SongTitle  VARCHAR(30) NOT NULL,
    AlbumTitle VARCHAR(25) NOT NULL,
    Year       INT,
    RockGenre  VARCHAR(10),
    PRIMARY KEY (Artist, SongTitle)
);

-- The following query goes through because `Price` attribute is nullable.
INSERT INTO Music
SELECT *
FROM RockAlbums
WHERE RockGenre IN ('Alternative', 'SpaceRock');

-- The following leads to a `SemanticError` because `RockAlbums` has closed schema.
INSERT INTO RockAlbums
SELECT *
FROM Music;

INSERT INTO Music (Artist, SongTitle, AlbumTitle)
SELECT Artist, SongTitle, AlbumTitle
FROM RockAlbums
WHERE RockGenre IN ('Alternative', 'SpaceRock');

-- The following `CREATE TABLE` syntax is arbitrary since the PartiQL DDL is yet to be defined:
CREATE TABLE Music /* SCHEMA OPEN */
(
   Artist     VARCHAR(20) NOT NULL,
   SongTitle  VARCHAR(30) NOT NULL,
   AlbumTitle VARCHAR(25) NOT NULL,
   Year       INT,
   Price      FLOAT,
   Genre      VARCHAR(10),
   PRIMARY KEY (Artist, SongTitle)
);

-- The following `CREATE TABLE` syntax is arbitrary since the PartiQL DDL is yet to be defined:
CREATE TABLE RockAlbums /* SCHEMA CLOSED */
(
    Artist     VARCHAR(20) NOT NULL,
    SongTitle  VARCHAR(30) NOT NULL,
    AlbumTitle VARCHAR(25) NOT NULL,
    Year       DATE,
    RockGenre  VARCHAR(10),
    PRIMARY KEY (Artist, SongTitle)
);

INSERT INTO Music
SELECT * FROM RockAlbums;

-- The following `CREATE TABLE` syntax is arbitrary since the PartiQL DDL is yet to be defined:
CREATE TABLE Foo /* SCHEMA CLOSED */
(
   id         INT     NOT NULL PRIMARY KEY,
   is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
   title      VARCHAR(50),
   bar        VARCHAR(10) DEFAULT 'baz'
);

INSERT INTO Foo (id, title)
<<
[2, 'some-name'],
>>;

SELECT * FROM Foo;
<<
{ 'id': 2, 'is_deleted': false, 'title': 'some-name', 'bar': 'baz' }
>>;

INSERT INTO Foo
<<
[3, true],
[4, true],
>>;

SELECT * FROM Foo;
<<
{ 'id': 3, 'is_deleted': true, 'title': NULL, 'bar': 'baz' },
{ 'id': 4, 'is_deleted': true, 'title': NULL, 'bar': 'baz' }
>>;

-- The following `CREATE TABLE` syntax is arbitrary since the PartiQL DDL is yet to be defined:
CREATE TABLE Foo /* SCHEMA OPEN */
(
   id         int     NOT NULL PRIMARY KEY,
   is_deleted boolean NOT NULL DEFAULT FALSE,
   title      varchar(50),
   bar        varchar(10) DEFAULT 'baz'
);

-- `SemanticError` because of the presence of target attribute names with bag of tuples.
-- In this case, the behavior for adding or not adding attributes like `is_deleted` is undefined.
INSERT INTO Foo (id, title)
<<
{ 'id': 1 },
{ 'id': 2, 'title': 'some-name' },
{ 'id': 3, 'is_deleted': true, 'title': NULL, 'bar': '10'}
>>;

-- `SemanticError` because not all <bag value> items (row values) can get mapped to the target attributes set.
INSERT INTO Foo (id, title)
<<
[2, 'some-name'],
1,
'some-other-name'
>>;

-- `SemanticError` because INSERT has more target attributes than row values specified by some of the `<bag value>` elements (E.g. `[1]`).
INSERT INTO Foo (id, title)
<<
[1],
[1, 'some_name']
>>;

-- `SemanticError` because usage of `DEFAULT` outside `VALUES(...)` is unsupported.
INSERT INTO Foo (id, title)
<<
[1, DEFAULT],
[2, 'some-name']
>>;

-- `SemanticError` because usage of `DEFAULT` outside `VALUES(...)` is unsupported.
INSERT INTO Foo
<<
{'id': 1, 'is_deleted': DEFAULT},
{'id': 2, 'is_deleted': true}
>>;

INSERT INTO Distributors (did, dname)
VALUES (5, 'Gizmo Transglobal'),
       (6, 'Associated Computing, Inc') ON CONFLICT (did)
       DO UPDATE SET dname = EXCLUDED.dname;

INSERT INTO Distributors
<<
{'id': 5, dname: 'Gizmo Transglobal'},
{'id': 6, dname: 'Associated Computing, Inc'}
>>
ON CONFLICT
    DO
UPDATE SET dname = EXCLUDED.dname;

INSERT INTO distributors
<<
{'id': 5, dname: 'Gizmo Transglobal'},
{'id': 6, dname: 'Associated Computing, Inc'}
>>
ON CONFLICT
    DO
UPDATE EXCLUDED;