Delete row by row number in postgresql

Question

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

Please show your table schema. Do you have a primary key defined on the table? — peterm
– peterm, Commented Oct 15, 2014 at 4:54
Table "public.student" Column | Type | Modifiers ------------+------------------+----------- name | text | rollno | integer | cgpa | double precision | department | text | branch | text | — Ganeshkumar SR
– Ganeshkumar SR, Commented Oct 15, 2014 at 4:57
@GaneshkumarSR dont you have any unique filed in your table ?? — Vivek S.
– Vivek S., Commented Oct 15, 2014 at 5:29

Community · Accepted Answer · 2017-05-23 12:31:59Z

7

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

edited May 23, 2017 at 12:31

CommunityBot

11 silver badge

answered Oct 15, 2014 at 5:31

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Ganeshkumar SR Over a year ago

Here what is ctid? In this table I can have duplicate data so I can't delete by any field value.For example I can have rollno value as 100 in multiple rows.When i try to delete with id it'll delete all the rows with rollno as 100.

Vivek S. Over a year ago

@GaneshkumarSR did you try my answer ?? in my answer i have clearly mentioned about ctid or just refer postgresql.org/docs/8.2/static/ddl-system-columns.html

Vivek S. Over a year ago

@GaneshkumarSR every table in PostgreSQL have a CTID if you want to check in your case try select ctid,* from student

user330315 Over a year ago

@GaneshkumarSR: you can still have a serial column in that table which is then automatically populated during the import (just don't provide a value for it). This will be a much better solution than relying on the ctid.

Felipe Desiderati Over a year ago

Saved my day! Thnks

|

Ganeshkumar SR · Accepted Answer · 2014-10-15 07:42:30Z

3

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.

answered Oct 15, 2014 at 7:42

Ganeshkumar SR

1011 gold badge2 silver badges10 bronze badges

Comments

vhadalgi · Accepted Answer · 2014-10-15 05:48:37Z

-3

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

edited Oct 15, 2014 at 5:48

user330315

answered Oct 15, 2014 at 4:55

vhadalgi

7,2376 gold badges44 silver badges67 bronze badges

8 Comments

Ganeshkumar SR Over a year ago

with cte as(select *,ROW_NUMBER() over() as rn from student)delete from cte where rn=1; I used this query but am getting an error like this. ERROR: relation "cte" does not exist LINE 1: ...OW_NUMBER() over() as rn from student)delete from cte where ...

Vivek S. Over a year ago

@GaneshkumarSR Which PostgreSQL version your using ??

Ganeshkumar SR Over a year ago

my psql version is 9.3.5. But this command working for select query. with CTE as(select *,ROW_NUMBER() over() as rn from student)select * from cte;

Vivek S. Over a year ago

@861051069712110711711710997114 CTE in PostgreSQL works differently, you cannot delete or update it

Craig Ringer Over a year ago

Yep, unfortunaetly CTEs in PostgreSQL are forced-materialisation optimisation fences that cannot be treated as simply-updatable views. So you can't DELETE FROM a CTE term.

|

Collectives™ on Stack Overflow

Delete row by row number in postgresql

3 Answers 3

10 Comments

Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related