Trigger function cannot reference field name with capital letter

Discussion:

Patrick Dung

2014-08-14 03:52:39 UTC

Hello PGSQL users,

I have a field called postTimestamp.
The trigger function could not reference it.
When I change my field to post_timestamp. I can reference it from the tigger function.
Version is 9.3.5. Any comment?

< 2014-08-14 00:23:32.717 HKT >ERROR: post "new" has no field "posttimestamp"
< 2014-08-14 00:23:32.717 HKT >CONTEXT: SQL statement "SELECT * from tbl1
where NEW.posttimestamp > "2014-01-01 00:00:00" )"
PL/pgSQL function test_trigger() line 9 at assignment
< 2014-08-14 00:23:32.717 HKT >STATEMENT: INSERT INTO public.tbl1("vendor", url, "postTimestamp", product, "type", "itemID") VALUES ('vendor1'::text, 'http://example.org'::text, '2014-01-01 01:01:01'::timestamp without time zone, 'product1'::text, 'food'::text, '1'::bigint)
< 2014-08-14 00:32:39.708 HKT >ERROR: syntax error at or near "SELECT" at character 314

Thanks and regards,
Patrick

Adrian Klaver

2014-08-14 04:07:04 UTC

Permalink

Post by Patrick Dung
Hello PGSQL users,
I have a field called postTimestamp.
The trigger function could not reference it.
When I change my field to post_timestamp. I can reference it from the tigger function.
Version is 9.3.5. Any comment?
< 2014-08-14 00:23:32.717 HKT >ERROR: post "new" has no field
"posttimestamp"

The clue is above. Postgres folds unquoted mixed case to lower case by
default, so it is looking for posttimestamp. If you want to preserve the
mixed case, quote the field name "postTimestamp".

Post by Patrick Dung
< 2014-08-14 00:23:32.717 HKT >CONTEXT: SQL statement "SELECT * from tbl1
where NEW.posttimestamp > "2014-01-01 00:00:00" )"
PL/pgSQL function test_trigger() line 9 at assignment
< 2014-08-14 00:23:32.717 HKT >STATEMENT: INSERT INTO
public.tbl1("vendor", url, "postTimestamp", product, "type", "itemID")
VALUES ('vendor1'::text, 'http://example.org'::text, '2014-01-01
01:01:01'::timestamp without time zone, 'product1'::text, 'food'::text,
'1'::bigint)
< 2014-08-14 00:32:39.708 HKT >ERROR: syntax error at or near "SELECT"
at character 314
Thanks and regards,
Patrick

--
Adrian Klaver
***@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

John R Pierce

2014-08-14 04:13:35 UTC

Permalink

Post by Patrick Dung
I have a field called postTimestamp.
The trigger function could not reference it.
When I change my field to post_timestamp. I can reference it from the tigger function.
Version is 9.3.5. Any comment?
< 2014-08-14 00:23:32.717 HKT >ERROR: post "new" has no field
"posttimestamp"
< 2014-08-14 00:23:32.717 HKT >CONTEXT: SQL statement "SELECT * from tbl1
where NEW.posttimestamp > "2014-01-01 00:00:00" )"
PL/pgSQL function test_trigger() line 9 at assignment

field and talbe names with mixed case have to be "Quoted". string
constants, on the other hand, are are in single 'quotes'.

try...

SELECT * from tbl1 where new."postTimestamp' > timestamp '2014-01-01
00:00:00'

Post by Patrick Dung
< 2014-08-14 00:23:32.717 HKT >STATEMENT: INSERT INTO
public.tbl1("vendor", url, "postTimestamp", product, "type", "itemID")
VALUES ('vendor1'::text, 'http://example.org'::text, '2014-01-01
01:01:01'::timestamp without time zone, 'product1'::text,
'food'::text, '1'::bigint)
< 2014-08-14 00:32:39.708 HKT >ERROR: syntax error at or near
"SELECT" at character 314

those two error logs have different timestamps, I don't believe that
error is on that statement.

there's a whole lot of unnecessary typecasting in that insert, however.

INSERT INTO public.tbl1("vendor", url, "postTimestamp", product, "type",
"itemID")
VALUES ('vendor1', 'http://example.org', timestamp '2014-01-01
01:01:01', 'product1', 'food', 1)

would suffice nicely.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

John R Pierce

2014-08-14 04:18:11 UTC

Permalink

Post by John R Pierce
SELECT * from tbl1 where new."postTimestamp' > timestamp '2014-01-01
00:00:00'

oops.

SELECT * from tbl1 where new."postTimestamp" > timestamp '2014-01-01
00:00:00'

I meant. I should proof what I write, hah!
--
john r pierce 37N 122W
somewhere on the middle of the left coast

--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Patrick Dung

2014-08-14 06:10:51 UTC

Permalink

Thanks all for the help.

BTW, letter casing is just a preference.
Some people liked to use all small caps, some liked to use all big caps.
I sometimes found that mixed case is more meaningful for the filed (eg. serialnumber vs serialNumber)

What is your preference or suggestion?

Post by John R Pierce
SELECT * from tbl1 where new."postTimestamp' > timestamp '2014-01-01
00:00:00'

oops.

SELECT * from tbl1 where new."postTimestamp" > timestamp '2014-01-01
00:00:00'

I meant. I should proof what I write, hah!
--
john r pierce 37N 122W
somewhere on the middle of the left coast

--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Pavel Stehule

2014-08-14 06:20:29 UTC

Permalink

Post by Patrick Dung
Thanks all for the help.
BTW, letter casing is just a preference.
Some people liked to use all small caps, some liked to use all big caps.
I sometimes found that mixed case is more meaningful for the filed (eg.
serialnumber vs serialNumber)
What is your preference or suggestion?

Camel notation is not practical for SQL identifiers - SQL is not case
sensitive, but there are possible exception when you use double quotes. And
then usually you can hit a situation when one identifier is in one
situation case sensitive and elsewhere it is translated to lowercase. It
is not bug, it has usually good reasons, but it is terrible issue for
beginners. So SQL identifiers should be in lowercase or uppercase.
Uppercase has bigger sense on Oracle, lowercase on PostgreSQL.

Regards

Pavel

Post by Patrick Dung
On Thursday, August 14, 2014 12:18 PM, John R Pierce <

Post by John R Pierce
SELECT * from tbl1 where new."postTimestamp' > timestamp '2014-01-01
00:00:00'

oops.
SELECT * from tbl1 where new."postTimestamp" > timestamp '2014-01-01
00:00:00'
I meant. I should proof what I write, hah!
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver

2014-08-14 13:41:00 UTC

Permalink

My preference is lower case with underscores between words. I stay away
from CamelCaps for the reasons Pavel mentioned. Probably the most
important thing is to be consistent in your style.

--
Adrian Klaver
***@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Alban Hertroys

2014-08-14 14:16:11 UTC

Permalink

You can use whatever case you like, as long as you never quote your
identifiers so they stay case-insensitive. As long as you stick to
that rule, you can use upper or lower camel caps or all upper or lower
case in your SQL.

The need for quoting identifiers often comes from ORM's that attempt
to preserve case, which is totally unnecessary and causes more trouble
than it has benefits.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general