Thread: #PERSONAL# Reg: date going as 01/01/0001

#PERSONAL# Reg: date going as 01/01/0001

From

Medhavi Mahansaria

Date:

06 March 2015, 07:35:12

Hi,

I need to enter the date in null column based on the results obtained.

but my date gets inserted as 01/01/0001 in postgresql 9.3 when there is no value.
I need to enter NULL in the column.

can anyone please help me out.

Its urgent.

Thanks in advance.

Regards
Medhavi Mahansaria
Tata Consultancy Services Limited
Unit-VI, No.78, 79& 83,
L-Centre, EPIP Industrial Estate,
Whitefield
Bangalore - 560066,Karnataka
India
Ph:- +91 80 67253769
Cell:- +91 9620053040
Mailto: medhavi.mahansaria@tcs.com
Website: http://www.tcs.com
____________________________________________
Experience certainty. IT Services
Business Solutions
Consulting
____________________________________________

=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you

Re: #PERSONAL# Reg: date going as 01/01/0001

From

John R Pierce

Date:

06 March 2015, 08:14:12

On 3/5/2015 8:34 PM, Medhavi Mahansaria wrote:

I need to enter the date in null column based on the results obtained.

but my date gets inserted as 01/01/0001 in postgresql 9.3 when there is no value.
I need to enter NULL in the column.

ummmmmm, it seems to work fine for me? you give us no clue how you're inserting this date field.

test=# create table test (calendar date);
CREATE TABLE
test=# insert into test (calendar) values (null);
INSERT 0 1
test=# select calendar, calendar IS NULL from test;
calendar | ?column?
----------+----------
| t
(1 row)

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Re: #PERSONAL# Reg: date going as 01/01/0001

From

Bill Moran

Date:

06 March 2015, 12:26:44

On Fri, 6 Mar 2015 10:04:38 +0530
Medhavi Mahansaria <medhavi.mahansaria@tcs.com> wrote:
>
> I need to enter the date in null column based on the results obtained.
>
> but my date gets inserted as 01/01/0001 in postgresql 9.3 when there is no
> value.
> I need to enter NULL in the column.

This isn't how PostgreSQL behaves. Something else is causing this
behavior ... either the client library or triggers or something
else configured on the table.

More detail would help us help you. Provide the CREATE TABLE
statement that created the table, as well as details of what
software you're using to execute the query, and the query
itself.

--
Bill Moran

Re: #PERSONAL# Reg: date going as 01/01/0001

From

Medhavi Mahansaria

Date:

06 March 2015, 12:54:43

Hi Bill,

Here are the details of the table and the query i want to insert.

aml_db=> \d+ check_date
Table "public.check_date"
Column | Type | Modifiers | Storage | Stats target | Description
-----------+-----------------------------+-----------+---------+--------------+-------------
some_date | date | | plain | |
sno | integer | | plain | |
new_date | timestamp without time zone | | plain | |
Has OIDs: no

aml_db=> select * from check_date;
some_date | sno | new_date
-----------+-----+----------
(0 rows)

aml_db=> insert into check_date values (to_date('','yyyymmddhh24miss'),1,to_date('','yyyymmddhh24miss'));
INSERT 0 1
aml_db=> select * from check_date;
some_date | sno | new_date
---------------+-----+------------------------
01/01/0001 BC | 1 | 01/01/0001 00:00:00 BC
(1 row)

I need to enter NULL as my date. but since I am getting these variables into the bind variables as empty string that is this issue is happening.

Thanks & Regards
Medhavi Mahansaria
Tata Consultancy Services Limited
Unit-VI, No.78, 79& 83,
L-Centre, EPIP Industrial Estate,
Whitefield
Bangalore - 560066,Karnataka
India
Ph:- +91 80 67253769
Cell:- +91 9620053040
Mailto: medhavi.mahansaria@tcs.com
Website: http://www.tcs.com
____________________________________________
Experience certainty. IT Services
Business Solutions
Consulting
____________________________________________

From: Bill Moran <wmoran@potentialtech.com>
To: Medhavi Mahansaria <medhavi.mahansaria@tcs.com>
Cc: pgsql-general@postgresql.org
Date: 03/06/2015 02:56 PM
Subject: Re: [GENERAL] #PERSONAL# Reg: date going as 01/01/0001

On Fri, 6 Mar 2015 10:04:38 +0530 Medhavi Mahansaria <medhavi.mahansaria@tcs.com> wrote: > > I need to enter the date in null column based on the results obtained. > > but my date gets inserted as 01/01/0001 in postgresql 9.3 when there is no > value. > I need to enter NULL in the column. This isn't how PostgreSQL behaves. Something else is causing this behavior ... either the client library or triggers or something else configured on the table. More detail would help us help you. Provide the CREATE TABLE statement that created the table, as well as details of what software you're using to execute the query, and the query itself. -- Bill Moran

Re: #PERSONAL# Reg: date going as 01/01/0001

From

Bill Moran

Date:

06 March 2015, 13:06:45

On Fri, 6 Mar 2015 15:24:28 +0530
Medhavi Mahansaria <medhavi.mahansaria@tcs.com> wrote:

> Hi Bill,
>
> Here are the details of the table and the query i want to insert.
>
>
> aml_db=> \d+ check_date
>                                  Table "public.check_date"
>   Column   |            Type             | Modifiers | Storage | Stats
> target | Description
> -----------+-----------------------------+-----------+---------+--------------+-------------
>  some_date | date                        |           | plain   |    |
>  sno       | integer                     |           | plain   |    |
>  new_date  | timestamp without time zone |           | plain   |    |
> Has OIDs: no
>
>
> aml_db=> select * from check_date;
>  some_date | sno | new_date
> -----------+-----+----------
> (0 rows)
>
>
> aml_db=> insert into check_date values
> (to_date('','yyyymmddhh24miss'),1,to_date('','yyyymmddhh24miss'));
> INSERT 0 1
> aml_db=> select * from check_date;
>    some_date   | sno |        new_date
> ---------------+-----+------------------------
>  01/01/0001 BC |   1 | 01/01/0001 00:00:00 BC
> (1 row)
>
>
> I need to enter NULL as my date. but since I am getting these variables
> into the bind variables as empty string that is this issue is happening.

You're not inserting null, so it's hard to expect null to be the
result. Try:

INSERT INTO check_date VALUES (null, 1, null);

or even:

INSERT INTO check_date
 VALUES (to_date(null, 'yyyymmddhh24miss'), 1, to_date(null, 'yyyymmddhh24miss'));

both of which result in what you desire.

null and the empty string are not the same thing.

--
Bill Moran

Re: #PERSONAL# Reg: date going as 01/01/0001

From

Karsten Hilbert

Date:

06 March 2015, 13:07:51

On Fri, Mar 06, 2015 at 03:24:28PM +0530, Medhavi Mahansaria wrote:

> aml_db=> \d+ check_date
>                                  Table "public.check_date"
>   Column   |            Type             | Modifiers | Storage | Stats
> target | Description
> -----------+-----------------------------+-----------+---------+--------------+-------------
>  some_date | date                        |           | plain   |    |
>  sno       | integer                     |           | plain   |    |
>  new_date  | timestamp without time zone |           | plain   |    |
> Has OIDs: no
>
>
> aml_db=> select * from check_date;
>  some_date | sno | new_date
> -----------+-----+----------
> (0 rows)
>
>
> aml_db=> insert into check_date values
> (to_date('','yyyymmddhh24miss'),1,to_date('','yyyymmddhh24miss'));
> INSERT 0 1
> aml_db=> select * from check_date;
>    some_date   | sno |        new_date
> ---------------+-----+------------------------
>  01/01/0001 BC |   1 | 01/01/0001 00:00:00 BC
> (1 row)
>
>
> I need to enter NULL as my date. but since I am getting these variables
> into the bind variables as empty string that is this issue is happening.

You might attach a BEFORE trigger turning ''s into NULLs.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: #PERSONAL# Reg: date going as 01/01/0001

From

Medhavi Mahansaria

Date:

06 March 2015, 13:12:38

Hi,

Yes true. I agree with you.

I am porting my application from oracle to postgresql. in oracle it enters as NULL and hence this problem was not faced.

Now my problem is that my bind variables are of string type and when the get a NULL value is converts it into an empty string in C++ programming.

Example:
insert into check_date values (to_date(:h1,'yyyymmddhh24miss'),1,to_date(:h2,'yyyymmddhh24miss'));

now h1 and h2 are of string datatype in my c++ program.

If no value is there, it is passed on as a empty string into my query.

How can I handle this to enter NULL values?

Thanks & Regards
Medhavi Mahansaria
Tata Consultancy Services Limited
Unit-VI, No.78, 79& 83,
L-Centre, EPIP Industrial Estate,
Whitefield
Bangalore - 560066,Karnataka
India
Ph:- +91 80 67253769
Cell:- +91 9620053040
Mailto: medhavi.mahansaria@tcs.com
Website: http://www.tcs.com
____________________________________________
Experience certainty. IT Services
Business Solutions
Consulting
____________________________________________

From: Bill Moran <wmoran@potentialtech.com>
To: Medhavi Mahansaria <medhavi.mahansaria@tcs.com>
Cc: pgsql-general@postgresql.org
Date: 03/06/2015 03:36 PM
Subject: Re: [GENERAL] #PERSONAL# Reg: date going as 01/01/0001

On Fri, 6 Mar 2015 15:24:28 +0530 Medhavi Mahansaria <medhavi.mahansaria@tcs.com> wrote: > Hi Bill, > > Here are the details of the table and the query i want to insert. > > > aml_db=> \d+ check_date > Table "public.check_date" > Column | Type | Modifiers | Storage | Stats > target | Description > -----------+-----------------------------+-----------+---------+--------------+------------- > some_date | date | | plain | | > sno | integer | | plain | | > new_date | timestamp without time zone | | plain | | > Has OIDs: no > > > aml_db=> select * from check_date; > some_date | sno | new_date > -----------+-----+---------- > (0 rows) > > > aml_db=> insert into check_date values > (to_date('','yyyymmddhh24miss'),1,to_date('','yyyymmddhh24miss')); > INSERT 0 1 > aml_db=> select * from check_date; > some_date | sno | new_date > ---------------+-----+------------------------ > 01/01/0001 BC | 1 | 01/01/0001 00:00:00 BC > (1 row) > > > I need to enter NULL as my date. but since I am getting these variables > into the bind variables as empty string that is this issue is happening. You're not inserting null, so it's hard to expect null to be the result. Try: INSERT INTO check_date VALUES (null, 1, null); or even: INSERT INTO check_date VALUES (to_date(null, 'yyyymmddhh24miss'), 1, to_date(null, 'yyyymmddhh24miss')); both of which result in what you desire. null and the empty string are not the same thing. -- Bill Moran

Re: #PERSONAL# Reg: date going as 01/01/0001

From

Kevin Grittner

Date:

06 March 2015, 17:02:33

Medhavi Mahansaria <medhavi.mahansaria@tcs.com> wrote:

> Now my problem is that my bind variables are of string type and
> when the get a NULL value is converts it into an empty string in
> C++ programming.

> Example:
> insert into check_date values
>   (to_date(:h1,'yyyymmddhh24miss'),
>    1,
>    to_date(:h2,'yyyymmddhh24miss'));
>
> now h1 and h2 are of string datatype in my c++ program.
>
> If no value is there, it is passed on as a empty string into my
> query.
>
> How can I handle this to enter NULL values?

Well, arguably the best solution is to use a tool chain that
doesn't conflate any particular "magic value" with NULL.  Where
that can't be done, you might want to look at the SQL standard's
NULLIF() construct.

insert into check_date values
  (to_date(nullif(:h1, ''),'yyyymmddhh24miss'),
   1,
   to_date(nullif(:h2, ''),'yyyymmddhh24miss'));

If the two arguments to nullif() are equal, it returns NULL;
otherwise it returns the first argument.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: #PERSONAL# Reg: date going as 01/01/0001

From

John R Pierce

Date:

06 March 2015, 23:40:33

On 3/6/2015 2:12 AM, Medhavi Mahansaria wrote:

I am porting my application from oracle to postgresql. in oracle it enters as NULL

Oracle has the unique 'feature' that an empty string is NULL. This is contrary to the SQL spec.

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Re: #PERSONAL# Reg: date going as 01/01/0001

From

Medhavi Mahansaria

Date:

12 March 2015, 10:10:20

Dear Kevin,

Thanks! it worked :)

Medhavi Mahansaria
Tata Consultancy Services Limited
Unit-VI, No.78, 79& 83,
L-Centre, EPIP Industrial Estate,
Whitefield
Bangalore - 560066,Karnataka
India
Ph:- +91 80 67253769
Cell:- +91 9620053040
Mailto: medhavi.mahansaria@tcs.com
Website: http://www.tcs.com
____________________________________________
Experience certainty. IT Services
Business Solutions
Consulting
____________________________________________

From: Kevin Grittner <kgrittn@ymail.com>
To: Medhavi Mahansaria <medhavi.mahansaria@tcs.com>, Bill Moran <wmoran@potentialtech.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Date: 03/06/2015 07:32 PM
Subject: Re: [GENERAL] #PERSONAL# Reg: date going as 01/01/0001

Medhavi Mahansaria <medhavi.mahansaria@tcs.com> wrote: > Now my problem is that my bind variables are of string type and > when the get a NULL value is converts it into an empty string in > C++ programming. > Example: > insert into check_date values > (to_date(:h1,'yyyymmddhh24miss'), > 1, > to_date(:h2,'yyyymmddhh24miss')); > > now h1 and h2 are of string datatype in my c++ program. > > If no value is there, it is passed on as a empty string into my > query. > > How can I handle this to enter NULL values? Well, arguably the best solution is to use a tool chain that doesn't conflate any particular "magic value" with NULL. Where that can't be done, you might want to look at the SQL standard's NULLIF() construct. insert into check_date values (to_date(nullif(:h1, ''),'yyyymmddhh24miss'), 1, to_date(nullif(:h2, ''),'yyyymmddhh24miss')); If the two arguments to nullif() are equal, it returns NULL; otherwise it returns the first argument. -- Kevin Grittner EDB:http://www.enterprisedb.comThe Enterprise PostgreSQL Company