Syntax Error at or near "CONSTRAINT" PostgreSQL

Question

I'm trying to create a new table and keep getting a syntax error at or near "CONSTRAINT" in the statement below. I've already made sure I have a , after the last column I declare, which is Tags right above the CONSTRAINT line. Does anyone know why this is happening? All the online examples of declaring constraints seem to involve CREATE TABLE statements, not CREATE TABLE AS statements. Does that matter? Thanks for your help!

By the way, the constraint line below was copied/pasted from a table that I created already, which used a CREATE TABLE statement, not a CREATE TABLE AS statement, which is why I ask if constraints don't work with CREATE TABLE AS statements.

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";

Answer 1

You need to add the constraint using a second statement:

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")

The CREATE TABLE and ALTER TABLE statements should be run in a single transaction.

Please have a look through this great Stack DBA article, with @a_horse_with_no_name as a contributor.