What would be the way to parse postgreSQL with "distributed by" clause?

[cauwt]

>>> import sqlglot      
>>> sqlglot.__version__
'11.5.9'
>>> from sqlglot import parse_one
>>> sql = """ create temp table temp_aaa (
... id varchar(18),
... name varchar(200)
... ) distributed by (id);
... """
>>> parse_one(sql, read='postgres')

running the above got the following error:

sqlglot.errors.ParseError: Invalid expression / Unexpected token. Line 4, Col: 3.
create temp table temp_aaa (
id varchar(18),
name varchar(200)
) distributed by (id);

[tobymao]

do you have documentation for this?

[cauwt]

Yes I have read the sqlglot documentation by forking from github but I didn't find an answer.

[georgesittas]

Hey @cauwt, I think Toby was asking about postgres' documentation on distributed by, so that we can use it as a guide for implementing it; it's currently not supported in SQLGlot. I couldn't find any reference to it in their docs.

[cauwt]

o

Sorry for my misunderstanding. I didn't read the official documentation of postgreSQL. I just checked the database I'm connecting to and found it is actually a Greenplum. So is the solution that I customize a Greenplum dialect which derived Postgres and add some Greenplum-specific functions and tokens?

[georgesittas]

No worries.

So is the solution that I customize a Greenplum dialect which derived Postgres and add some Greenplum-specific functions and tokens?

It depends. For example, is there any other dialect that supports the features you want to use? If this is the case, then maybe we could just add support for them in the base parser / generator so that all corresponding dialects could use them. If not, then extending Postgres through a Greenplum dialect would work in this case, assuming they're similar.