Snowflake parsing error when creating table with casted default column values

[rafal-dabrowski]

Search before asking

• I searched the issues and found no similar issues.

What Happened

When i have a CREATE TABLE statement, that defines a default value with a explicit type casting - parsing error occurs.

Expected Behaviour

table is properly parsed

Observed Behaviour

== [bug.sql] FAIL
L: 1 | P: 32 | PRS | Line 1, Position 32: Found unparsable section: " (\n txt
| TEXT NOT NULL DEFAULT 'bug'::..."
WARNING: Parsing errors found and dialect is set to 'snowflake'. Have you configured your dialect correctly?

How to reproduce

create file bug.sql

CREATE OR REPLACE TABLE stg.bug (
txt TEXT NOT NULL DEFAULT 'bug'::text
)

sqlfluff lint bug.sql

Dialect

snowflake, with jinja templater

Version

sqlfluff --version
sqlfluff, version 1.4.2

Configuration

[sqlfluff]
templater = jinja
dialect = snowflake
sql_file_exts = .sql

rules = all

warnings = L016, L044, L057
exclude_rules = L031
output_line_length = 80

[sqlfluff:rules]
tab_space_size = 4
max_line_length = 120
indent_unit = space

[sqlfluff:rules:L010]
capitalisation_policy = upper

[sqlfluff:rules:L014]
extended_capitalisation_policy = lower
unquoted_identifiers_policy = all

[sqlfluff:rules:L028]
single_table_references = qualified

[sqlfluff:rules:L030]
extended_capitalisation_policy = lower

[sqlfluff:rules:L040]
capitalisation_policy = upper

[sqlfluff:rules:L063]
#data types capitalisation
extended_capitalisation_policy = upper

[sqlfluff:rules:L042]
forbid_subquery_in = both

[sqlfluff:rules:L054]
group_by_and_order_by_style = explicit

Are you willing to work on and submit a PR to address the issue?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[NelsonTorres]

I can issue a PR to fix this but I wonder if casting in the default is a good approach because the column already has the type

[rafal-dabrowski]

I think that it is not a good practice, but i would exect to have a rule to check it rather than parsing error that stops everything :)