sqlancer · yaojiejia · Jun 10, 2025 · Jul 13, 2025 · Jul 27, 2025 · Jul 27, 2025
diff --git a/src/sqlancer/postgres/PostgresSchema.java b/src/sqlancer/postgres/PostgresSchema.java
@@ -31,7 +31,7 @@ public class PostgresSchema extends AbstractSchema<PostgresGlobalState, Postgres
     private final String databaseName;
 
     public enum PostgresDataType {
-        INT, BOOLEAN, TEXT, DECIMAL, FLOAT, REAL, RANGE, MONEY, BIT, INET;
+        INT, BOOLEAN, TEXT, DECIMAL, FLOAT, REAL, RANGE, MONEY, BIT, INET, TIMESTAMP, DATE, TIME;
 
         public static PostgresDataType getRandomType() {
             List<PostgresDataType> dataTypes = new ArrayList<>(Arrays.asList(values()));
@@ -43,9 +43,46 @@ public static PostgresDataType getRandomType() {
                 dataTypes.remove(PostgresDataType.RANGE);
                 dataTypes.remove(PostgresDataType.MONEY);
                 dataTypes.remove(PostgresDataType.BIT);
+                dataTypes.remove(PostgresDataType.TIMESTAMP);
+                dataTypes.remove(PostgresDataType.DATE);
+                dataTypes.remove(PostgresDataType.TIME);
             }
             return Randomly.fromList(dataTypes);
         }
+
+        @Override
+        public String toString() {
+            switch (this) {
+            case INT:
+                return "INTEGER";
+            case BOOLEAN:
+                return "BOOLEAN";
+            case TEXT:
+                return "TEXT";
+            case DECIMAL:
+                return "DECIMAL";
+            case FLOAT:
+                return "FLOAT";
+            case REAL:
+                return "REAL";
+            case RANGE:
+                return "INT4RANGE";
+            case MONEY:
+                return "MONEY";
+            case BIT:
+                return "BIT";
+            case INET:
+                return "INET";
+            case TIMESTAMP:
+                return "TIMESTAMP";
+            case DATE:
+                return "DATE";
+            case TIME:
+                return "TIME WITH TIME ZONE";
+            default:
+                throw new AssertionError(this);
+            }
+        }
     }
 
     public static class PostgresColumn extends AbstractTableColumn<PostgresTable, PostgresDataType> {
@@ -141,6 +178,17 @@ public static PostgresDataType getColumnType(String typeString) {
             return PostgresDataType.BIT;
         case "inet":
             return PostgresDataType.INET;
+
+        case "timestamp":
+        case "timestamp with time zone":
+        case "timestamp without time zone":
+            return PostgresDataType.TIMESTAMP;
+        case "date":
+            return PostgresDataType.DATE;
+        case "time":
+        case "time with time zone":
+        case "time without time zone":
+            return PostgresDataType.TIME;
         default:
             throw new AssertionError(typeString);
         }

diff --git a/src/sqlancer/postgres/PostgresToStringVisitor.java b/src/sqlancer/postgres/PostgresToStringVisitor.java
@@ -197,15 +197,28 @@ public void visit(PostgresOrderByTerm term) {
 
     @Override
     public void visit(PostgresFunction f) {
-        sb.append(f.getFunctionName());
-        sb.append("(");
-        int i = 0;
-        for (PostgresExpression arg : f.getArguments()) {
-            if (i++ != 0) {
-                sb.append(", ");
+        if (f.isExtractFunction()) {
+            visitExtractFunction(f);
+        } else {
+            sb.append(f.getFunctionName());
+            sb.append("(");
+            int i = 0;
+            for (PostgresExpression arg : f.getArguments()) {
+                if (i++ != 0) {
+                    sb.append(", ");
+                }
+                visit(arg);
             }
-            visit(arg);
+            sb.append(")");
         }
+    }
+
+    private void visitExtractFunction(PostgresFunction f) {
+        sb.append(f.getFunctionName());
+        sb.append("(");
+        visit(f.getArguments()[0]);
+        sb.append(" FROM ");
+        visit(f.getArguments()[1]);
         sb.append(")");
     }
 
@@ -258,11 +271,15 @@ private void appendType(PostgresCastOperation cast) {
             break;
         case BIT:
             sb.append("BIT");
-            // if (Randomly.getBoolean()) {
-            // sb.append("(");
-            // sb.append(Randomly.getNotCachedInteger(1, 100));
-            // sb.append(")");
-            // }
+            break;
+        case TIMESTAMP:
+            sb.append("TIMESTAMP");
+            break;
+        case DATE:
+            sb.append("DATE");
+            break;
+        case TIME:
+            sb.append("TIME");
             break;
         default:
             throw new AssertionError(cast.getType());

diff --git a/src/sqlancer/postgres/ast/PostgresFunction.java b/src/sqlancer/postgres/ast/PostgresFunction.java
@@ -31,6 +31,82 @@ public PostgresExpression[] getArguments() {
         return args.clone();
     }
 
+    public boolean isExtractFunction() {
+        return false;
+    }
+
+    public String getArgString() {
+        StringBuilder sb = new StringBuilder();
+        for (int i = 0; i < args.length; i++) {
+            if (i != 0) {
+                sb.append(", ");
+            }
+            sb.append(formatArgumentForPostgresFunction(args[i]));
+        }
+        return sb.toString();
+    }
+
+    /**
+     * Formats an argument for use in PostgreSQL function calls. Date or time values need explicit CAST statements
+     * because they are stored as text but PostgreSQL requires proper type annotations for function parameters.
+     *
+     * @param arg
+     *            the expression to format
+     *
+     * @return the formatted argument string
+     */
+    private String formatArgumentForPostgresFunction(PostgresExpression arg) {
+        if (arg.getExpressionType() == PostgresDataType.TIME || arg.getExpressionType() == PostgresDataType.TIMESTAMP
+                || arg.getExpressionType() == PostgresDataType.DATE) {
+            return String.format("CAST(%s AS %s)", arg, arg.getExpressionType().toString());
+        } else {
+            return arg.toString();
+        }
+    }
+
+    public static class PostgresExtractFunction extends PostgresFunction {
+
+        public PostgresExtractFunction(PostgresFunctionWithUnknownResult f, PostgresDataType returnType,
+                PostgresExpression... args) {
+            super(f, returnType, args);
+        }
+
+        @Override
+        public String getFunctionName() {
+            return "EXTRACT";
+        }
+
+        @Override
+        public boolean isExtractFunction() {
+            return true;
+        }
+
+        @Override
+        public String getArgString() {
+            return String.format("%s FROM %s", getArguments()[0], getArguments()[1]);
+        }
+    }
+
+    @Override
+    public PostgresConstant getExpectedValue() {
+        if (functionWithKnownResult == null) {
+            return null;
+        }
+        PostgresConstant[] constants = new PostgresConstant[args.length];
+        for (int i = 0; i < constants.length; i++) {
+            constants[i] = args[i].getExpectedValue();
+            if (constants[i] == null) {
+                return null;
+            }
+        }
+        return functionWithKnownResult.apply(constants, args);
+    }
+
+    @Override
+    public PostgresDataType getExpressionType() {
+        return returnType;
+    }
+
     public enum PostgresFunctionWithResult {
         ABS(1, "abs") {
 
@@ -76,7 +152,6 @@ public boolean supportsReturnType(PostgresDataType type) {
             public PostgresDataType[] getInputTypesForReturnType(PostgresDataType returnType, int nrArguments) {
                 return new PostgresDataType[] { PostgresDataType.TEXT };
             }
-
         },
         LENGTH(1, "length") {
             @Override
@@ -265,24 +340,4 @@ public boolean checkArguments(PostgresExpression... constants) {
 
     }
 
-    @Override
-    public PostgresConstant getExpectedValue() {
-        if (functionWithKnownResult == null) {
-            return null;
-        }
-        PostgresConstant[] constants = new PostgresConstant[args.length];
-        for (int i = 0; i < constants.length; i++) {
-            constants[i] = args[i].getExpectedValue();
-            if (constants[i] == null) {
-                return null;
-            }
-        }
-        return functionWithKnownResult.apply(constants, args);
-    }
-
-    @Override
-    public PostgresDataType getExpressionType() {
-        return returnType;
-    }
-
 }
diff --git a/src/sqlancer/postgres/ast/PostgresFunctionWithUnknownResult.java b/src/sqlancer/postgres/ast/PostgresFunctionWithUnknownResult.java
@@ -141,7 +141,33 @@ public PostgresExpression[] getArguments(PostgresDataType returnType, PostgresEx
     RANGE_MERGE("range_merge", PostgresDataType.RANGE, PostgresDataType.RANGE, PostgresDataType.RANGE), //
 
     // https://www.postgresql.org/docs/13/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE
-    GET_COLUMN_SIZE("get_column_size", PostgresDataType.INT, PostgresDataType.TEXT);
+    GET_COLUMN_SIZE("get_column_size", PostgresDataType.INT, PostgresDataType.TEXT),
+
+    // Extract function implementation
+    EXTRACT("extract", PostgresDataType.FLOAT, PostgresDataType.TEXT, PostgresDataType.TIMESTAMP, PostgresDataType.DATE,
+            PostgresDataType.TIME) {
+        @Override
+        public PostgresExpression[] getArguments(PostgresDataType returnType, PostgresExpressionGenerator gen,
+                int depth) {
+            PostgresExpression[] args = new PostgresExpression[2];
+            PostgresDataType sourceType = Randomly.fromOptions(PostgresDataType.TIMESTAMP, PostgresDataType.DATE,
+                    PostgresDataType.TIME);
+            args[1] = gen.generateExpression(depth + 1, sourceType);
+
+            String[] validFields;
+            if (sourceType == PostgresDataType.DATE) {
+                validFields = new String[] { "YEAR", "MONTH", "DAY", "DECADE", "CENTURY", "MILLENNIUM", "QUARTER",
+                        "WEEK", "DOY", "DOW", "ISODOW", "ISOYEAR" };
+            } else if (sourceType == PostgresDataType.TIME) {
+                validFields = new String[] { "HOUR", "MINUTE", "SECOND" };
+            } else {
+                validFields = new String[] { "YEAR", "MONTH", "DAY", "HOUR", "MINUTE", "SECOND", "DECADE", "CENTURY",
+                        "MILLENNIUM", "QUARTER", "WEEK", "DOY", "DOW", "ISODOW", "ISOYEAR", "EPOCH" };
+            }
+            args[0] = PostgresConstant.createTextConstant(Randomly.fromOptions(validFields));
+            return args;
+        }
+    };
     // PG_DATABASE_SIZE("pg_database_size", PostgresDataType.INT, PostgresDataType.INT);
     // PG_SIZE_BYTES("pg_size_bytes", PostgresDataType.INT, PostgresDataType.TEXT);
 

diff --git a/src/sqlancer/postgres/gen/PostgresAlterTableGenerator.java b/src/sqlancer/postgres/gen/PostgresAlterTableGenerator.java
@@ -53,9 +53,7 @@ protected enum Action {
         ALTER_VIEW_RENAME_COLUMN // RENAME COLUMN old_name TO new_name (for views)
     }
 
-    private static final List<Action> VIEW_ACTIONS = List.of(
-        Action.ALTER_VIEW_RENAME_COLUMN
-    );
+    private static final List<Action> VIEW_ACTIONS = List.of(Action.ALTER_VIEW_RENAME_COLUMN);
 
     public PostgresAlterTableGenerator(PostgresTable randomTable, PostgresGlobalState globalState,
             boolean generateOnlyKnown) {

diff --git a/src/sqlancer/postgres/gen/PostgresCommon.java b/src/sqlancer/postgres/gen/PostgresCommon.java
@@ -287,6 +287,21 @@ public static boolean appendDataType(PostgresDataType type, StringBuilder sb, bo
         case INET:
             sb.append("inet");
             break;
+        case DATE:
+            sb.append("DATE");
+            break;
+        case TIME:
+            sb.append("TIME");
+            if (!generateOnlyKnown && Randomly.getBoolean()) {
+                sb.append(" WITH TIME ZONE");
+            }
+            break;
+        case TIMESTAMP:
+            sb.append("TIMESTAMP");
+            if (!generateOnlyKnown && Randomly.getBoolean()) {
+                sb.append(" WITH TIME ZONE");
+            }
+            break;
         default:
             throw new AssertionError(type);
         }