-1

we are in the process of migrating oracle to postgresql. during the process we are getting error for the below index.

Oracle Index: CREATE INDEX idx1 ON table1 (column1||'-A1' ASC) ;

By using schema tool the above index automatically converted into like below. when we try to execute it and getting exception like below.

Postgresql:

CREATE INDEX idx1 ON table1 USING BTREE (CONCAT_WS() ASC);

Error: ERROR:function concat_ws() does not exist LINE 3: USING BTREE (CONCAT_WS() ASC)

Can someone suggest how to match the oracle index to postgresql:

Ram
  • 149
  • 2
  • 6
  • Any migration tool is not 100% prefect you must need to do some manual intervention into the migration process. Index get created on column, not methods/function. The PostgreSQL CREATE INDEX is having CONCATE_WS() which is method not function. Replace it with column name and it should work. – Rajesh Ranjan Nov 23 '23 at 01:35

1 Answers1

0

What you need is:

CREATE INDEX my_ix ON test USING BTREE ((x || '-a1') ASC);

Note the extra brackets! The USING BTREE and the ASC are optional as they are the defaults.

Fiddle here.

Vérace
  • 29,825
  • 9
  • 70
  • 84