$this->db->distinct() weirdness with PostgreSQL

$this->db->distinct() weirdness with PostgreSQL - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: $this->db->distinct() weirdness with PostgreSQL (/showthread.php?tid=7669)

$this->db->distinct() weirdness with PostgreSQL - El Forum - 04-18-2008

[eluser]redguy[/eluser]
I was trying to get a distinct list of questions, but the distinct function doesn't work (with PostgreSQL).

The following code:

Code:
$this->db->distinct();

$this->db->get('questions');

Produces duplicate items in the result set. This is because the SQL is:

Code:
SELECT DISTINCT * FROM "questions"

while it should be:

Code:
SELECT DISTINCT ON ("question") * FROM "questions"

See PostgreSQL manual for reference.

The distinct() function only takes a boolean, so supplying the column name doesn't help.

$this->db->distinct() weirdness with PostgreSQL - El Forum - 04-18-2008

[eluser]Seppo[/eluser]
DISTINCT ON accepts one or multiple field according to the docs... does it accept * too?

I'm asking this because the MySQL behaviour un distinct is removing duplicated rows using all columns and I don't see in the postgre manual if it's valid

$this->db->distinct() weirdness with PostgreSQL - El Forum - 04-18-2008

[eluser]redguy[/eluser]
Something like:

Code:
SELECT DISTINCT *

FROM questions;

is possible. But that just returns every row, since each row has/should an unique identifier. It's also not SQL compliant. In PostgreSQL you always have to supply at least one column name.