nene
Postgres support (basic syntax)
Basic syntax
- [ ] Keywords
- [ ] Some reserved keywords can be used in implicit alias names
- [x] All reserved keywords can be used in explicit
ASalias 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] Standard SQL line 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]
- [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] just dollars:
- [x] Blob literals
- [x] Bit strings:
B'1001orb'0110' - [x] Hex strings:
X'1FA'orx'1FA'
- [x] Bit strings:
- [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] hex literals:
- [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
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?
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.
@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()) );
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?
