Обсуждение: Delimited identifier brhavior

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?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

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