Thread: cant create index on decimal column
Hi I have a problem. I cant create indexes on decimal columns. CREATE TABLE REQUIREMENT_COST( WORKORDER_TYPE CHAR(1) NOT NULL, WORKORDER_BASE_ID VARCHAR(30) NOT NULL, WORKORDER_LOT_ID VARCHAR(3) NOT NULL, WORKORDER_SPLIT_ID VARCHAR(3) NOT NULL, WORKORDER_SUB_ID VARCHAR(3) NOT NULL, OPERATION_SEQ_NO SMALLINT NOT NULL, REQ_PIECE_NO SMALLINT NOT NULL, QTY DECIMAL(14,4) NOT NULL, UNIT_COST DECIMAL(14,4) NOT NULL, FIXED_COST DECIMAL(15,2) NOT NULL, MINIMUM_COST DECIMAL(15,2), PRIMARY KEY(WORKORDER_TYPE,WORKORDER_BASE_ID,WORKORDER_LOT_ID,WORKORDER_SPLIT_ID,WORKORDER_SUB_ID,OPERATION_SEQ_NO,REQ_PIECE_NO,QTY )) ; NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'requirement_cost_pkey' for table 'requirement_cost' ERROR: Can't find a default operator class for type 1700. If the field qty is deleted from the above primary key the create works. I assume that there is an operator I can add to fix this? bob -- rdavis@lillysoftware.com rsdavis@mediaone.net http://people.ne.mediaone.net/rsdavis
Hi, try to delete the comma ( , ) behind MINIMUM_COST DECIMAL(15,2), because the way you have it atm. is that PostgreSQL thinks you want to create a Primary Key, without a name or datatype. Hope this helps, Joost Roeleveld > Hi I have a problem. I cant create indexes on decimal columns. > > CREATE TABLE REQUIREMENT_COST( > WORKORDER_TYPE CHAR(1) NOT NULL, > WORKORDER_BASE_ID VARCHAR(30) NOT NULL, > WORKORDER_LOT_ID VARCHAR(3) NOT NULL, > WORKORDER_SPLIT_ID VARCHAR(3) NOT NULL, > WORKORDER_SUB_ID VARCHAR(3) NOT NULL, > OPERATION_SEQ_NO SMALLINT NOT NULL, > REQ_PIECE_NO SMALLINT NOT NULL, > QTY DECIMAL(14,4) NOT NULL, > UNIT_COST DECIMAL(14,4) NOT NULL, > FIXED_COST DECIMAL(15,2) NOT NULL, > MINIMUM_COST DECIMAL(15,2), > PRIMARY > KEY(WORKORDER_TYPE,WORKORDER_BASE_ID,WORKORDER_LOT_ID,WORKORDER_SPLIT_ID,WOR KORDER_SUB_ID,OPERATION_SEQ_NO,REQ_PIECE_NO,QTY > )) > ; > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'requirement_cost_pkey' > for table 'requirement_cost' > ERROR: Can't find a default operator class for type 1700. > > If the field qty is deleted from the above primary key the create works. > I assume that there is an operator I can add to fix this?
Tried it.
Here is the error message:
CREATE TABLE REQUIREMENT_COST(
WORKORDER_TYPE CHAR(1) NOT NULL,
WORKORDER_BASE_ID VARCHAR(30) NOT NULL,
WORKORDER_LOT_ID VARCHAR(3) NOT NULL,
WORKORDER_SPLIT_ID VARCHAR(3) NOT NULL,
WORKORDER_SUB_ID VARCHAR(3) NOT NULL,
OPERATION_SEQ_NO SMALLINT NOT NULL,
REQ_PIECE_NO SMALLINT NOT NULL,
QTY DECIMAL(14,4) NOT NULL,
UNIT_COST DECIMAL(14,4) NOT NULL,
FIXED_COST DECIMAL(15,2) NOT NULL,
MINIMUM_COST DECIMAL(15,2)
PRIMARY
KEY(WORKORDER_TYPE,WORKORDER_BASE_ID,WORKORDER_LOT_ID,WORKORDER_SPLIT_ID,WORKORDER_SUB_ID,OPERATION_SEQ_NO,REQ_PIECE_NO,QTY))
Error: PostgreSQL:1Error while executing the query;
ERROR: parser: parse error at or near "("
Also note that I said this can be made to work by deleting the field "qty" from the primary key clause.
Of course this is not what the application demands.
TIA
bob
"J. Roeleveld" wrote:
> Hi,
>
> try to delete the comma ( , ) behind
> MINIMUM_COST DECIMAL(15,2),
>
> because the way you have it atm. is that PostgreSQL thinks you want to
> create a Primary Key, without a name or datatype.
>
> Hope this helps,
>
> Joost Roeleveld
>
> > Hi I have a problem. I cant create indexes on decimal columns.
> >
> > CREATE TABLE REQUIREMENT_COST(
> > WORKORDER_TYPE CHAR(1) NOT NULL,
> > WORKORDER_BASE_ID VARCHAR(30) NOT NULL,
> > WORKORDER_LOT_ID VARCHAR(3) NOT NULL,
> > WORKORDER_SPLIT_ID VARCHAR(3) NOT NULL,
> > WORKORDER_SUB_ID VARCHAR(3) NOT NULL,
> > OPERATION_SEQ_NO SMALLINT NOT NULL,
> > REQ_PIECE_NO SMALLINT NOT NULL,
> > QTY DECIMAL(14,4) NOT NULL,
> > UNIT_COST DECIMAL(14,4) NOT NULL,
> > FIXED_COST DECIMAL(15,2) NOT NULL,
> > MINIMUM_COST DECIMAL(15,2),
> > PRIMARY
> >
> KEY(WORKORDER_TYPE,WORKORDER_BASE_ID,WORKORDER_LOT_ID,WORKORDER_SPLIT_ID,WOR
> KORDER_SUB_ID,OPERATION_SEQ_NO,REQ_PIECE_NO,QTY
> > ))
> > ;
> > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
> 'requirement_cost_pkey'
> > for table 'requirement_cost'
> > ERROR: Can't find a default operator class for type 1700.
> >
> > If the field qty is deleted from the above primary key the create works.
> > I assume that there is an operator I can add to fix this?
--
rdavis@lillysoftware.com
rsdavis@mediaone.net
http://people.ne.mediaone.net/rsdavis
i have a database that was "lost" -- the database is still there (i can see the files on disk in the database's directory), but when i try to connect to the database, it says that it doesn't exist in pg_database. what happened is that the database got dropped while the drive with the data on it was unmounted, but now that i thought about it, it would be nice to have a backup of it if i can get it. is there a way that i can manually add it to the pg_database table (and are there other tables i have to mess with?) so i can access it again? thanks, jeff
> i have a database that was "lost" -- the database is still there (i can > see the files on disk in the database's directory), but when i try to > connect to the database, it says that it doesn't exist in pg_database. > what happened is that the database got dropped while the drive with the > data on it was unmounted, but now that i thought about it, it would be > nice to have a backup of it if i can get it. is there a way that i can > manually add it to the pg_database table (and are there other tables i > have to mess with?) so i can access it again? Go into template1 database and insert a new row into the pg_database table for the missing databases. -- 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, Pennsylvania 19026