Thread: relation does not exist error
I'm evaluationg PostgreSQL 8.0 beta 5 for a new development project. I've created a user 'simon' and a database named 'WMSDV'. I've created some tables (see below) and assigned arwdRxt privledges to the 'simon' user. However, when i use psql, I am unable to select these tables. I receive a 'relation "'tabename' does not exist" error. I'm completely new to PostgreSQL, and this problem has me stumped. Can anyone point me in the right direction? Sw. WMSDV=# \dt List of relations Schema | Name | Type | Owner --------+-----------+-------+------- public | Customers | table | simon public | Persons | table | simon (2 rows) WMSDV=# select * from Customers; ERROR: relation "customers" does not exist WMSDV=# select * from public.Customers; ERROR: relation "public.customers" does not exist WMSDV=#
On Fri, Dec 03, 2004 at 11:01:16AM +0800, Simon Wittber wrote: > List of relations > Schema | Name | Type | Owner > --------+-----------+-------+------- > public | Customers | table | simon > public | Persons | table | simon > (2 rows) > > WMSDV=# select * from Customers; > ERROR: relation "customers" does not exist See the "Identifiers and Key Words" section in the "SQL Syntax" of the PostgreSQL documentation. Pay particular attention to what it says about case sensitivity, case folding, and quoting of identifiers. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Simon Wittber <simonwittber@gmail.com> writes: > WMSDV=# \dt > List of relations > Schema | Name | Type | Owner > --------+-----------+-------+------- > public | Customers | table | simon > public | Persons | table | simon > (2 rows) > > WMSDV=# select * from Customers; > ERROR: relation "customers" does not exist Identifiers get mashed to lower case unless you quote them: SELECT * FROM "Customers"; -Doug
Wow, 4 responses in 10 minutes to my newbie question. Thanks guys. I'm coming from a SQL Server background, so quoting table names didn't immediately spring to mind. I can see support will not be a problem. 10 out of 10. Sw.
The answer is in the question! It's the difference between Customers and customers. PostgreSQL folds identifiers to lower case if not double quoted. The best solution is to use lower case consistently. The alternative is to use double quotes consistently. Try SELECT * FROM "Customers"; and you will be happy again. Ian Harding Programmer/Analyst II Tacoma-Pierce County Health Department iharding@tpchd.org Phone: (253) 798-3549 Pager: (253) 754-0002 >>> Simon Wittber <simonwittber@gmail.com> 12/02/04 7:01 PM >>> I'm evaluationg PostgreSQL 8.0 beta 5 for a new development project. I've created a user 'simon' and a database named 'WMSDV'. I've created some tables (see below) and assigned arwdRxt privledges to the 'simon' user. However, when i use psql, I am unable to select these tables. I receive a 'relation "'tabename' does not exist" error. I'm completely new to PostgreSQL, and this problem has me stumped. Can anyone point me in the right direction? Sw. WMSDV=# \dt List of relations Schema | Name | Type | Owner --------+-----------+-------+------- public | Customers | table | simon public | Persons | table | simon (2 rows) WMSDV=# select * from Customers; ERROR: relation "customers" does not exist WMSDV=# select * from public.Customers; ERROR: relation "public.customers" does not exist WMSDV=# ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend