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

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.

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.

Select * from searchterms WHERE gene_id <> 0 AND searchterm <> 0 AND types <> 0 group by gene_id,searchterm,types

I think distinct will not work in this case. because if you gave distinct it will still show you 2 records i.e. gene_id = 0 and gene_id = 1253246.

So in order to avoid those other records, you would probably need to check condition something like gene_id <> 0 or gene_id > 0

You should prevent them from inserting in the first place.

For example

if(geneid == 0){
     break;
}else{
     # insert query}