Обсуждение: Column does not exists?
Ok, I have this table:
CREATE TABLE sessions
(
"SESSIONID" integer NOT NULL,
"SESSIONTIMESTAMP" character varying(45) NOT NULL,
"SESSIONDATA" character varying(200) DEFAULT NULL::character varying,
CONSTRAINT sessions_pkey PRIMARY KEY ("SESSIONID")
)
Now, when I do:
DELETE From sessions WHERE SESSIONTIMESTAMP < '2010-01-01 10:02:02'
I get:
ERROR: column "sessiontimestamp" does not exist
LINE 1: DELETE From sessions WHERE SESSIONTIMESTAMP < '2010-01-01 10...
^
********** Error **********
ERROR: column "sessiontimestamp" does not exist
SQL state: 42703
Character: 28
But if I do:
DELETE From sessions WHERE "SESSIONTIMESTAMP" < '2010-01-01 10:02:02'
It DOES work.
Why the db doesn't recognize the name of the table without quotes?.
Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877
[Sent through mobile]
On Mar 26, 2015 10:43 PM, Leonardo M. Ramé <l.rame@griensu.com> wrote:
>
> Ok, I have this table:
>
> CREATE TABLE sessions
> (
> "SESSIONID" integer NOT NULL,
> "SESSIONTIMESTAMP" character varying(45) NOT NULL,
> "SESSIONDATA" character varying(200) DEFAULT NULL::character varying,
> CONSTRAINT sessions_pkey PRIMARY KEY ("SESSIONID")
> )
>
> Now, when I do:
>
> DELETE From sessions WHERE SESSIONTIMESTAMP < '2010-01-01 10:02:02'
>
Use this query:
DELETE FROM sessions WHERE " SESSIONTIMESTAMP" < '2010-01-01 10:02:02'::timestamp;
--
Thanks & Regards,
Ashesh Vashi
EnterpriseDB (Software Architect)
> I get:
>
> ERROR: column "sessiontimestamp" does not exist
> LINE 1: DELETE From sessions WHERE SESSIONTIMESTAMP < '2010-01-01 10...
> ^
> ********** Error **********
>
> ERROR: column "sessiontimestamp" does not exist
> SQL state: 42703
> Character: 28
>
> But if I do:
>
> DELETE From sessions WHERE "SESSIONTIMESTAMP" < '2010-01-01 10:02:02'
>
> It DOES work.
>
> Why the db doesn't recognize the name of the table without quotes?.
>
> Regards,
> --
> Leonardo M. Ramé
> Medical IT - Griensu S.A.
> Av. Colón 636 - Piso 8 Of. A
> X5000EPT -- Córdoba
> Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
> Cel.: +54 9 (011) 40871877
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
On 03/26/2015 10:12 AM, "Leonardo M. Ramé" wrote:
> Ok, I have this table:
>
> CREATE TABLE sessions
> (
> "SESSIONID" integer NOT NULL,
> "SESSIONTIMESTAMP" character varying(45) NOT NULL,
> "SESSIONDATA" character varying(200) DEFAULT NULL::character varying,
> CONSTRAINT sessions_pkey PRIMARY KEY ("SESSIONID")
> )
>
> Now, when I do:
>
> DELETE From sessions WHERE SESSIONTIMESTAMP < '2010-01-01 10:02:02'
>
> I get:
>
> ERROR: column "sessiontimestamp" does not exist
> LINE 1: DELETE From sessions WHERE SESSIONTIMESTAMP < '2010-01-01 10...
> ^
> ********** Error **********
>
> ERROR: column "sessiontimestamp" does not exist
> SQL state: 42703
> Character: 28
>
> But if I do:
>
> DELETE From sessions WHERE "SESSIONTIMESTAMP" < '2010-01-01 10:02:02'
>
> It DOES work.
>
> Why the db doesn't recognize the name of the table without quotes?.
See here, bottom of 4.1.1. Identifiers and Key Words:
http://www.postgresql.org/docs/9.3/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
>
> Regards,
--
Adrian Klaver
adrian.klaver@aklaver.com
El 26/03/15 a las 14:17, Ashesh Vashi escibió:
>
>
> [Sent through mobile]
> On Mar 26, 2015 10:43 PM, Leonardo M. Ramé <l.rame@griensu.com
> <mailto:l.rame@griensu.com>> wrote:
> >
> > Ok, I have this table:
> >
> > CREATE TABLE sessions
> > (
> > "SESSIONID" integer NOT NULL,
> > "SESSIONTIMESTAMP" character varying(45) NOT NULL,
> > "SESSIONDATA" character varying(200) DEFAULT NULL::character varying,
> > CONSTRAINT sessions_pkey PRIMARY KEY ("SESSIONID")
> > )
> >
> > Now, when I do:
> >
> > DELETE From sessions WHERE SESSIONTIMESTAMP < '2010-01-01 10:02:02'
> >
Hi Ashen, adding ::timestamp does not solve the problem, the issue is
solved by adding double quotes to the field name. Anyway, I would like
to know why the error.
On Thu, 26 Mar 2015 14:12:36 -0300
"Leonardo M. Ramé" <l.rame@griensu.com> wrote:
> Ok, I have this table:
>
> CREATE TABLE sessions
> (
> "SESSIONID" integer NOT NULL,
> "SESSIONTIMESTAMP" character varying(45) NOT NULL,
> "SESSIONDATA" character varying(200) DEFAULT NULL::character varying,
> CONSTRAINT sessions_pkey PRIMARY KEY ("SESSIONID")
> )
>
> Now, when I do:
>
> DELETE From sessions WHERE SESSIONTIMESTAMP < '2010-01-01 10:02:02'
>
> I get:
>
> ERROR: column "sessiontimestamp" does not exist
> LINE 1: DELETE From sessions WHERE SESSIONTIMESTAMP < '2010-01-01 10...
> ^
> ********** Error **********
>
> ERROR: column "sessiontimestamp" does not exist
> SQL state: 42703
> Character: 28
>
> But if I do:
>
> DELETE From sessions WHERE "SESSIONTIMESTAMP" < '2010-01-01 10:02:02'
>
> It DOES work.
>
> Why the db doesn't recognize the name of the table without quotes?.
See:
http://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
--
Bill Moran
El 26/03/15 a las 14:18, Adrian Klaver escibió:
> On 03/26/2015 10:12 AM, "Leonardo M. Ramé" wrote:
>> Ok, I have this table:
>>
>> CREATE TABLE sessions
>> (
>> "SESSIONID" integer NOT NULL,
>> "SESSIONTIMESTAMP" character varying(45) NOT NULL,
>> "SESSIONDATA" character varying(200) DEFAULT NULL::character varying,
>> CONSTRAINT sessions_pkey PRIMARY KEY ("SESSIONID")
>> )
>>
>> Now, when I do:
>>
>> DELETE From sessions WHERE SESSIONTIMESTAMP < '2010-01-01 10:02:02'
>>
>> I get:
>>
>> ERROR: column "sessiontimestamp" does not exist
>> LINE 1: DELETE From sessions WHERE SESSIONTIMESTAMP < '2010-01-01 10...
>> ^
>> ********** Error **********
>>
>> ERROR: column "sessiontimestamp" does not exist
>> SQL state: 42703
>> Character: 28
>>
>> But if I do:
>>
>> DELETE From sessions WHERE "SESSIONTIMESTAMP" < '2010-01-01 10:02:02'
>>
>> It DOES work.
>>
>> Why the db doesn't recognize the name of the table without quotes?.
>
> See here, bottom of 4.1.1. Identifiers and Key Words:
>
> http://www.postgresql.org/docs/9.3/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
>
Thanks, then it looks like SESSIONTIMESTAMP is an identifier?.
Hi Leonardo: On Thu, Mar 26, 2015 at 6:12 PM, "Leonardo M. Ramé" <l.rame@griensu.com> wrote: > DELETE From sessions WHERE SESSIONTIMESTAMP < '2010-01-01 10:02:02' > ERROR: column "sessiontimestamp" does not exist > LINE 1: DELETE From sessions WHERE SESSIONTIMESTAMP < '2010-01-01 10... ... > DELETE From sessions WHERE "SESSIONTIMESTAMP" < '2010-01-01 10:02:02' > > It DOES work. > > Why the db doesn't recognize the name of the table without quotes?. Unquoted identifiers for several things, column names amongst them, are treated by case folding in SQL. Many DBs do it to uppercase, postgres does it to lower case ( as hinted by the column name being printed in lowercase ). So if you QUOTE an UPPERCASE name you must quote it always. As a rule of thumb, I'll recommend quoting your identifiers always or never, quoting it in some statements ( create ) and not others ( 1st delete ) will normally surprise you on unpleasant ways. Francisco Olarte.
On 03/26/2015 10:21 AM, "Leonardo M. Ramé" wrote:
>
> El 26/03/15 a las 14:18, Adrian Klaver escibió:
>> On 03/26/2015 10:12 AM, "Leonardo M. Ramé" wrote:
>>> Ok, I have this table:
>>>
>>> CREATE TABLE sessions
>>> (
>>> "SESSIONID" integer NOT NULL,
>>> "SESSIONTIMESTAMP" character varying(45) NOT NULL,
>>> "SESSIONDATA" character varying(200) DEFAULT NULL::character varying,
>>> CONSTRAINT sessions_pkey PRIMARY KEY ("SESSIONID")
>>> )
>>>
>>> Now, when I do:
>>>
>>> DELETE From sessions WHERE SESSIONTIMESTAMP < '2010-01-01 10:02:02'
>>>
>>> I get:
>>>
>>> ERROR: column "sessiontimestamp" does not exist
>>> LINE 1: DELETE From sessions WHERE SESSIONTIMESTAMP < '2010-01-01 10...
>>> ^
>>> ********** Error **********
>>>
>>> ERROR: column "sessiontimestamp" does not exist
>>> SQL state: 42703
>>> Character: 28
>>>
>>> But if I do:
>>>
>>> DELETE From sessions WHERE "SESSIONTIMESTAMP" < '2010-01-01 10:02:02'
>>>
>>> It DOES work.
>>>
>>> Why the db doesn't recognize the name of the table without quotes?.
>>
>> See here, bottom of 4.1.1. Identifiers and Key Words:
>>
>> http://www.postgresql.org/docs/9.3/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
>>
>>
>
> Thanks, then it looks like SESSIONTIMESTAMP is an identifier?.
"The tokens MY_TABLE and A are examples of identifiers. They identify
names of tables, columns, or other database objects, depending on the
command they are used in. Therefore they are sometimes simply called
"names". "
--
Adrian Klaver
adrian.klaver@aklaver.com
El 26/03/15 a las 14:23, Francisco Olarte escibió: > Hi Leonardo: > > On Thu, Mar 26, 2015 at 6:12 PM, "Leonardo M. Ramé" <l.rame@griensu.com> wrote: >> DELETE From sessions WHERE SESSIONTIMESTAMP < '2010-01-01 10:02:02' >> ERROR: column "sessiontimestamp" does not exist >> LINE 1: DELETE From sessions WHERE SESSIONTIMESTAMP < '2010-01-01 10... > ... >> DELETE From sessions WHERE "SESSIONTIMESTAMP" < '2010-01-01 10:02:02' >> >> It DOES work. >> >> Why the db doesn't recognize the name of the table without quotes?. > > Unquoted identifiers for several things, column names amongst them, > are treated by case folding in SQL. Many DBs do it to uppercase, > postgres does it to lower case ( as hinted by the column name being > printed in lowercase ). So if you QUOTE an UPPERCASE name you must > quote it always. > > As a rule of thumb, I'll recommend quoting your identifiers always or > never, quoting it in some statements ( create ) and not others ( 1st > delete ) will normally surprise you on unpleasant ways. > > Francisco Olarte. > Aha, the problem, then, was caused by the Create statement. This table was copied from a MySql dump where all columns were named "column".
Hi Leonardo: > Aha, the problem, then, was caused by the Create statement. This table was > copied from a MySql dump where all columns were named "column". In part. The problem was caused by non-uniform quote usaga, quotes in create, no quotes elsewhere. Had you used quotes in delete or not used quotes in create everything would have go well. You can try to game the system, but it'e easier if you use them always or never, and always use the same case. I mean, create table "a", delete from A may work in postgres but fail in other db . create a, delete A normally works everywhere ( case folding, in either direction, but you are asking for problems if you decide to quote identifiers for any reason ), and create "a" delete "A" should fail everywhere. Francisco Olarte.