PostgreSQL – How to Generate Row Number Without Window Function

postgresql

In PostgreSQL, how do you generate a row number:

  • WITHOUT a Window Function (like row_number())
  • WITHOUT a Temp Table
  • Only using a single SELECT

Here is some sample data to play with,

CREATE TEMP TABLE foo AS 
SELECT * FROM ( VALUES
  ('wgates', 'Gates', 'William' ),
  ('wgrant', 'Grant', 'Wallace' ),
  ('jjones', 'Jones', 'John' ),
  ('psmith', 'Smith', 'Paul' )
) AS t(name_id, last_name, first_name);

The desired output would be:

 row_number │ name_id │ last_name │ first_name 
────────────┼─────────┼───────────┼────────────
          1 │ wgates  │ Gates     │ William
          2 │ wgrant  │ Grant     │ Wallace
          3 │ jjones  │ Jones     │ John
          4 │ psmith  │ Smith     │ Paul

Some of these methods can get tricky. Please explain your answers. I can also imagine two categories of answers that work:

  • data with a UNIQUE or PRIMARY KEY (we can still use name_id here)
  • nothing UNIQUE at all.

All features are on the table for the most recent version of PostgreSQL.

Ultimately, I need a unique key on a table that has no ID so I can update it against a cross-join of itself. I am also asking out of simple curiosity.

Best Answer

If it is performance you are worried about, use row_number without order by to avoid sorting.

row_number() over ()
Related Question