Delete a row at a certain line number in Postgres

Question

I am building an app in which I need to delete a table row at a certain line number. I don't want to use or rely on an id, because if I delete a row, the following rows won't "shift down" -- line 8 today could be line 7 tomorrow, but line 8 will still have an id of 8.

How can I write a postgres SQL that essentially does this:

DELETE FROM Table
WHERE <row_number> = n;

And row_number isn't a real attribute.

Answer 1

I'd go for:

DELETE FROM Table OFFSET <row_number> limit 1 order by id

Answer 2

What you want is probably the OID:

DELETE FROM Table WHERE oid = n;

See here for more details

Answer 3

Your question is rather ill-defined; as Milen comments, what do you really mean by "line" and "line number"? I hope you've got ORDER BYs in all your queries if you're doing stuff like that. This question also becomes trivial if your rows have primary keys... do yours not? A table with no primary keys is a table that's asking for trouble, and usually indicates a serious design flaw.

Anyway, if you want to go full speed ahead, damning the potential problems en route, the windowing functions in 8.4 will probably do what you need with minimal fuss. Or you could save yourself a ton of trouble tomorrow by writing a better schema today.