Thread: Problem with timestamp and primary key.
When I try to execute this sentence:
CREATE TABLE ACCESO(
USR_CODIGO VARCHAR(20) NOT NULL DEFAULT '0', --Código
del usuario
SER_CODIGO VARCHAR(10) NOT NULL DEFAULT '0', --Código
del servicio
ACC_FECHAA TIMESTAMP NOT NULL,
ACC_FECHAZ TIMESTAMP,
CONSTRAINT C_ACC PRIMARY KEY (USR_CODIGO,SER_CODIGO,ACC_FECHAA));
I get this error message:
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index c_acc for
table acceso
ERROR: Can't find a default operator class for type 1296.
EOF
Does anybody know what could be the problem. What should I do?
The problem is that I could subtitute TIMESTAMP with DATE or DATETIME
but that's not compatible with Oracle's DATE and, when I try to access
the table with JDBC in Oracle works fine but in Postgres it needs to be
Timestamp.
Please help,
Esteban
------------------------------------------------------------Esteban Chiner Sanz mailto (work):
echiner@tissat.esTISSAT mailto (home): echiner@abonados.cplus.esAv. Aragon, 30, 5ª planta Phone: 96
3939950Valencia (SPAIN)
I have the same problem, except that my TIMESTAMP column is not the PK; instead it's just a column that I wanted to index. Unfortunately, there is no solution since Postgres currently does not support indexes on TIMESTAMPS (Postgres automatically tries to create an index on all PKs) and does not provide JDBC access to DATETIME columns. For your particular situation you might try placing the TIMESTAMP column outside the PK (and not have it indexed) and then use a surrogate key with a sequence. This isn't entirely normalized, but I've seen worse! Hope this helps At 09:14 AM 7/13/99 +0200, you wrote: >When I try to execute this sentence: > >CREATE TABLE ACCESO( >USR_CODIGO VARCHAR(20) NOT NULL DEFAULT '0', --Código >del usuario >SER_CODIGO VARCHAR(10) NOT NULL DEFAULT '0', --Código >del servicio >ACC_FECHAA TIMESTAMP NOT NULL, >ACC_FECHAZ TIMESTAMP, >CONSTRAINT C_ACC PRIMARY KEY (USR_CODIGO,SER_CODIGO,ACC_FECHAA)); > >I get this error message: > >NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index c_acc for >table acceso >ERROR: Can't find a default operator class for type 1296. >EOF > >Does anybody know what could be the problem. What should I do? >The problem is that I could subtitute TIMESTAMP with DATE or DATETIME >but that's not compatible with Oracle's DATE and, when I try to access >the table with JDBC in Oracle works fine but in Postgres it needs to be >Timestamp. >Please help, > > Esteban > >------------------------------------------------------------ > Esteban Chiner Sanz mailto (work): echiner@tissat.es > TISSAT mailto (home): echiner@abonados.cplus.es > Av. Aragon, 30, 5ª planta Phone: 96 393 9950 > Valencia (SPAIN) > > > > > Steven Bradley Lawrence Livermore National Laboratory PO Box 808 Livermore, California 94550 (925) 423-2101 sbradley@llnl.gov
At 18:24 +0300 on 13/07/1999, Steven Bradley wrote: > I have the same problem, except that my TIMESTAMP column is not the PK; > instead it's just a column that I wanted to index. Unfortunately, there is > no solution since Postgres currently does not support indexes on TIMESTAMPS > (Postgres automatically tries to create an index on all PKs) and does not > provide JDBC access to DATETIME columns. For your particular situation you > might try placing the TIMESTAMP column outside the PK (and not have it > indexed) and then use a surrogate key with a sequence. This isn't entirely > normalized, but I've seen worse! I think I have a good solution. The column in the database should be datetime, because that's what you can index. Right? The column returned from a query should be timestamp, because that's what JDBC parses correctl. Right? So, when you are doing something like: SELECT the_datetime_col, other_cols FROM the_table WHERE the_datetime_col = 'some value'; You should put a function that converts the_datetime_col to timestamp. But only in the returned columns! If you put a conversion function in the WHERE clause, the index will not be used. But how to convert? timestamp( the_datetime_col) doesn't work (Hey, it's a bug. A function exists which is supposed to be doing this). Well, define it yourself: testing=> CREATE FUNCTION to_stamp( datetime ) RETURNS timestamp AS testing-> 'SELECT timestamp_in( datetime_out( $1 ) ) WHERE $1 IS NOT NULL' testing-> LANGUAGE 'sql'; CREATE (I found that without WHERE clause it will bug on NULL input, so don't leave it out). testing=> select dt, to_stamp( dt ) as ts from test2; dt |ts ----------------------------+---------------------- Sat May 15 13:30:00 1948 IST|1948-05-15 13:30:00+02 Wed Jan 15 16:00:00 1969 IST|1969-01-15 16:00:00+02 Sun Oct 21 02:00:00 1973 IST|1973-10-21 02:00:00+02 Tue Jul 13 14:05:00 1999 IDT|1999-07-13 14:05:00+03 (4 rows) (dt is a datetime column). Thus, your query should be: SELECT to_stamp( the_datetime_col ), other_cols FROM the_table WHERE the_datetime_col = 'some value'; HTH, Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
> Does anybody know what could be the problem. What should I do? > The problem is that I could subtitute TIMESTAMP with DATE or DATETIME > but that's not compatible with Oracle's DATE and, when I try to access > the table with JDBC in Oracle works fine but in Postgres it needs to be > Timestamp. > Please help, This is a known problem because our jdbc does not know DATETIME, and our TIMESTAMP can't do indexes. People, any solutions? -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026