[FORMATTING] DEFAULT VALUES not properly supported in INSERT statement

[jonatanschroeder]

Input data

Which SQL and options did you provide as input?

INSERT INTO workspaces DEFAULT VALUES RETURNING id;

options:

{ language: 'postgresql', paramTypes: { named: ['$'] } }

Expected Output

The main issue is that DEFAULT VALUES should be treated as a unit, as it is distinct from the usual VALUES or the query that typically follows INSERT INTO.

INSERT INTO
  workspaces
DEFAULT VALUES
RETURNING
  id;

Actual Output

INSERT INTO
  workspaces DEFAULT
VALUES
RETURNING
  id;

Usage

• How are you calling / using the library?
  • I'm using prettier, and we created a custom prettier plugin that uses sql-formatter to format the file.
• What SQL language(s) does this apply to?
  • This is specifically for Postgres.
• Which SQL Formatter version are you using?
  • 15.2.0

[nene]

Thanks for reporting.

This can probably be patched in sql-formatter. Though there are many scenarios like that where it fails.

If you want a Prettier plugin to format PostgreSQL, I suggest you try prettier-plugin-sql-cst. It does not yet support full PostgreSQL syntax, but it should work for most of the common scenarios (this issue tracks the current progress of PostgreSQL support) and it does an overall better job at formatting than SQL Formatter will ever be capable of.

[jonatanschroeder]

Thanks for the quick reply.

About the Prettier plugin, I just did a quick check, and it seems to work well in most cases, but there are some cases that it does not support which we use in our case, such as types (CREATE TYPE), DO code statements, FULL JOIN, and a few minor constructs. Also, the fact that it's in experimental stage means it's probably a good idea for us to stick with the custom plugin package has been working for us for the last 12 months until this package is in a more stable setting. That said, it looks promising, and it's likely something to consider in a future release. Also, some of the formatting choices in that plugin seem to me (personally) to make a bit more sense than the ones used in sql-formatter.

[nene]

Thanks for the feedback. Always good to learn which actual features, that people are using, are missing. I can then possibly prioritize these before others.