- merge r6586 from 0.5 branch, for [ticket:1647]

1 files changed, 60 insertions, 13 deletions

diff --git a/test/dialect/test_postgresql.py b/test/dialect/test_postgresql.py
index c929d38b3..7cf22a3ed 100644
--- a/test/dialect/test_postgresql.py
+++ b/test/dialect/test_postgresql.py
@@ -100,19 +100,66 @@ class CompileTest(TestBase, AssertsCompiledSQL):
             "CREATE INDEX test_idx1 ON testtbl (data) WHERE data > 5 AND data < 10", dialect=postgresql.dialect())
 
     def test_extract(self):
-        t = table('t', column('col1'))
-
-        for field in 'year', 'month', 'day':
-            self.assert_compile(
-                select([extract(field, t.c.col1)]),
-                "SELECT EXTRACT(%s FROM t.col1 :: timestamp) AS anon_1 "
-                "FROM t" % field)
-
-        for field in 'year', 'month', 'day':
-            self.assert_compile(
-                select([extract(field, func.timestamp() - datetime.timedelta(days =5))]),
-                "SELECT EXTRACT(%s FROM (timestamp() - %%(timestamp_1)s) :: timestamp) AS anon_1"
-                 % field)
+
+        t = table('t', column('col1', DateTime), column('col2', Date), column('col3', Time))
+
+        for field in 'year', 'month', 'day', 'epoch', 'hour':
+            for expr, compiled_expr in [
+                ( t.c.col1, "t.col1 :: timestamp" ),
+                ( t.c.col2, "t.col2 :: date" ),
+                ( t.c.col3, "t.col3 :: time" ),
+                (func.current_timestamp() - datetime.timedelta(days=5),
+                    "(CURRENT_TIMESTAMP - %(current_timestamp_1)s) :: timestamp"
+                ),
+                (func.current_timestamp() + func.current_timestamp(), 
+                    "CURRENT_TIMESTAMP + CURRENT_TIMESTAMP" # invalid, no cast.
+                ),
+                (text("foo.date + foo.time"), 
+                    "foo.date + foo.time" # plain text.  no cast.
+                ),
+                (func.current_timestamp() + datetime.timedelta(days=5), 
+                    "(CURRENT_TIMESTAMP + %(current_timestamp_1)s) :: timestamp"
+                ),
+                (t.c.col2 + t.c.col3,
+                    "(t.col2 + t.col3) :: timestamp"
+                ),
+                # addition is commutative
+                (t.c.col2 + datetime.timedelta(days=5),
+                    "(t.col2 + %(col2_1)s) :: timestamp"
+                ),
+                (datetime.timedelta(days=5) + t.c.col2,
+                    "(%(col2_1)s + t.col2) :: timestamp"
+                ),
+                # subtraction is not
+                (t.c.col1 - datetime.timedelta(seconds=30),
+                    "(t.col1 - %(col1_1)s) :: timestamp"
+                ),
+                (datetime.timedelta(seconds=30) - t.c.col1,
+                    "%(col1_1)s - t.col1" # invalid - no cast.
+                ),
+                (func.coalesce(t.c.col1, func.current_timestamp()),
+                    "coalesce(t.col1, CURRENT_TIMESTAMP) :: timestamp"
+                ),
+                (t.c.col3 + datetime.timedelta(seconds=30),
+                    "(t.col3 + %(col3_1)s) :: time"
+                ),
+                (func.current_timestamp() - func.coalesce(t.c.col1, func.current_timestamp()),
+                    "(CURRENT_TIMESTAMP - coalesce(t.col1, CURRENT_TIMESTAMP)) :: interval",
+                ),
+                (3 * func.foobar(type_=Interval),
+                    "(%(foobar_1)s * foobar()) :: interval"
+                ),
+                (literal(datetime.timedelta(seconds=10)) - literal(datetime.timedelta(seconds=10)),
+                    "(%(param_1)s - %(param_2)s) :: interval"
+                ),
+            ]:
+                self.assert_compile(
+                    select([extract(field, expr)]).select_from(t),
+                    "SELECT EXTRACT(%s FROM %s) AS anon_1 FROM t" % (
+                        field, 
+                        compiled_expr
+                    )
+                )
 
 class FloatCoercionTest(TablesTest, AssertsExecutionResults):
     __only_on__ = 'postgresql'