Repository: sqlfluff/sqlfluff
Pull request: Improve datatypes: CHARACTER VARYING for ANSI, and Postgres and also TIMESTAMP AT TIME ZONE for Postgres
### Brief summary of the change made
Added CHARACTER VARYING as parsable data types to ANSI
Added CHARACTER VARYING and TIMESTAMP AT TIME ZONE "MY_TIMEZONE" as parsable data types to Postgres

Fixes #1331 
Fixes #733

### Are there any other side effects of this change that we should be aware of?
No

### Pull Request checklist
Tests added for both of the bugs' examples. 


Problem statement:
[Postgres] unparseable error on `character varying` type
-- If this is a parsing or linting issue, please include a minimal SQL example which reproduces the issue, along with the `sqlfluff parse` output, `sqlfluff lint` output and `sqlfluff fix` output when relevant. Finally, please add any labels that are related to the issue before submitting so that it can be addressed easily! --

## Expected Behaviour

```sql
CREATE TABLE main.test_table (
    "col1" character varying(40) NOT NULL
);
```

Linting the DDL statement would be success.


## Observed Behaviour

The linting would be failed.

```bash
$ sqlfluff lint --dialect postgres postgres/table/test.sql
== [postgres/table/test.sql] FAIL
L:   1 | P:   1 |  PRS | Found unparsable section: 'CREATE TABLE main.test_table
                       | (\n "col1...'
```

If we update the DDL to use `varchar` instead linting will success.

```diff
CREATE TABLE main.test_table (
-    "col1" character varying(40) NOT NULL
+    "col1" varchar(40) NOT NULL
);
```



## Version

```bash
$ sqlfluff --version
sqlfluff, version 0.3.6

$ python -V
Python 3.9.1
```

## Configuration

No configurations, using default.


Parsing support for postgres `at time zone`
Hello folks, Postgres's `at time zone` conversion syntax seems to throw a parser error. Seems like `with time zone`/`without time zone` also. Postgres doesn't seem to have a function call version so I believe this is the only way to do it.

`example.sql`

```sql
SELECT ('2015-10-24 16:38:46'::timestamp AT TIME ZONE 'UTC');
```

## Observed Behaviour

```
==== sqlfluff ====
sqlfluff:                0.6.4 python:                 3.7.11
implementation:        cpython dialect:              postgres
verbosity:                   1 templater:               jinja

==== readout ====

=== [ path: example.sql ] ===

== [example.sql] FAIL
L:   1 | P:  41 |  PRS | Line 1, Position 41: Found unparsable section: " AT TIME
                       | ZONE 'UTC'"
==== summary ====
violations:        1 status:         FAIL
All Finished 📜 🎉!
```

## Steps to Reproduce

```bash
sqlfluff lint --dialect postgres -v example.sql 
```

Hints from issue discussion:

Commit message: Regenerated yml

Patch to review:
diff --git a/src/sqlfluff/dialects/dialect_ansi.py b/src/sqlfluff/dialects/dialect_ansi.py
index 5db776a98a3..c4aec27b53c 100644
--- a/src/sqlfluff/dialects/dialect_ansi.py
+++ b/src/sqlfluff/dialects/dialect_ansi.py
@@ -527,19 +527,28 @@ class DatatypeSegment(BaseSegment):
         Sequence(
             OneOf("time", "timestamp"),
             Bracketed(Ref("NumericLiteralSegment"), optional=True),
-            Sequence(OneOf("WITH", "WITHOUT"), "TIME", "ZONE", optional=True),
+            OneOf(
+                Sequence(OneOf("WITH", "WITHOUT"), "TIME", "ZONE"),
+                optional=True,
+            ),
         ),
         Sequence(
-            Sequence(
-                # Some dialects allow optional qualification of data types with schemas
+            OneOf(
                 Sequence(
-                    Ref("SingleIdentifierGrammar"),
-                    Ref("DotSegment"),
+                    OneOf("CHARACTER", "BINARY"),
+                    OneOf("VARYING", Sequence("LARGE", "OBJECT")),
+                ),
+                Sequence(
+                    # Some dialects allow optional qualification of data types with schemas
+                    Sequence(
+                        Ref("SingleIdentifierGrammar"),
+                        Ref("DotSegment"),
+                        allow_gaps=False,
+                        optional=True,
+                    ),
+                    Ref("DatatypeIdentifierSegment"),
                     allow_gaps=False,
-                    optional=True,
                 ),
-                Ref("DatatypeIdentifierSegment"),
-                allow_gaps=False,
             ),
             Bracketed(
                 OneOf(
diff --git a/src/sqlfluff/dialects/dialect_postgres.py b/src/sqlfluff/dialects/dialect_postgres.py
index c8b5fa6c797..37fa0a9c80f 100644
--- a/src/sqlfluff/dialects/dialect_postgres.py
+++ b/src/sqlfluff/dialects/dialect_postgres.py
@@ -91,6 +91,56 @@
 )
 
 
+@postgres_dialect.segment(replace=True)
+class DatatypeSegment(BaseSegment):
+    """A data type segment.
+
+    Supports timestamp with(out) time zone. Doesn't currently support intervals.
+    """
+
+    type = "data_type"
+    match_grammar = OneOf(
+        Sequence(
+            OneOf("time", "timestamp"),
+            Bracketed(Ref("NumericLiteralSegment"), optional=True),
+            OneOf(
+                Sequence(OneOf("WITH", "WITHOUT"), "TIME", "ZONE"),
+                Sequence("AT", "TIME", "ZONE", Ref("LiteralGrammar")),
+                optional=True,
+            ),
+        ),
+        Sequence(
+            OneOf(
+                Sequence(
+                    OneOf("CHARACTER", "BINARY"),
+                    OneOf("VARYING", Sequence("LARGE", "OBJECT")),
+                ),
+                Sequence(
+                    # Some dialects allow optional qualification of data types with schemas
+                    Sequence(
+                        Ref("SingleIdentifierGrammar"),
+                        Ref("DotSegment"),
+                        allow_gaps=False,
+                        optional=True,
+                    ),
+                    Ref("DatatypeIdentifierSegment"),
+                    allow_gaps=False,
+                ),
+            ),
+            Bracketed(
+                OneOf(
+                    Delimited(Ref("ExpressionSegment")),
+                    # The brackets might be empty for some cases...
+                    optional=True,
+                ),
+                # There may be no brackets for some data types
+                optional=True,
+            ),
+            Ref("CharCharacterSetSegment", optional=True),
+        ),
+    )
+
+
 @postgres_dialect.segment(replace=True)
 class CreateFunctionStatementSegment(BaseSegment):
     """A `CREATE FUNCTION` statement.
diff --git a/test/fixtures/parser/postgres/postgres_create_table.sql b/test/fixtures/parser/postgres/postgres_create_table.sql
index a5c9909622c..22e9b1bcce0 100644
--- a/test/fixtures/parser/postgres/postgres_create_table.sql
+++ b/test/fixtures/parser/postgres/postgres_create_table.sql
@@ -208,3 +208,7 @@ date_created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 date_updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 operation_id int4 NOT NULL DEFAULT '-1'::integer
 );
+
+CREATE TABLE main.test_table (
+    "col1" character varying(40) NOT NULL
+);
diff --git a/test/fixtures/parser/postgres/postgres_create_table.yml b/test/fixtures/parser/postgres/postgres_create_table.yml
index 037b3d355fc..15870521188 100644
--- a/test/fixtures/parser/postgres/postgres_create_table.yml
+++ b/test/fixtures/parser/postgres/postgres_create_table.yml
@@ -3,7 +3,7 @@
 # computed by SQLFluff when running the tests. Please run
 # `python test/generate_parse_fixture_yml.py`  to generate them after adding or
 # altering SQL files.
-_hash: 2114cc2fc68b4798a524fc3d945965f506344dd3fed86488e876280812496550
+_hash: c8b4a8bec045d020f92027b6c46ac8d8e830ca5f1d533c763430577a2800cdc7
 file:
 - statement:
     create_table_statement:
@@ -1200,3 +1200,28 @@ file:
                 data_type_identifier: integer
       - end_bracket: )
 - statement_terminator: ;
+- statement:
+    create_table_statement:
+    - keyword: CREATE
+    - keyword: TABLE
+    - table_reference:
+      - identifier: main
+      - dot: .
+      - identifier: test_table
+    - bracketed:
+        start_bracket: (
+        column_reference:
+          identifier: '"col1"'
+        data_type:
+        - keyword: character
+        - keyword: varying
+        - bracketed:
+            start_bracket: (
+            expression:
+              literal: '40'
+            end_bracket: )
+        column_constraint_segment:
+        - keyword: NOT
+        - keyword: 'NULL'
+        end_bracket: )
+- statement_terminator: ;
diff --git a/test/fixtures/parser/postgres/postgres_select.sql b/test/fixtures/parser/postgres/postgres_select.sql
index 01c7763373a..f9365ecc506 100644
--- a/test/fixtures/parser/postgres/postgres_select.sql
+++ b/test/fixtures/parser/postgres/postgres_select.sql
@@ -5,3 +5,5 @@ SELECT DATEADD(day, -2, current_date);
 SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00';
 
 SELECT 1.0::int;
+
+SELECT ('2015-10-24 16:38:46'::timestamp AT TIME ZONE 'UTC');
diff --git a/test/fixtures/parser/postgres/postgres_select.yml b/test/fixtures/parser/postgres/postgres_select.yml
index d62f67456e8..0075e4f1641 100644
--- a/test/fixtures/parser/postgres/postgres_select.yml
+++ b/test/fixtures/parser/postgres/postgres_select.yml
@@ -3,7 +3,7 @@
 # computed by SQLFluff when running the tests. Please run
 # `python test/generate_parse_fixture_yml.py`  to generate them after adding or
 # altering SQL files.
-_hash: 80b987a428f17979341c6affd2866ac11ba9353f169ffb2542d90921cbb6eb06
+_hash: df0d1ddb5b2465348990c16a8c91a0bad2ed4534357dbb2791ba93c0dcdc62c6
 file:
 - statement:
     select_statement:
@@ -70,3 +70,23 @@ file:
               data_type:
                 data_type_identifier: int
 - statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          expression:
+            bracketed:
+              start_bracket: (
+              expression:
+                literal: "'2015-10-24 16:38:46'"
+                cast_expression:
+                  casting_operator: '::'
+                  data_type:
+                  - keyword: timestamp
+                  - keyword: AT
+                  - keyword: TIME
+                  - keyword: ZONE
+                  - literal: "'UTC'"
+              end_bracket: )
+- statement_terminator: ;


Code review comments:
diff_hunk: @@ -527,19 +527,28 @@ class DatatypeSegment(BaseSegment):
         Sequence(
             OneOf("time", "timestamp"),
             Bracketed(Ref("NumericLiteralSegment"), optional=True),
-            Sequence(OneOf("WITH", "WITHOUT"), "TIME", "ZONE", optional=True),
+            OneOf(
+                Sequence(OneOf("WITH", "WITHOUT"), "TIME", "ZONE"),
+                optional=True,
+            ),
path: src/sqlfluff/dialects/dialect_ansi.py
text: @user1:
Might as well revert this change now?

```suggestion
            Sequence(OneOf("WITH", "WITHOUT"), "TIME", "ZONE"), optional=True),
```