Cannot parse table alias or cte alias in Postgres dialect

[KennyChenFight]

Sorry for bothering you.
I'm using sqlparser = "0.40.0"
This is my code:

use sqlparser::dialect::PostgreSqlDialect;
use sqlparser::parser::{Parser, ParserError};

fn main() {
    let dialect = PostgreSqlDialect {};

    let sql1 = r#"
    WITH existing AS (SELECT test_table.id FROM test_tables AS test_table WHERE (a = 12) AND (b = 34)),
     inserted AS (INSERT INTO test_tables AS test_table (id, a, b, c) VALUES (DEFAULT, 56, 78, 90) ON CONFLICT (a, b)
     DO UPDATE SET c = EXCLUDED.c WHERE (test_table.c != EXCLUDED.c)) SELECT c FROM existing"#;

    let sql2 = "INSERT INTO test_tables AS test_table (id, a) VALUES (DEFAULT, 123) ON CONFLICT DO NOTHING RETURNING id";

    let ast1 = Parser::parse_sql(&dialect, sql1);
    match ast1 {
        Ok(_) => {

        }
        Err(e) => {
            println!("fail ast1: {}", e);
        }
    }
    let ast2 = Parser::parse_sql(&dialect, sql2);
    match ast2 {
        Ok(_) => {
        }
        Err(e) => {
            println!("fail ast2: {}", e);
        }
    }
}

error output:

fail ast1: sql parser error: Expected SELECT, VALUES, or a subquery in the query body, found: AS at Line: 3, Column 43
fail ast2: sql parser error: Expected SELECT, VALUES, or a subquery in the query body, found: AS at Line: 1, Column 25

Thank you!

[alamb]

It would be great to figure out what is going on here and add the relevant support

[boydjohnson]

I'm interested in working on this issue.

So adding these lines of code under https://github.com/sqlparser-rs/sqlparser-rs/blob/main/src/parser/mod.rs#L7459

let alias = if dialect_of!(self is PostgreSqlDialect) && self.parse_keyword(Keyword::AS)
{
    Some(self.parse_identifier()?)
} else {
    None
};

will cause both of the insert statements above to parse without error. The problem is then that the Statement::Insert uses the type ObjectName for table_name. My first thought is to add a table_alias field to Insert, and not change the type of table_name.

Do others have thoughts on where to store the alias field?

[alamb]

I wonder if it would be apprpriate to use IdentWithAlias instead of ObjectName 🤔

[boydjohnson]

So wrt IdentWithAlias I have only seen that being used for column renames ( select foo as bar from table ) because of the possibility of referring to a table with a dot between schema and table (public.table) the ObjectName is a Vec of Idents.

I looked at at Statement::Delete and Statement::Update which currently handle table aliases by using TableWithJoins.

I think the least disruptive change is to add table_alias: Option<Ident> to Statement::Insert.

I'll have a PR up shortly.