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.

identify a good technical PK for duplicate removal

Now that is a completely different question then finding a workaround for row_number().

In Postgres you can use ctid for that. No need for window functions or slow and non-scalable workarounds.

To answer the direct question:

This can be done without window functions, but this is going to be horribly slow:

select name_id, last_name, first_name, 
       (select count(*)
        from the_table t2
        where t2.name_id <= t1.name_id) as row_number
from the_table t1
order by name_id;

The above is identical to:

select name_id, last_name, first_name, 
       row_number() over (order by name_id) as row_number
from the_table
order by name_id;

But the solution with a window function will be a lot faster. If you don't need any ordering, then use

select name_id, last_name, first_name, 
       row_number() over () as row_number
from the_table
order by name_id;

You won't get a "stable" row number that way, but it will be unique.

Another possible alternative is to create a sequence, then use nextval() in the select statement.

StackExchange Database Administrators Q#159634, answer score: 10