Extracting the month and year from a Timestamp on a Unique check?

postgresql, sql

Solution

A quick workaround:

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

Problem

I'm trying to create a table with a timestamp column. The problem is, I want the month and the year combination to be unique. I tried this, but it doesn't 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 of how I could do that without having specific Year and Month columns? Thanks.

Original source

Related problems