Обсуждение: Timestamp Datatype Changing automatically to Timestamp without time zone
Dear Sir/Madam,
This is important and I need your help here.
Please suggest me the cause of this automatic datatype change. Also, tell me how to make my TO_TIMESTAMP and other data functions work.
Thank You
Rajdeep
Rajdeep Das <sendrajster@gmail.com> writes:
> I have taken a dump of my database from the old version of pg viz.8.1, using
> pg_dump command. In the db I have a table with a column of datatype
> 'timestamp'. Now, when I try to restore it on the new pg version 8.3, the
> datatype of the column is getting altered to 'timestamp with no time zone'.
Those are the same type.
> This change is causing my SQL to fail as TO_TIMESTAMP function is reporting
> error.
This is not what's causing your problem. But since you gave no details,
we can't guess what your problem actually is.
regards, tom lane
On Tue, Jun 30, 2009 at 1:13 AM, Rajdeep Das<sendrajster@gmail.com> wrote: > Dear Sir/Madam, > This is important and I need your help here. > I have taken a dump of my database from the old version of pg viz.8.1, using > pg_dump command. In the db I have a table with a column of datatype > 'timestamp'. Now, when I try to restore it on the new pg version 8.3, the > datatype of the column is getting altered to 'timestamp with no time zone'. > This change is causing my SQL to fail as TO_TIMESTAMP function is reporting > error. > Please suggest me the cause of this automatic datatype change. Also, tell me > how to make my TO_TIMESTAMP and other data functions work. If I remember correctly, the sql standard says that timestamp, alone, means timestamp without timezone. But prior to 8.1 pgsql implemented timestamp as timestamp with timezone. I'm guessing you're getting bitten by that. You'd probably have to edit the db dump to change the ddl declarations of timestamp to timestamptz.
Scott Marlowe <scott.marlowe@gmail.com> writes:
> If I remember correctly, the sql standard says that timestamp, alone,
> means timestamp without timezone. But prior to 8.1 pgsql implemented
> timestamp as timestamp with timezone.
No, you're thinking of a change that happened in 7.3.
regards, tom lane
On Tue, Jun 30, 2009 at 8:45 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote: > Scott Marlowe <scott.marlowe@gmail.com> writes: >> If I remember correctly, the sql standard says that timestamp, alone, >> means timestamp without timezone. But prior to 8.1 pgsql implemented >> timestamp as timestamp with timezone. > > No, you're thinking of a change that happened in 7.3. Ahh, ok. I knew I'd lived through it, but it was just longer ago than I thought.