Обсуждение: relation does not exist!?
Just installed postgresql today - I keep getting the error "relation ... does not exist" when I try some simple selects. This is the scenario: Created a user X and a schema by the same name X using pgAdmin (pg 8.0.3) , and set the user as owner of the schema. Then I created a table Y under schema X, and set user X as owner of the table. Then I put some rows in the table. As postgres user when I try "select * from Y" I get the error "relation Y does not exist". I have tried "select * from X.Y", same error. The I tried logging in as X and executing the select, but still get the same error. I have also tried the same select from a Java program using JDBC and get the same error. What am I doing wrong? _________________________________________________________________ Få alle de nye og sjove ikoner med MSN Messenger http://messenger.msn.dk/
On Jul 10, 2005, at 9:12 AM, Søren Poulsen wrote: > Just installed postgresql today - I keep getting the error > "relation ... does not exist" > when I try some simple selects. This is the scenario: > > Created a user X and a schema by the same name X using pgAdmin (pg > 8.0.3) , and set > the user as owner of the schema. > Then I created a table Y under schema X, and set user X as owner of > the table. Then > I put some rows in the table. > As postgres user when I try "select * from Y" I get the error > "relation Y does not exist". > I have tried "select * from X.Y", same error. The I tried logging > in as X and executing the > select, but still get the same error. I have also tried the same > select from a Java program > using JDBC and get the same error. What am I doing wrong? Perhaps you created the table (or schema) with the name quoted. By default, PostgreSQL folds names to lower case. So to "select * from Y" there has to be a table named with a lowercase 'y'. If the table name is really a capital 'Y' you need: select * from "Y" or select * from "X"."Y" depending on the name of the schema. Click on the table in pgAdmin and look at the SQL definition to see if the names are quoted. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
OK, I created the tables using pgAdmin so I didnt notice the quotes - after changing the names to lowercase I dont have to quote - tnx for the help. >From: John DeSoi <desoi@pgedit.com> >To: Søren Poulsen <wmsopou@hotmail.com> >CC: pgsql-novice@postgresql.org >Subject: Re: [NOVICE] relation does not exist!? >Date: Sun, 10 Jul 2005 10:27:05 -0400 > > >On Jul 10, 2005, at 9:12 AM, Søren Poulsen wrote: > >>Just installed postgresql today - I keep getting the error "relation ... >>does not exist" >>when I try some simple selects. This is the scenario: >> >>Created a user X and a schema by the same name X using pgAdmin (pg 8.0.3) >>, and set >>the user as owner of the schema. >>Then I created a table Y under schema X, and set user X as owner of the >>table. Then >>I put some rows in the table. >>As postgres user when I try "select * from Y" I get the error "relation Y >>does not exist". >>I have tried "select * from X.Y", same error. The I tried logging in as X >>and executing the >>select, but still get the same error. I have also tried the same select >>from a Java program >>using JDBC and get the same error. What am I doing wrong? > > >Perhaps you created the table (or schema) with the name quoted. By >default, PostgreSQL folds names to lower case. So to "select * from Y" >there has to be a table named with a lowercase 'y'. If the table name is >really a capital 'Y' you need: > >select * from "Y" >or >select * from "X"."Y" >depending on the name of the schema. > >Click on the table in pgAdmin and look at the SQL definition to see if the >names are quoted. > > > >John DeSoi, Ph.D. >http://pgedit.com/ >Power Tools for PostgreSQL > _________________________________________________________________ Få alle de nye og sjove ikoner med MSN Messenger http://messenger.msn.dk/
Im denied permission to select from a table that im the owner of, in a schema that own too. Only if I grant access to public on both am I permitted to select - shouldnt I have have usage permission as owner? Btw. im logging in using jdbc. pg 8.0.3 >From: John DeSoi <desoi@pgedit.com> >To: Søren Poulsen <wmsopou@hotmail.com> >CC: pgsql-novice@postgresql.org >Subject: Re: [NOVICE] relation does not exist!? >Date: Sun, 10 Jul 2005 10:27:05 -0400 > > >On Jul 10, 2005, at 9:12 AM, Søren Poulsen wrote: > >>Just installed postgresql today - I keep getting the error "relation ... >>does not exist" >>when I try some simple selects. This is the scenario: >> >>Created a user X and a schema by the same name X using pgAdmin (pg 8.0.3) >>, and set >>the user as owner of the schema. >>Then I created a table Y under schema X, and set user X as owner of the >>table. Then >>I put some rows in the table. >>As postgres user when I try "select * from Y" I get the error "relation Y >>does not exist". >>I have tried "select * from X.Y", same error. The I tried logging in as X >>and executing the >>select, but still get the same error. I have also tried the same select >>from a Java program >>using JDBC and get the same error. What am I doing wrong? > > >Perhaps you created the table (or schema) with the name quoted. By >default, PostgreSQL folds names to lower case. So to "select * from Y" >there has to be a table named with a lowercase 'y'. If the table name is >really a capital 'Y' you need: > >select * from "Y" >or >select * from "X"."Y" >depending on the name of the schema. > >Click on the table in pgAdmin and look at the SQL definition to see if the >names are quoted. > > > >John DeSoi, Ph.D. >http://pgedit.com/ >Power Tools for PostgreSQL > > >---------------------------(end of broadcast)--------------------------- >TIP 4: Have you searched our list archives? > > http://archives.postgresql.org _________________________________________________________________ Del din verden med MSN Spaces http://spaces.msn.com
On Sun, Jul 10, 2005 at 22:39:33 +0200, Søren Poulsen <wmsopou@hotmail.com> wrote: > Im denied permission to select from a table that im the owner of, in a > schema that own too. Only if I grant access to public on both am I permitted > to select - shouldnt I have have usage permission as owner? Btw. im logging > in using jdbc. The only of a table doesn't necessarily have select access to it. However, you would have that access after creating the table unless you explicitly revoked it. A similar situation applies to usage access to the schema.