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');
1 Answer
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.
2 Comments
underdisplayname
Isn't the postgresql's format for date is dd-mm-yyyy (if you have set datestyle dmy).
harmic
@soobster
dd-mm-yyyy or dd/mm/yyyy will be accepted as input in dmy mode (as well as other formats). I would normally use yyyy-mm-dd in my own code, but here I am just reflecting OPs inputs
to_date()to convert a string to a date, or use ANSI date literals.