PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL 12.18 :: ERROR: syntax error at or near "NOT" LINE 3: , date_raised TIMESTAMPZ NOT NULL

Trying to create a table in PostgreSQL from the Commandline. Below is the code that I am try to execute to create the table.

execute to create the table.

CREATE TABLE RISK_DETAILS(
RISK_DTL_ID SERIAL PRIMARY KEY, DATE_RAISED TIMESTAMP NOT NULL, PROJECT_NAME VARCHAR (50),
RAISED_BY VARCHAR(50), DESCIPTION VARCHAR(1024), CATEGORY VARCHAR(50), PROBABILITY NUMERIC, IMPACT_ID INTEGER, INITIAL_RISL_VALUE NUMERIC,
CURRENT_RISK_VALUE NUMERIC, I
MITIGATING_ACTION VARCHAR(500),
MITIGATING_PLANNED_DATE TIMESTAMP NOT NULL, CREATED_ON TIMESTAMP NOT NUMM DEFAULT NOW,
CONSTRAINT(FK_IMPACT_ID) FOREIGN KEY(IMPACT_ID) REFERENCES IMPACT_DROPDOWN( IMPACT_ID)
);

When I run this code on the command Line, I get the following error.

ERROR: syntax error at or near "NOT" LINE 3: DATE_RAISED TIMESTAMP NOT NULL

If I remove the code which is causing the error and execute once more, then it complains at the varchar in Project name field.

I

Tags:

2

Answers


  1. 0

    After beautifying the query, you will see what the problem is.

    1. The DESCIPTION column name was changed to DESCRIPTION.

    2. The INITIAL_RISL_VALUE column name was changed to INITIAL_RISK_VALUE.

    3. Delete "I" between CURRENT_RISK_VALUE NUMERIC, and MITIGATING_ACTION VARCHAR(500),

    4. Changed NOT NUMM to NOT NULL

    5. When setting the default value for the CREATED_ON column, parentheses were added to NOW.

    6. The foreign key constraint FK_IMPACT_ID was added using the ALTER TABLE syntax.

    Result Query is below

    CREATE TABLE RISK_DETAILS(
  RISK_DTL_ID SERIAL PRIMARY KEY,
  DATE_RAISED TIMESTAMP NOT NULL,
  PROJECT_NAME VARCHAR(50),
  RAISED_BY VARCHAR(50),
  DESCRIPTION VARCHAR(1024),
  CATEGORY VARCHAR(50),
  PROBABILITY NUMERIC,
  IMPACT_ID INTEGER,
  INITIAL_RISK_VALUE NUMERIC,
  CURRENT_RISK_VALUE NUMERIC,
  MITIGATING_ACTION VARCHAR(500),
  MITIGATING_PLANNED_DATE TIMESTAMP NOT NULL,
  CREATED_ON TIMESTAMP NOT NULL DEFAULT NOW()
);

ALTER TABLE RISK_DETAILS
ADD CONSTRAINT FK_IMPACT_ID
FOREIGN KEY (IMPACT_ID)
REFERENCES IMPACT_DROPDOWN(IMPACT_ID);
    Login or Signup to reply.
  2. 0

    I formatted your query to make it easier to read:

    create table risk_details (
  risk_dtl_id serial primary key, 
  date_raised timestamp not null, 
  project_name varchar (50),
  raised_by varchar(50), 
  desciption varchar(1024), 
  category varchar(50), 
  probability numeric, 
  impact_id integer, 
  initial_risl_value numeric,
  current_risk_value numeric, i -- what's this "i" here?
  mitigating_action varchar(500),
  mitigating_planned_date timestamp not null,
  created_on timestamp not numm default now,
    -- "numm": this is a type and should say "null"
    -- also NOW() is a function
  constraint (fk_impact_id) foreign key (impact_id) 
    references impact_dropdown (impact_id)
  -- remove the parenthesis in the constraint name
);

    After fixing these issues the query can run as:

    create table risk_details (
  risk_dtl_id serial primary key, 
  date_raised timestamp not null, 
  project_name varchar (50),
  raised_by varchar(50), 
  desciption varchar(1024), 
  category varchar(50), 
  probability numeric, 
  impact_id integer, 
  initial_risl_value numeric,
  current_risk_value numeric,
  mitigating_action varchar(500),
  mitigating_planned_date timestamp not null,
  created_on timestamp not null default now(),
  constraint fk_impact_id foreign key (impact_id) 
    references impact_dropdown (impact_id)
);

    See running example at db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search