Deleting rows in PostgreSQL if column begins with a specific pattern

Question

I am very new at PostgreSQL and want to be able to delete rows from a table if a specific column starts with a certain pattern.

For example,

I have a reference column which is a sort of time stamp (YYMMDDHHMM). If the reference column starts with 16********, then i'd like it to be deleted.

So far, I know how to delete the column if the whole reference is known. E.g:

DELETE FROM my_tbl WHERE name='test1' AND ref_no=1601010000;

However, I need to change this so that the condition only specifies the first 2 digits of the reference number.

Answer 1

Read about LIKE operator: https://www.postgresql.org/docs/8.3/static/functions-matching.html

Use this command to delete rows:

DELETE FROM my_tbl WHERE reference LIKE '16%' 

---

Before deleting rows from the table, first check if a delete condition really works as expected using it in a regular SELECT statement:

SELECT * FROM my_tbl WHERE reference LIKE '16%'

---

If reference column is not of type varchar but for example int, you need first to conver (cast) it to a varchar datatype in this way:

SELECT * FROM my_tbl WHERE reference::varchar LIKE '16%'
DELETE FROM my_tbl WHERE reference::varchar LIKE '16%'