Thread: ERROR: relation "default_cols" does not exist

ERROR: relation "default_cols" does not exist

From
"David Sadler"
Date:
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"
 
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: ************

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

Re: ERROR: relation "default_cols" does not exist

From
John DeSoi
Date:
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


Re: ERROR: relation "default_cols" does not exist

From
"David Sadler"
Date:
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



Re: ERROR: relation "default_cols" does not exist

From
John DeSoi
Date:
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


Re: ERROR: relation "default_cols" does not exist

From
"David Sadler"
Date:
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