6

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.

sym246
  • 1,836
  • 3
  • 24
  • 50

2 Answers2

10

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%'
krokodilko
  • 35,300
  • 7
  • 55
  • 79
2

Try the 'like' operator, it allows you to do pattern matching.

DELETE FROM my_tbl WHERE name LIKE '%test%';

The '%' character is a wildcard. The above line should delete every column where the name contains the string 'test'.

More info here: pattern matching

Community
  • 1
  • 1
Dirk
  • 599
  • 1
  • 4
  • 18