node.jspostgresqldatepsqlpg

how to set the default value for postgresql DATE datatype - CURRENT_DATE is not working


I'm using pg package in node.js and have created my table schema:

CREATE TABLE clients_steps (
  id SERIAL PRIMARY KEY,
  client_id INTEGER NOT NULL
    REFERENCES clients ON DELETE CASCADE,
  step_id INTEGER NOT NULL
    REFERENCES steps ON DELETE CASCADE,
  update_date DATE NOT NULL DEFAULT CURRENT_DATE 
);

Now I'm trying to seed my database. But it kept throwing errors at me. My seed file:

### this works fine ###
INSERT INTO clients_steps (client_id, step_id, update_date)
VALUES (1, 2, '2023-02-02');

### this is not working ###
INSERT INTO clients_steps (client_id, step_id, update_date)
VALUES (1, 1);

And the error is:

ERROR: INSERT has more target columns than expressions

LINE 1: INSERT INTO clients_steps (client_id,step_id,update_date)

I think the reason is it can't insert the default value into the column.

So how should I set up the default value for a DATE datatype? I don't need the time stamp, only the date will be required.

Thank you so much!


Solution

  • You have two options to turn your second insert into valid SQL:

    1. omit the column for which you want the default value from the list:

      INSERT INTO clients_steps (client_id, step_id)
      VALUES (1, 1);
      
    2. use the special value DEFAULT:

      INSERT INTO clients_steps (client_id, step_id, update_date)
      VALUES (1, 1, DEFAULT);