Обсуждение: first message: SELECT FROM
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
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
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
---------------------------------------------------------------
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
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
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
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
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 ---------------------------------------------------------------
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!
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