insert.txt

================================================================================
Simple insert
================================================================================

INSERT INTO my_table
VALUES('foo','bar', 3);

--------------------------------------------------------------------------------

(program
  (statement
    (insert
      (keyword_insert)
      (keyword_into)
      (table_reference
        name: (identifier))
      (keyword_values)
      (list
        (literal)
        (literal)
        (literal)))))

================================================================================
Simple insert with alias
================================================================================

INSERT INTO my_table AS x
VALUES('foo','bar', 3);

--------------------------------------------------------------------------------

(program
  (statement
    (insert
      (keyword_insert)
      (keyword_into)
      (table_reference
        name: (identifier))
      (keyword_as)
      table_alias: (identifier)
      (keyword_values)
      (list
        (literal)
        (literal)
        (literal)))))

================================================================================
Simple insert with column ordering
================================================================================

REPLACE INTO my_table (name, id, year)
VALUES ('foo', 123, '2020');

--------------------------------------------------------------------------------

(program
  (statement
    (insert
      (keyword_replace)
      (keyword_into)
      (table_reference
        name: (identifier))
      (list
        (column
          (identifier))
        (column
          (identifier))
        (column
          (identifier)))
      (keyword_values)
      (list
        (literal)
        (literal)
        (literal)))))

================================================================================
Insert-select
================================================================================

INSERT INTO my_table (a, b, c)
SELECT a, b, c
FROM my_other_table;

--------------------------------------------------------------------------------

(program
  (statement
    (insert
      (keyword_insert)
      (keyword_into)
      (table_reference
        name: (identifier))
      (list
        (column
          (identifier))
        (column
          (identifier))
        (column
          (identifier)))
      (select
        (keyword_select)
        (select_expression
          (term
            value: (field
              name: (identifier)))
          (term
            value: (field
              name: (identifier)))
          (term
            value: (field
              name: (identifier)))))
      (from
        (keyword_from)
        (relation
          (table_reference
            name: (identifier)))))))

================================================================================
Insert returning
================================================================================

INSERT INTO my_table
VALUES('foo','bar', 3)
RETURNING *;

--------------------------------------------------------------------------------

(program
  (statement
    (insert
      (keyword_insert)
      (keyword_into)
      (table_reference
        name: (identifier))
      (keyword_values)
      (list
        (literal)
        (literal)
        (literal)))
    (returning
      (keyword_returning)
      (select_expression
        (all_fields)))))

================================================================================
Insert returning single column
================================================================================

INSERT INTO my_table
VALUES('foo','bar', 3)
RETURNING id;

--------------------------------------------------------------------------------

(program
  (statement
    (insert
      (keyword_insert)
      (keyword_into)
      (table_reference
        name: (identifier))
      (keyword_values)
      (list
        (literal)
        (literal)
        (literal)))
    (returning
      (keyword_returning)
      (select_expression
        (term
          value: (field
            name: (identifier)))))))

================================================================================
Insert returning multiple defined columns
================================================================================

INSERT INTO my_table
VALUES('foo','bar', 3)
RETURNING id, val1, val2;

--------------------------------------------------------------------------------

(program
  (statement
    (insert
      (keyword_insert)
      (keyword_into)
      (table_reference
        name: (identifier))
      (keyword_values)
      (list
        (literal)
        (literal)
        (literal)))
    (returning
      (keyword_returning)
      (select_expression
        (term
          value: (field
            name: (identifier)))
        (term
          value: (field
            name: (identifier)))
        (term
          value: (field
            name: (identifier)))))))

================================================================================
Insert with multple values
================================================================================

INSERT INTO some_table
  (field)
VALUES
  ("String value"),
  ("String value");

--------------------------------------------------------------------------------

(program
  (statement
    (insert
      (keyword_insert)
      (keyword_into)
      (table_reference
        (identifier))
      (list
        (column
          (identifier)))
      (keyword_values)
      (list
        (literal))
      (list
        (literal)))))

================================================================================
Insert with field name
================================================================================

INSERT INTO some_table
  SET field = "String does not get highlight in INSERT SET syntax";

--------------------------------------------------------------------------------

(program
  (statement
    (insert
      (keyword_insert)
      (keyword_into)
      (table_reference
        name: (identifier))
      (keyword_set)
      (assignment
        left: (field
          name: (identifier))
        right: (literal)))))

================================================================================
Simple insert with on conflict do nothing
================================================================================

INSERT INTO my_table
VALUES('foo','bar', 3)
ON CONFLICT DO NOTHING;

--------------------------------------------------------------------------------

(program
  (statement
    (insert
      (keyword_insert)
      (keyword_into)
      (table_reference
        name: (identifier))
      (keyword_values)
      (list
        (literal)
        (literal)
        (literal))
      (keyword_on)
      (keyword_conflict)
      (keyword_do)
      (keyword_nothing))))

================================================================================
Simple insert with on conflict do update
================================================================================

INSERT INTO my_table
VALUES('foo','bar', 3)
ON CONFLICT DO UPDATE SET dname = EXCLUDED.dname;

--------------------------------------------------------------------------------

(program
  (statement
    (insert
      (keyword_insert)
      (keyword_into)
      (table_reference
        name: (identifier))
      (keyword_values)
      (list
        (literal)
        (literal)
        (literal))
      (keyword_on)
      (keyword_conflict)
      (keyword_do)
      (keyword_update)
      (keyword_set)
      (assignment
        left: (field
          name: (identifier))
        right: (field
          table_alias: (identifier)
          name: (identifier))))))

================================================================================
Simple insert with low priority
================================================================================

INSERT LOW_PRIORITY my_table
VALUES('foo','bar', 3);

--------------------------------------------------------------------------------

(program
  (statement
    (insert
      (keyword_insert)
      (keyword_low_priority)
      (table_reference
        name: (identifier))
      (keyword_values)
      (list
        (literal)
        (literal)
        (literal)))))

================================================================================
Simple insert with delayed
================================================================================

INSERT DELAYED my_table
VALUES('foo','bar', 3);

--------------------------------------------------------------------------------

(program
  (statement
    (insert
      (keyword_insert)
      (keyword_delayed)
      (table_reference
        name: (identifier))
      (keyword_values)
      (list
        (literal)
        (literal)
        (literal)))))

================================================================================
Simple insert with high priority
================================================================================

INSERT HIGH_PRIORITY my_table
VALUES('foo','bar', 3);

--------------------------------------------------------------------------------

(program
  (statement
    (insert
      (keyword_insert)
      (keyword_high_priority)
      (table_reference
        name: (identifier))
      (keyword_values)
      (list
        (literal)
        (literal)
        (literal)))))

================================================================================
Simple insert with ignore
================================================================================

INSERT IGNORE my_table
VALUES('foo','bar', 3);

--------------------------------------------------------------------------------

(program
  (statement
    (insert
      (keyword_insert)
      (keyword_ignore)
      (table_reference
        name: (identifier))
      (keyword_values)
      (list
        (literal)
        (literal)
        (literal)))))

================================================================================
Insert overwrite
================================================================================

INSERT OVERWRITE tab1
SELECT
  col1,
  col2
FROM
(
  SELECT
    *
  FROM
    tab2
  WHERE
    key1 >= 'val'
) a1;

--------------------------------------------------------------------------------

(program
  (statement
    (insert
      (keyword_insert)
      (keyword_overwrite)
      (table_reference
        (identifier))
      (select
        (keyword_select)
        (select_expression
          (term
            (field
              (identifier)))
          (term
            (field
              (identifier)))))
      (from
        (keyword_from)
        (relation
          (subquery
            (select
              (keyword_select)
              (select_expression
                (all_fields)))
            (from
              (keyword_from)
              (relation
                (table_reference
                  (identifier)))
              (where
                (keyword_where)
                (binary_expression
                  (field
                    (identifier))
                  (literal)))))
          (identifier))))))

================================================================================
Insert overwrite with partition
================================================================================

INSERT OVERWRITE tab1
PARTITION (key1 = 'val1', key2 = 'val2')
SELECT
  col1,
  col2
FROM
(
  SELECT
    *
  FROM
    tab2
  WHERE
    key1 >= 'val'
) a1;

--------------------------------------------------------------------------------

(program
  (statement
    (insert
      (keyword_insert)
      (keyword_overwrite)
      (table_reference
        (identifier))
      (table_partition
        (keyword_partition)
        (identifier)
        (literal)
        (identifier)
        (literal))
      (select
        (keyword_select)
        (select_expression
          (term
            (field
              (identifier)))
          (term
            (field
              (identifier)))))
      (from
        (keyword_from)
        (relation
          (subquery
            (select
              (keyword_select)
              (select_expression
                (all_fields)))
            (from
              (keyword_from)
              (relation
                (table_reference
                  (identifier)))
              (where
                (keyword_where)
                (binary_expression
                  (field
                    (identifier))
                  (literal)))))
          (identifier))))))