Thread: date type changing to timestamp without time zone in postgres 9.4

date type changing to timestamp without time zone in postgres 9.4

From

Rishi Gokhale

Date:

31 May 2015, 08:05:28

When I create a table with a column whose type is date the type gets forced to timestamp without timezone after it gets created

ops=# CREATE TABLE test (

ops(# name varchar(40) NOT NULL,

ops(# start date NOT NULL

ops(# );

CREATE TABLE

ops=# \d test;

Table "public.test"

Column | Type | Modifiers

--------+-----------------------------+-----------

name | character varying(40) | not null

start | timestamp without time zone | not null

ops=#

The table creation is just a test, my original issue is while restoring a backup (pg_dump/pg_restore) from another server also 9.4, where the date types on numerous columns get forced to change to timestamp without timezone.

Any help would be appreciated.

Thanks,

Rishi

Re: date type changing to timestamp without time zone in postgres 9.4

From

Adrian Klaver

Date:

31 May 2015, 17:34:29

On 05/30/2015 10:05 PM, Rishi Gokhale wrote:
> When I create a table with a column whose type is date the type gets
> forced to timestamp without timezone after it gets created
>
>
> ops=# CREATE TABLE test (
>
> ops(#     name    varchar(40) NOT NULL,
>
> ops(#     start date NOT NULL
>
> ops(# );
>
> CREATE TABLE
>
> ops=# \d test;
>
>                 Table "public.test"
>
>   Column |            Type             | Modifiers
>
> --------+-----------------------------+-----------
>
>   name   | character varying(40)       | not null
>
>   start  | timestamp without time zone | not null
>
>
> ops=#
>
>
> The table creation is just a test, my original issue is while restoring
> a backup (pg_dump/pg_restore) from another server also 9.4, where the
> date types on numerous columns get forced to change to timestamp without
> timezone.
>
>
> Any help would be appreciated.

Not seeing that here:

test=# select version();
                                                            version


-----------------------------------------------------------------------------------------------------------------------------
  PostgreSQL 9.4.2 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.8.1 20130909 [gcc-4_8-branch revision 202388], 32-bit
(1 row)

test=# create table date_test(dt_fld date);
CREATE TABLE

test=# \d date_test
  Table "public.date_test"
  Column | Type | Modifiers
--------+------+-----------
  dt_fld | date |


Sure someone has not overridden the date type in your installation?
See what \dT or \dD return?

>
>
> Thanks,
>
> Rishi
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: date type changing to timestamp without time zone in postgres 9.4

From

Albe Laurenz

Date:

01 June 2015, 10:33:01

Adrian Klaver wrote:
> On 05/30/2015 10:05 PM, Rishi Gokhale wrote:
>> When I create a table with a column whose type is date the type gets
>> forced to timestamp without timezone after it gets created
>>
>> ops=# CREATE TABLE test (
>> ops(#     name    varchar(40) NOT NULL,
>> ops(#     start date NOT NULL
>> ops(# );
>> CREATE TABLE
>>
>> ops=# \d test;
>>                 Table "public.test"
>>   Column |            Type             | Modifiers
>>  --------+-----------------------------+-----------
>>   name   | character varying(40)       | not null
>>   start  | timestamp without time zone | not null

>> The table creation is just a test, my original issue is while restoring
>> a backup (pg_dump/pg_restore) from another server also 9.4, where the
>> date types on numerous columns get forced to change to timestamp without
>> timezone.

> Not seeing that here:

A wild guess, since "date" in Oracle is effectively a timestamp:
Are you using EDB's Postgres Plus?

Yours,
Laurenz Albe

Re: date type changing to timestamp without time zone in postgres 9.4

From

Rishi Gokhale

Date:

01 June 2015, 16:04:36

Hey Adrian and Albe,

Thanks very much for your quick responses. I am indeed using EDB's postgres plus.

It looks like it has a function thats forcing the date type to change to a timestamp. I actually deleted that function,
butit still didn't help. 

Thanks,
Rishi

________________________________________
From: Albe Laurenz <laurenz.albe@wien.gv.at>
Sent: Monday, June 1, 2015 3:32 AM
To: 'Adrian Klaver  *EXTERN*'; Rishi Gokhale; pgsql-general@postgresql.org
Subject: RE: [GENERAL] date type changing to timestamp without time zone in postgres 9.4

Adrian Klaver wrote:
> On 05/30/2015 10:05 PM, Rishi Gokhale wrote:
>> When I create a table with a column whose type is date the type gets
>> forced to timestamp without timezone after it gets created
>>
>> ops=# CREATE TABLE test (
>> ops(#     name    varchar(40) NOT NULL,
>> ops(#     start date NOT NULL
>> ops(# );
>> CREATE TABLE
>>
>> ops=# \d test;
>>                 Table "public.test"
>>   Column |            Type             | Modifiers
>>  --------+-----------------------------+-----------
>>   name   | character varying(40)       | not null
>>   start  | timestamp without time zone | not null

>> The table creation is just a test, my original issue is while restoring
>> a backup (pg_dump/pg_restore) from another server also 9.4, where the
>> date types on numerous columns get forced to change to timestamp without
>> timezone.

> Not seeing that here:

A wild guess, since "date" in Oracle is effectively a timestamp:
Are you using EDB's Postgres Plus?

Yours,
Laurenz Albe

Re: date type changing to timestamp without time zone in postgres 9.4

From

Albe Laurenz

Date:

01 June 2015, 16:06:34

Rishi Gokhale wrote:
> Thanks very much for your quick responses. I am indeed using EDB's postgres plus.
> 
> It looks like it has a function thats forcing the date type to change to a timestamp. I actually
> deleted that function, but it still didn't help.

You shouldn't delete any functions.

But since EDB's PostgreSQL fork behaves differently in this respect,
you'd be better off asking them for help.
This mailing list only deals with standard PostgreSQL.

Yours,
Laurenz Albe

Re: date type changing to timestamp without time zone in postgres 9.4

From

Adrian Klaver

Date:

01 June 2015, 16:56:36

On 06/01/2015 06:04 AM, Rishi Gokhale wrote:
> Hey Adrian and Albe,
>
> Thanks very much for your quick responses. I am indeed using EDB's postgres plus.
>
> It looks like it has a function thats forcing the date type to change to a timestamp. I actually deleted that
function,but it still didn't help. 

I think the below is what you want to look at:

http://www.enterprisedb.com/docs/en/9.4/eeguide
/Postgres_Plus_Enterprise_Edition_Guide.1.017.html#pID0E0HPQ0HA

>
> Thanks,
> Rishi
>
> ________________________________________
> From: Albe Laurenz <laurenz.albe@wien.gv.at>
> Sent: Monday, June 1, 2015 3:32 AM
> To: 'Adrian Klaver  *EXTERN*'; Rishi Gokhale; pgsql-general@postgresql.org
> Subject: RE: [GENERAL] date type changing to timestamp without time zone in postgres 9.4
>
> Adrian Klaver wrote:
>> On 05/30/2015 10:05 PM, Rishi Gokhale wrote:
>>> When I create a table with a column whose type is date the type gets
>>> forced to timestamp without timezone after it gets created
>>>
>>> ops=# CREATE TABLE test (
>>> ops(#     name    varchar(40) NOT NULL,
>>> ops(#     start date NOT NULL
>>> ops(# );
>>> CREATE TABLE
>>>
>>> ops=# \d test;
>>>                  Table "public.test"
>>>    Column |            Type             | Modifiers
>>>   --------+-----------------------------+-----------
>>>    name   | character varying(40)       | not null
>>>    start  | timestamp without time zone | not null
>
>>> The table creation is just a test, my original issue is while restoring
>>> a backup (pg_dump/pg_restore) from another server also 9.4, where the
>>> date types on numerous columns get forced to change to timestamp without
>>> timezone.
>
>> Not seeing that here:
>
> A wild guess, since "date" in Oracle is effectively a timestamp:
> Are you using EDB's Postgres Plus?
>
> Yours,
> Laurenz Albe
>


--
Adrian Klaver
adrian.klaver@aklaver.com