Postgres support (basic syntax)

[nene]

Basic syntax

• [ ] Keywords
  • [ ] Some reserved keywords can be used in implicit alias names
  • [x] All reserved keywords can be used in explicit AS alias names
  • [ ] Restrict certain keywords as function or type names
  • [x] Up-to-date keywords list for PostgreSQL 16
• [x] Comments
  • [x] Standard SQL line comments: -- foo
  • [x] Multiline comments: /* blah */
  • [x] Nested multiline comments: /* foo /*blah*/ bar */
  • [x] Does not support #-comments
• [x] identifiers
  • [x] Identifiers can start with unicode letter or underscore
  • [x] Subsequent characters can be letters, underscores, digits (0-9) and $.
  • [x] Quoted identifiers "foo bar"
  • [x] Unicode identifiers U&"d\0061t\+000061"
  • [x] Unicode identifiers with custom escaping U&"d!0061t!+000061" UESCAPE '!'
• [x] String literals
  • [x] Single-quoted strings
  • [x] Quote escaping with repeating: 'foo''bar'
  • [x] String concatenation with whitespace: 'foo'\n'bar' (but not 'foo' 'bar', must have at least on \n, then any number of spaces can also be included. Can also include line comments, but no block comments.)
    • [x] in plain single-quoted strings
    • [x] in C-style escaped strings
    • [x] in Unicode strings
  • [x] C-style escapes: E'foo\nbar'
    • [x] \b, \f, \n, \r, \t
    • [x] octal: \o, \oo, \ooo
    • [x] hex: \xh, \xhh
    • [x] unicode: \uxxxx, \Uxxxxxxxx
    • [x] quote escaping with both \' and '',
  • [x] Unicode strings: U&'d\0061t\+000061'
  • [x] Unicode strings with custom escape: U&'d!0061t!+000061' UESCAPE '!'
  • [x] Dollar-quoted strings
    • [x] just dollars: $$foo bar$$
    • [x] with tags: $SomeTag$Dianne's horse$SomeTag$
• [x] Blob literals
  • [x] Bit strings: B'1001 or b'0110'
  • [x] Hex strings: X'1FA' or x'1FA'
• [x] Number literals (Postgres >= 16)
  • [x] hex literals: 0xFFFF
  • [x] oct literals: 0o666
  • [x] bin literals: 0b0110
  • [x] underscores in number literals: 10_000_000, 0xFFFF_FFFF, 1.618_034
• [x] Array literals: ARRAY[1,2,3]
  • [ ] probably need a bit of refactor in how we represent it in CST
• [ ] Array constructors: ARRAY( SELECT ... )
• [x] Interval literals: INTERVAL '1 year 3 hours'
• [x] Date/Time literals: TIMESTAMP '2000-01-01T10:30:15', DATE '1999-07-20', TIME '13:30:00'
• [x] JSON literals: JSON '{"key": "value"}'
• [x] JSONB literals: JSONB '{"key": "value"}'
• [x] Row constructors: ROW(1, 2.5, 'this is a test')
• [x] Parameters: $foo, $123

[karlhorky]

Not sure if listing out missing features is wanted in this issue, but I saw on SQL Explorer that now() functions are also not supported for PostgreSQL:

CREATE TABLE users ( created_at timestamp DEFAULT now() );

Syntax Error: Unexpected "now"
Was expecting to see: "(", "DATE", "DATETIME", "FALSE", "NULL", "TIME", "TIMESTAMP", "TRUE", "X", number, or string
--> undefined:1:51
  |
1 | CREATE TABLE users ( created_at timestamp DEFAULT now() );
  |                                                   ^

Maybe this is already covered under Keywords -> Restrict certain keywords as function or type names?

[nene]

Well, always good to have examples of not supported code. Don't really know what the issue might be in here. Possibly indeed related to Postgres keyword handling.

[nene]

@karlhorky FYI, this error happens currently because the DEFAULT value implementation is based on the existing SQLite and BigQuery implementation. In these dialects only literal values or a parenthesized expression can be used as default value. So the following parses fine:

CREATE TABLE users ( created_at timestamp DEFAULT (now()) );

[jming422]

Thanks for your work on this! I've been experimenting with the Prettier plugin using this parser and overall it's been great.

Parameters: $foo

In PostgreSQL, parameters are typically referred to by position number instead of by name (docs) -- would it be possible to add a $nr param type similar to ?nr but with a dollar sign?

[nene]

Yeah, that should be simple to add.

[nene]

@jming422 Should be available in 0.27.1 release.

[jming422]

Should be available in 0.27.1 release.

Woah, thank you, you rock! 🚀

[nene]

Also pushed a new prettier plugin release.