I am having trouble importing the tables from the 00_buildscript.sql dump file to postgresql db "cw". I am using postgres 7.3 that comes with redhat linux 9. This is the message i get at the beginning of the import:
psql:01_core.sql:21: ERROR: Type "datetime" does not exist
psql:01_core.sql:23: ERROR: Relation "person" does not exist
psql:01_core.sql:37: ERROR: Type "datetime" does not exist
psql:01_core.sql:51: ERROR: Type "datetime" does not exist
psql:01_core.sql:66: ERROR: Type "datetime" does not exist
psql:01_core.sql:81: ERROR: Type "datetime" does not exist
psql:01_core.sql:90: ERROR: Type "datetime" does not exist
psql:01_core.sql:111: ERROR: Type "datetime" does not exist
psql:01_core.sql:132: ERROR: Type "datetime" does not exist
psql:01_core.sql:141: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit
index 'person_attribute_pkey' for table 'person_attribute'
psql:01_core.sql:141: NOTICE: CREATE TABLE / UNIQUE will create implicit index
'person_attribute_personid_key' for table 'person_attribute'
psql:01_core.sql:141: ERROR: Relation 'person_attribute' already exists
psql:01_core.sql:155: ERROR: Type "datetime" does not exist
psql:01_core.sql:164: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit
index 'p_session_attribute_pkey' for table 'p_session_attribute'
psql:01_core.sql:164: NOTICE: CREATE TABLE / UNIQUE will create implicit index
'p_session_attribute_personid_key' for table 'p_session_attribute'
psql:01_core.sql:164: ERROR: Relation 'p_session_attribute' already exists
psql:01_core.sql:173: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit
index 'nexus_attribute_pkey' for table 'nexus_attribute'
psql:01_core.sql:173: NOTICE: CREATE TABLE / UNIQUE will create implicit index
'nexus_attribute_nexusid_key' for table 'nexus_attribute'
psql:01_core.sql:173: ERROR: Relation 'nexus_attribute' already exists
psql:01_core.sql:189: ERROR: Type "datetime" does not exist
psql:01_core.sql:198:
[3]+ Stopped psql cw <00_buildscript.sql
any help would be great, Thanks!
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Recent versions of Postgres have deprecated the column type 'datetime' in favor of the more standard 'timestamp'.
---------Quote from http://www.postgresql.org/docs/6.3/interactive/c0804.htm----
In future releases, the number of date/time types will decrease, with the current implementation of datetime becoming timestamp, timespan becoming interval, and (possibly) abstime and reltime being deprecated in favor of timestamp and interval. The more arcane features of the date/time definitions from the SQL92 standard are not likely to be pursued.
------------------------------------------------------------------------------------
If you are on a *nix system, here is an easy command to change all references of 'datetime' to 'timestamp':
Do older version of Postgres also support the timestamp column type? Would there be any harm in changing the SQL files in CVS to comply with new versions of Postgres? Would we be losing backward-compatability?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
On the issue of updating CVS with timestamp rather than datatime.
In pricipal, yes.
However I have noticed some issues with certain versions of functions that work with datetime but dont work with timestamp and some differences due to "timestamp with time zone" and "timestamp without timezone" before changing all the datetime's to timestamps I would like to have done some testing to ensure it doesnt create additional bugs in the SQL within the java code.
Presumably those of you who have changed over are not having a problem with this ?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I haven't noticed irregular behavior outside of the fact that my search-and-replace command was a little too global. It should have avoided changing a couple of column names that were also named "datetime".
I also ran across some deprecated Postgres behavior in RegisterSQL.java. It deals with current_timestamp and trying to add days to the current_timestamp without casting. jrcole has a good solution to this problem.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
# psql cw < 00_buildscript.sql
psql:01_core.sql:21: ERROR: Type "datetime" does not exist
psql:01_core.sql:23: ERROR: Relation "person" does not exist
psql:01_core.sql:37: ERROR: Type "datetime" does not exist
psql:01_core.sql:51: ERROR: Type "datetime" does not exist
psql:01_core.sql:66: ERROR: Type "datetime" does not exist
psql:01_core.sql:81: ERROR: Type "datetime" does not exist
psql:01_core.sql:90: ERROR: Type "datetime" does not exist
psql:01_core.sql:111: ERROR: Type "datetime" does not exist
psql:01_core.sql:132: ERROR: Type "datetime" does not exist
psql:01_core.sql:141: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'person_attribute_pkey' for table 'person_attribute'
psql:01_core.sql:141: NOTICE: CREATE TABLE / UNIQUE will create implicit index
'person_attribute_personid_key' for table 'person_attribute'
CREATE TABLE
psql:01_core.sql:155: ERROR: Type "datetime" does not exist
psql:01_core.sql:164: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'p_session_attribute_pkey' for table 'p_session_attribute'
psql:01_core.sql:164: NOTICE: CREATE TABLE / UNIQUE will create implicit index
'p_session_attribute_personid_key' for table 'p_session_attribute'
CREATE TABLE
psql:01_core.sql:173: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'nexus_attribute_pkey' for table 'nexus_attribute'
psql:01_core.sql:173: NOTICE: CREATE TABLE / UNIQUE will create implicit index
'nexus_attribute_nexusid_key' for table 'nexus_attribute'
CREATE TABLE
psql:01_core.sql:189: ERROR: Type "datetime" does not exist
psql:01_core.sql:198: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'tool_attribute_pkey' for table 'tool_attribute'
psql:01_core.sql:198: NOTICE: CREATE TABLE / UNIQUE will create implicit index
'tool_attribute_toolid_key' for table 'tool_attribute'
CREATE TABLE
psql:01_core.sql:207: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'content_attribute_pkey' for table 'content_attribute'
psql:01_core.sql:207: NOTICE: CREATE TABLE / UNIQUE will create implicit index
'content_attribute_contentid_key' for table 'content_attribute'
CREATE TABLE
psql:01_core.sql:213: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'person_password_pkey' for table 'person_password'
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
one revision to my suggestion of doing a global replace on all occurrences of "datetime" with "timestamp". There is a column called "datetime" in the table graded_item. this column should still be called "datetime" with column type "timestamp".
sam
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I have updated all the build scripts in CVS to use timestamp with time zone instead of datetime. We have been using this with a production 2.1 system for some time now and it appears to be ok.
Ian
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I am having trouble importing the tables from the 00_buildscript.sql dump file to postgresql db "cw". I am using postgres 7.3 that comes with redhat linux 9. This is the message i get at the beginning of the import:
psql:01_core.sql:21: ERROR: Type "datetime" does not exist
psql:01_core.sql:23: ERROR: Relation "person" does not exist
psql:01_core.sql:37: ERROR: Type "datetime" does not exist
psql:01_core.sql:51: ERROR: Type "datetime" does not exist
psql:01_core.sql:66: ERROR: Type "datetime" does not exist
psql:01_core.sql:81: ERROR: Type "datetime" does not exist
psql:01_core.sql:90: ERROR: Type "datetime" does not exist
psql:01_core.sql:111: ERROR: Type "datetime" does not exist
psql:01_core.sql:132: ERROR: Type "datetime" does not exist
psql:01_core.sql:141: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit
index 'person_attribute_pkey' for table 'person_attribute'
psql:01_core.sql:141: NOTICE: CREATE TABLE / UNIQUE will create implicit index
'person_attribute_personid_key' for table 'person_attribute'
psql:01_core.sql:141: ERROR: Relation 'person_attribute' already exists
psql:01_core.sql:155: ERROR: Type "datetime" does not exist
psql:01_core.sql:164: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit
index 'p_session_attribute_pkey' for table 'p_session_attribute'
psql:01_core.sql:164: NOTICE: CREATE TABLE / UNIQUE will create implicit index
'p_session_attribute_personid_key' for table 'p_session_attribute'
psql:01_core.sql:164: ERROR: Relation 'p_session_attribute' already exists
psql:01_core.sql:173: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit
index 'nexus_attribute_pkey' for table 'nexus_attribute'
psql:01_core.sql:173: NOTICE: CREATE TABLE / UNIQUE will create implicit index
'nexus_attribute_nexusid_key' for table 'nexus_attribute'
psql:01_core.sql:173: ERROR: Relation 'nexus_attribute' already exists
psql:01_core.sql:189: ERROR: Type "datetime" does not exist
psql:01_core.sql:198:
[3]+ Stopped psql cw <00_buildscript.sql
any help would be great, Thanks!
I posted a response to this question on coursework-help.
Here is the response I posted on coursework-help:
Recent versions of Postgres have deprecated the column type 'datetime' in favor of the more standard 'timestamp'.
---------Quote from http://www.postgresql.org/docs/6.3/interactive/c0804.htm----
In future releases, the number of date/time types will decrease, with the current implementation of datetime becoming timestamp, timespan becoming interval, and (possibly) abstime and reltime being deprecated in favor of timestamp and interval. The more arcane features of the date/time definitions from the SQL92 standard are not likely to be pursued.
------------------------------------------------------------------------------------
If you are on a *nix system, here is an easy command to change all references of 'datetime' to 'timestamp':
find ./ -name \*sql |xargs perl -pi -e 's/datetime/timestamp/g'
Do older version of Postgres also support the timestamp column type? Would there be any harm in changing the SQL files in CVS to comply with new versions of Postgres? Would we be losing backward-compatability?
On the issue of updating CVS with timestamp rather than datatime.
In pricipal, yes.
However I have noticed some issues with certain versions of functions that work with datetime but dont work with timestamp and some differences due to "timestamp with time zone" and "timestamp without timezone" before changing all the datetime's to timestamps I would like to have done some testing to ensure it doesnt create additional bugs in the SQL within the java code.
Presumably those of you who have changed over are not having a problem with this ?
I haven't noticed irregular behavior outside of the fact that my search-and-replace command was a little too global. It should have avoided changing a couple of column names that were also named "datetime".
I also ran across some deprecated Postgres behavior in RegisterSQL.java. It deals with current_timestamp and trying to add days to the current_timestamp without casting. jrcole has a good solution to this problem.
BTW, here is what happens with a fresh cw db.
# psql cw < 00_buildscript.sql
psql:01_core.sql:21: ERROR: Type "datetime" does not exist
psql:01_core.sql:23: ERROR: Relation "person" does not exist
psql:01_core.sql:37: ERROR: Type "datetime" does not exist
psql:01_core.sql:51: ERROR: Type "datetime" does not exist
psql:01_core.sql:66: ERROR: Type "datetime" does not exist
psql:01_core.sql:81: ERROR: Type "datetime" does not exist
psql:01_core.sql:90: ERROR: Type "datetime" does not exist
psql:01_core.sql:111: ERROR: Type "datetime" does not exist
psql:01_core.sql:132: ERROR: Type "datetime" does not exist
psql:01_core.sql:141: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'person_attribute_pkey' for table 'person_attribute'
psql:01_core.sql:141: NOTICE: CREATE TABLE / UNIQUE will create implicit index
'person_attribute_personid_key' for table 'person_attribute'
CREATE TABLE
psql:01_core.sql:155: ERROR: Type "datetime" does not exist
psql:01_core.sql:164: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'p_session_attribute_pkey' for table 'p_session_attribute'
psql:01_core.sql:164: NOTICE: CREATE TABLE / UNIQUE will create implicit index
'p_session_attribute_personid_key' for table 'p_session_attribute'
CREATE TABLE
psql:01_core.sql:173: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'nexus_attribute_pkey' for table 'nexus_attribute'
psql:01_core.sql:173: NOTICE: CREATE TABLE / UNIQUE will create implicit index
'nexus_attribute_nexusid_key' for table 'nexus_attribute'
CREATE TABLE
psql:01_core.sql:189: ERROR: Type "datetime" does not exist
psql:01_core.sql:198: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'tool_attribute_pkey' for table 'tool_attribute'
psql:01_core.sql:198: NOTICE: CREATE TABLE / UNIQUE will create implicit index
'tool_attribute_toolid_key' for table 'tool_attribute'
CREATE TABLE
psql:01_core.sql:207: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'content_attribute_pkey' for table 'content_attribute'
psql:01_core.sql:207: NOTICE: CREATE TABLE / UNIQUE will create implicit index
'content_attribute_contentid_key' for table 'content_attribute'
CREATE TABLE
psql:01_core.sql:213: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'person_password_pkey' for table 'person_password'
the above messages was after i did the
find ./ -name \*sql |xargs perl -pi -e 's/datetime/timestamp/g'
command.
thanks!
Looks like the same errors as before. Did you look at your new SQL files to see if "datetime" was replaced with "timestamp"?
Please verify your read/write permissions on the files. I've imported postgres tables and they are working fine.
one revision to my suggestion of doing a global replace on all occurrences of "datetime" with "timestamp". There is a column called "datetime" in the table graded_item. this column should still be called "datetime" with column type "timestamp".
sam
I have updated all the build scripts in CVS to use timestamp with time zone instead of datetime. We have been using this with a production 2.1 system for some time now and it appears to be ok.
Ian