3

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.

Uri
  • 2,207
  • 2
  • 21
  • 21

1 Answers1

3

A quick workaround:

create unique index on adempiere.SOIP_Deudas ( EXTRACT (MONTH FROM fecha), EXTRACT(YEAR FROM fecha));
Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
  • 2
    A variation on this which might be a tiny bit faster would be to index on `(date_trunc('month', fecha))`. http://www.postgresql.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC – kgrittn May 07 '12 at 18:09
  • 1
    @kgrittn Or: `date_trunc('month', fecha)::date`, because `datetrunc()` returns a timestamp and we only need a date (8 vs. 4 bytes -> index size). However, due to [data alignment & padding, the saved 4 bytes go to waste](http://stackoverflow.com/a/7431468/939860) unless you have additional columns in the index. – Erwin Brandstetter May 08 '12 at 01:04
  • Either way, it will be important to write the queries such that you are comparing with the same data type as you wind up with in the index. – kgrittn May 08 '12 at 12:04
  • 1
    Actually, you'd have to use `cast(date_trunc('month', fecha) AS date)`, as the `::` operator is not supported in index creation. – Erwin Brandstetter May 08 '12 at 13:36
  • When i try doing this it gives me an error saying "ERROR: functions in index expression must be marked IMMUTABLE" – pratz Mar 19 '13 at 15:15