Обсуждение: Why can't I see the definition of my relations
Hi,
I just finished defining a couple of tables with PgAdmin III and I'm
seeing something peculiar. I'm not sure what the problem is. When I
connect to the DB using psql and do "\d <table>" I get an error saying
that there's not relations by that name. What? When I do, "\d" I see
the tables listed. Here's a sample of the output:
cgems=# \d
List of relations
Schema | Name | Type | Owner
--------+---------+-------+-------
public | Mineral | table | cgems
public | Stone | table | cgems
(2 rows)
cgems=# \d Stone
Did not find any relation named "Stone".
I'm guessing that it has something to do with permissions, but I'm
connecting using psql using the same UID that made the DB and the
tables. If I connect to the DB using the UID of the table owner, I
get the same response.
Thanks for any help,
Andy
In response to Andrew Falanga :
> Hi,
>
> I just finished defining a couple of tables with PgAdmin III and I'm
> seeing something peculiar. I'm not sure what the problem is. When I
> connect to the DB using psql and do "\d <table>" I get an error saying
> that there's not relations by that name. What? When I do, "\d" I see
> the tables listed. Here's a sample of the output:
>
> cgems=# \d
> List of relations
> Schema | Name | Type | Owner
> --------+---------+-------+-------
> public | Mineral | table | cgems
> public | Stone | table | cgems
> (2 rows)
>
>
> cgems=# \d Stone
> Did not find any relation named "Stone".
>
> I'm guessing that it has something to do with permissions, but I'm
No, the reason is another:
test=# create table "Stone"(id serial);
NOTICE: CREATE TABLE will create implicit sequence "Stone_id_seq" for serial column "Stone.id"
CREATE TABLE
test=*# \d Stone
Did not find any relation named "Stone".
test=*# \d "Stone"
Table "public.Stone"
Column | Type | Modifiers
--------+---------+------------------------------------------------------
id | integer | not null default nextval('"Stone_id_seq"'::regclass)
You have to use the " if the table-name contains upper-case characters.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
On Monday 12 July 2010 10:18:07 pm A. Kretschmer wrote:
>
> No, the reason is another:
>
> test=# create table "Stone"(id serial);
> NOTICE: CREATE TABLE will create implicit sequence "Stone_id_seq" for
> serial column "Stone.id" CREATE TABLE
> test=*# \d Stone
> Did not find any relation named "Stone".
> test=*# \d "Stone"
> Table "public.Stone"
> Column | Type | Modifiers
> --------+---------+------------------------------------------------------
> id | integer | not null default nextval('"Stone_id_seq"'::regclass)
>
>
>
> You have to use the " if the table-name contains upper-case characters.
>
>
> Andreas
Just for completeness, it is the quoted table name that causes the case
sensitivity. If you do as below than the table name is folded to lower case.
test=> create table Stone(id serial);
NOTICE: CREATE TABLE will create implicit sequence "stone_id_seq" for serial
column "stone.id"
CREATE TABLE
test=> \d Stone
Table "public.stone"
Column | Type | Modifiers
--------+---------+----------------------------------------------------
id | integer | not null default nextval('stone_id_seq'::regclass)
--
Adrian Klaver
adrian.klaver@gmail.com
On Jul 13, 9:12 am, adrian.kla...@gmail.com (Adrian Klaver) wrote:
> On Monday 12 July 2010 10:18:07 pm A. Kretschmer wrote:
>
>
>
>
>
> > No, the reason is another:
>
> > test=# create table "Stone"(id serial);
> > NOTICE: CREATE TABLE will create implicit sequence "Stone_id_seq" for
> > serial column "Stone.id" CREATE TABLE
> > test=*# \d Stone
> > Did not find any relation named "Stone".
> > test=*# \d "Stone"
> > Table "public.Stone"
> > Column | Type | Modifiers
> > --------+---------+------------------------------------------------------
> > id | integer | not null default nextval('"Stone_id_seq"'::regclass)
>
> > You have to use the " if the table-name contains upper-case characters.
>
> > Andreas
>
> Just for completeness, it is the quoted table name that causes the case
> sensitivity. If you do as below than the table name is folded to lower case.
>
> test=> create table Stone(id serial);
> NOTICE: CREATE TABLE will create implicit sequence "stone_id_seq" for serial
> column "stone.id"
> CREATE TABLE
> test=> \d Stone
> Table "public.stone"
> Column | Type | Modifiers
> --------+---------+----------------------------------------------------
> id | integer | not null default nextval('stone_id_seq'::regclass)
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
Thank you both for you help. I look forward to accessing the table
tonight when I get home.
For my own sake, would there happen to be any documentation on-line
that I could read up on regarding this? (The PostgreSQL website
perhaps? Is this discussed in their documentation?)
Thanks,
Andy
On 07/13/2010 09:27 AM, Andrew Falanga wrote: > On Jul 13, 9:12 am, adrian.kla...@gmail.com (Adrian Klaver) wrote: > > Thank you both for you help. I look forward to accessing the table > tonight when I get home. > > For my own sake, would there happen to be any documentation on-line > that I could read up on regarding this? (The PostgreSQL website > perhaps? Is this discussed in their documentation?) > > Thanks, > Andy > www.postgresql.org/docs/8.4/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS Go to end of the above section for documentation on case folding. -- Adrian Klaver adrian.klaver@gmail.com