Repository: shinichi-takii/ddlparse

Issue:
Add Support for RedShift column_attributes 
RedShift DDL:

```sql
CREATE TABLE IF NOT EXISTS "sample_schema"."sample_table"
(
	"staff_flg" CHAR(1) SORTKEY
	,"leader_flg" float(1,1)  DISTKEY
)
;
```

Code:
```py
from ddlparse import DdlParse

sample_ddl = """
CREATE TABLE IF NOT EXISTS "sample_schema"."sample_table"
(
	
	"staff_flg" CHAR(1)  DEFAULT '0'::bpchar ENCODE lzo SORTKEY
	,"leader_flg" float(1,1)  DEFAULT '0'::bpchar ENCODE lzo DISTKEY
)
DISTSTYLE EVEN
;

"""

# parse pattern (1)
table = DdlParse().parse(sample_ddl)

# parse pattern (2)
parser = DdlParse()
parser.ddl = sample_ddl
table = parser.parse()

print("* TABLE *")
print("schema = {} : name = {} : is_temp = {}".format(table.schema, table.name, table.is_temp))

print("* BigQuery Fields *")
print(table.to_bigquery_fields())

print("* BigQuery Fields - column name to lower case / upper case *")
print(table.to_bigquery_fields(DdlParse.NAME_CASE.lower))
print(table.to_bigquery_fields(DdlParse.NAME_CASE.upper))

print("* COLUMN *")
for col in table.columns.values():
    print("name = {} : data_type = {} : length = {} : precision(=length) = {} : scale = {} : constraint = {} : not_null =  {} : PK =  {} : unique =  {} : BQ {}".format(
        col.name,
        col.data_type,
        col.length,
        col.precision,
        col.scale,
        col.constraint,
        col.not_null,
        col.primary_key,
        col.unique,
        col.to_bigquery_field()
        ))

```

Output:
```py
* COLUMN *
name = staff_flg : data_type = CHAR : length = 1 : precision(=length) = 1 : scale = None : constraint =  : not_null =  False : PK =  False : unique =  False : BQ {"name": "staff_flg", "type": "STRING", "mode": "NULLABLE"}
```

Its not moving to the next column after the `SORTKEY` keyword.

Discussion:

Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 55f0b1d..45dfd14 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -5,6 +5,18 @@ The format is based on [Keep a Changelog](https://keepachangelog.com/en/1.0.0/),
 and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0.html).
 
 
+## [1.7.0] - 2020-08-13
+### Added
+- Add `DdlParseColumn.default` property.
+- Add `DdlParseColumn.auto_increment` property.
+- Add `DdlParseColumn.encode` property.
+- Add `DdlParseColumn.distkey` property.
+- Add `DdlParseColumn.sortkey` property.
+
+### Fixed
+- Fix parse of column name with space character.
+
+
 ## [1.6.1] - 2020-08-08
 ### Fixed
 - Fix parse Redshift `ENCODE` attribute.
@@ -166,6 +178,7 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
 - Initial released.
 
 
+[1.7.0]: https://github.com/shinichi-takii/ddlparse/compare/v1.6.1...v1.7.0
 [1.6.1]: https://github.com/shinichi-takii/ddlparse/compare/v1.6.0...v1.6.1
 [1.6.0]: https://github.com/shinichi-takii/ddlparse/compare/v1.5.0...v1.6.0
 [1.5.0]: https://github.com/shinichi-takii/ddlparse/compare/v1.4.0...v1.5.0
diff --git a/README.md b/README.md
index a9b91cc..02134ff 100644
--- a/README.md
+++ b/README.md
@@ -116,22 +116,29 @@ print(table.to_bigquery_fields(DdlParse.NAME_CASE.upper))
 print("* COLUMN *")
 for col in table.columns.values():
     col_info = {}
-    col_info["name"] = col.name
-    col_info["data_type"] = col.data_type
-    col_info["length"] = col.length
-    col_info["precision(=length)"] = col.precision
-    col_info["scale"] = col.scale
-    col_info["is_unsigned"] = col.is_unsigned
-    col_info["is_zerofill"] = col.is_zerofill
-    col_info["constraint"] = col.constraint
-    col_info["not_null"] = col.not_null
-    col_info["PK"] = col.primary_key
-    col_info["unique"] = col.unique
-    col_info["bq_legacy_data_type"] = col.bigquery_legacy_data_type
+
+    col_info["name"]                  = col.name
+    col_info["data_type"]             = col.data_type
+    col_info["length"]                = col.length
+    col_info["precision(=length)"]    = col.precision
+    col_info["scale"]                 = col.scale
+    col_info["is_unsigned"]           = col.is_unsigned
+    col_info["is_zerofill"]           = col.is_zerofill
+    col_info["constraint"]            = col.constraint
+    col_info["not_null"]              = col.not_null
+    col_info["PK"]                    = col.primary_key
+    col_info["unique"]                = col.unique
+    col_info["auto_increment"]        = col.auto_increment
+    col_info["distkey"]               = col.distkey
+    col_info["sortkey"]               = col.sortkey
+    col_info["encode"]                = col.encode
+    col_info["default"]               = col.default
+    col_info["bq_legacy_data_type"]   = col.bigquery_legacy_data_type
     col_info["bq_standard_data_type"] = col.bigquery_standard_data_type
-    col_info["comment"] = col.comment
+    col_info["comment"]               = col.comment
     col_info["description(=comment)"] = col.description
-    col_info["bigquery_field"] = json.loads(col.to_bigquery_field())
+    col_info["bigquery_field"]        = json.loads(col.to_bigquery_field())
+
     print(json.dumps(col_info, indent=2, ensure_ascii=False))
 
 print("* DDL (CREATE TABLE) statements *")
@@ -143,6 +150,7 @@ print(table.to_bigquery_ddl(DdlParse.NAME_CASE.upper))
 
 print("* Get Column object (case insensitive) *")
 print(table.columns["total"])
+print(table.columns["total"].data_type)
 ```
 
 ## License
@@ -151,7 +159,7 @@ print(table.columns["total"])
 
 ## Author
 
-Shinichi Takii <shinichi.takii@gmail.com>
+Shinichi Takii <shinichi.takii@shaketh.com>
 
 ## Links
 
diff --git a/ddlparse/__init__.py b/ddlparse/__init__.py
index 1e327c9..ed0b05b 100644
--- a/ddlparse/__init__.py
+++ b/ddlparse/__init__.py
@@ -1,6 +1,6 @@
 # -*- coding: utf-8 -*-
 #
-# Copyright (C) 2018 Shinichi Takii, shinichi.takii@gmail.com
+# Copyright (C) 2018 Shinichi Takii, shinichi.takii@shaketh.com
 #
 # This module is part of python-ddlparse and is released under
 # the BSD License: https://opensource.org/licenses/BSD-3-Clause
@@ -8,10 +8,10 @@
 from .ddlparse import *
 
 __copyright__    = 'Copyright (C) 2018-2020 Shinichi Takii'
-__version__      = '1.6.1'
+__version__      = '1.7.0'
 __license__      = 'BSD-3-Clause'
 __author__       = 'Shinichi Takii'
-__author_email__ = 'shinichi.takii@gmail.com'
+__author_email__ = 'shinichi.takii@shaketh.com'
 __url__          = 'http://github.com/shinichi-takii/ddlparse'
 
 __all__ = ['DdlParse', 'DdlParseTable', 'DdlParseColumn', 'DdlParseColumnDict']
diff --git a/ddlparse/ddlparse.py b/ddlparse/ddlparse.py
index 0dc81e6..5cb3686 100644
--- a/ddlparse/ddlparse.py
+++ b/ddlparse/ddlparse.py
@@ -1,6 +1,6 @@
 # -*- coding: utf-8 -*-
 #
-# Copyright (C) 2018 Shinichi Takii, shinichi.takii@gmail.com
+# Copyright (C) 2018 Shinichi Takii, shinichi.takii@shaketh.com
 #
 # This module is part of python-ddlparse and is released under
 # the BSD License: https://opensource.org/licenses/BSD-3-Clause
@@ -139,17 +139,76 @@ class DdlParseColumn(DdlParseTableColumnBase):
 
     @constraint.setter
     def constraint(self, constraint):
-        self._constraint = None if constraint is None else constraint.upper()
 
-        self._not_null = False if self._constraint is None or not re.search(r"(NOT NULL|PRIMARY KEY)", self._constraint) else True
-        self._pk = False if self._constraint is None or not re.search("PRIMARY KEY", self._constraint) else True
-        self._unique = False if self._constraint is None or not re.search("UNIQUE", self._constraint) else True
+        # Compatibility v1.6.1 and earlier
+        if type(constraint) is str:
+            self._constraint = None if constraint is None else constraint.upper()
+
+            self._not_null = False if self._constraint is None or not re.search(r"(NOT NULL|PRIMARY KEY)", self._constraint) else True
+            self._pk = False if self._constraint is None or not re.search("PRIMARY KEY", self._constraint) else True
+            self._unique = False if self._constraint is None or not re.search("UNIQUE", self._constraint) else True
+
+            self._comment = None
+            if constraint is not None:
+                matches = re.findall(r"(?:\bCOMMENT\b\s+)(['\"])(.+)\1", constraint, re.IGNORECASE)
+                if len(matches) > 0:
+                    self._comment = matches[0][1]
+
+            return
+
+
+        # v1.7.0 or later
+
+        self._constraint = None if constraint is None else ' '.join(constraint).upper()
+
+        constraints = {}
+        constraints['null'] = ''
+        constraints['auto_increment'] = ''
+        constraints['key'] = ''
+        constraints['default'] = ''
+        constraints['comment'] = ''
+        constraints['encode'] = ''
+        constraints['distkey'] = ''
+        constraints['sortkey'] = ''
+
+        if constraint:
+            for constraint_name, val in constraint.items():
+                constraints[constraint_name] = val
+
+
+        self._pk = True if re.search("PRIMARY", constraints['key'], re.IGNORECASE) else False
+
+        self._not_null = False
+        if self._pk or re.search(r"(NOT\s+NULL)", constraints['null'], re.IGNORECASE):
+            self._not_null = True
+
+        self._unique = True if re.search("UNIQUE", constraints['key'], re.IGNORECASE) else False
+
+        self._auto_increment = True if len(constraints['auto_increment']) > 0 else False
+        self._distkey        = True if len(constraints['distkey']) > 0 else False
+        self._sortkey        = True if len(constraints['sortkey']) > 0 else False
+
+        self._encode = None
+        if constraint is not None:
+            matches = re.findall(r"\bENCODE\s+([A-Za-z0-9]+)\b", constraints['encode'], re.IGNORECASE)
+            if len(matches) > 0:
+                self._encode = matches[0]
+
+        self._default = None
+        if constraint is not None:
+            matches = re.findall(
+                r"\bDEFAULT\b\s+(?:((?:[A-Za-z0-9_\.\'\" -\{\}]|[^\x01-\x7E])*\:\:(?:character varying)?[A-Za-z0-9\[\]]+)|(?:\')((?:\\\'|[^\']|,)+)(?:\')|(?:\")((?:\\\"|[^\"]|,)+)(?:\")|([^,\s]+))",
+                constraints['default'],
+                re.IGNORECASE)
+            if len(matches) > 0:
+                self._default = ''.join(matches[0])
 
         self._comment = None
         if constraint is not None:
-            matches = re.findall(r"(?:\bCOMMENT\b\s+)(['\"])(.+)\1", constraint, re.IGNORECASE)
+            matches = re.findall(r"\bCOMMENT\b\s+(?:(?:\')((?:\\\'|[^\']|,)+)(?:\')|(?:\")((?:\\\"|[^\"]|,)+)(?:\")|([^,\s]+))", constraints['comment'], re.IGNORECASE)
             if len(matches) > 0:
-                self._comment = matches[0][1]
+                self._comment = ''.join(matches[0])
+
 
     @property
     def comment(self):
@@ -196,6 +255,26 @@ class DdlParseColumn(DdlParseTableColumnBase):
     def unique(self, flag):
         self._unique = flag
 
+    @property
+    def auto_increment(self):
+        return self._auto_increment
+
+    @property
+    def distkey(self):
+        return self._distkey
+
+    @property
+    def sortkey(self):
+        return self._sortkey
+
+    @property
+    def encode(self):
+        return self._encode
+
+    @property
+    def default(self):
+        return self._default
+
     @property
     def bigquery_data_type(self):
         """Get BigQuery Legacy SQL data type"""
@@ -496,49 +575,12 @@ class DdlParse(DdlParseBase):
         map(CaselessKeyword, "CREATE, TABLE, TEMP, CONSTRAINT, NOT NULL, PRIMARY KEY, UNIQUE, UNIQUE KEY, FOREIGN KEY, REFERENCES, KEY, CHAR, BYTE".replace(", ", ",").split(","))
     _TYPE_UNSIGNED, _TYPE_ZEROFILL = \
         map(CaselessKeyword, "UNSIGNED, ZEROFILL".replace(", ", ",").split(","))
+    _COL_ATTR_DISTKEY, _COL_ATTR_SORTKEY = \
+        map(CaselessKeyword, "DISTKEY, SORTKEY".replace(", ", ",").split(","))
     _SUPPRESS_QUOTE = _BACKQUOTE | _DOUBLEQUOTE
 
     _COMMENT = Suppress("--" + Regex(r".+"))
 
-    _COLUMN_CONSTRAINT_BASE = r"""
-    (?!--)
-    (
-        (
-            \s*\b(?:NOT\s+)NULL?\b
-        )?(
-            \s*\bAUTO_INCREMENT\b
-        )?(
-            \s*\b(UNIQUE|PRIMARY)(?:\s+KEY)?\b
-        )?(
-            \s*\bDEFAULT\b\s+(
-                ([A-Za-z0-9_\.\'\" -]|[^\x01-\x7E])*\:\:[A-Za-z0-9\[\]]+
-                |
-                \'(\\\'|[^\']|,)+\'
-                |
-                \"(\\\"|[^\"]|,)+\"
-                |
-                [^,]+
-            )
-        )?(
-            \s*\bCOMMENT\b\s+(
-                \'(\\\'|[^\']|,)+\'
-                |
-                \"(\\\"|[^\"]|,)+\"
-                |
-                [^,]+
-            )
-        )?(
-            \s*\bENCODE\s+\w+\b
-        )?(
-            \s*\b(?:NOT\s+)NULL?\b
-        )?(
-            \s*\b(UNIQUE|PRIMARY)(?:\s+KEY)?\b
-        )?
-    )
-    """
-
-    _COLUMN_CONSTRAINT = re.sub(r"(^\s+|\n)", r"", _COLUMN_CONSTRAINT_BASE, flags=re.MULTILINE)
-
 
     _CREATE_TABLE_STATEMENT = Suppress(_CREATE) + Optional(_TEMP)("temp") + Suppress(_TABLE) + Optional(Suppress(CaselessKeyword("IF NOT EXISTS"))) \
         + Optional(_SUPPRESS_QUOTE) + Optional(Word(alphanums + "_")("schema") + Optional(_SUPPRESS_QUOTE) + _DOT + Optional(_SUPPRESS_QUOTE)) + Word(alphanums + "_<>")("table") + Optional(_SUPPRESS_QUOTE) \
@@ -571,7 +613,7 @@ class DdlParse(DdlParseBase):
                 )("constraint")
                 |
                 Group(
-                    Optional(_SUPPRESS_QUOTE) + Word(alphanums + "_")("name") + Optional(_SUPPRESS_QUOTE)
+                    ((_SUPPRESS_QUOTE + Word(alphanums + " _")("name") + _SUPPRESS_QUOTE) ^ (Optional(_SUPPRESS_QUOTE) + Word(alphanums + "_")("name") + Optional(_SUPPRESS_QUOTE)))
                     + Group(
                         Group(
                             Word(alphanums + "_")
@@ -582,7 +624,21 @@ class DdlParse(DdlParseBase):
                         + Optional(_TYPE_ZEROFILL)("zerofill")
                     )("type")
                     + Optional(Word(r"\[\]"))("array_brackets")
-                    + Optional(Regex(_COLUMN_CONSTRAINT, re.IGNORECASE))("constraint")
+                    + Optional(
+                        Regex(r"(?!--)", re.IGNORECASE)
+                        + Group(
+                            Optional(Regex(r"\b(?:NOT\s+)NULL?\b", re.IGNORECASE))("null")
+                            & Optional(Regex(r"\bAUTO_INCREMENT\b", re.IGNORECASE))("auto_increment")
+                            & Optional(Regex(r"\b(UNIQUE|PRIMARY)(?:\s+KEY)?\b", re.IGNORECASE))("key")
+                            & Optional(Regex(
+                                r"\bDEFAULT\b\s+(?:((?:[A-Za-z0-9_\.\'\" -\{\}]|[^\x01-\x7E])*\:\:(?:character varying)?[A-Za-z0-9\[\]]+)|(?:\')((?:\\\'|[^\']|,)+)(?:\')|(?:\")((?:\\\"|[^\"]|,)+)(?:\")|([^,\s]+))",
+                                re.IGNORECASE))("default")
+                            & Optional(Regex(r"\bCOMMENT\b\s+(\'(\\\'|[^\']|,)+\'|\"(\\\"|[^\"]|,)+\"|[^,\s]+)", re.IGNORECASE))("comment")
+                            & Optional(Regex(r"\bENCODE\s+[A-Za-z0-9]+\b", re.IGNORECASE))("encode")
+                            & Optional(_COL_ATTR_DISTKEY)("distkey")
+                            & Optional(_COL_ATTR_SORTKEY)("sortkey")
+                        )("constraint")
+                    )
                 )("column")
                 |
                 _COMMENT
diff --git a/example/example.py b/example/example.py
index eccf91a..6e362b9 100644
--- a/example/example.py
+++ b/example/example.py
@@ -1,6 +1,6 @@
 # -*- coding: utf-8 -*-
 #
-# Copyright (C) 2018 Shinichi Takii, shinichi.takii@gmail.com
+# Copyright (C) 2018 Shinichi Takii, shinichi.takii@shaketh.com
 #
 # This module is part of python-ddlparse and is released under
 # the BSD License: https://opensource.org/licenses/BSD-3-Clause
@@ -71,22 +71,29 @@ print(table.to_bigquery_fields(DdlParse.NAME_CASE.upper))
 print("* COLUMN *")
 for col in table.columns.values():
     col_info = {}
-    col_info["name"] = col.name
-    col_info["data_type"] = col.data_type
-    col_info["length"] = col.length
-    col_info["precision(=length)"] = col.precision
-    col_info["scale"] = col.scale
-    col_info["is_unsigned"] = col.is_unsigned
-    col_info["is_zerofill"] = col.is_zerofill
-    col_info["constraint"] = col.constraint
-    col_info["not_null"] = col.not_null
-    col_info["PK"] = col.primary_key
-    col_info["unique"] = col.unique
-    col_info["bq_legacy_data_type"] = col.bigquery_legacy_data_type
+
+    col_info["name"]                  = col.name
+    col_info["data_type"]             = col.data_type
+    col_info["length"]                = col.length
+    col_info["precision(=length)"]    = col.precision
+    col_info["scale"]                 = col.scale
+    col_info["is_unsigned"]           = col.is_unsigned
+    col_info["is_zerofill"]           = col.is_zerofill
+    col_info["constraint"]            = col.constraint
+    col_info["not_null"]              = col.not_null
+    col_info["PK"]                    = col.primary_key
+    col_info["unique"]                = col.unique
+    col_info["auto_increment"]        = col.auto_increment
+    col_info["distkey"]               = col.distkey
+    col_info["sortkey"]               = col.sortkey
+    col_info["encode"]                = col.encode
+    col_info["default"]               = col.default
+    col_info["bq_legacy_data_type"]   = col.bigquery_legacy_data_type
     col_info["bq_standard_data_type"] = col.bigquery_standard_data_type
-    col_info["comment"] = col.comment
+    col_info["comment"]               = col.comment
     col_info["description(=comment)"] = col.description
-    col_info["bigquery_field"] = json.loads(col.to_bigquery_field())
+    col_info["bigquery_field"]        = json.loads(col.to_bigquery_field())
+
     print(json.dumps(col_info, indent=2, ensure_ascii=False))
 
 print("* DDL (CREATE TABLE) statements *")
@@ -98,3 +105,4 @@ print(table.to_bigquery_ddl(DdlParse.NAME_CASE.upper))
 
 print("* Get Column object (case insensitive) *")
 print(table.columns["total"])
+print(table.columns["total"].data_type)


Test patch:
diff --git a/test/test_ddlparse.py b/test/test_ddlparse.py
index 083ec18..406f8ee 100644
--- a/test/test_ddlparse.py
+++ b/test/test_ddlparse.py
@@ -3,7 +3,7 @@
 import pytest, re, textwrap
 from enum import IntEnum
 
-from ddlparse.ddlparse import DdlParse
+from ddlparse.ddlparse import DdlParse, DdlParseColumn
 
 
 TEST_DATA = {
@@ -251,7 +251,7 @@ TEST_DATA = {
             {"name": "Col_03", "type": "INTEGER", "length": None, "scale": None, "array_dimensional": 0, "is_unsigned": False, "is_zerofill": False, "not_null": False, "pk": False, "unique": True, "constraint": "UNIQUE", "description": None},
             {"name": "Col_04", "type": "DOUBLE", "length": None, "scale": None, "array_dimensional": 0, "is_unsigned": False, "is_zerofill": False, "not_null": False, "pk": False, "unique": True, "constraint": "UNIQUE", "description": None},
             {"name": "Col_05", "type": "DATETIME", "length": None, "scale": None, "array_dimensional": 0, "is_unsigned": False, "is_zerofill": False, "not_null": False, "pk": False, "unique": False, "constraint": "", "description": None},
-            {"name": "Col_06", "type": "DECIMAL", "length": 2, "scale": 1, "array_dimensional": 0, "is_unsigned": False, "is_zerofill": False, "not_null": True, "pk": False, "unique": False, "constraint": "NOT NULL", "description": None},
+            {"name": "Col_06", "type": "DECIMAL", "length": 2, "scale": 1, "array_dimensional": 0, "is_unsigned": False, "is_zerofill": False, "not_null": True, "pk": False, "unique": False, "constraint": "NOT NULL", "description": None, "default": "0.0"},
             {"name": "Col_07", "type": "INTEGER", "length": None, "scale": None, "array_dimensional": 0, "is_unsigned": False, "is_zerofill": False, "not_null": False, "pk": False, "unique": False, "constraint": "", "description": None},
         ],
         "bq_field": [
@@ -322,24 +322,24 @@ TEST_DATA = {
             """
             CREATE TABLE Sample_Table (
               Col_01 char(1) DEFAULT '0'::bpchar PRIMARY KEY,
-              Col_02 char(1) DEFAULT '0'::bpchar ENCODE lzo NOT NULL,
-              Col_03 integer DEFAULT 0,
+              Col_02 char(1) DEFAULT '0'::bpchar ENCODE lzo DISTKEY NOT NULL,
+              Col_03 integer DEFAULT 0 SORTKEY,
               Col_04 numeric(10) DEFAULT 0,
               Col_05 numeric(20,3) DEFAULT 0.0,
               Col_06 varchar(100) DEFAULT '!\"#$%&\\\'()*+,-./:;<=>?@[\\]^_`{|}~',
-              Col_07 character varying[] DEFAULT '{}'::character varying[]
+              Col_07 character varying[] DEFAULT '{}'::character varying[] NOT NULL
             );
             """,
         "database": None,
         "table": {"schema": None, "name": "Sample_Table", "temp": False},
         "columns": [
-            {"name": "Col_01", "type": "CHAR", "length": 1, "scale": None, "array_dimensional": 0, "is_unsigned": False, "is_zerofill": False, "not_null": True, "pk": True, "unique": False, "constraint": "PRIMARY KEY", "description": None},
-            {"name": "Col_02", "type": "CHAR", "length": 1, "scale": None, "array_dimensional": 0, "is_unsigned": False, "is_zerofill": False, "not_null": True, "pk": False, "unique": False, "constraint": "NOT NULL", "description": None},
-            {"name": "Col_03", "type": "INTEGER", "length": None, "scale": None, "array_dimensional": 0, "is_unsigned": False, "is_zerofill": False, "not_null": False, "pk": False, "unique": False, "constraint": "", "description": None},
-            {"name": "Col_04", "type": "NUMERIC", "length": 10, "scale": None, "array_dimensional": 0, "is_unsigned": False, "is_zerofill": False, "not_null": False, "pk": False, "unique": False, "constraint": "", "description": None},
-            {"name": "Col_05", "type": "NUMERIC", "length": 20, "scale": 3, "array_dimensional": 0, "is_unsigned": False, "is_zerofill": False, "not_null": False, "pk": False, "unique": False, "constraint": "", "description": None},
-            {"name": "Col_06", "type": "VARCHAR", "length": 100, "scale": None, "array_dimensional": 0, "is_unsigned": False, "is_zerofill": False, "not_null": False, "pk": False, "unique": False, "constraint": "", "description": None},
-            {"name": "Col_07", "type": "CHARACTER VARYING", "length": None, "scale": None, "array_dimensional": 1, "is_unsigned": False, "is_zerofill": False, "not_null": False, "pk": False, "unique": False, "constraint": "", "description": None},
+            {"name": "Col_01", "type": "CHAR", "length": 1, "scale": None, "array_dimensional": 0, "not_null": True, "pk": True, "constraint": "PRIMARY KEY", "default": "'0'::bpchar"},
+            {"name": "Col_02", "type": "CHAR", "length": 1, "scale": None, "array_dimensional": 0, "not_null": True, "pk": False, "constraint": "NOT NULL", "distkey": True, "encode": "lzo", "default": "'0'::bpchar"},
+            {"name": "Col_03", "type": "INTEGER", "length": None, "scale": None, "array_dimensional": 0, "not_null": False, "pk": False, "constraint": "", "sortkey": True, "default": "0"},
+            {"name": "Col_04", "type": "NUMERIC", "length": 10, "scale": None, "array_dimensional": 0, "not_null": False, "pk": False, "constraint": "", "default": "0"},
+            {"name": "Col_05", "type": "NUMERIC", "length": 20, "scale": 3, "array_dimensional": 0, "not_null": False, "pk": False, "constraint": "", "default": "0.0"},
+            {"name": "Col_06", "type": "VARCHAR", "length": 100, "scale": None, "array_dimensional": 0, "not_null": False, "pk": False, "constraint": "", "default": '!\"#$%&\\\'()*+,-./:;<=>?@[\\]^_`{|}~'},
+            {"name": "Col_07", "type": "CHARACTER VARYING", "length": None, "scale": None, "array_dimensional": 1, "not_null": True, "pk": False, "constraint": "NOT NULL", "default": "'{}'::character varying[]"},
         ],
         "bq_field": [
             '{"name": "Col_01", "type": "STRING", "mode": "REQUIRED"}',
@@ -780,7 +780,7 @@ TEST_DATA = {
               "Col_02" char(200),
               "Col_03" integer,
               "Col_04" double,
-              "Col_05" datetime,
+              "Col 05" datetime,
               PRIMARY KEY ("Col_01", "Col_02"),
               UNIQUE ("Col_03", "Col_04")
             );
@@ -792,14 +792,14 @@ TEST_DATA = {
             {"name": "Col_02", "type": "CHAR", "length": 200, "scale": None, "array_dimensional": 0, "is_unsigned": False, "is_zerofill": False, "not_null": True, "pk": True, "unique": False, "constraint": "PRIMARY KEY", "description": None},
             {"name": "Col_03", "type": "INTEGER", "length": None, "scale": None, "array_dimensional": 0, "is_unsigned": False, "is_zerofill": False, "not_null": False, "pk": False, "unique": True, "constraint": "UNIQUE", "description": None},
             {"name": "Col_04", "type": "DOUBLE", "length": None, "scale": None, "array_dimensional": 0, "is_unsigned": False, "is_zerofill": False, "not_null": False, "pk": False, "unique": True, "constraint": "UNIQUE", "description": None},
-            {"name": "Col_05", "type": "DATETIME", "length": None, "scale": None, "array_dimensional": 0, "is_unsigned": False, "is_zerofill": False, "not_null": False, "pk": False, "unique": False, "constraint": "", "description": None},
+            {"name": "Col 05", "type": "DATETIME", "length": None, "scale": None, "array_dimensional": 0, "is_unsigned": False, "is_zerofill": False, "not_null": False, "pk": False, "unique": False, "constraint": "", "description": None},
         ],
         "bq_field": [
             '{"name": "Col_01", "type": "STRING", "mode": "REQUIRED"}',
             '{"name": "Col_02", "type": "STRING", "mode": "REQUIRED"}',
             '{"name": "Col_03", "type": "INTEGER", "mode": "NULLABLE"}',
             '{"name": "Col_04", "type": "FLOAT", "mode": "NULLABLE"}',
-            '{"name": "Col_05", "type": "DATETIME", "mode": "NULLABLE"}',
+            '{"name": "Col 05", "type": "DATETIME", "mode": "NULLABLE"}',
         ],
         "bq_standard_data_type": [
             "STRING",
@@ -873,7 +873,7 @@ TEST_DATA = {
             {"name": "Col_03", "type": "INTEGER", "length": None, "scale": None, "array_dimensional": 1, "is_unsigned": False, "is_zerofill": False, "not_null": False, "pk": False, "unique": False, "constraint": "", "description": "in \"Quote\""},
             {"name": "Col_04", "type": "INTEGER", "length": None, "scale": None, "array_dimensional": 2, "is_unsigned": False, "is_zerofill": False, "not_null": False, "pk": False, "unique": False, "constraint": "", "description": "in 'Quote'"},
             {"name": "Col_05", "type": "INTEGER", "length": None, "scale": None, "array_dimensional": 3, "is_unsigned": False, "is_zerofill": False, "not_null": False, "pk": False, "unique": False, "constraint": "", "description": "コメント is full-width(Japanese) character"},
-            {"name": "Col_06", "type": "CHARACTER", "length": None, "scale": None, "array_dimensional": 0, "is_unsigned": False, "is_zerofill": False, "not_null": True, "pk": False, "unique": False, "constraint": "NOT NULL", "description": "Comma, strings, ,"},
+            {"name": "Col_06", "type": "CHARACTER", "length": None, "scale": None, "array_dimensional": 0, "is_unsigned": False, "is_zerofill": False, "not_null": True, "pk": False, "unique": False, "constraint": "NOT NULL", "description": "Comma, strings, ,", "default": "a\\'bc"},
             {"name": "Col_07", "type": "CHARACTER VARYING", "length": None, "scale": None, "array_dimensional": 1, "is_unsigned": False, "is_zerofill": False, "not_null": False, "pk": False, "unique": False, "constraint": "", "description": None},
         ],
         "bq_field": [
@@ -1171,21 +1171,47 @@ def test_parse(test_case, parse_pattern):
     for col in table.columns.values():
         data_col = data["columns"][i]
 
-        assert col.name == data_col["name"]
-        assert col.data_type == data_col["type"]
-        assert col.is_unsigned == data_col["is_unsigned"]
-        assert col.is_zerofill == data_col["is_zerofill"]
-        assert col.length == data_col["length"] if data_col["length"] is not None else col.length is None
-        assert col.precision == data_col["length"] if data_col["length"] is not None else col.length is None
-        assert col.scale == data_col["scale"] if data_col["scale"] is not None else col.scale is None
+        # カラム制約 テスト評価のデフォルト値をセット
+        _DEFAULT_CONSTRAINT = {
+            'is_unsigned': False,
+            'is_zerofill': False,
+            'description': None,
+            'array_dimensional': 0,
+            'not_null': False,
+            'primary_key': False,
+            'unique': False,
+            'auto_increment': False,
+            'distkey': False,
+            'sortkey': False,
+            'encode': None,
+            'default': None,
+        }
+
+        for constraint_name, default_val in _DEFAULT_CONSTRAINT.items():
+            if constraint_name not in data_col.keys():
+                data_col[constraint_name] = default_val
+
+
+        assert col.name              == data_col["name"]
+        assert col.data_type         == data_col["type"]
+        assert col.is_unsigned       == data_col["is_unsigned"]
+        assert col.is_zerofill       == data_col["is_zerofill"]
+        assert col.length            == data_col["length"] if data_col["length"] is not None else col.length is None
+        assert col.precision         == data_col["length"] if data_col["length"] is not None else col.length is None
+        assert col.scale             == data_col["scale"] if data_col["scale"] is not None else col.scale is None
         assert col.array_dimensional == data_col["array_dimensional"]
-        assert col.not_null == data_col["not_null"]
-        assert col.primary_key == data_col["pk"]
-        assert col.unique == data_col["unique"]
-        assert col.constraint == data_col["constraint"]
-        assert col.source_database == data["database"]
-        assert col.comment == data_col["description"] if data_col["description"] is not None else col.comment is None
-        assert col.description == data_col["description"] if data_col["description"] is not None else col.description is None
+        assert col.not_null          == data_col["not_null"]
+        assert col.primary_key       == data_col["pk"]
+        assert col.unique            == data_col["unique"]
+        assert col.constraint        == data_col["constraint"]
+        assert col.source_database   == data["database"]
+        assert col.comment           == data_col["description"] if data_col["description"] is not None else col.comment is None
+        assert col.description       == data_col["description"] if data_col["description"] is not None else col.description is None
+        assert col.auto_increment    == data_col["auto_increment"]
+        assert col.distkey           == data_col["distkey"]
+        assert col.sortkey           == data_col["sortkey"]
+        assert col.encode            == data_col["encode"]
+        assert col.default           == data_col["default"]
 
         data_bq_field = data["bq_field"][i]
         assert col.to_bigquery_field() == data_bq_field
@@ -1281,3 +1307,22 @@ def test_set_comment():
     # Check description setter
     table.columns["Col_01"].description = "comment_2"
     assert table.columns["Col_01"].description == "comment_2"
+
+
+def test_constraint_compatibility():
+    """
+    Test compatibility v1.6.1 and earlier
+    """
+
+    dl_col = DdlParseColumn(
+        name='Col',
+        data_type_array={'type_name': ['INT']},
+    )
+
+    dl_col.constraint = "PRIMARY KEY COMMENT 'foo'"
+
+    # Check column constraint
+    assert dl_col.not_null is True
+    assert dl_col.primary_key is True
+    assert dl_col.unique is False
+    assert dl_col.comment == 'foo'