Thread: Column does not exists?

Column does not exists?

From

"Leonardo M. Ramé"

Date:

26 March 2015, 17:12:49

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

Re: Column does not exists?

From

Ashesh Vashi

Date:

26 March 2015, 17:17:13

[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

Re: Column does not exists?

From

Adrian Klaver

Date:

26 March 2015, 17:19:04

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

Re: Column does not exists?

From

"Leonardo M. Ramé"

Date:

26 March 2015, 17:19:41

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.

Re: Column does not exists?

From

Bill Moran

Date:

26 March 2015, 17:21:19

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

Re: Column does not exists?

From

"Leonardo M. Ramé"

Date:

26 March 2015, 17:22:02

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?.

Re: Column does not exists?

From

Francisco Olarte

Date:

26 March 2015, 17:24:21

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.

Re: Column does not exists?

From

Adrian Klaver

Date:

26 March 2015, 17:25:30

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

Re: Column does not exists?

From

"Leonardo M. Ramé"

Date:

26 March 2015, 17:30:46

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".

Re: Column does not exists?

From

Francisco Olarte

Date:

26 March 2015, 17:58:04

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.