[FORMATTING] sqlite UPSERT formatting

[ulikunitz]

Input data

Which SQL and options did you provide as input?

I use Prettier SQL VSCode with sqlite mode.

insert into  test_table(id, name)
values (1, 'foobar') 
on conflict (id) do update
set name = 'foobar';

Expected Output

insert into
        test_table (id, name)
values
        (1, 'foobar')
on conflict (id) do update
set
        name = 'foobar';

Actual Output

insert into
        test_table (id, name)
values
        (1, 'foobar') on conflict (id) do
update
set
        name = 'foobar';

I suggest to move on conflict (id) do to the next line.

Usage

• How are you calling / using the library?

I'm using the library with Prettier SQL VS Code and use sqlite mode.

• What SQL language(s) does this apply to?

Sqlite. Note that sqlite supports multiple on conflict clauses. The syntax is defined here: (https://www.sqlite.org/lang_upsert.html) The page reports that it used the syntax from postfgresql and changed it.

• Which SQL Formatter version are you using?

Prettier SQL VSCode 1.6

[nene]

First, please read the FAQ. There's nothing I can do about the Prettier SQL VSCode extension.

However, this bug does partly exist in the latest SQL Formatter library. There are essentially two issues:

• The ON CONFLICT clause is not printed to a separate line (this is fixed in latest SQL Formatter)
• The UPDATE in DO UPDATE is recognized as start of an UPDATE statement.

The latter can be easily fixed in SQL Formatter. Though I suggest you give a try to prettier-plugin-sql-cst, which has full support for SQLite, and in general does much better job at formatting SQL.