Postgres support (basic syntax)

[nene]

Basic syntax

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