current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 0 down vote favorite

I would like to know how to delete entire rows in a postgres table that contain a particular string in the title column. So if the string is:

it is a sunny day

How can I issue a command in postgres to remove this row from the table completely. For example, the following row would be deleted:

12, I woke up and to my surprise it is a sunny day, 54645646,

Also, I would like it to ignore case as the string could be a mixture of capital and lower case letters (i.e. the case my vary throughout the table). Any help appreciated.

I've tried:

DELETE FROM mytable where title like 'it is a sunny day'

But no records were updated despite the fact that I know there are a number of lines with this string in.

share|improve this question

closed as off-topic by derobert, don_crissti, Jeff Schaller, Anthon, Scott May 25 '16 at 20:04

  • This question does not appear to be about Unix or Linux within the scope defined in the help center.
If this question can be reworded to fit the rules in the help center, please edit the question.
    
Did you even try googling for this? The PostgreSQL online docs tell you exactly how to do this... – John May 25 '16 at 16:11
    
Yes I've edited my question to show what i've tried – treetop May 25 '16 at 16:33
2  
I'm voting to close this question as off-topic because I think it belongs on dba.stackexchange.com – Jeff Schaller May 25 '16 at 17:54
1  
@JeffSchaller note that the Database Administrators's help center says "but, dba.se is not the right place to ask questions about [b]asic SQL - ask on Stack Overflow". – derobert May 25 '16 at 21:01
up vote 0 down vote accepted

By LOWER function, you can search case insensitively your string.

DELETE FROM mytable WHERE LOWER(title) LIKE '%it is a sunny day%';
share|improve this answer
up vote 0 down vote

You're part way there. You need to use a regexp pattern, not just a string, to search only a part of the entry string, and the ILIKE keyword for case insensitivity. See https://www.postgresql.org/docs/9.0/static/functions-matching.html for details (that URL valid only for 9.x versions of PostgreSQL).

share|improve this answer
    
Thanks for the link, i'm looking through it, but it's not the easiest type of information to decipher when you are first starting out! – treetop May 25 '16 at 16:47

Not the answer you're looking for? Browse other questions tagged or ask your own question.