Thread: Referencing created tables fails with message that they do not exist!
I have just installed Postgresql and tried it for the first time. One very serious problem I ran into was when actually trying to use created tables. Creating a simple table without any foreign keys works OK, but after creating the table it is not possible to do a select on it! I tried the following variants: SELECT * FROM <table>; SELECT * FROM public.<table>; SELECT * FROM <schema>.public.<table>; All result in the message "The relation <table> does not exist!" or "The relation public.<table> does not exist!". Creating a new table with a foreign key referencing the first table is also impossible due to exactly the same error message! This behaviour is the same using DBVisualizer/jdbc or psql. So the question is how do you actually reference the tables you have created so that postgres will find them ? The tables do actually get created. I can se them in DBVisualizer. I'm using version 7.4.5 on Linux Mandrake 10.1. Best Regards, Tommy Svensson
Tommy Svensson <tommy@tommysvensson.net> writes: > I have just installed Postgresql and tried it for the first time. > > One very serious problem I ran into was when actually trying to use > created tables. > Creating a simple table without any foreign keys works OK, but after > creating the > table it is not possible to do a select on it! I tried the following > variants: > > SELECT * FROM <table>; > SELECT * FROM public.<table>; > SELECT * FROM <schema>.public.<table>; > > All result in the message "The relation <table> does not exist!" or > "The relation public.<table> does not exist!". I bet it's a case problem. Please give the actual table name and the exact SQL you are using to create and access it. -Doug
On Sun, 2005-02-27 at 18:50 -0500, Tommy Svensson wrote: >I have just installed Postgresql and tried it for the first time. > >One very serious problem I ran into was when actually trying to use >created tables. >Creating a simple table without any foreign keys works OK, but after >creating the >table it is not possible to do a select on it! I tried the following >variants: > >SELECT * FROM <table>; >SELECT * FROM public.<table>; >SELECT * FROM <schema>.public.<table>; > >All result in the message "The relation <table> does not exist!" or "The >relation public.<table> does not exist!". > >Creating a new table with a foreign key referencing the first table is >also impossible due to exactly the same error message! > >This behaviour is the same using DBVisualizer/jdbc or psql. > >So the question is how do you actually reference the tables you have >created so that postgres will find them ? >The tables do actually get created. I can se them in DBVisualizer. > >I'm using version 7.4.5 on Linux Mandrake 10.1. > >Best Regards, >Tommy Svensson What do your CREATE TABLE commands look like? Also, what does \d tablename respond with? -Robby -- /*************************************** * Robby Russell | Owner.Developer.Geek * PLANET ARGON | www.planetargon.com * Portland, OR | robby@planetargon.com * 503.351.4730 | blog.planetargon.com * PHP/PostgreSQL Hosting & Development * --- Now hosting Ruby on Rails Apps --- ****************************************/
On Sun, Feb 27, 2005 at 06:50:50PM -0500, Tommy Svensson wrote: > SELECT * FROM <table>; > SELECT * FROM public.<table>; > SELECT * FROM <schema>.public.<table>; > > All result in the message "The relation <table> does not exist!" or "The > relation public.<table> does not exist!". Could you copy and paste the *exact* commands and error messages and send them to the list? That might help us see what's going on. > The tables do actually get created. I can se them in DBVisualizer. If you run psql, what does \d show? (Again, please copy and paste the exact output.) Is it possible that you created the tables in mixed case and didn't quote their names when you tried to query them? If so, then you might want to read "Identifiers and Key Words" in the "SQL Syntax" chapter of the documentation. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Thanks for all the replys, I got quite a lot of them, and haven't had time to read them all yet, but one common comment I get is "You didn't supply enough information" , "be more specific", etc. You are of course right. From the answers I got however, it seems to be a very high propability that it is different case in table name between create and select, etc that causes the problem. It does make sense (now). I got the suggestion to quote the table names. I also switched tools after creating the first table and trying to create the next since the first tool was so simple it did not support foreign keys. That was probably a bad thing to do :-) I will drop the table in the tool that created it and then recreate them in the same tool and quote the names as suggested. I'm quite sure that will solve my problem. <inMyDefense skip="OK"> - I've used Oracle, DB2, Mimer, and HSQLDB before, and my experience with these led me to beleive that SQL was case insensitive. In fact, I was so sure of it that a case problem just never occured to me. - Since I could create the table I assumed it was OK and there were no point in describing the table. </inMyDefense> Regards, Tommy Svensson Tino Wildenhain wrote: >Am Sonntag, den 27.02.2005, 18:50 -0500 schrieb Tommy Svensson: > > >>I have just installed Postgresql and tried it for the first time. >> >>One very serious problem I ran into was when actually trying to use >>created tables. >>Creating a simple table without any foreign keys works OK, but after >>creating the >>table it is not possible to do a select on it! I tried the following >>variants: >> >>SELECT * FROM <table>; >>SELECT * FROM public.<table>; >>SELECT * FROM <schema>.public.<table>; >> >>All result in the message "The relation <table> does not exist!" or "The >>relation public.<table> does not exist!". >> >>Creating a new table with a foreign key referencing the first table is >>also impossible due to exactly the same error message! >> >>This behaviour is the same using DBVisualizer/jdbc or psql. >> >>So the question is how do you actually reference the tables you have >>created so that postgres will find them ? >>The tables do actually get created. I can se them in DBVisualizer. >> >>I'm using version 7.4.5 on Linux Mandrake 10.1. >> >> > >You should have provided an exact example of _how_ do you create >your tables. > >I bet you created tables with mixed case (in a tool?) but dont use the >" " quoting on these table names when you select. > > > > > >
Am Sonntag, den 27.02.2005, 18:50 -0500 schrieb Tommy Svensson: > I have just installed Postgresql and tried it for the first time. > > One very serious problem I ran into was when actually trying to use > created tables. > Creating a simple table without any foreign keys works OK, but after > creating the > table it is not possible to do a select on it! I tried the following > variants: > > SELECT * FROM <table>; > SELECT * FROM public.<table>; > SELECT * FROM <schema>.public.<table>; > > All result in the message "The relation <table> does not exist!" or "The > relation public.<table> does not exist!". > > Creating a new table with a foreign key referencing the first table is > also impossible due to exactly the same error message! > > This behaviour is the same using DBVisualizer/jdbc or psql. > > So the question is how do you actually reference the tables you have > created so that postgres will find them ? > The tables do actually get created. I can se them in DBVisualizer. > > I'm using version 7.4.5 on Linux Mandrake 10.1. You should have provided an exact example of _how_ do you create your tables. I bet you created tables with mixed case (in a tool?) but dont use the " " quoting on these table names when you select.
On Sun, 2005-02-27 at 18:50 -0500, Tommy Svensson wrote: > I have just installed Postgresql and tried it for the first time. > > One very serious problem I ran into was when actually trying to use > created tables. > Creating a simple table without any foreign keys works OK, but after > creating the > table it is not possible to do a select on it! I tried the following > variants: > > SELECT * FROM <table>; > SELECT * FROM public.<table>; > SELECT * FROM <schema>.public.<table>; > > All result in the message "The relation <table> does not exist!" or "The > relation public.<table> does not exist!". you do not give actual examples, nor do you say how you created the tables, but one possibility is that you ran into the case-folding feature. names are folded to lowercase unless quoted in doublequotes. if you (or the client you use) created your table with quoted upper-case or mixed case names, you must do the same with the selects. CREATE TABLE "Foo" (a text); SELECT a from "Foo"; -- works SELECT a from Foo; -- fails the same applies to other names, such as columns. gnari
On Sun, 27 Feb 2005, Tommy Svensson wrote: > I have just installed Postgresql and tried it for the first time. > > One very serious problem I ran into was when actually trying to use > created tables. > Creating a simple table without any foreign keys works OK, but after > creating the > table it is not possible to do a select on it! I tried the following > variants: > > SELECT * FROM <table>; > SELECT * FROM public.<table>; > SELECT * FROM <schema>.public.<table>; > > All result in the message "The relation <table> does not exist!" or "The > relation public.<table> does not exist!". You haven't given alot of information in the above, but this often happens if the table was created with double quotes (often implicitly by a tool) in mixed-case but is referenced without double quotes which causes case-folding. This also sometimes similarly happens with all uppercase names because PostgreSQL does folding to lowercase rather than the SQL folding to uppercase.
Tommy Svensson presumably uttered the following on 02/27/05 18:50: > I have just installed Postgresql and tried it for the first time. > > One very serious problem I ran into was when actually trying to use > created tables. > Creating a simple table without any foreign keys works OK, but after > creating the > table it is not possible to do a select on it! I tried the following > variants: > > SELECT * FROM <table>; > SELECT * FROM public.<table>; > SELECT * FROM <schema>.public.<table>; > > All result in the message "The relation <table> does not exist!" or "The > relation public.<table> does not exist!". > > Creating a new table with a foreign key referencing the first table is > also impossible due to exactly the same error message! > > This behaviour is the same using DBVisualizer/jdbc or psql. > > So the question is how do you actually reference the tables you have > created so that postgres will find them ? > The tables do actually get created. I can se them in DBVisualizer. > What database did you create the tables in? and what database are you connecting to when trying to issue your SELECT statements? Are you trying to SELECT from the same application you are using to CREATE TABLE? It sounds like you created the tables in <database> and perhaps are trying to SELECT * FROM while connected to template1. Sven
Try putting quotes around the table name: select * from "Table"; ? Do you see it in the table list? \dt Tommy Svensson wrote: > I have just installed Postgresql and tried it for the first time. > > One very serious problem I ran into was when actually trying to use > created tables. > Creating a simple table without any foreign keys works OK, but after > creating the > table it is not possible to do a select on it! I tried the following > variants: > > SELECT * FROM <table>; > SELECT * FROM public.<table>; > SELECT * FROM <schema>.public.<table>; > > All result in the message "The relation <table> does not exist!" or "The > relation public.<table> does not exist!". > > Creating a new table with a foreign key referencing the first table is > also impossible due to exactly the same error message! > > This behaviour is the same using DBVisualizer/jdbc or psql. > > So the question is how do you actually reference the tables you have > created so that postgres will find them ? > The tables do actually get created. I can se them in DBVisualizer. > > I'm using version 7.4.5 on Linux Mandrake 10.1. > > Best Regards, > Tommy Svensson > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Regards, Chris Smith Suite 30, 45-51 Huntley St, Alexandria, NSW 2015 Australia Ph: +61 2 9517 2505 Fx: +61 2 9517 1915 email: info@interspire.com web: www.interspire.com
On Mon, Feb 28, 2005 at 12:50:25PM +0100, Tommy Svensson wrote:
> - I've used Oracle, DB2, Mimer, and HSQLDB before, and my experience
> with these led me
> to beleive that SQL was case insensitive. In fact, I was so sure of it
> that a case problem
> just never occured to me.
Case isn't a problem if you don't quote identifiers because unquoted
identifiers will be folded to lower case, both when you create them
and then later when you reference them. For example, if you create
a table with this command:
CREATE TABLE XYZ (I INTEGER);
then the system folds XYZ and I to lower case:
\dt
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+-------
public | xyz | table | mfuhr
\d xyz
Table "public.xyz"
Column | Type | Modifiers
--------+---------+-----------
i | integer |
The following queries should all work (not an all-inclusive list):
SELECT I FROM XYZ;
SELECT i FROM xyz;
SELECT I FROM Xyz;
select i from xyz;
sEleCt i fRoM xYz;
But if you quote identifiers when you create them, then they'll be
created with the exact case you specified and you'll need to quote
them whenever you use them:
CREATE TABLE "XYZ" ("I" INTEGER);
\dt
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+-------
public | XYZ | table | mfuhr
\d "XYZ"
Table "public.XYZ"
Column | Type | Modifiers
--------+---------+-----------
I | integer |
SELECT "I" FROM "XYZ"; -- works
SELECT I FROM XYZ; -- fails
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
OK, I see. I first used the Postgres admin tool in webmin (Linux/unix
web admin tool)
to create the first table. I guess that it quoted my "Project" table.
Anyhow, I dropped
the table from the same tool, and then went to DBVisualizer and
recreated all my tables
there, using uppercase for all table and field names, and then it worked
fine. But as you
explained below, I guess it would not have mattered even if i called my
first table
PrOjEcT since it would be changed to project.
I also realize that it is much safer to actually write the SQL yourself
than let a tool
produce it for you!
/Tommy
Michael Fuhr wrote:
>On Mon, Feb 28, 2005 at 12:50:25PM +0100, Tommy Svensson wrote:
>
>
>
>>- I've used Oracle, DB2, Mimer, and HSQLDB before, and my experience
>>with these led me
>> to beleive that SQL was case insensitive. In fact, I was so sure of it
>>that a case problem
>> just never occured to me.
>>
>>
>
>Case isn't a problem if you don't quote identifiers because unquoted
>identifiers will be folded to lower case, both when you create them
>and then later when you reference them. For example, if you create
>a table with this command:
>
>CREATE TABLE XYZ (I INTEGER);
>
>then the system folds XYZ and I to lower case:
>
>\dt
> List of relations
> Schema | Name | Type | Owner
>--------+--------------+-------+-------
> public | xyz | table | mfuhr
>
>\d xyz
> Table "public.xyz"
> Column | Type | Modifiers
>--------+---------+-----------
> i | integer |
>
>The following queries should all work (not an all-inclusive list):
>
>SELECT I FROM XYZ;
>SELECT i FROM xyz;
>SELECT I FROM Xyz;
>select i from xyz;
>sEleCt i fRoM xYz;
>
>But if you quote identifiers when you create them, then they'll be
>created with the exact case you specified and you'll need to quote
>them whenever you use them:
>
>CREATE TABLE "XYZ" ("I" INTEGER);
>
>\dt
> List of relations
> Schema | Name | Type | Owner
>--------+--------------+-------+-------
> public | XYZ | table | mfuhr
>
>\d "XYZ"
> Table "public.XYZ"
> Column | Type | Modifiers
>--------+---------+-----------
> I | integer |
>
>SELECT "I" FROM "XYZ"; -- works
>SELECT I FROM XYZ; -- fails
>
>
>