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

Community · Accepted Answer · 2017-04-13 12:42:39Z

2

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.

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Jun 28, 2016 at 12:37

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Radek Postołowicz Over a year ago

For reference: postgresql.org/docs/9.5/static/sql-createtableas.html. There is no constraint section in create table as select grammar.

jones-chris Over a year ago

@RadekPostołowicz Sorry, totally missed that for some reason. I searched for CREATE TABLE AS and for some reason only saw the CREATE TABLE documentation which shows a constraint section.

jones-chris Over a year ago

@TimBiegeleisen Thanks, I was thinking I could do that but was hoping that there was a way to do it in the CREATE TABLE AS statement. Thanks for your help!

Collectives™ on Stack Overflow

Syntax Error at or near "CONSTRAINT" PostgreSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related