Обсуждение: Problem with null timestamp fields

Here is the table I have.

CREATE TABLE "user_history" (
        "id" integer DEFAULT nextval('"user_history_id_seq"'::text) NOT
NULL,
        "userid" integer NOT NULL,
        "ipaddr" character(15) NOT NULL,
        "login_ts " timestamp with time zone,
        "logout_ts " timestamp with time zone,
        Constraint "user_history_pkey" Primary Key ("id")
);


I try this:

insert into user_history(id, userid, ipaddr, login_ts)
values (1, 2, '127.0.0.1', now())

And I get this:

PostgreSQL said: ERROR: Relation 'user_history' does not have attribute
'login_ts'

Obviously, I DO have login_ts.  The field is nullable, so why the headache?

Thanks in advance,

Tom Veldhouse
veldy@veldy.net

>         "ipaddr" character(15) NOT NULL,
>         "login_ts " timestamp with time zone,
>         "logout_ts " timestamp with time zone,
>
> PostgreSQL said: ERROR: Relation 'user_history' does not have attribute
> 'login_ts'
>
> Obviously, I DO have login_ts.  The field is nullable, so why the headache?

Obviously, you do not.
You have 'login_ts '. Note the trailing space. Note the same for
'logout_ts'.

QED

gh


>
> Thanks in advance,
>
> Tom Veldhouse
> veldy@veldy.net

There is a difference between "login_ts" and the
"login_ts " (notice the extra space) that I see in
your create statement.

I got the same error by using your example, but when I
changed the insert to look like this:

insert into user_history(id, userid, ipaddr,
"login_ts ") values (1, 2, '127.0.0.1', now());

Notice the " characters and the space.

Because of ambiguities like that I tend to create my
tables without the '"' character.  I also don't use
the formal 'timestamp with time zone' (but mostly
because timestamp is easier to type).

Try this:

CREATE TABLE user_history (
    id integer DEFAULT
nextval('user_history_id_seq'::text) NOT NULL,
    userid integer NOT NULL    ,
    ipaddr character(15) NOT NULL,
    login_ts  timestamp with time zone,
    logout_ts  timestamp with time zone,
    Constraint user_history_pkey Primary Key (id)
);


Jason

--- "Thomas T. Veldhouse" <veldy@veldy.net> wrote:
> Here is the table I have.
>
> CREATE TABLE "user_history" (
>         "id" integer DEFAULT
> nextval('"user_history_id_seq"'::text) NOT
> NULL,
>         "userid" integer NOT NULL,
>         "ipaddr" character(15) NOT NULL,
>         "login_ts " timestamp with time zone,
>         "logout_ts " timestamp with time zone,
>         Constraint "user_history_pkey" Primary Key
> ("id")
> );
>
>
> I try this:
>
> insert into user_history(id, userid, ipaddr,
> login_ts)
> values (1, 2, '127.0.0.1', now())
>
> And I get this:
>
> PostgreSQL said: ERROR: Relation 'user_history' does
> not have attribute
> 'login_ts'
>
> Obviously, I DO have login_ts.  The field is
> nullable, so why the headache?
>
> Thanks in advance,
>
> Tom Veldhouse
> veldy@veldy.net
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html


__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/

Yes, that was it.  It should have been more obvious had I looked closer.  A
second pair of eyes are always helpful.  Still, I am a bit amazed that the
database allows this (trailing or leading spaces in the column names).

Thanks for you help!

Tom Veldhouse
veldy@veldy.net

----- Original Message -----
From: "GH" <grasshacker@over-yonder.net>
To: "Thomas T. Veldhouse" <veldy@veldy.net>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, June 27, 2001 10:03 PM
Subject: Re: [GENERAL] Problem with null timestamp fields


> >         "ipaddr" character(15) NOT NULL,
> >         "login_ts " timestamp with time zone,
> >         "logout_ts " timestamp with time zone,
> >
> > PostgreSQL said: ERROR: Relation 'user_history' does not have attribute
> > 'login_ts'
> >
> > Obviously, I DO have login_ts.  The field is nullable, so why the
headache?
>
> Obviously, you do not.
> You have 'login_ts '. Note the trailing space. Note the same for
> 'logout_ts'.
>
> QED
>
> gh
>
>
> >
> > Thanks in advance,
> >
> > Tom Veldhouse
> > veldy@veldy.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

"Thomas T. Veldhouse" <veldy@veldy.net> writes:

> Yes, that was it.  It should have been more obvious had I looked closer.  A
> second pair of eyes are always helpful.  Still, I am a bit amazed that the
> database allows this (trailing or leading spaces in the column names).

If you use double quotes around names, it means "this is exactly what
I want, don't do anything to it."  If you don't like this, don't use
double quotes (there's no real reason to except possibly for
compatibility reasons).

-Doug
--
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time...          --Dylan