0

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?

  • What DBMS is this? – ypercubeᵀᴹ Oct 20 '15 at 16:26
  • @ypercube I am using dbvis – smac89 Oct 20 '15 at 16:27
  • @ypercube I updated the question, I was actually inserting into a view not a table and I used a rule to control this – smac89 Oct 20 '15 at 16:34
  • What's the point of the rule? – ypercubeᵀᴹ Oct 20 '15 at 17:13
  • The rule is for the view. Can't insert into a view without the rule. – smac89 Oct 20 '15 at 17:26
4

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;
  • Thanks! I was able to get an answer to this earlier. What is the difference between VALUES(NEW.name, coalesce(NEW._date, current_date)); and VALUES(NEW.name, (SELECT coalesce(NEW._date, current_date)));? – smac89 Oct 20 '15 at 18:16
  • @Smac89: the first one is shorter and probably more efficient. Although syntactically correct, the second one just looks "wrong" in my eyes. – a_horse_with_no_name Oct 20 '15 at 18:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.