I'm currently working on a postgresql 15 database.

I've created a data table named "measures" with following schema

CREATE TABLE measures ( timestamp TIMESTAMPTZ NOT NULL, value double precision, variable_id INTEGER REFERENCES variables(id) ON DELETE CASCADE, PRIMARY KEY (variable_id, timestamp) ) 

When i create this table, the primary key (variable_id, timestamp) create a unique constraint with a unique index.

My problem is that the default index is ordered ASC. I have a need to add another index like this

CREATE UNIQUE INDEX IF NOT EXISTS _index_measure_timestamp_variable ON measures (timestamp DESC, variable_id DESC ); 

It create another index which is duplicated with the constraint index. The only difference is the order.

My questions are:

• after the creation of the second index, can i remove the constraint index  ?
• If i can't remove the constraint index is it possible to change the order of a index  ?
• If not should i keep the two index ?

Thx for your help.

I'm currently working on a postgresql 15 database.

I've created a data table named "measures" with following schema

CREATE TABLE measures ( timestamp TIMESTAMPTZ NOT NULL, value double precision, variable_id INTEGER REFERENCES variables(id) ON DELETE CASCADE, PRIMARY KEY (variable_id, timestamp) ) 

When I create this table, the primary key (variable_id, timestamp) creates a unique constraint with a unique index.

My problem is that the default index is ordered ASC. I need to add another index like this

CREATE UNIQUE INDEX IF NOT EXISTS _index_measure_timestamp_variable ON measures (timestamp DESC, variable_id DESC ); 

It creates another index which is duplicated with the constraint index. The only difference is the order.

My questions are:

• after the creation of the second index, can I remove the constraint index?
• If I can't remove the constraint index is it possible to change the order of an index?
• If not should I keep the two indexes?

Thx for your help.

I'm currently working on a postgresql 15 database.

I've created a data table named "measures" with following schema

CREATE TABLE measures ( timestamp TIMESTAMPTZ NOT NULL, value double precision, variable_id INTEGER REFERENCES variables(id) ON DELETE CASCADE, PRIMARY KEY (variable_id, timestamp) ) 

When i create this table, the primary key (variable_id, timestamp) create a unique constraint with a unique index.

My problem is that the default index is ordered ASC. I have a need to add another index like this

CREATE UNIQUE INDEX IF NOT EXISTS _index_measure_timestamp_variable ON measures (timestamp DESC, variable_id DESC ); 

It create another index which is duplicated with the constraint index. The only difference is the order.

My questions ares:

• after the creation of the second index, can i remove the constraint index ?
• If i can't remove the constraint index is it possible to change the order of a index ?
• If not should i keep the two index ?

Thx for your help.

I'm currently working on a postgresql 15 database.

I've created a data table named "measures" with following schema

CREATE TABLE measures ( timestamp TIMESTAMPTZ NOT NULL, value double precision, variable_id INTEGER REFERENCES variables(id) ON DELETE CASCADE, PRIMARY KEY (variable_id, timestamp) ) 

When i create this table, the primary key (variable_id, timestamp) create a unique constraint with a unique index.

My problem is that the default index is ordered ASC. I have a need to add another index like this

CREATE UNIQUE INDEX IF NOT EXISTS _index_measure_timestamp_variable ON measures (timestamp DESC, variable_id DESC ); 

It create another index which is duplicated with the constraint index. The only difference is the order.

My questions are:

• after the creation of the second index, can i remove the constraint index ?
• If i can't remove the constraint index is it possible to change the order of a index ?
• If not should i keep the two index ?

Thx for your help.