Postgres Pro
Downloads
Home   >   mailing lists

Thread: first message: SELECT FROM

first message: SELECT FROM

From
"Aílsom F. Heringer"
Date:
Hi,
This is my first message, and I need some help. I have just installed
Postgresql 8.2. (Windows).

At pgAdmin III Query, when I send SELECT * FROM USUARIOS, I get all
columns correctly. But when I try to get only one column, SELECT senha
FROM USUARIOS, I get the error message:

ERROR: column "senha" does not exist
SQL state: 42703
Character: 8

What is the problem ?

--
--
Aílsom F. Heringer
Rio de Janeiro, Brasil
ailsom@gmail.com
ailsom@uol.com.br

Re: first message: SELECT FROM

From
Bill Moran
Date:
On Sat, 2 Feb 2008 15:43:15 -0200
"Aílsom F. Heringer" <ailsom@gmail.com> wrote:

> Hi,
> This is my first message, and I need some help. I have just installed
> Postgresql 8.2. (Windows).
>
> At pgAdmin III Query, when I send SELECT * FROM USUARIOS, I get all
> columns correctly. But when I try to get only one column, SELECT senha
> FROM USUARIOS, I get the error message:
>
> ERROR: column "senha" does not exist
> SQL state: 42703
> Character: 8
>
> What is the problem ?

The problem is that the column doesn't exist.  Just like the error message
says.  Without some more information (such as the table definition) I can
only speculate on exactly why that is the case, and my speculation is that
you created the table with the column names in uppercase and pgAdmin
preserved that.  PostgreSQL is case-sensative, so try matching the column
name exactly and putting "" around it.  If that doesn't work, provide some
more information in your question.

-Bill

Re: first message: SELECT FROM

From
Raymond O'Donnell
Date:
On 02/02/2008 17:43, Aílsom F. Heringer wrote:

> At pgAdmin III Query, when I send SELECT * FROM USUARIOS, I get all
> columns correctly. But when I try to get only one column, SELECT senha
> FROM USUARIOS, I get the error message:
>
> ERROR: column "senha" does not exist
> SQL state: 42703
> Character: 8

Can you show us the columns in your schema?

That message means exactly what it says - the column you specified
doesn't exist. One gotcha is that if you created the table using
double-quoted MiXeD-CaSe or UPPER-CASE column names, then

     select senha .....

won't be the same as

     select "Senha" .....

Ray.


---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

Re: first message: SELECT FROM

From
Ragnar
Date:
On lau, 2008-02-02 at 15:43 -0200, Aílsom F. Heringer wrote:

> At pgAdmin III Query, when I send SELECT * FROM USUARIOS, I get all
> columns correctly. But when I try to get only one column, SELECT senha
> FROM USUARIOS, I get the error message:
>
> ERROR: column "senha" does not exist
> SQL state: 42703
> Character: 8

if you created the column name mixedcase with quotes,
you need to access it the same way.
so if the column name is really "Senha" do:
SELECT "Senha" FROM USUARIOS;

if that is not the problem, can you repeat this in
the command-line tool psql ?

gnari

Re: first message: SELECT FROM

From
"Aílsom F. Heringer"
Date:
Bill,
The collumn is defined as "Senha", and I am sending "SELECT Senha FROM
Usuarios".

Below, the table definition from SQL Pane:

CREATE TABLE usuarios
(
  "CdUsuario" character(6) NOT NULL,
  "NmUsuario" character(15) NOT NULL,
  "DtCadastro" timestamp without time zone NOT NULL DEFAULT now(),
  "StSuper" boolean DEFAULT false,
  "Senha" character(10),
  "MudarSenha" boolean DEFAULT true,
  "UltimoAcesso" timestamp without time zone,
  "Ativo" boolean DEFAULT true,
  CONSTRAINT "PkCdusuario" PRIMARY KEY ("CdUsuario")
)
WITHOUT OIDS;
ALTER TABLE usuarios OWNER TO postgres;
GRANT ALL ON TABLE usuarios TO postgres;


Ailsom




2008/2/2, Bill Moran <wmoran@potentialtech.com>:
> On Sat, 2 Feb 2008 15:43:15 -0200
> "Aílsom F. Heringer" <ailsom@gmail.com> wrote:
>
> > Hi,
> > This is my first message, and I need some help. I have just installed
> > Postgresql 8.2. (Windows).
> >
> > At pgAdmin III Query, when I send SELECT * FROM USUARIOS, I get all
> > columns correctly. But when I try to get only one column, SELECT senha
> > FROM USUARIOS, I get the error message:
> >
> > ERROR: column "senha" does not exist
> > SQL state: 42703
> > Character: 8
> >
> > What is the problem ?
>
> The problem is that the column doesn't exist.  Just like the error message
> says.  Without some more information (such as the table definition) I can
> only speculate on exactly why that is the case, and my speculation is that
> you created the table with the column names in uppercase and pgAdmin
> preserved that.  PostgreSQL is case-sensative, so try matching the column
> name exactly and putting "" around it.  If that doesn't work, provide some
> more information in your question.
>
> -Bill
>


--
--
Aílsom F. Heringer
Rio de Janeiro, Brasil
ailsom@gmail.com
ailsom@uol.com.br

Re: first message: SELECT FROM

From
"Scott Marlowe"
Date:
On Feb 2, 2008 11:43 AM, Aílsom F. Heringer <ailsom@gmail.com> wrote:
> Hi,
> This is my first message, and I need some help. I have just installed
> Postgresql 8.2. (Windows).
>
> At pgAdmin III Query, when I send SELECT * FROM USUARIOS, I get all
> columns correctly. But when I try to get only one column, SELECT senha
> FROM USUARIOS, I get the error message:
>
> ERROR: column "senha" does not exist
> SQL state: 42703
> Character: 8
>
> What is the problem ?

Most likely this is a mixed case issue.

If you create a table like so:

create table abc (Field1 int);

it will create table abc with a field called field1.  Notice the
automatic case folding.  If you quote it, like so

create table abc ("Field1" int);

It will create Field1 instead.  From then on, you'll have to quote
that field to get it by name, because asking for Field1 without
quotes, or field1 with or without quotes, will get folded to lower
case, and won't match Field1

Re: first message: SELECT FROM

From
"Aílsom F. Heringer"
Date:
Ok. That was the problem. Now SELECT "Senha" FROM USUARIOS is working fine.

But, At an application using ODBC connection I will need to send "Senha" too ?

Aílsom


2008/2/2, Ragnar <gnari@hive.is>:
> On lau, 2008-02-02 at 15:43 -0200, Aílsom F. Heringer wrote:
>
> > At pgAdmin III Query, when I send SELECT * FROM USUARIOS, I get all
> > columns correctly. But when I try to get only one column, SELECT senha
> > FROM USUARIOS, I get the error message:
> >
> > ERROR: column "senha" does not exist
> > SQL state: 42703
> > Character: 8
>
> if you created the column name mixedcase with quotes,
> you need to access it the same way.
> so if the column name is really "Senha" do:
> SELECT "Senha" FROM USUARIOS;
>
> if that is not the problem, can you repeat this in
> the command-line tool psql ?
>
> gnari
>
>
>


--
--
Aílsom F. Heringer
Rio de Janeiro, Brasil
ailsom@gmail.com
ailsom@uol.com.br

Re: first message: SELECT FROM

From
Raymond O'Donnell
Date:
On 02/02/2008 17:59, Aílsom F. Heringer wrote:

> The collumn is defined as "Senha", and I am sending "SELECT Senha FROM
[....]
> CREATE TABLE usuarios
> (
[....]
>   "Senha" character(10),
[....]

There you go - you need to do

   select "Senha" ....

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

Re: first message: SELECT FROM

From
Alban Hertroys
Date:
On Feb 2, 2008, at 6:56 PM, Bill Moran wrote:

> preserved that.  PostgreSQL is case-sensative, so try matching the
> column
> name exactly and putting "" around it.  If that doesn't work,
> provide some

That is just plain incorrect, PostgreSQL is *not* case sensitive. The
real problem here (as Scott pointed out) is that the column in the
table is defined so that the case is preserved (by quoting the column
name), but the column in the query isn't and thus gets folded (to
lowercase) and can't be found.

I'm inclined to call this a bug in PgAdmin III (except that I hardly
know it), it shouldn't quote identifiers to keep case if the user
doesn't explicitly ask it to. This question pops up frequently, so it
would be nice if something was done about it (my preference would be
by not /implicitly/ quote identifiers). It certainly seems to confuse
novice users.

My €0.02.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,47a4c3e7817485094119420!

Re: first message: SELECT FROM

From
Garry Saddington
Date:
On Saturday 02 February 2008 17:43, Aílsom F. Heringer wrote:
> Hi,
> This is my first message, and I need some help. I have just installed
> Postgresql 8.2. (Windows).
>
> At pgAdmin III Query, when I send SELECT * FROM USUARIOS, I get all
> columns correctly. But when I try to get only one column, SELECT senha
> FROM USUARIOS, I get the error message:
>
> ERROR: column "senha" does not exist
> SQL state: 42703
> Character: 8
>
> What is the problem ?
Please post your table definition so that we can see what you are selecting
from. If you were one of my students I would say "check your table definition
or your spelling".
Regards
Garry