Metabase MetaBot 的 PostgreSQL 方言提示词SQL 生成规则全文精读与加载机制源码解析【免费下载链接】metabaseThe easy-to-use open source Business Intelligence and Embedded Analytics tool that lets everyone work with data :bar_chart:项目地址: https://gitcode.com/GitHub_Trending/me/metabase本文以 Metabase 仓库中resources/metabot/prompts/dialects/postgresql.md这份 PostgreSQL 方言提示词文件为主体逐节讲解其中规定的标识符引用、字符串与日期函数、数组、JSON/JSONB、窗口函数、聚合与常用模式等 SQL 生成规则并结合 metabase.metabot.skills 与 metabase.llm.api 的源码说明这份 Markdown 如何被注册为隐藏的方言技能、在什么场景下被注入 LLM 提示词帮助你在 Metabase 的 MetaBot / AI 生成 SQL 链路中获得方言正确的 PostgreSQL 查询。一、这份方言文档是什么定位与生效位置postgresql.md位于resources/metabot/prompts/dialects/目录下同目录还有 bigquery.md、mysql.md、clickhouse.md 等 14 个引擎的方言指令文件。它不面向人类用户而是作为给 LLM 的方言规则提示词当 Metabase 的 AI 功能MetaBot、SQL 编辑器中的 AI 生成 SQL需要为 PostgreSQL 数据库生成 SQL 时这份文件中的规则会被整体注入系统提示词约束模型写出符合 PostgreSQL 方言的查询。仓库中有两条确认其生效位置的源码链路驱动到方言文件的映射src/metabase/driver/postgres.clj 中为:postgres驱动定义了方法(defmethod driver/llm-sql-dialect-resource :postgres [_] metabot/prompts/dialects/postgresql.md)该多方法在 src/metabase/driver.clj 中声明默认返回nil即没有方言专属指令的驱动不受此机制影响。技能注册src/metabase/metabot/skills.clj 会扫描metabot/prompts/dialects/下的全部.md文件按文件名去扩展名生成引擎名注册为 id 形如sql-dialect-postgresql的隐藏技能hidden skill——它不会出现在技能目录catalog中因此模型不能通过load_skill主动加载它skills_test.clj 中的registry-populated-test断言了:sql-dialect-postgresql已注册skills-for-profile-test断言方言技能从不通过工具关联或 profile 路径暴露。两条注入途径MetaBot 会话预载dialect-preload-parts 在 SQL 编辑器会话中根据 viewing context 提供的引擎名如postgres先经engine-dialect解析到postgresql再返回一对合成的load_skill工具调用与结果把方言正文作为消息流的一部分预载进来。源码注释明确说明这些预载消息位于系统缓存断点之下从而让不同数据库之间共享相同的系统提示词缓存前缀。一次性 SQL 生成端点src/metabase/llm/api.clj 的POST /api/llm/generate-sql端点中load-dialect-instructionsapi.clj已memoize缓存按驱动读取本文件全文再由build-system-prompt渲染 one-shot-sql-generation.mustache 模板把:dialect_instructions一并填入系统提示词。理解了这个加载机制就能明白为什么这份 Markdown 的写法如此指令化它假定读者LLM已经在写某个具体数据库的 SQL只需照规则执行不需要解释 PostgreSQL 是什么。下面逐节精读规则内容。二、标识符引用Identifier Quoting文档第一组规则确立了 PostgreSQL 的引用习惯这是生成 SQL 时最基础也最容易出错的约定标识符使用双引号MyColumn、table-name不加引号的标识符会被折叠为小写SELECT MyColumn实际查的是mycolumn所以列名含大写字母时必须加双引号字符串字面量使用单引号string value。示例SELECT CamelCaseColumn, reserved-word FROM My Table这条规则的实战意义在于Metabase 的表/列元数据里经常出现混合大小写或带连字符的原始列名若不遵守含大写必须加引号生成的 SQL 会静默查错列。文档末部的对比表也强调PostgreSQL 与 Snowflake 用双引号而 BigQuery / MySQL 用反引号——这是跨方言生成时最常见的踩坑点。三、字符串操作文档给出的字符串函数清单可直接作为生成时的合法函数白名单-- 连接|| 运算符首选 SELECT first_name || || last_name AS full_name SELECT LOWER(name), UPPER(name), INITCAP(name), TRIM(name), LTRIM(name), RTRIM(name), SUBSTRING(name FROM 1 FOR 3), -- 1 起始下标 LENGTH(name), CHAR_LENGTH(name), REPLACE(name, old, new), SPLIT_PART(csv, ,, 1), -- 取第 n 个元素1 起始 STRING_TO_ARRAY(csv, ,), -- 拆成数组 POSITION(sub IN name), -- 子串位置 LEFT(name, 3), RIGHT(name, 3), LPAD(num::TEXT, 5, 0), RPAD(name, 10, )以及模式匹配部分标注为PostgreSQL 专属的运算符要用上SELECT * FROM t WHERE name LIKE A% -- 大小写敏感 SELECT * FROM t WHERE name ILIKE a% -- 大小写不敏感PG 专属 SELECT * FROM t WHERE name ~ ^[A-Z] -- POSIX 正则匹配 SELECT * FROM t WHERE name ~* ^[a-z] -- 大小写不敏感正则 SELECT REGEXP_REPLACE(text, pattern, replacement, g)文档特别强调ILIKE是 PostgreSQL 专属能力其他数据库需要LOWER()变通。从对比表看MySQL 的LIKE默认不区分大小写取决于排序规则BigQuery 只能LOWER()绕行——因此在 Metabase 里为 Postgres 生成忽略大小写的模糊搜索时应当首选ILIKE而不是LOWER(col) LIKE LOWER(…)。四、日期与时间这一节是全文最长的规则块覆盖了当前时间、截断、运算、差值、抽取与格式化五类操作-- 当前日期/时间 SELECT CURRENT_DATE, -- DATE无需括号 CURRENT_TIMESTAMP, -- TIMESTAMP WITH TIME ZONE NOW(), -- 等价于 CURRENT_TIMESTAMP LOCALTIME, LOCALTIMESTAMP -- 不带时区 -- 日期截断 SELECT DATE_TRUNC(month, order_date) -- year, quarter, month, week, day, hour, minute, second SELECT DATE_TRUNC(week, order_date) -- 截断到周一 -- INTERVAL 日期运算 SELECT order_date INTERVAL 7 days, order_date - INTERVAL 1 month, order_date INTERVAL 2 hours 30 minutes, order_date 7 -- 直接加整数表示加天 -- 日期差 SELECT end_date - start_date, -- 返回 INTERVAL AGE(end_date, start_date), -- 返回年/月/日形式的 INTERVAL EXTRACT(EPOCH FROM (end_date - start_date)) / 86400 AS days -- 数值天数 -- 抽取 SELECT EXTRACT(YEAR FROM order_date), -- 或 DATE_PART(year, order_date) EXTRACT(MONTH FROM order_date), EXTRACT(DOW FROM order_date), -- 0周日, 6周六 EXTRACT(ISODOW FROM order_date), -- 1周一, 7周日 EXTRACT(EPOCH FROM timestamp_col) -- Unix 时间戳 -- 格式化与解析 SELECT TO_CHAR(order_date, YYYY-MM-DD), TO_CHAR(order_date, Mon DD, YYYY), TO_CHAR(amount, 999,999.00), -- 也能格式化数字 TO_DATE(2024-01-15, YYYY-MM-DD), TO_TIMESTAMP(2024-01-15 10:30:00, YYYY-MM-DD HH24:MI:SS)几个值得注意的细节DOW与ISODOW的编号体系不同0 起始周日 vs 1 起始周一生成周几相关查询时选错会得到整体偏移一天的结果两个TIMESTAMP相减得到的是INTERVAL而非数值若要数值天数需要EXTRACT(EPOCH FROM …)/86400参数顺序陷阱在文末对比表中再次出现PostgreSQL 是DATE_TRUNC(month, d)单位在前BigQuery 是DATE_TRUNC(d, MONTH)单位在后跨库迁移时最容易写反。五、类型转换Type Casting文档将::简写列为首选标准CAST作为备选-- PostgreSQL 简写首选简洁 SELECT 123::INTEGER, 2024-01-15::DATE, 123::TEXT -- 标准 CAST SELECT CAST(123 AS INTEGER), CAST(order_date AS TEXT) -- 常用类型名INTEGER, BIGINT, SMALLINT, NUMERIC, DECIMAL, REAL, DOUBLE PRECISION, -- TEXT, VARCHAR(n), CHAR(n), BOOLEAN, DATE, TIME, TIMESTAMP, -- TIMESTAMPTZ, INTERVAL, UUID, JSON, JSONB, BYTEA, ARRAY在字符串拆分的示例中可以看到::TEXT的典型用途LPAD(num::TEXT, 5, 0)这是 LLM 生成 SQL 时最常用的一类转换。六、NULL 处理SELECT COALESCE(nullable_col, default), -- 取第一个非 NULL 值 NULLIF(col, ), -- 若 col 则返回 NULL col IS DISTINCT FROM other_col, -- NULL 安全的不等比较 col IS NOT DISTINCT FROM other_col -- NULL 安全的相等比较文档特别指出IS DISTINCT FROM把 NULL 当作可比较的值处理而/遇到 NULL 会得到 NULL 而非 true/false。这一条对判断两列是否相同类问题尤为关键——用会漏掉两边含 NULL 的行。另外NULLIF(col, )与文末安全除法模式NULLIF(denominator, 0)组合使用是 Metabase 报表查询中规避空串与除零的常见手法。七、数组1 起始下标是Critical级警示PostgreSQL 原生数组是本方言的重头戏文档规则如下-- 数组字面量 SELECT ARRAY[1, 2, 3], {1,2,3}::INT[] -- 数组访问1 起始下标 SELECT my_array[1] AS first_element -- 数组函数 SELECT ARRAY_LENGTH(arr, 1), -- 第一维长度 CARDINALITY(arr), -- 总元素数 ARRAY_CAT(arr1, arr2), -- 拼接数组 ARRAY_APPEND(arr, element), ARRAY_PREPEND(element, arr), ARRAY_TO_STRING(arr, , ), -- 拼成字符串 ARRAY_POSITION(arr, value), -- 查找元素下标 ARRAY_REMOVE(arr, value), value ANY(arr), -- 成员判断 arr ARRAY[1, 2], -- 包含 arr ARRAY[1, 2] -- 有重叠 -- UNNEST把数组展开成行 SELECT UNNEST(ARRAY[1, 2, 3]) SELECT t.id, u.element FROM t, UNNEST(t.arr) AS u(element) -- 数组聚合 SELECT category, ARRAY_AGG(product ORDER BY name) FROM t GROUP BY category SELECT ARRAY_AGG(DISTINCT val) FROM t文档以Critical级别标注PostgreSQL 数组是1 起始下标而 BigQuery 是 0 起始。这一行对应的正是文末对比表中的 Array index 行1-based vs 0-based。value ANY(arr)成员判断与UNNEST行展开是把逗号分隔列当集合查询时的两个核心手段也解释了为什么 Metabase 的 AI 生成 SQL 在 Postgres 上被鼓励用原生数组而非字符串拼接。八、JSON 与 JSONB文档首先区分两种类型JSON文本存储与JSONB二进制、可索引、首选然后给出运算符、函数与修改操作三类规则-- 提取 SELECT json_col - key, -- 返回 JSON/JSONB json_col - key, -- 返回 TEXT json_col - 0, -- 按下标取数组元素 json_col # {nested,path}, -- 路径提取返回 JSON json_col # {nested,path}, -- 路径提取返回 TEXT json_col {key: value}, -- 包含判断仅 JSONB json_col ? key, -- 是否有该 key仅 JSONB json_col ?| ARRAY[key1, key2], -- 是否有任一 key json_col ? ARRAY[key1, key2] -- 是否同时有所有 key -- JSON 函数 SELECT JSONB_EXTRACT_PATH(json_col, a, b), -- 等价 # JSONB_EXTRACT_PATH_TEXT(json_col, a), -- 等价 # JSONB_ARRAY_ELEMENTS(json_col), -- 数组展开成行 JSONB_ARRAY_LENGTH(json_col), JSONB_OBJECT_KEYS(json_col), -- key 展开成行 JSONB_EACH(json_col), -- 键值对展开成行 JSONB_BUILD_OBJECT(a, 1, b, 2), -- 构造 JSON 对象 JSONB_AGG(val), -- 聚合为 JSON 数组 JSONB_OBJECT_AGG(key, val) -- 聚合为 JSON 对象 -- JSONB 修改仅 JSONB SELECT json_col || {new: value}, -- 合并 json_col - key, -- 删除 key json_col #- {nested,path} -- 按路径删除这里的-/-与 MySQL 的同名运算符语义接近但对比表指出 Snowflake 使用:路径记法、BigQuery 使用JSON_VALUE函数族——同一份 JSON 提取逻辑在四个引擎上有四种写法这正是方言提示词存在的价值让模型按目标引擎选择正确语法。九、窗口函数与聚合窗口函数一节给出完整函数面与**命名窗口WINDOW 子句**用法SELECT ROW_NUMBER() OVER (PARTITION BY cat ORDER BY amt DESC), RANK() OVER w, DENSE_RANK() OVER w, -- 命名窗口 SUM(amt) OVER (PARTITION BY cat), LAG(amt, 1, 0) OVER (ORDER BY dt), -- 带默认值 LEAD(amt) OVER (ORDER BY dt), FIRST_VALUE(amt) OVER w, LAST_VALUE(amt) OVER w, NTH_VALUE(amt, 2) OVER w, NTILE(4) OVER (ORDER BY amt), -- 四分位 PERCENT_RANK() OVER w, CUME_DIST() OVER w FROM t WINDOW w AS (PARTITION BY cat ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)聚合一节则强调FILTER子句是 PostgreSQL 专属的条件聚合手段比CASE WHEN更干净SELECT COUNT(*), COUNT(DISTINCT col), SUM(amount), AVG(amount), MIN(val), MAX(val), ARRAY_AGG(col ORDER BY sort_col), STRING_AGG(col, , ORDER BY sort_col), BOOL_AND(flag), BOOL_OR(flag), -- FILTER 子句PostgreSQL 专属 COUNT(*) FILTER (WHERE status active) AS active_count, SUM(amount) FILTER (WHERE type revenue) AS revenue FROM t GROUP BY category对比表中对应一行也确认了这一差异BigQuery 用COUNTIF、MySQL 用CASE WHEN、Snowflake 用IFF而 PostgreSQL 用FILTER (WHERE)。STRING_AGG、ARRAY_AGG允许在聚合内写ORDER BY这是生成每类产品按名称排序拼串类查询时值得直接采用的写法。十、CTE 与常用模式10.1 CTE 与递归 CTE-- 标准 CTE WITH active_users AS ( SELECT * FROM users WHERE status active ) SELECT * FROM active_users -- 递归 CTE用于层级、序列 WITH RECURSIVE subordinates AS ( SELECT id, name, manager_id, 1 AS depth FROM employees WHERE id 1 UNION ALL SELECT e.id, e.name, e.manager_id, s.depth 1 FROM employees e JOIN subordinates s ON e.manager_id s.id ) SELECT * FROM subordinates递归 CTE 是处理组织架构、物料清单等树形结构的正解也用于生成日期序列与 10.3 的GENERATE_SERIES互为替代方案。10.2 DISTINCT ONPostgreSQL 专属-- 取每组第一行比窗口函数干净得多 SELECT DISTINCT ON (category) * FROM products ORDER BY category, updated_at DESC文档明确指出DISTINCT ON比窗口函数 外层过滤的通用套路更简洁这是 PostgreSQL 独有的语法对比表 First per group 一行BigQuery / Snowflake 用QUALIFYMySQL 只能用窗口函数。使用时必须保证ORDER BY的第一组列与DISTINCT ON的列一致否则结果不符合预期。10.3 RETURNING 子句-- 从 INSERT/UPDATE/DELETE 返回受影响行 INSERT INTO t (col) VALUES (val) RETURNING * UPDATE t SET col new WHERE id 1 RETURNING id, col DELETE FROM t WHERE id 1 RETURNING *RETURNING让单条 DML 即可带回新插入的主键或修改后的行省去写后查的第二次往返。10.4 UpsertON CONFLICTINSERT INTO t (id, name, count) VALUES (1, foo, 1) ON CONFLICT (id) DO UPDATE SET count t.count 1 -- 或ON CONFLICT DO NOTHING对比表确认四种引擎四种 Upsert 写法PostgreSQL 用ON CONFLICTBigQuery / Snowflake 用MERGEMySQL 用ON DUPLICATE KEY。10.5 安全除法SELECT NULLIF(denominator, 0) AS safe_denom, numerator / NULLIF(denominator, 0)与第六节的NULLIF呼应分母为 0 时结果整体变为 NULL 而非抛出除零错误适合直接嵌入 BI 报表计算列。10.6 GENERATE_SERIES 序列生成-- 数字序列 SELECT GENERATE_SERIES(1, 10) SELECT GENERATE_SERIES(1, 10, 2) -- 步长 2 -- 日期序列 SELECT GENERATE_SERIES(2024-01-01::DATE, 2024-12-31::DATE, 1 month)生成补齐时间轴如过去 12 个月日历表时优先于递归 CTE。10.7 LATERAL 连接-- FROM 中的关联子查询非常强大 SELECT u.*, recent.* FROM users u, LATERAL ( SELECT * FROM orders WHERE user_id u.id ORDER BY created_at DESC LIMIT 3 ) AS recentLATERAL 允许内层子查询引用外层行实现每个用户最近 3 笔订单这类分组取 Top-N查询是普通子查询无法表达的。十一、跨方言差异总表这份文档的结论章文档末尾的对比表把上述所有规则压缩成一张速查表这也是对 LLM 约束力最强的一段——它直接告诉模型选哪个写法取决于当前引擎特性PostgreSQLBigQueryMySQLSnowflake标识符引用双引号反引号反引号双引号字符串连接||||或CONCATCONCAT||大小写不敏感 LIKEILIKELOWER()变通LIKE默认ILIKE数组下标1 起始0 起始不支持0 起始日期截断DATE_TRUNC(month, d)DATE_TRUNC(d, MONTH)DATE_FORMATDATE_TRUNCJSON 访问-、-JSON_VALUE-、-:路径记法条件聚合FILTER (WHERE)COUNTIFCASE WHENIFF每组取首行DISTINCT ONQUALIFY窗口函数QUALIFYUpsertON CONFLICTMERGEON DUPLICATE KEYMERGE从源码结构看这张表在 metabase.llm.api 的一次性 SQL 生成流程中与驱动显示名driver/display-name一起填入系统提示词而在 MetaBot 会话中则由 dialect-preload-parts 以预载消息形式注入。十二、小结方言提示词的设计模式与扩展方式通读postgresql.md后可以提炼出 Metabase 方言提示词的编写模式供对照其他 13 个方言文件目录resources/metabot/prompts/dialects/指令化而非教程化全文是规则 可执行 SQL 示例的清单不解释 PostgreSQL 是什么因为它的读者是已经拿到 schema 的 LLM专属语法单独标记ILIKE、~正则、FILTER (WHERE)、DISTINCT ON、RETURNING、ON CONFLICT、LATERAL都被显式标注 PostgreSQL-specific避免模型把 Postgres 语法误用到其他引擎陷阱用最高级别标注数组 1 起始下标被标为 Critical对比 BigQuery 的 0 起始这是跨方言迁移中最容易静默出错的一点以对比表收尾把单方言规则放回四引擎矩阵约束模型在多库环境下的选择。如果你想为 Metabase 新增或调整某个引擎的方言指令从源码看入口就是 metabase/driver/postgres.clj 这类driver/llm-sql-dialect-resource方法与resources/metabot/prompts/dialects/下的对应.md文件——两者缺一不可映射缺失时 llm-sql-dialect-resource 返回nilload-dialect-instructions 便不会把任何方言正文注入提示词。测试侧可参考 test/metabase/metabot/skills_test.clj 中对技能注册、目录过滤与load_skill门禁的断言方式来验证方言技能的注册与隐藏行为是否符合预期。适用前提与限制以上机制描述基于当前仓库源码llm-sql-dialect-resource标注:added 0.59.0其中一次性 SQL 生成端点要求已配置 Anthropic API key见 api.clj 的 403 检查与至少一张可访问的表MetaBot 会话的方言预载则发生在 SQL 编辑器上下文中。方言文件本身是纯静态资源修改后需要重新加载技能注册表load-skills!注释说明可在 REPL 中刷新才会生效于 MetaBot 技能链路。【免费下载链接】metabaseThe easy-to-use open source Business Intelligence and Embedded Analytics tool that lets everyone work with data :bar_chart:项目地址: https://gitcode.com/GitHub_Trending/me/metabase创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考