[FORMATTING] Formatter inserts space in between ^@ operator, making the SQL invalid

[felixfbecker]

Input data

Which SQL and options did you provide as input?

SELECT 'foo' ^@ ANY ('{f,b}')

Expected Output

SELECT 'foo' ^@ ANY ('{f,b}')

Actual Output

SELECT 'foo' ^ @ ANY ('{f,b}')

which breaks the SQL with

ERROR:  syntax error at or near "ANY"

The ^@ operator is equivalent to starts_with(), but starts_with cannot be used as a workaround when using ANY.

Usage

• How are you calling / using the library? prettier-plugin-sql
• What SQL language(s) does this apply to? PostgreSQL
• Which SQL Formatter version are you using? 15.0.2

[nene]

Thanks for reporting. Looks like ^@ is another one of PostgreSQL's billion operators I've managed to not include.

Additionaly, don't use prettier-sql. See the FAQ

[nene]

Fixed in v15.3.1

[karlhorky]

TIL about the ^@ operator, interesting 👀

Seems like documentation around it is hard to locate, but you can confirm existence of it in pg_operator:

=> SELECT oprname, oprleft::regtype, oprright::regtype, oprresult::regtype, oprcode::regproc FROM pg_operator WHERE oprname = '^@';
 oprname | oprleft | oprright | oprresult |   oprcode
---------+---------+----------+-----------+-------------
 ^@      | text    | text     | boolean   | starts_with
(1 row)

Simple example query:

=> SELECT 'example' ^@ 'ex';
 ?column?
----------
 t
(1 row)

[felixfbecker]

Awesome! The docs are here btw: https://www.postgresql.org/docs/15/functions-string.html#:~:text=text-,%5E%40,-text%20%E2%86%92%20boolean

[felixfbecker]

Additionaly, don't use prettier-sql. See the FAQ

I'm not using the VS Code extension, but the prettier-plugin-sql Prettier plugin. Is that discouraged too?

[nene]

That one should be fine :)

You should be even able to upgrade the sql-formatter lib and have this plugin pick up the latest version. Or you could send a dependency update PR to that plugin repo.