1

I need to join two tables as part of a sql query, and whoever set up the database's login table did not include a column with a unique id for each login. I need to join login with a table where each login will line up with one or more rows of the other table, and I want to be able to uniquely identify each row from login without having to rely on the user id and the login timestamp being unique.

I'd like to do something like this:

SELECT l.*, pp.personpositionid
FROM (SELECT login.*, next_unique_bigint() AS loginid FROM login) l, personposition pp, personpositionstatus pps
WHERE l.personid = pp.personid
AND [...]

I could use random(), but I'd rather have the temporary unique "id's" be sequential (or even just predictable) rather than random. I've done a search for what I'm trying to do, but haven't found what I'm looking for.

Is there a way to do this in SQL, especially PostgreSQL? I'm guessing there is and I just don't know what keywords to search for.

Bonus points if you have a different way to keep the login records strait than giving each row a temporary id.

EDIT: The overall purpose of this is to get a count of logins for a given date range. Each user has a set of Positions (roles? jobs?) that they hold from one point in time to another, and I need to count logins according to what position(s) the user held at the time they logged in. Each user can have 1 or more positions at any given time. They can only have 0 positions if their user account is deactivated, so naturally no logins will be recorded at a point in time when they have 0 positions.

Mar
  • 7,765
  • 9
  • 48
  • 82

2 Answers2

4

Why don't you add a serial primary key column to the table?

ALTER TABLE login ADD column login_id serial;
ALTER TABLE login ADD CONSTRAINT login_pkey PRIMARY KEY(login_id);

The first operation will rewrite the table and take a lock for some time. I would then run

VACCUM FULL ANALYZE login;

Inferior alternatives: row_number() as pointed out by @Joachim. For maximum performance you can leave the OVER clause empty:

row_number() OVER () AS rn

Aside: use the AS keyword for column aliases (while they are just noise for table aliases).

Or you can use the ctid as poor man's surrogate for a primary key. That would be even faster:

Details:
In-order sequence generation

Example on dba.SE:
numbering rows consecutively for a number of tables

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • All of these work, and this offers more than one solution, so you get the answer credit. – Mar Mar 10 '14 at 22:03
2

You could probably use ROW_NUMBER();

SELECT login.*, ROW_NUMBER() OVER (ORDER BY personid) rn

...for deterministic ordering or...

SELECT login.*, ROW_NUMBER() OVER () rn

...to just pick pseudo random (but sequential) numbers.

A small SQLfiddle to test with.

Other alternatives are ctid or oid, but they are PostgreSQL specific and both have some restrictions of their own (documented at the linked page)

Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
  • This works but multiplies my query's runtime by 10 (~6 seconds vs ~0.6 seconds without `ROW_NUMBER()`). Does it matter what I do the OVER on? Any other ways I can optimize it? – Mar Mar 10 '14 at 21:40