How can I generate a row_number without using a window function? 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.
If it is performance you are worried about, use row_number without order by to avoid sorting.

row_number() over ()