Postgresql – Postgres: error message “does not exist” when dropping existing trigger

postgresqltrigger

I have a trigger associated to a table. When i drop it via

DROP TRIGGER IF EXISTS myTrigger on dummytable;

postgres tells me

NOTICE:  trigger "mytrigger" for table "dummytable" does not exist, skipping
DROP TRIGGER

When i use a dummy table for tests this works. I've tried:

changing CaSe,
making the trigger name longer (the real trigger is 17 chars long)

When testing it always drops without issue.

This is Postgres 8.2 on Linux. What does work is adding quotation marks:

DROP TRIGGER IF EXISTS "myTrigger" on dummytable;

Why this is so is beyond me, there's nothing in the docs that mentions the name must be in quotation marks.

Am i missing something obvious?

Best Answer

The behaviour you've described is correct and documented : http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

If the trigger have been created with double quote and special char, it must be used with the same syntax.

Related Solutions

Postgresql – ERROR: database “dbname” does not exist

Your database was created using double quotes so its name is now case-sensitive. Therefore, you now need to always use double quotes when referring to it:

drop database "Blog_test";

More details about quoted identifiers (a database name is an identifier just like a column or table name) can be found in the manual:
http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

Mysql create trigger if not exist

There is no CREATE TRIGGER IF NOT EXISTS

There is DROP TRIGGER IF EXISTS

You must execute the trigger's creation as follows:

USE xxx_admin
DELIMITER $$
DROP TRIGGER IF EXISTS test_AFTER_UPDATE $$
CREATE DEFINER=`root`@`localhost` TRIGGER `xxx_admin`.`test_AFTER_UPDATE`
AFTER UPDATE ON `test` FOR EACH ROW
BEGIN
    INSERT INTO auditTest 
    select *, now() from test where id = NEW.id;
END $$
DELIMITER ;

GIVE IT A TRY !!!

As for you error, using IF like that is not valid from the CLI.

Best Answer

Related Solutions

Postgresql – ERROR: database “dbname” does not exist

Mysql create trigger if not exist

Related Question