Column does not exists?

[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)

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
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: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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general