CODEWITHSUNDEEP
postgresqldelete-rowsql-delete
Ganeshkumar SR
Ganeshkumar SR

Reputation: 101

Delete row by row number in postgresql

I am new to postgreSql and I used following query to retrieve all the fields from database.

SELECT student.*,row_number() OVER () as rnum FROM student;

I don't know how to delete particular row by row number.Please give me some idea.

This is my table:

Column      | Type
------------+------------------
name        | text
rollno      | integer 
cgpa        | double precision 
department  | text 
branch      | text

Upvotes: 2

Views: 15029

Answers (3)

Ganeshkumar SR
Ganeshkumar SR

Reputation: 101

I don't know whether its a correct alternative for this problem.But it satisfies my problem.What my problem is I need to delete a row without help of anyone of it's column.I created table with OIDS,and with help of oid I deleted the rows.

CREATE TABLE Student(Name Text,RollNo Integer,Cgpa Float,Department Text,Branch Text)WITH OIDS;
 DELETE FROM STUDENT WHERE oid=18789;
 DELETE FROM STUDENT WHERE oid=18790;

Quoted from PostgreSQL - System Columns

Thanks to @WingedPanther for suggesting this idea.

Upvotes: 3

Vivek S.
Vivek S.

Reputation: 21905

with a as
(
SELECT student.*,row_number() OVER () as rnum FROM student
)

delete from student where ctid in (select ctid from a where rnum =1) -- the 
                                                                     -- row_number you want    
                                                                     -- to delete

Quoted from PostgreSQL - System Columns

ctid :
The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change each time it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier. The OID, or even better a user-defined serial number, should be used to identify logical rows.

Note : I strongly recommend you to use an unique filed in student table.

As per Craig's comment, I'll give another way to solve OP's issue it's a bit tricky

First create a unique column for table student, for this use below query

alter table student add column stu_uniq serial

this will produce stu_uniq with corresponding unique values for each row, so that OP can easily DELETE any row(s) using this stu_uniq

Upvotes: 7

vhadalgi
vhadalgi

Reputation: 7189

You could try like this.

create table t(id int,name varchar(10));
insert into t values(1,'a'),(2,'b'),(3,'c'),(4,'d');


with cte as
(
select *,ROW_NUMBER()over(order by id) as rn from t
)
delete from cte where rn=1;

Cte in Postgres

Upvotes: -3

Related Questions