Delimited identifier brhavior

Tatsuo Ishii <ishii@postgresql.org> wrote:

It seems PostgreSQL thinks "t1"_foo is equivalent to t1.

It thinks you've given "t1" an alias of "_foo" in that query, same
as if you'd had a space between "t1" and _foo.

-Kevin

Tatsuo Ishii wrote:

test=# select * from "t1"_foo;
select * from "t1"_foo;
i
---
(0 rows)

It seems PostgreSQL thinks "t1"_foo is equivalent to t1. Is this an
expected behavior?

That code looks badly written in any event. Delimiters should be put around
each part of an identifier or chain as a whole, such as:

select * from "t1_foo";

Or with schema-delimited objects, for example, any of these:

schema.table

"schema".table

schema."table"

"schema"."table"

Personally, I treat all of my identifiers as being case-sensitive. Knowing that
Pg treats non-delimited identifiers as being lowercase, I write undelimited when
the identifier is entirely lowercase, and I delimit ones that have any
uppercase. And by doing this consistently everything works correctly. Since
most of my identifiers are lowercase anyway, the code also reads cleanly in general.

-- Darren Duncan

On 11/11/2010 06:03 PM, Tatsuo Ishii wrote:

Hi,

I' wondering if following delimited identifier brhavior is correct or
not:

test=# create table t1(i int);
create table t1(i int);
CREATE TABLE
test=# create table t1_foo(i int, j int);
create table t1_foo(i int, j int);
CREATE TABLE
test=# select * from t1;
select * from t1;
i
---
(0 rows)

test=# select * from t1_foo;
select * from t1_foo;
i | j
---+---
(0 rows)

test=# select * from "t1"_foo;
select * from "t1"_foo;
i
---
(0 rows)

It seems PostgreSQL thinks "t1"_foo is equivalent to t1. Is this an
expected behavior?

It's treating _foo as an alias in the query for t1. So the behaviour is
quite correct, I think.

cheers

andrew

It seems PostgreSQL thinks "t1"_foo is equivalent to t1.

It thinks you've given "t1" an alias of "_foo" in that query, same
as if you'd had a space between "t1" and _foo.

Oh, ok. I thought we always need at least one space character between
the table name and the alias.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp