Thread: ERROR: relation "default_cols" does not exist
Hello,
Just beginning...
winXPsp2/home
PostgreSQL 8.0.2
> background
Was unable to successfully install pgsql manually.
Removed install and reinstalled making pgsql start as a service.
This was successful.
> psql
Unsuccessful in using psql.exe.
Launching psql.exe prompts for password.
Entering the password and hitting return quit psql.exe.
> pgAdmin III
Launched successfully.
Logon (using superuser password) to "PostgreSQL Database Server 8.0 (localhost:5432) successful.
Select Databases(0)
Allowed creation of test db named 'gov' via Edit/New Object/New database.
Properties:
Owner: postgres,
ACL: '',
Tablespace: pg_default,
Default schema: public,
Allow connections? yes,
Connected? yes,
System database? no;
Allowed creation of a test table named 'default_cols'.
> psql shortcuts modified
Launching the 'psql to template1' shortcut (installed by service install) accepts the superuser password and logs on (?) to template1 or just the server(?).
Presents this prompt: template1=#
Copied shortcut target path pointing to template1 ...
"C:\Program Files\PostgreSQL\8.0\bin\psql.exe" -h localhost -p 5432 template1 "postgres"
... and modified a generic shortcut to psql.exe to point to test database.
"C:\Program Files\PostgreSQL\8.0\bin\psql.exe" -h localhost -p 5432 gov "postgres"
"C:\Program Files\PostgreSQL\8.0\bin\psql.exe" -h localhost -p 5432 template1 "postgres"
... and modified a generic shortcut to psql.exe to point to test database.
"C:\Program Files\PostgreSQL\8.0\bin\psql.exe" -h localhost -p 5432 gov "postgres"
Launching the 'psql to gov' shortcut accepts the password and logs on (?) to test database(?) or the server(?).
Presents this prompt: gov=#
> ERROR: relation "default_cols" does not exist
Any attempt to insert or select a count from default_cols yeilds the error above.
> During install I chose to allow all connections. Here is the warning presented.
<<<
Initialize database cluster
-------------------------------------
port #: 5432
addresses: accept connections on all addresses, not just local host
locale: C
encoding: SQL_ASCII
Superuser name: postgres
Superuser password: ************
Initialize database cluster
-------------------------------------
port #: 5432
addresses: accept connections on all addresses, not just local host
locale: C
encoding: SQL_ASCII
Superuser name: postgres
Superuser password: ************
You have opted for the server to listen for connections on all local address, not just 'localhost'.
In order for clients to connect successfully, you must also grant access to specific host addresses or networks by editting the pg_hba.conf file in the data directory and restarting the PostgreSQL service.
>>>
Since I am only attempting to connect locally during this tutorial (client/server on same machine), and since pgAdmin III is allowing access to the server to view and create a db, I'm assuming the default values in the pg_hba.conf are sufficient for this tutorial. I list the host records from the pg_hba.conf below, however, for completeness.
<<<
# Put your actual configuration here
# ----------------------------------
#
# If you want to allow non-local connections, you need to add more
# "host" records. In that case you will also need to make PostgreSQL listen
# on a non-local interface via the listen_addresses configuration parameter,
# or via the -i or -h command line switches.
#
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
#host all all ::1/128 md5
===
I will reread the tutorial docs on install, create db and logon, but a bit of help to clarify the tutorial docs would be appreciated.
Specifically:
Where did the service install actally place the pgsql data folder that is holding the created databases?
How do I logon directly to the test db using psql.exe without modifying its target path?
Why am I not able to select from the test db table I created with getting the
ERROR: relation "default_cols" does not exist?
Thank you.
David Sadler
David, On May 26, 2005, at 1:21 PM, David Sadler wrote: > > Where did the service install actally place the pgsql data folder that > is holding the created databases? The default is C:\Program Files\PostgreSQL\8.0\data > How do I logon directly to the test db using psql.exe without > modifying its target path? One option is to navigate to the postgresql bin directory and then enter the psql command. Another is to add the bin directory to your PATH variable. > Why am I not able to select from the test db table I created with > getting the > ERROR: relation "default_cols" does not exist? Connect via psql and enter the command \dt to see a list of tables in the database. If it does not answer your question, show the output with the error and ask again. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
Hello, John, Thanks for your help. dss: How do I logon directly to the test db using psql.exe without modifying its target path? js: One option is to navigate to the postgresql bin directory and then enter the psql command. Another is to add the bin directory to your PATH variable. John, is the psql.exe like sql*plus in oracle? Am I logging on to the server when it presents the password prompt, and then from there, I connect to whatever db I specify? ... dss: Why am I not able to select from the test db table I created? Instead, I get... ERROR: relation "default_cols" does not exist? js: Connect via psql and enter the command \dt to see a list of tables in the database. If it does not answer your question, show the output with the error and ask again. dss: Results of \dt against testdb "gov"... <<< List of relations Schema = public name = DEFAULT_COLS type = table owner = postgres (1 row) >>> dss: Result of select * from default_cols; <<< ERROR: relation "default_cols" does not exist >>> dss: Upon launching psql to gov shortcut, this warning is presented... <<< Warning: Console code page (437) differs from Window code page (1252) 8-bit characters may not work correctly. See psql reference page "Notes for Windows users" for details. >>> "Notes for Windows users" was not found in the installed doc. I did find it in the more robust doc here. "comprehensive manual" http://www.postgresql.org/files/documentation/pdf/8.0/postgresql-8.0-US.pdf dss: It reads... <<< Notes for Windows users psql is built as a "console application". Since the Windows console windows use a different encoding than the rest of the system, you must take special care when using 8-bit characters within psql. If psql detects a problematic console code page, it will warn you at startup. To change the console code page, two things are necessary: Set the code page by entering cmd.exe /c chcp 1252. (1252 is a code page that is appropriate for German; replace it with your value.) If you are using Cygwin, you can put this command in /etc/profile. Set the console font to "Lucida Console", because the raster font does not work with the ANSI code page. >>> dss: I'm looking for cmd.exe /c chcp [code] for English. Any thoughts on the "relation "default_cols" does not exist error now? Thanks, David Sadler ref: (1) ----- Original Message ----- From: "John DeSoi" <desoi@pgedit.com> To: "David Sadler" <david@dicapp.com> Cc: "pgsql-novice" <pgsql-novice@postgresql.org> Sent: Wednesday, May 25, 2005 11:19 PM Subject: Re: [NOVICE] ERROR: relation "default_cols" does not exist > John DeSoi, Ph.D. > http://pgedit.com/ > Power Tools for PostgreSQL
On May 28, 2005, at 2:15 PM, David Sadler wrote: > > dss: How do I logon directly to the test db using psql.exe without > modifying > its target path? > > js: One option is to navigate to the postgresql bin directory and then > enter > the psql command. Another is to add the bin directory to your PATH > variable. > > John, is the psql.exe like sql*plus in oracle? Am I logging on to the > server when it presents the password prompt, and then from there, I > connect to whatever db I specify? > No, not quite. When you use psql, you must specify the database you want to connect to. It is not a step after connecting. This might not be apparent if you quickly glance at the documentation because it shows that dbname and user name are optional parameters. But if you don't supply a database name, it tries to connect to a database which matches your account user name. > ... > > dss: Why am I not able to select from the test db table I created? > Instead, I get... ERROR: relation "default_cols" does not exist? > > js: Connect via psql and enter the command \dt to see a list of tables > in the database. If it does not answer your question, show the output > with the error and ask again. > > dss: Results of \dt against testdb "gov"... > > <<< > List of relations > Schema = public > name = DEFAULT_COLS > type = table > owner = postgres > (1 row) >>>> > > dss: Result of select * from default_cols; > > <<< > ERROR: relation "default_cols" does not exist >>>> PostgreSQL folds unquoted identifier names to lower case. If your table listing shows the name in upper (or mixed) case, it likely means that the table was named with quotes as in CREATE TABLE "DEFAULT_COLS" ... rather than CREATE TABLE DEFAULT_COLS ... So if it is named using double quotes, you will have to use that in your commands: select * from "DEFAULT_COLS"; See the ALTER TABLE command if you want to rename your table so you don't have to use the double quotes. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
Hi, John, jd: When you use psql, you must specify the database you want to connect to. It is not a step after connecting. This might not be apparent if you quickly glance at the documentation because it shows that dbname and user name are optional parameters. But if you don't supply a database name, it tries to connect to a database which matches your account user name. dss: I will take a closer look at that logon command syntax. ... dss: >> Why am I not able to select from the test db table I created? Instead, I get... ERROR: relation "default_cols" does not exist? jd: PostgreSQL folds unquoted identifier names to lower case. If your table listing shows the name in upper (or mixed) case, it likely means that the table was named with quotes as in CREATE TABLE "DEFAULT_COLS" ... rather than CREATE TABLE DEFAULT_COLS ... So if it is named using double quotes, you will have to use that in your commands: select * from "DEFAULT_COLS"; dss: PROBLEM SOLVED. The quotes were the issue. fyi, the table was created by using pgAdmin III (default settings), so that default is naming tables using quotes. Thank you very much, Sir. David Sadler Ref: (1) ----- Original Message ----- From: "John DeSoi" <desoi@pgedit.com> To: "David Sadler" <david@dicapp.com> Cc: "pgsql-novice" <pgsql-novice@postgresql.org> Sent: Saturday, May 28, 2005 12:11 AM Subject: Re: [NOVICE] ERROR: relation "default_cols" does not exist > John DeSoi, Ph.D. > http://pgedit.com/ > Power Tools for PostgreSQL