Cannot Create Trigger For a Table with Name In Upper Case

[ghost]

• Given I am using PostgreSQL 9.4
• And I have a table in public schema

CREATE TABLE "CHECK_ROW_LEVEL_LOCKS"
(
  id serial NOT NULL,
  msg text,
  CONSTRAINT "CHECK_ROW_LEVEL_LOCKS_pkey" PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "CHECK_ROW_LEVEL_LOCKS"
  OWNER TO postgres;

• And when I try to add audit to it

SELECT audit.audit_table('CHECK_ROW_LEVEL_LOCKS');

• Then I receive an error message

ERROR: relationship "check_row_level_locks" does not exist

If the table was created in lower case check_row_level_locks, then it would all be fine.

[ghost]

Interestingly that if the table name is not quoted at creation time it all works fine.

CREATE TABLE CHECK_ROW_LEVEL_LOCKS
(
  id serial NOT NULL,
  msg text,
  CONSTRAINT "CHECK_ROW_LEVEL_LOCKS_pkey" PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE CHECK_ROW_LEVEL_LOCKS
  OWNER TO postgres;

[nineinchnick]

When you don't quote it it's the same as if you would use lower case. Unquoted object names are case insensitive. Citing the manual:

Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case.

[nineinchnick]

I've checked the code and suprisingly it uses quote_literal for some parts but not quote_ident for table names.

[ghost]

Thanks for your comments and for the manual. A question on _quoteliteral and checking the code: which code did you check? I had a look at https://github.com/2ndQuadrant/audit-trigger/blob/master/audit.sql and there are no references of _quoteliteral.

[nineinchnick]

I found a few: https://github.com/2ndQuadrant/audit-trigger/blob/master/audit.sql#L194 https://github.com/2ndQuadrant/audit-trigger/blob/master/audit.sql#L199 https://github.com/2ndQuadrant/audit-trigger/blob/master/audit.sql#L209

[ghost]

Indeed. :) Wonder how I managed to miss it with CTRl+F.