Postgresql: How can we insert only time and date into a variable using timestamp?

Question

Error: Getting an error near time format while inserting.

Example:

--Table "Test"  

create table test  
(  
name varchar(10),  
date1 timestamp,  
time1 timestamp  
);  

-- Insertion  
insert into test values('xyz','03/03/2014','02:03:04');  

Answer 1

The type 'timestamp' holds both a time and a date. You are trying to insert the date and time separately. Either use date and time types:

--Table "Test"

create table test
(
name varchar(10),
date1 date,
time1 time
);

-- Insertion
insert into test values('xyz','03/03/2014','02:03:04');

Or, just use one field:

--Table "Test"

create table test
(
name varchar(10),
datetime timestamp

);

-- Insertion
insert into test values('xyz','03/03/2014 02:03:04');

I recommend this second approach, as the set of operators and functions available is much larger and it is easier to compare timestamps.