Postgres Pro
Downloads
Home   >   mailing lists

Thread: Date data type

Date data type

From
Mike Hall
Date:
Gday,

I'm currently converting an MS Access database to PostgreSQL (version 8.1 as it is the vesion that ships with CentOS
5).

I'm having trouble with an INSERT statement attempting to insert an empty value ('') into a field with data type DATE.
Thisproduces the following error message: 

ERROR: invalid input syntax for type date: ""

It does the same whether the field has a NOT NULL restraint or not.

I can't find anything relevant in the documentation.

What am I doing wrong.

Thanks


Michael Hall
IT Communications Officer
Alice Springs Town Council
mhall@astc.nt.gov.au
(08) 8950 0561

Re: Date data type

From
Klint Gore
Date:
Mike Hall wrote:
> Gday,
>
> I'm currently converting an MS Access database to PostgreSQL (version 8.1 as it is the vesion that ships with CentOS
5).
>
> I'm having trouble with an INSERT statement attempting to insert an empty value ('') into a field with data type
DATE.This produces the following error message: 
>
> ERROR: invalid input syntax for type date: ""
>
> It does the same whether the field has a NOT NULL restraint or not.
>
> I can't find anything relevant in the documentation.
>
> What am I doing wrong.
>

You're trying to put the empty string into a date field
insert into atable (date_col) values ('');  -- syntax error
insert into atable (date_col) values (null);  -- works

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au

Re: Date data type

From
"A. Kretschmer"
Date:
am  Mon, dem 03.11.2008, um 16:03:33 +0930 mailte Mike Hall folgendes:
> Gday,
>
> I'm currently converting an MS Access database to PostgreSQL (version 8.1 as it is the vesion that ships with CentOS
5).
>
> I'm having trouble with an INSERT statement attempting to insert an empty value ('') into a field with data type
DATE.This produces the following error message: 
>
> ERROR: invalid input syntax for type date: ""

An empty string isn't a valid date. Use NULL instead.


>
> It does the same whether the field has a NOT NULL restraint or not.

Doesn't matter.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: Date data type

From
"Albe Laurenz"
Date:
Mike Hall wrote:
> I'm currently converting an MS Access database to PostgreSQL
> (version 8.1 as it is the vesion that ships with CentOS 5).
>
> I'm having trouble with an INSERT statement attempting to
> insert an empty value ('') into a field with data type DATE.
> This produces the following error message:
>
> ERROR: invalid input syntax for type date: ""
>
> It does the same whether the field has a NOT NULL restraint or not.
>
> I can't find anything relevant in the documentation.
>
> What am I doing wrong.

An empty string is not a NULL value.
An empty string cannot be converted to a valid date.

Use NULL instead of '' to insert a NULL value.

Yours,
Laurenz Albe

Re: Date data type

From
Craig Ringer
Date:
Mike Hall wrote:

> I'm currently converting an MS Access database to PostgreSQL (version 8.1 as it is the vesion that ships with CentOS
5).

What version of Microsoft Access are you using? I haven't seen this
issue with Access 2007, which I've been forced to use in a recent
project. It actually seems to have some idea what NULL is and use it
vaguely appropriately.

What ODBC driver version are you using?

Is the data type in the linked table shown as being declared of type
DATE too?

What type of field is bound to the linked table? A proper date field, or
a free-form text field? Does it default to null, or to the empty string?

--
Craig Ringer