主页 / 问题 / 1321921
Accepted
Viktor Andriichuk
Asked:2022-08-27 20:45:11 +0000 UTC2022-08-27 20:45:11 +0000 UTC

RedShift-PostgreSQL：错误：“AS”位置或附近的语法错误：42

  • 772

我正在尝试在 RedShift 中提出请求：

INSERT INTO target.table AS t (
            project_id,
            project_number,
            project_name,
            connect_project_id,
            project_desc,
            project_type,
            project_status,
            project_path,
            language_code,
            country_code,
            timezone,
            project_created_at,
            project_modified_at
            )
        SELECT
            s.id,
            s.number,
            s.name,
            s.connect_project_id,
            s.description,
            s.type,
            s.status,
            s.path,
            s.language,
            s.country,
            s.timezone,
            s.created,
            s.modified
        FROM source.table AS s
        ON CONFLICT (project_id)
        DO UPDATE SET
            (project_number, project_name, connect_project_id, project_desc,
            project_type, project_status, project_path, language_code,
            country_code, timezone, project_created_at, project_modified_at,
            date_updated) = (EXCLUDED.project_number,
            EXCLUDED.project_name, EXCLUDED.connect_project_id,
            EXCLUDED.project_desc, EXCLUDED.project_type, EXCLUDED.project_status,
            EXCLUDED.project_path, EXCLUDED.language_code, EXCLUDED.country_code,
            EXCLUDED.timezone, EXCLUDED.project_created_at,
            EXCLUDED.project_modified_at, NOW())
        WHERE
            t.project_number != EXCLUDED.project_number
            OR t.project_name != EXCLUDED.project_name
            OR t.connect_project_id != EXCLUDED.connect_project_id
            OR t.project_desc != EXCLUDED.project_desc
            OR t.project_type != EXCLUDED.project_type
            OR t.project_status != EXCLUDED.project_status
            OR t.project_path != EXCLUDED.project_path
            OR t.language_code != EXCLUDED.language_code
            OR t.country_code != EXCLUDED.country_code
            OR t.timezone != EXCLUDED.timezone
            OR t.project_created_at != EXCLUDED.project_created_at
            OR t.project_modified_at != EXCLUDED.project_modified_at;

但我收到一个错误：错误：“AS”位置或附近的语法错误：42

我不知道出了什么问题。也许有人知道？

postgresql

1 个回答

  1. Best Answer

    我意识到 RedShift 不接受某些东西，例如 INSERT 中的 AS 或 ON CONFLICT。

    所以我用以下内容替换了该查询：

    begin transaction;

update target 
set col1 = stage.col1, 
col2 = stage.col2, 
col3 = 'expression' 
from stage 
where target.primarykey = stage.primarykey 
and target.distkey = stage.distkey 
and target.col3 > 'last_update_time' 
and (target.col1 != stage.col1 
or target.col2 != stage.col2 
or target.col3 = 'filter_expression');

insert into target
(select col1, col2, 'expression'
from stage);

end transaction;

    我从官方 RedShift 文档中获取它。

    • 0

相关问题