insert.txt

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

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

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

(program
  (statement
    (insert
      (keyword_insert)
      (keyword_into)
      (object_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)
      (object_reference
        name: (identifier))
      (keyword_as)
      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)
      (object_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)
      (object_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
          (object_reference
            name: (identifier)))))))

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

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

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

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

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

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

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

(program
  (statement
    (insert
      (keyword_insert)
      (keyword_into)
      (object_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)
      (object_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)
      (object_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)
      (object_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)
      (object_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)
      (object_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
          (object_reference
            name: (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)
      (object_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)
      (object_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)
      (object_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)
      (object_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)
      (object_reference
        (identifier))
      (select
        (keyword_select)
        (select_expression
          (term
            (field
              (identifier)))
          (term
            (field
              (identifier)))))
      (from
        (keyword_from)
        (relation
          (subquery
            (select
              (keyword_select)
              (select_expression
                (term
                  (all_fields))))
            (from
              (keyword_from)
              (relation
                (object_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)
      (object_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
                (term
                  (all_fields))))
            (from
              (keyword_from)
              (relation
                (object_reference
                  (identifier)))
              (where
                (keyword_where)
                (binary_expression
                  (field
                    (identifier))
                  (literal)))))
          (identifier))))))

================================================================================
Insert from unioned select
================================================================================

INSERT INTO some_table
  (field)
  (SELECT "String value"
   UNION
   SELECT "String value");

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

(program
  (statement
    (insert
      (keyword_insert)
      (keyword_into)
      (object_reference
        (identifier))
      (list
        (column
          (identifier)))
      (set_operation
        (select
          (keyword_select)
          (select_expression
            (term
              (literal))))
        (keyword_union)
        (select
          (keyword_select)
          (select_expression
            (term
              (literal))))))))

================================================================================
COPY FROM STDIN CSV
================================================================================

COPY some_table (a, b, c) FROM STDIN (FORMAT CSV);

--------------------------------------------------------------------------------
(program
  (statement
    (keyword_copy)
    (object_reference
      (identifier))
    (column
      (identifier))
    (column
      (identifier))
    (column
      (identifier))
    (keyword_from)
    (keyword_stdin)
    (keyword_format)
    (keyword_csv)))

================================================================================
COPY FROM FILE
================================================================================

COPY some_table (a, b, c) FROM '/tmp/data.csv' (FORMAT CSV);

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

(program
  (statement
    (keyword_copy)
    (object_reference
      (identifier))
    (column
      (identifier))
    (column
      (identifier))
    (column
      (identifier))
    (keyword_from)
    (keyword_format)
    (keyword_csv)))

================================================================================
ON DUPLICATE KEY UPDATE
================================================================================

INSERT INTO table_1 (pk, col1, col2)
VALUES
  (1, 1, 1),
  (2, 2, 2)
ON DUPLICATE KEY UPDATE
  pk = VALUES(pk),
  col1 = 1,
  col2 = "foo";

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

(program
  (statement
    (insert
      (keyword_insert)
      (keyword_into)
      (object_reference
        name: (identifier))
      (list
        (column
          (identifier))
        (column
          (identifier))
        (column
          (identifier)))
      (keyword_values)
      (list
        (literal)
        (literal)
        (literal))
      (list
        (literal)
        (literal)
        (literal))
      (keyword_on)
      (keyword_duplicate)
      (keyword_key)
      (keyword_update)
      (assignment_list
        (assignment
          left: (field
            name: (identifier))
          right: (invocation
            (object_reference
              name: (identifier))
            parameter: (term
              value: (field
                name: (identifier)))))
        (assignment
          left: (field
            name: (identifier))
          right: (literal))
        (assignment
          left: (field
            name: (identifier))
          right: (literal))))))

================================================================================
COPY FROM STDIN CSV WITH OPTIONS
================================================================================

COPY some_table (a, b, c) FROM STDIN WITH
(
    FORMAT CSV
    DELIMITER ';'
    QUOTE '"'
    ESCAPE '\'
    FREEZE true
    HEADER MATCH
    NULL 'NaN'
);

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

(program
  (statement
    (keyword_copy)
    (object_reference
      (identifier))
    (column
      (identifier))
    (column
      (identifier))
    (column
      (identifier))
    (keyword_from)
    (keyword_stdin)
    (keyword_with)
    (keyword_format)
    (keyword_csv)
    (keyword_delimiter)
    (identifier)
    (keyword_quote)
    (identifier)
    (keyword_escape)
    (identifier)
    (keyword_freeze)
    (keyword_true)
    (keyword_header)
    (keyword_match)
    (keyword_null)
    (identifier)))