Skip to content

Support postgresql on commit for ctas #1274

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
fdzuJ opened this issue May 17, 2024 · 4 comments
Open

Support postgresql on commit for ctas #1274

fdzuJ opened this issue May 17, 2024 · 4 comments

Comments

@fdzuJ
Copy link

fdzuJ commented May 17, 2024

Add support for postgresql [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]. Currently panics:

use sqlparser::dialect::PostgreSqlDialect;
use sqlparser::parser::Parser;
 
fn main() {
    let sql = "create local temporary table test on commit preserve rows as select 1 as a;";
 
    let dialect = PostgreSqlDialect {};
 
    let ast = Parser::parse_sql(&dialect, sql).unwrap();
 
    for stmt in ast.iter() {
        println!("{}", stmt.to_string())
    }
}

ParserError("Expected end of statement, found: as at Line: 1, Column 59")

@git-hulk
Copy link
Member

git-hulk commented Jul 10, 2024

Hi @fdzuJ

I cannot find where mentioned the syntax that supports the alias after ON COMMIT clause, seems it only supports [TABLESPACE tablespace_name] according to Postgre documentation: https://www.postgresql.org/docs/current/sql-createtable.html.

It will be helpful if you can provide the reference and I'm happy to support this feature if it did miss in sqlparse-rs.

@fdzuJ
Copy link
Author

fdzuJ commented Jul 11, 2024

Hi @git-hulk!
Here is documentation for this syntax.

https://www.postgresql.org/docs/current/sql-createtableas.html

@git-hulk
Copy link
Member

@fdzuJ Thank you! I will submit a PR to resolve this.

git-hulk added a commit to git-hulk/sqlparser-rs that referenced this issue Jul 12, 2024
According to MySQL[1] and PostgreSQL[2] create table syntax,
the AS query should be sit after the table options. But it
is parsed before table options include CHARSET and COLLATE,
as well as ON COMMIT clause. This PR resolves this issue by
moving the position of AS query, and the issue apache#1274 will be
aslo resolved after this PR.

MySQL:

```
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression
```

PostgreSQL:

```
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    [ (column_name [, ...] ) ]
    [ USING method ]
    [ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]
```

[1] https://dev.mysql.com/doc/refman/8.0/en/create-table.html
[2] https://www.postgresql.org/docs/current/sql-createtableas.html
@git-hulk
Copy link
Member

@fdzuJ Would you mind having a try at the latest version, it should be resolved now. cc @alamb

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants