PHP - 根据postgresql列中的值删除行

Question

I am generating a table in postgresql with PHP.

Resulting table

There are a lot of rows with the same values with exception of the first column, this is the unique ID. When I am trying to remove based on similar values in gene_id, searchterm and type.

Therefore, I used the following code:

$sql = "SELECT DISTINCT gene_id FROM searchterms";

Or

$sql = "SELECT DISTINCT ON (gene_id,searchterm,types) * FROM searchterms";

After executing this code I still got the same result. I would like to keep only row with id 4.

Answer 1

You are trying to delete all the empty rows?

DELETE FROM searchterms WHERE gene_id = 0

As for non empty rows with duplicates, you will want to select rows with more than one:

SELECT gene_d, COUNT(*) c FROM table GROUP BY name HAVING c > 1;

Then you can pop one element from the array and then delete the other ids.

To stop duplicates being added in the first place, simply do a SELECT query first, and if no rows are found, perform the INSERT.