Thread: 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