Insert timestamp into PostgreSQL table

Question

I would like to insert TIMESTAMP into column LAST_LOGIN

CREATE TABLE USER(
 ID INTEGER NOT NULL,
 USER_NAME TEXT,
 FIRST_NAME TEXT,
 LAST_NAME TEXT,
 LAST_LOGIN DATE,
 DATE_REGISTERED DATE,
 ROLE INTEGER,
 CAN_LOGIN INTEGER
)
;

ALTER TABLE USER ADD CONSTRAINT KEY1 PRIMARY KEY (ID)
;

I tried this:

UPDATE USERS SET LAST_LOGIN = TIMESTAMP WHERE USER_NAME = ?

But I get org.postgresql.util.PSQLException: ERROR: column "timestamp" does not exist Position: 31

What is the correct way to insert current time into table column LAST_LOGIN?

sagi · Accepted Answer · 2016-03-28 12:09:00Z

5

TIMESTAMP is not a known function is POSTGRES , therefore, it recognize it as a column .

POSTGRES dates/time functions:

NOW();
current_date;
current_time;
current_timestamp;

So your correct query should be

UPDATE USERS SET LAST_LOGIN = now() WHERE USER_NAME = ?

You can read all about postgres time/date functions in this document.

edited Mar 28, 2016 at 12:09

user330315

answered Mar 28, 2016 at 11:28

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Peter Penzov Over a year ago

Ok, can I use CURRENT_TIMESTAMP as Oracle Timestamp?

Elad Over a year ago

Now() returns TIME STAMP WITHOUT TIME ZONE, note that according to his table LAST_LOGIN column is of type Date. This won't make much of a difference for this specific query but it is better to use current_date.

Eduard Uta · Accepted Answer · 2016-03-28 11:24:21Z

1

Maybe the error comes from using the wrong function. Try this instead:

UPDATE USERS SET LAST_LOGIN = CURRENT_TIMESTAMP WHERE USER_NAME = ?

answered Mar 28, 2016 at 11:24

Eduard Uta

2,6165 gold badges30 silver badges39 bronze badges

1 Comment

Eduard Uta Over a year ago

@Peter Penzov, even if my answer is earlier, set sagi's answer as accepted since it's more detailed and has correct referencing.

Collectives™ on Stack Overflow

Insert timestamp into PostgreSQL table

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related