Removing the month and year from a unique timestamp?

I am trying to create a table with a timestamp column. The problem is that I want the combination of month and year to be unique. I tried this, but this does not help:

CREATE TABLE adempiere.SOIP_Deudas (
    --Columnas del sistema
    SOIP_Deudas_ID      numeric(10)     NOT NULL PRIMARY KEY,
    ad_client_id    numeric(10)     NOT NULL,
    ad_org_id   numeric(10)     NOT NULL,
    updatedby   numeric(10)     NOT NULL,
    createdby   numeric(10)     NOT NULL,
    updated     timestamp       NOT NULL,
    created     timestamp       NOT NULL,
    isactive    char(1)     DEFAULT 'Y'::bpchar NOT NULL,

    --Columnas del usuario
    SOIP_Departamentos_ID numeric(10) NOT NULL,
    fecha       timestamp   NOT NULL,
    monto       real        NOT NULL DEFAULT 0,

    FOREIGN KEY (SOIP_Departamatos_ID) REFERENCES SOIP_Departamentos(SOIP_Departamentos_ID),
    UNIQUE (EXTRACT (MONTH FROM TIMESTAMP fecha), EXTRACT(YEAR FROM TIMESTAMP fecha))
)

Any idea on how I can do this without specific columns of the year and month?

Thank.

+3
source share
1 answer

Quick workaround:

create unique index on adempiere.SOIP_Deudas ( EXTRACT (MONTH FROM fecha), EXTRACT(YEAR FROM fecha));
+3
source

All Articles