Lists: | pgsql-hackers |
---|
From: | Tatsuo Ishii <ishii(at)postgresql(dot)org> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Delimited identifier brhavior |
Date: | 2010-11-11 23:03:50 |
Message-ID: | 20101112.080350.91516597177315965.t-ishii@sraoss.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
From: | "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> |
---|---|
To: | "Tatsuo Ishii" <ishii(at)postgresql(dot)org>, <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Delimited identifier brhavior |
Date: | 2010-11-11 23:12:30 |
Message-ID: | 4CDC23FE0200002500037643@gw.wicourts.gov |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Tatsuo Ishii <ishii(at)postgresql(dot)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
From: | Darren Duncan <darren(at)darrenduncan(dot)net> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Delimited identifier brhavior |
Date: | 2010-11-11 23:32:12 |
Message-ID: | 4CDC7CFC.4090207@darrenduncan.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
From: | Andrew Dunstan <andrew(at)dunslane(dot)net> |
---|---|
To: | Tatsuo Ishii <ishii(at)postgresql(dot)org> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Delimited identifier brhavior |
Date: | 2010-11-12 00:08:03 |
Message-ID: | 4CDC8563.1030603@dunslane.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
From: | Tatsuo Ishii <ishii(at)postgresql(dot)org> |
---|---|
To: | Kevin(dot)Grittner(at)wicourts(dot)gov |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Delimited identifier brhavior |
Date: | 2010-11-12 01:29:08 |
Message-ID: | 20101112.102908.994833219732935305.t-ishii@sraoss.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
>> 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