In PostgreSQL, how do you generate a row number:
- WITHOUT a Window Function (like row_number())
- WITHOUT a Temp Table
- Only using a single
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
PRIMARY KEY(we can still use
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.