PostgreSQL – Using Default Values in Primary Key

default valuepostgresqlprimary-key

Good day.

I have a table like this:

CREATE TABLE mytable (
    myname varchar(30),
    mydate date DEFAULT CURRENT_DATE,
    PRIMARY KEY(myname, mydate)
);

EDIT:
Forgot to mention I am using a view and a rule

So I have a view that is basically the same thing as the table

CREATE VIEW mytable_view (name, _date)
AS SELECT (myname, mydate)
FROM mytable;

And the rule is as follows:

CREATE RULE update_mytable
AS ON INSERT TO mytable_view
DO INSTEAD (
    INSERT INTO mytable(myname, mydate)
    VALUES(NEW.name, NEW._date);
);

Now I try to insert into the table using the view by doing this:

INSERT INTO mytable_view(name)
    VALUES("Mozzart");

However, I get an error saying:

ERROR: null value in column "mydate" violates not-null constraint

Why is this? I expected the default value to be used if the value being inserted is null or wasn't specified. Is this not how default works?

Best Answer

To begin with: for a simple view like that you don't need a rule (at least not in any current Postgres version).

The reason you get that problem is this: 

INSERT INTO mytable(myname, mydate)
VALUES(NEW.name, NEW._date);

Now if you don't supply value for mydate when inserting into the view, then NEW._date will be null, and thus you are trying to put an explicit NULL value into that column. You need to take that situation into account:

INSERT INTO mytable(myname, mydate)
VALUES(NEW.name, coalesce(NEW._date, current_date));

Unrelated but: you have another problem in your statement: "Mozzart" is a column name, string literals need to be enclosed in single quotes: 'Mozzart'. Postgres should have thrown a column "Mozzart" does not exist error.

For details, please see the manual: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

Finally: your view definition is wrong: 

SELECT (myname, mydate)

selects a single column that is an anonymous structure with two elements. You need to get rid of those useless parentheses, the CREATE VIEW should have thrown an error CREATE VIEW specifies more column names than columns.

You need to use:

CREATE VIEW mytable_view (name, _date)
AS 
SELECT myname, mydate --<< NO parentheses!
FROM mytable;
Related Question