PostgreSQL Trigger creation issues

Question

I've looked all over the web and stackoverflow and despite trying to do everything I see I can't get this to work. Code:

CREATE TABLE "ExpressionHistory" (
ExpressionHistoryId SERIAL PRIMARY KEY,
ExpressionId INT NOT NULL,
UserId INT NOT NULL,
AccessDate TIMESTAMP NOT NULL,
CreatedBy VARCHAR(12) NOT NULL,
CreatedDate TIMESTAMP NOT NULL,
ModifiedBy VARCHAR(12) NOT NULL,
ModifiedDate TIMESTAMP NOT NULL);

ALTER TABLE "ExpressionHistory"
ADD CONSTRAINT FK_EXPRESSIONHISTORY_EXPRESSION
FOREIGN KEY ("expressionid")
REFERENCES "Expression";

CREATE OR REPLACE FUNCTION prune_expression_history() RETURNS TRIGGER AS $pruned_expression_history$
    BEGIN
        DELETE FROM ExpressionHistory WHERE ExpressionHistoryId = 
            (SELECT ExpressionHistoryId FROM ExpressionHistory WHERE 
            UserId = NEW.UserId AND
            (SELECT COUNT(1) FROM ExpressionHistory WHERE UserId = NEW.UserId) > 10
            ORDER BY AccessDate DESC 
            LIMIT 1 OFFSET 10 );
        RETURN NULL;
    END;
$pruned_expression_history$ LANGUAGE plpgsql;

CREATE TRIGGER PruneExpressionHistoryTable
    AFTER INSERT ON ExpressionHistory
    FOR EACH ROW
    EXECUTE PROCEDURE prune_expression_history();

Error:

ERROR:  relation "expressionhistory" does not exist
********** Error **********

ERROR: relation "expressionhistory" does not exist
SQL state: 42P01

So, the goal is that on an insert to this table, I delete the oldest record of 10 for a user when an insert on the table occurs. I can't get the trigger to instantiate and this is clearly a problem. I suspect the issue is something subtle but I can't isolate it. This occurs in the CREATE TRIGGER execution. Thanks in advance.

Answer 1

The problem is that you are mixing naming conventions. The principle is simple: identifiers in double quotes are case sensitive; identifiers without quotes are case insensitive. So ExpressionHistory is the same as expressionhistory, but "ExpressionHistory" is not the same as ExpressionHistory.

You need to decide on a clear naming convention. Most Postgres users prefer identifiers without double quotes (case insensitive).