字符串"电动"太长，但数据类型设置为 VARCHAR(500)

Question

我收到错误，我卡住了。

我创建了一个表：

CREATE TABLE filtered_derived_analytics.um_state_stats 
( 
    issued_but_not_imported integer, 
    imported_but_not_sent_to_apm integer, 
    in_apm_but_not_batched integer, 
    batched_but_not_approved integer, 
    approved_but_check_not_cut integer, 
    paid integer, 
    total integer, 
    collected_on date, 
    utility_type varchar(500),
    utility_company_name varchar(500),
    DATA OBJECT
) IF NOT EXISTS;

但是当我尝试使用以下方法将数据插入该表时：

SET current_schema =
  (SELECT LEFT("schema", 10) AS imported_at
   FROM PUBLIC.imports
   ORDER BY "schema" DESC
   LIMIT 1);

SELECT m.data_type AS utility_type,
       uc.name AS utility_company_name,
       COUNT(*) AS COUNT
FROM wegowise_meters m
LEFT JOIN wegowise_properties p ON p.meter_id = m.id
LEFT JOIN wegowise_users u ON p.user_id = u.id
LEFT JOIN wegowise_utility_companies uc ON m.utility_company_id = uc.id
WHERE u.username IN
    (SELECT wegowise_username
     FROM um_charter_customers)
  AND m.next_expected_datum_date < $current_schema
  AND m.closed = FALSE
  AND m.scope != 'VacantUnitMeter'
  AND m.apm_bill_creation_config_uuid IS NOT NULL
GROUP BY utility_type,
         utility_company_name;

MERGE INTO um_state_stats stats USING
  (SELECT *
   FROM table(RESULT_SCAN (LAST_QUERY_ID(-1)))) results ON stats.collected_on = $current_schema WHEN matched THEN
UPDATE
SET stats.utility_type = results.utility_type,
    stats.utility_company = results.utility_company_name,
    stats.issued_but_not_imported = results.COUNT WHEN NOT matched THEN
INSERT (utility_type,
        utility_company,
        issued_but_not_imported,
        DATA,
        collected_on)
VALUES (results.utility_type,
        results.utility_company_name,
        results.COUNT,
        OBJECT_CONSTRUCT(),
        $current_schema);

我收到一个错误，说字符串"电动"(utility_type(太长。我检查了utility_type中的值，它们都很短：

Gas
Electric
Water
Oil
Steam
Solar
Oil4
Oil6
Trash
Propane

有什么想法吗？我做错了什么？谢谢

Answer 1

尝试在MERGE INTO um_state_stats stats USING...之前放置一个DESCRIBE TABLE um_state_stats。 该MERGE语句可能没有使用我认为应该使用的filtered_derived_analytics架构。

同样，您可以通过将MERGE INTO um_state_stats stats USING...更改为 MERGE INTO filtered_derived_analytics.um_state_stats stats USING... 来在表名称前面加上架构名称

(虽然希望你在 3 个月😀后解决了它(