Postgres LIKE unique constraint possible?

Question

I'm new to Postgres and am creating a table (metrics_reaches) using pgAdmin III.

In my table, I have an insertion_timestamp of timestamp with timezome type column.

I'd like to create a UNIQUE constraint that, amongst other fields, checks only the date portion of the insertion_timestamp and not the time.

Is there a way to do that? Here's what my script looks like at the moment (see the last CONSTRAINT).

-- Table: metrics_reaches

-- DROP TABLE metrics_reaches;

CREATE TABLE metrics_reaches
(
  organizations_id integer NOT NULL,
  applications_id integer NOT NULL,
  countries_id integer NOT NULL,
  platforms_id integer NOT NULL,
  ...
  insertion_timestamp timestamp with time zone NOT NULL,
  id serial NOT NULL,
  CONSTRAINT metrics_reaches_pkey PRIMARY KEY (id),
  CONSTRAINT metrics_reaches_applications_id_fkey FOREIGN KEY (applications_id)
      REFERENCES applications (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT metrics_reaches_countries_id_fkey FOREIGN KEY (countries_id)
      REFERENCES countries (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT metrics_reaches_organizations_id_fkey FOREIGN KEY (organizations_id)
      REFERENCES organizations (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT metrics_reaches_platforms_id_fkey FOREIGN KEY (platforms_id)
      REFERENCES platforms (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT metrics_reaches_organizations_id_key UNIQUE (organizations_id, applications_id, countries_id, platforms_id, insertion_timestamp)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE metrics_reaches
  OWNER TO postgres;

Frank Heikens · Accepted Answer · 2013-05-30 17:54:25Z

1

Try a CAST():

CONSTRAINT metrics_reaches_organizations_id_key UNIQUE (
  organizations_id, 
  applications_id, 
  countries_id, 
  platforms_id, 
  CAST(insertion_timestamp AS date)
)

answered May 30, 2013 at 17:54

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Richard Huxton Over a year ago

Comment on Frank's answer wouldn't fit here, so posted as separate answer.

Richard Huxton · Accepted Answer · 2013-05-30 19:45:55Z

This is really a comment to Frank's answer, but it's too long for the comment box.

If you are being paranoid, you need to watch the local timezone carefully when dealing with date casts:

bookings=> SET timezone='GMT';
SET
bookings=> SELECT now() at time zone 'GMT', (now() at time zone 'GMT')::date, now(), now()::date;
         timezone          |  timezone  |             now              |    now     
---------------------------+------------+------------------------------+------------
 2013-05-30 19:36:04.23684 | 2013-05-30 | 2013-05-30 19:36:04.23684+00 | 2013-05-30
(1 row)

bookings=> set timezone='GMT-7';
SET
bookings=> SELECT now() at time zone 'GMT', (now() at time zone 'GMT')::date, now(), now()::date;
          timezone          |  timezone  |              now              |    now     
----------------------------+------------+-------------------------------+------------
 2013-05-30 19:36:13.723558 | 2013-05-30 | 2013-05-31 02:36:13.723558+07 | 2013-05-31

Now, PG is smart enough to know this is a problem, and if you try to create a constraint with a date cast then you should see something like:

ERROR:  functions in index expression must be marked IMMUTABLE

If you try to cast after applying "at time zone" then it really is immutable and you can have your constraint.

Of course the other option is to wrap the cast in a function and mark the function as immutable. If you're going to lie to the system like that though, don't come complaining when your database behaves oddly a year from now.

Collectives™ on Stack Overflow

Postgres LIKE unique constraint possible?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related