SpringBoot集成PostgreSQL执行CREATE TABLE AS查询报错如何解决？

问题原因 #

你的SQL存在多个不符合PostgreSQL语法规范的问题，这是H2兼容特性和PostgreSQL标准语法的差异导致的：

• 子查询非法引用外层表别名：你在子查询SELECT MAX(CONVERT(TIMESTAMP, DATETIME)) AS LATESTDATE FROM T GROUP BY ISIN中用到的表别名T是外层RTDATABASE AS T的别名，PostgreSQL不允许在该层级子查询中直接引用外层表别名作为查询来源。
• 类型转换函数写法错误：PostgreSQL的CONVERT函数用于字符集转换，类型转换需要使用CAST(表达式 AS 目标类型)或者表达式::目标类型语法，不支持你用到的CONVERT(字段, 类型)写法。
• JOIN语句缺失关联条件：你对RTDATABASE和最新时间子查询执行JOIN时没有指定ON关联条件，语法不合法，同时你需要匹配每个ISIN对应的最新时间，必须关联ISIN字段相等。
• 子查询括号冗余：CREATE TABLE AS后直接跟合法SELECT语句即可，不需要额外嵌套括号加别名，你最后的AS LATESTDATA属于冗余写法。

修正后的代码 #

"CREATE TABLE IF NOT EXISTS EOD AS " +
                        "SELECT ID, " +
                        "CUSIP, " +
                        "NAME, " +
                        "ISIN, " +
                        "NAV, " +
                        "RIC, " +
                        "RTV," +
                        "VOLATILITY as volatility, " +
                        "CURRENCY as currency, "+ 
                        "ASK_ISSUER as ask_issuer, " +
                        "ASK_KECH as ask_kech, " +
                        "AVERAGE_SCORE as average_score, " +
                        "BID_ISSUER as bid_issuer, " +
                        "BID_KECH as bid_kech, " +
                        "BID_ONLY as bid_only, " +
                        "CREATOR as creator, " +
                        "DECENTER as decenter, " +
                        "DENOMINATION as denomination, " +
                        "FORCE_PUBLISH as force_publish, " +
                        "ISSUER_SPREAD as issuer_spread, " +
                        "KECH_MARGIN as kech_margin, " +
                        "KECH_SPREAD as kech_spread, " +
                        "DISTANCE_TO_BANDS_SCORE as distance_to_bands_score, " +
                        "DIFFERENCE_TO_LEXIFI_PRICE_SCORE as difference_to_lexifi_price_score, " +
                        "METRIC_C as metric_c, " +
                        "PRICE_EXPRESSION as price_expression, " +
                        "PRODUCT_TYPE as product_type, " +
                        "PUBLISHED as published, " +
                        "PUBLISHING as publishing, " +
                        "TIMESTAMP as timestamp " +
                        "FROM RTDATABASE AS T " +
                        "JOIN (SELECT ISIN, MAX(CAST(TIMESTAMP AS TIMESTAMP)) AS LATESTDATE FROM RTDATABASE GROUP BY ISIN) tm " +
                        "ON T.ISIN = tm.ISIN AND CAST(T.TIMESTAMP AS TIMESTAMP) = tm.LATESTDATE;"

注意：如果你的RTDATABASE表中TIMESTAMP字段本身已经是时间类型，不需要额外做类型转换，可以直接去掉CAST函数，使用MAX(TIMESTAMP)即可，减少不必要的运算开销。

内容的提问来源于stack exchange，提问作者user16806803