PostgreSQL Delete Statement

Question

Table 1: Schema for the bookworm database. Primary keys are underlined. There are some foreign key references to link the tables together; you can make use of these with natural joins.

Author(aid, alastname, afirstname, acountry, aborn, adied).
Book(bid, btitle, pid, bdate, bpages, bprice).
City(cid, cname, cstate, ccountry).
Publisher(pid, pname).
Author_Book(aid, bid).
Publisher_City(pid, cid).

I have been trying to delete all books published after 1985 while deleting tuples from both the author_book and book tables, using only two delete statements.

So far I have tried..

delete from book
where bdate > 1985;

giving me the syntax:

ERROR:  update or delete on table "book" violates foreign key constraint 
"author_book_bid_fkey" on table "author_book"
DETAIL:  Key (bid)=(cltl) is still referenced from table "author_book".

and...

delete from author_book
where bid > 1985;

with another syntax:

ERROR:  operator does not exist: character > integer
LINE 2: where bid > 1985;
              ^
HINT:  No operator matches the given name and argument type(s). 
You might need to add explicit type casts.

I know this is easier than I think, but just cant grasp what is going wrong. Look forward to hearing your input. This still isn't working can someone help please!!

"Key (bid)=(cltl) is still referenced from table author_book" is pretty clear: You have a foreign key to the table author_book and there is still a row in that table referencing the book you are trying to delete. You need to first delete the rows from author_book or declare the foreign key as on delete cascade — user330315
– user330315, Commented Feb 24, 2015 at 20:24
so something like "delete * from author_book;" first? @a_horse_with_no_name — Zeke P
– Zeke P, Commented Feb 24, 2015 at 20:31

Zhengde Zhen · Accepted Answer · 2015-02-24 20:23:20Z

2

change this

delete from book
where bdate = bdate > 1985;

to

delete from book
where bdate  > 1985;

the reason why it does not working is that (bdate > 1985) returns a boolean value and you compare the boolean value to smallint. Delete needs a true value from where to be executed. similar to the next issue

edited Feb 24, 2015 at 20:23

user330315

answered Feb 24, 2015 at 20:17

Zhengde Zhen

361 bronze badge

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

2 Comments

Zeke P Over a year ago

I tried that, look at my original post above as I have edited it. Thank you for your help, slowly but surely moving along.

Zhengde Zhen Over a year ago

first of all, book's bdate is a foreign key to other relations. you cannot just delete whatever you want in book without affecting our relations. so there is a foreign key constraint. second, bid > 1985; compare a char type to int type. As a starter, you need to read the error messages and understand what is going in on

Collectives™ on Stack Overflow

PostgreSQL Delete Statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related