1

I have a table, with the columns login, lineno, line.
Each login can have 3 lineno and 3 lines, each lineno has to be unique if the login is the same.

I've tried:

create unique index unique_Lineno on rs_line (Lineno) where login=login;

It doesn't work. Can anyone please give me some help?
I've read the http://www.postgresql.org/docs/8.0/static/indexes-unique.html but there is no sample.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user2858149
  • 11
  • 1
  • Your tag was wrong. [psql](http://stackoverflow.com/tags/psql/info) is the comman line client of Postgres. You want Postgres. Also please have a look at formatting options ... – Erwin Brandstetter Oct 14 '13 at 10:50

1 Answers1

1

Your condition for the partial index: login=login doesn't do what you seem to be expecting. It always evaluates to TRUE, if login is NOT NULL, which is not helping at all for your case.

You need a multicolumn index instead:

CREATE UNIQUE INDEX rs_line_uni_idx ON rs_line (login, lineno);

Assuming your columns are NOT NULL. Else, also consider:
How to add a conditional unique index on PostgreSQL

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228