nebius/SWE-rebench-V2-PRs huggingface dataset:
Repository: sql-formatter-org/sql-formatter
Pull request #574 (2023-03-08 22:03:09)

Add FOR UPDATE clause support for Postgres
Like #421, but for Postgres.

IMO things look better if `OF` (if present) is placed on the same like as `FOR ...` and any specific columns are placed on a separate line. I make this change for PL/SQL as well, but I'd be happy to revert that if you feel differently.


Patch:
diff --git a/src/languages/plsql/plsql.formatter.ts b/src/languages/plsql/plsql.formatter.ts
index bcb4258b..e484145b 100644
--- a/src/languages/plsql/plsql.formatter.ts
+++ b/src/languages/plsql/plsql.formatter.ts
@@ -17,7 +17,7 @@ const reservedClauses = expandPhrases([
   'ORDER [SIBLINGS] BY',
   'OFFSET',
   'FETCH {FIRST | NEXT}',
-  'FOR UPDATE',
+  'FOR UPDATE [OF]',
   // Data manipulation
   // - insert:
   'INSERT [INTO | ALL INTO]',
diff --git a/src/languages/postgresql/postgresql.formatter.ts b/src/languages/postgresql/postgresql.formatter.ts
index 5cce19a9..3c269953 100644
--- a/src/languages/postgresql/postgresql.formatter.ts
+++ b/src/languages/postgresql/postgresql.formatter.ts
@@ -18,6 +18,7 @@ const reservedClauses = expandPhrases([
   'LIMIT',
   'OFFSET',
   'FETCH {FIRST | NEXT}',
+  'FOR {UPDATE | NO KEY UPDATE | SHARE | KEY SHARE} [OF]',
   // Data manipulation
   // - insert:
   'INSERT INTO',


Test patch:
diff --git a/test/plsql.test.ts b/test/plsql.test.ts
index b1f6010d..fba1ddf0 100644
--- a/test/plsql.test.ts
+++ b/test/plsql.test.ts
@@ -159,8 +159,8 @@ describe('PlSqlFormatter', () => {
         *
       FROM
         tbl
-      FOR UPDATE
-        OF tbl.salary;
+      FOR UPDATE OF
+        tbl.salary;
     `);
   });
 });
diff --git a/test/postgresql.test.ts b/test/postgresql.test.ts
index 5ebe5c14..d50e850c 100644
--- a/test/postgresql.test.ts
+++ b/test/postgresql.test.ts
@@ -262,4 +262,26 @@ describe('PostgreSqlFormatter', () => {
       DROP NOT NULL;
     `);
   });
+
+  it('formats FOR UPDATE clause', () => {
+    expect(
+      format(`
+        SELECT * FROM tbl FOR UPDATE;
+        SELECT * FROM tbl FOR UPDATE OF tbl.salary;
+      `)
+    ).toBe(dedent`
+      SELECT
+        *
+      FROM
+        tbl
+      FOR UPDATE;
+
+      SELECT
+        *
+      FROM
+        tbl
+      FOR UPDATE OF
+        tbl.salary;
+    `);
+  });
 });
Repository: sql-formatter-org/sql-formatter
Pull request #608 (2023-06-08 14:35:39)

Add Additional TSQL Data Definitions
Linked to #606 and a duplicate of #607 


Patch:
diff --git a/src/languages/transactsql/transactsql.formatter.ts b/src/languages/transactsql/transactsql.formatter.ts
index 9bfbf385..5e473e0e 100644
--- a/src/languages/transactsql/transactsql.formatter.ts
+++ b/src/languages/transactsql/transactsql.formatter.ts
@@ -29,7 +29,8 @@ const reservedClauses = expandPhrases([
   'UPDATE SET',
   // Data definition
   'CREATE [OR ALTER] [MATERIALIZED] VIEW',
-  'CREATE TABLE',
+  'CREATE [OR ALTER] TABLE',
+  'CREATE [OR ALTER] PROCEDURE',
 ]);
 
 const onelineClauses = expandPhrases([


Test patch:
diff --git a/test/transactsql.test.ts b/test/transactsql.test.ts
index 83ea3142..eacc919a 100644
--- a/test/transactsql.test.ts
+++ b/test/transactsql.test.ts
@@ -117,4 +117,28 @@ describe('TransactSqlFormatter', () => {
       ALTER COLUMN foo INT NOT NULL DEFAULT 5;
     `);
   });
+
+  it('formats SET NOCOUNT ON; GO CREATE OR ALTER TABLE', () => {
+    const result = format('SET NOCOUNT ON; GO CREATE OR ALTER TABLE t');
+    expect(result).toBe(dedent`
+    SET
+    NOCOUNT ON;
+
+    GO
+    CREATE OR ALTER TABLE
+      t
+    `);
+  });
+
+  it('formats SET NOCOUNT ON; GO CREATE OR ALTER PROCEDURE', () => {
+    const result = format('SET NOCOUNT ON; GO CREATE OR ALTER PROCEDURE p');
+    expect(result).toBe(dedent`
+    SET
+    NOCOUNT ON;
+
+    GO
+    CREATE OR ALTER PROCEDURE
+      p
+    `);
+  });
 });
Repository: sql-formatter-org/sql-formatter
Pull request #609 (2023-06-09 18:59:40)

Add INTO Clause and Remove Invalid Data Definition
Reverting a change I had made previously and adding `INTO` as a reserved clause.


Patch:
diff --git a/src/languages/transactsql/transactsql.formatter.ts b/src/languages/transactsql/transactsql.formatter.ts
index 5e473e0e..f3924294 100644
--- a/src/languages/transactsql/transactsql.formatter.ts
+++ b/src/languages/transactsql/transactsql.formatter.ts
@@ -8,6 +8,7 @@ const reservedSelect = expandPhrases(['SELECT [ALL | DISTINCT]']);
 const reservedClauses = expandPhrases([
   // queries
   'WITH',
+  'INTO',
   'FROM',
   'WHERE',
   'GROUP BY',
@@ -29,8 +30,8 @@ const reservedClauses = expandPhrases([
   'UPDATE SET',
   // Data definition
   'CREATE [OR ALTER] [MATERIALIZED] VIEW',
-  'CREATE [OR ALTER] TABLE',
-  'CREATE [OR ALTER] PROCEDURE',
+  'CREATE TABLE',
+  'CREATE [OR ALTER] {PROC | PROCEDURE}',
 ]);
 
 const onelineClauses = expandPhrases([


Test patch:
diff --git a/test/transactsql.test.ts b/test/transactsql.test.ts
index eacc919a..d48ec415 100644
--- a/test/transactsql.test.ts
+++ b/test/transactsql.test.ts
@@ -118,27 +118,24 @@ describe('TransactSqlFormatter', () => {
     `);
   });
 
-  it('formats SET NOCOUNT ON; GO CREATE OR ALTER TABLE', () => {
-    const result = format('SET NOCOUNT ON; GO CREATE OR ALTER TABLE t');
+  it('formats GO CREATE OR ALTER PROCEDURE', () => {
+    const result = format('GO CREATE OR ALTER PROCEDURE p');
     expect(result).toBe(dedent`
-    SET
-    NOCOUNT ON;
-
-    GO
-    CREATE OR ALTER TABLE
-      t
+      GO
+      CREATE OR ALTER PROCEDURE
+        p
     `);
   });
 
-  it('formats SET NOCOUNT ON; GO CREATE OR ALTER PROCEDURE', () => {
-    const result = format('SET NOCOUNT ON; GO CREATE OR ALTER PROCEDURE p');
+  it('formats SELECT ... INTO clause', () => {
+    const result = format('SELECT col INTO #temp FROM tbl');
     expect(result).toBe(dedent`
-    SET
-    NOCOUNT ON;
-
-    GO
-    CREATE OR ALTER PROCEDURE
-      p
+      SELECT
+        col
+      INTO
+        #temp
+      FROM
+        tbl
     `);
   });
 });
Repository: sql-formatter-org/sql-formatter
Pull request #794 (2024-10-25 16:13:05)

added support for decimal values without leading digits
Encountered issue while handling decimal values without leading digits (for example .00457). Lexer was breaking, updated number token regex to support the similar cases and added a corresponding test case.

Sample SQL query:  `SELECT employee_id FROM employees WHERE salary > .456 * 1000000 AND bonus < .0000239 * salary;`
```
An Unexpected Error Occurred
Parse error at token: 456 at line 1 column 51 Unexpected NUMBER token: {"type":"NUMBER","raw":"456","text":"456","start":50}
```


Patch:
diff --git a/src/lexer/Tokenizer.ts b/src/lexer/Tokenizer.ts
index cd6f9bd6..59571720 100644
--- a/src/lexer/Tokenizer.ts
+++ b/src/lexer/Tokenizer.ts
@@ -51,7 +51,7 @@ export default class Tokenizer {
       {
         type: TokenType.NUMBER,
         regex:
-          /(?:0x[0-9a-fA-F]+|0b[01]+|(?:-\s*)?[0-9]+(?:\.[0-9]*)?(?:[eE][-+]?[0-9]+(?:\.[0-9]+)?)?)(?![\w\p{Alphabetic}])/uy,
+          /(?:0x[0-9a-fA-F]+|0b[01]+|(?:-\s*)?(?:[0-9]*\.[0-9]+|[0-9]+(?:\.[0-9]*)?)(?:[eE][-+]?[0-9]+(?:\.[0-9]+)?)?)(?![\w\p{Alphabetic}])/uy,
       },
       // RESERVED_PHRASE is matched before all other keyword tokens
       // to e.g. prioritize matching "TIMESTAMP WITH TIME ZONE" phrase over "WITH" clause.


Test patch:
diff --git a/test/behavesLikeSqlFormatter.ts b/test/behavesLikeSqlFormatter.ts
index d8c90f5e..21e94dcc 100644
--- a/test/behavesLikeSqlFormatter.ts
+++ b/test/behavesLikeSqlFormatter.ts
@@ -279,4 +279,19 @@ export default function behavesLikeSqlFormatter(format: FormatFn) {
         tbl;
     `);
   });
+
+  it('supports decimal values without leading digits', () => {
+    const result = format(`
+	  SELECT employee_id FROM employees WHERE salary > .456 * 1000000 AND bonus < .0000239 * salary;
+	`);
+    expect(result).toBe(dedent`
+      SELECT
+        employee_id
+      FROM
+        employees
+      WHERE
+        salary > .456 * 1000000
+        AND bonus < .0000239 * salary;
+    `);
+  });
 }
Repository: sql-formatter-org/sql-formatter
Pull request #831 (2025-02-21 14:04:39)

Add support for Postgresql JSON and JSONB data types
Hey there,

I noticed some data types in where missing in [`postgres.keywords.ts`.](https://github.com/sql-formatter-org/sql-formatter/blob/93a423bcb552d5158cac153208485256f57e9f71/src/languages/postgresql/postgresql.keywords.ts#L121) There is a comment in that file linking to the official documentation: https://www.postgresql.org/docs/current/datatype.html. 

We are using the `JSONB` data type in our project so I thought I would add support for `JSON` + `JSONB` in this pull request. There might be more types needed to be added, but for now I thought I'd add these that I knew where missing.

Please let me know if there is anything else I should do to get this pull request merged.


Patch:
diff --git a/src/languages/postgresql/postgresql.keywords.ts b/src/languages/postgresql/postgresql.keywords.ts
index 91a54c9f..4b7cf8be 100644
--- a/src/languages/postgresql/postgresql.keywords.ts
+++ b/src/languages/postgresql/postgresql.keywords.ts
@@ -139,6 +139,8 @@ export const dataTypes: string[] = [
   'INTERVAL', // (cannot be function or type)
   'NCHAR', // (cannot be function or type)
   'NUMERIC', // (cannot be function or type)
+  'JSON',
+  'JSONB',
   'PRECISION', // (cannot be function or type), requires AS
   'REAL', // (cannot be function or type)
   'SMALLINT', // (cannot be function or type)


Test patch:
diff --git a/test/postgresql.test.ts b/test/postgresql.test.ts
index 92967a4a..cd4f9a9e 100644
--- a/test/postgresql.test.ts
+++ b/test/postgresql.test.ts
@@ -390,4 +390,12 @@ describe('PostgreSqlFormatter', () => {
       CREATE OR REPLACE FUNCTION foo ();
     `);
   });
+
+  it('formats JSON and JSONB data types', () => {
+    expect(
+      format(`CREATE TABLE foo (bar json, baz jsonb);`, {
+        dataTypeCase: 'upper',
+      })
+    ).toBe('CREATE TABLE foo (bar JSON, baz JSONB);');
+  });
 });
Repository: sql-formatter-org/sql-formatter
Pull request #851 (2025-03-25 21:59:49)

fix(postgresql): add options to create statements
Many of the `CREATE ...` statements in the postgres dialect were missing options, especially the `OR REPLACE` option that is common to many commands.

Fixes #850

codesandbox-ci[bot]: This pull request is automatically built and testable in [CodeSandbox](https://codesandbox.io).

  To see build info of the built libraries, click [here](https://ci.codesandbox.io/status/sql-formatter-org/sql-formatter/pr/851/builds/601119) or the icon next to each commit SHA.

Patch:
diff --git a/src/languages/postgresql/postgresql.formatter.ts b/src/languages/postgresql/postgresql.formatter.ts
index e46fddce..27bdfe5e 100644
--- a/src/languages/postgresql/postgresql.formatter.ts
+++ b/src/languages/postgresql/postgresql.formatter.ts
@@ -116,10 +116,10 @@ const tabularOnelineClauses = expandPhrases([
   'COMMIT PREPARED',
   'COPY',
   'CREATE ACCESS METHOD',
-  'CREATE AGGREGATE',
+  'CREATE [OR REPLACE] AGGREGATE',
   'CREATE CAST',
   'CREATE COLLATION',
-  'CREATE CONVERSION',
+  'CREATE [DEFAULT] CONVERSION',
   'CREATE DATABASE',
   'CREATE DOMAIN',
   'CREATE EVENT TRIGGER',
@@ -128,18 +128,18 @@ const tabularOnelineClauses = expandPhrases([
   'CREATE FOREIGN TABLE',
   'CREATE [OR REPLACE] FUNCTION',
   'CREATE GROUP',
-  'CREATE INDEX',
-  'CREATE LANGUAGE',
+  'CREATE [UNIQUE] INDEX',
+  'CREATE [OR REPLACE] [TRUSTED] [PROCEDURAL] LANGUAGE',
   'CREATE OPERATOR',
   'CREATE OPERATOR CLASS',
   'CREATE OPERATOR FAMILY',
   'CREATE POLICY',
-  'CREATE PROCEDURE',
+  'CREATE [OR REPLACE] PROCEDURE',
   'CREATE PUBLICATION',
   'CREATE ROLE',
-  'CREATE RULE',
-  'CREATE SCHEMA',
-  'CREATE SEQUENCE',
+  'CREATE [OR REPLACE] RULE',
+  'CREATE SCHEMA [AUTHORIZATION]',
+  'CREATE {TEMPORARY | TEMP | UNLOGGED} SEQUENCE',
   'CREATE SERVER',
   'CREATE STATISTICS',
   'CREATE SUBSCRIPTION',
@@ -148,8 +148,8 @@ const tabularOnelineClauses = expandPhrases([
   'CREATE TEXT SEARCH DICTIONARY',
   'CREATE TEXT SEARCH PARSER',
   'CREATE TEXT SEARCH TEMPLATE',
-  'CREATE TRANSFORM',
-  'CREATE TRIGGER',
+  'CREATE [OR REPLACE] TRANSFORM',
+  'CREATE [OR REPLACE] [CONSTRAINT] TRIGGER',
   'CREATE TYPE',
   'CREATE USER',
   'CREATE USER MAPPING',


Test patch:
diff --git a/test/postgresql.test.ts b/test/postgresql.test.ts
index cd4f9a9e..c725b57b 100644
--- a/test/postgresql.test.ts
+++ b/test/postgresql.test.ts
@@ -398,4 +398,11 @@ describe('PostgreSqlFormatter', () => {
       })
     ).toBe('CREATE TABLE foo (bar JSON, baz JSONB);');
   });
+
+  // Issue #850
+  it('supports OR REPLACE in CREATE PROCEDURE', () => {
+    expect(format(`CREATE OR REPLACE PROCEDURE foo () LANGUAGE sql AS $$ BEGIN END $$;`)).toBe(
+      dedent`CREATE OR REPLACE PROCEDURE foo () LANGUAGE sql AS $$ BEGIN END $$;`
+    );
+  });
 });