date type changing to timestamp without time zone in postgres 9.4

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general