在“CONSTRAINT”处或附近的语法错误PostgreSQL

问题描述：

我试图创建一个新表，并在下面的语句中继续获取syntax error at or near "CONSTRAINT"。我已经确认在我宣布的最后一列之后有一个,，它是CONSTRAINT行右上方的Tags。有谁知道为什么会发生这种情况？所有在线声明约束的例子似乎涉及CREATE TABLE语句，而不是CREATE TABLE AS语句。这很重要吗？谢谢你的帮助！在“CONSTRAINT”处或附近的语法错误PostgreSQL

顺便说一下，下面的约束线是从我创建的表中复制/粘贴的，它使用了CREATE TABLE语句，而不是CREATE TABLE AS语句，这就是为什么我询问约束是否不起作用CREATE TABLE AS语句。

CREATE TABLE "2017Projects" AS 
SELECT 
    "STAGING_PROJECTS"."_PROJECT_NUMBER", 
    "STAGING_PROJECTS"."_PROJECT_DESCRIPTION", 
    "STAGING_PROJECTS"."_PROJECT_STATUS", 
    "STAGING_PROJECTS"."_TEMPLATE_FLAG", 
    "STAGING_PROJECTS"."_PROJECT_START_DATE", 
    "STAGING_PROJECTS"."_PROJECT_COMPLETION_DATE", 
    "STAGING_PROJECTS"."_COMPANY", 
    "STAGING_PROJECTS"."_LOCATION_NUMBER", 
    "STAGING_TASKS"."_PARENT_TASK_NUMBER", 
    "STAGING_TASKS"."_PARENT_TASK_DESCRIPTION", 
    "STAGING_TASKS"."_TASK_NUMBER", 
    "STAGING_TASKS"."_TASK_DESCRIPTION", 
    "STAGING_TASKS"."_TASK_START_DATE", 
    "STAGING_TASKS"."_TASK_COMPLETION_DATE", 
    "STAGING_TASKS"."_EBS_ACCOUNT", 
    "STAGING_TASKS"."_FUSION_ACCOUNT", 
    CASE 
     WHEN left("STAGING_PROJECTS"."_PROJECT_NUMBER",2) = 'C0' THEN 
      '('||"STAGING_PROJECTS"."_PROJECT_NUMBER"||') '||"STAGING_PROJECTS"."_PROJECT_DESCRIPTION" 
     ELSE 
      "STAGING_PROJECTS"."_PROJECT_DESCRIPTION" 
    END AS "Project", 
    '('||"STAGING_TASKS"."_TASK_NUMBER"||') '||"STAGING_TASKS"."_TASK_DESCRIPTION" AS "Task", 
    NULL AS "Investment_Priority", 
    NULL AS "Class", 
    NULL AS "Tags", 

    --ERROR ON NEXT LINE HERE!!! 
    CONSTRAINT "2017Projects_pk" PRIMARY KEY ("_PROJECT_NUMBER", "_TASK_NUMBER") 
FROM 
    "STAGING_PROJECTS";

答

你需要使用第二语句添加约束：

CREATE TABLE "2017Projects" AS 
SELECT 
    "STAGING_PROJECTS"."_PROJECT_NUMBER", 
    ..., 
    NULL AS "Tags" 
FROM 
    "STAGING_PROJECTS"; 

ALTER TABLE "2017Projects" ADD CONSTRAINT "2017Projects_pk" 
    PRIMARY KEY ("_PROJECT_NUMBER", "_TASK_NUMBER")

的CREATE TABLE和ALTER TABLE报表应在一个事务中运行。

请通过this great Stack DBA article查看，并以@a_horse_with_no_name作为贡献者。

仅供参考：https：//www.postgresql.org/docs/9.5/static/sql-createtableas.html。 'create table as select'语法中没有'constraint'部分。 –

@RadekPostołowicz对不起，完全错过了，由于某种原因。我搜索了“CREATE TABLE AS”，出于某种原因只看到了“CREATE TABLE”文档，其中显示了“约束”部分。 – christopheralan88

@TimBiegeleisen谢谢，我想我可以做到这一点，但希望有一种方法可以在CREATE TABLE AS语句中完成。谢谢你的帮助！ – christopheralan88

在“CONSTRAINT”处或附近的语法错误PostgreSQL

相关推荐