CREATE TABLE AS — define a new table from the results of a query

[tconbeer]

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

[mlemainque]

Hello, I'm using sqlfmt as it is the best SQL formatter I found, to apply it on the Snowflake SQL dialect, but unfortunately a lot of commands such as this CREATE TABLE AS ... seems not supported yet.

Do you think I can help you solving this? And in the meantime, would you recommend a workaround to make sqlfmt still format the rest of the query?

Thanks a lot 🙏🏻

[tconbeer]

@mlemainque Thanks for your feedback and offer to contribute.

Unfortunately adding new commands like this requires changing lexing rules, which gets complex fast. I can point you to this PR, which implements (snowflake's) create ... clone statement, which is quite a bit simpler than CTAS.

I think it's likely that the implementation for CTAS will be closely related to supporting all create table statements (#300), and I don't know when I'll get around to that -- to be transparent this isn't my highest priority at the moment.

As for a workaround, for safety reasons, sqlfmt interprets create table as a FMT_OFF token, identical to the tokens we use to lex --fmt: off comments. This means you can use a --fmt: on comment to reactivate formatting. In other words this:

create table foo as --fmt: on
    select 1,
    2, 3

Will actually get formatted to this:

create table foo as --fmt: on
select 1, 2, 3

[RoelantStegmann]

That trick doesn't seem to work for me in sqlfmt, version 0.26.0. (I am trying to use sqlfmt for Athena Presto queries and all the CTAS queries are ignored).

Originally I was using a multiline syntax, but the issue remains for single line. Removing the CREATE TABLE line does however activate the format checks.

CREATE TABLE IF NOT EXISTS {table_name}
WITH (
    format='PARQUET',
    external_location='{s3_location}'
)
AS --fmt: on
SELECT 
   ...