PostgreSQL PostGIS – Fix Pseudo-Type Record Error

postgispostgresqlsql-view

I've got, in PostgreSQL/PostGIS,

create table datos (
gbifID int primary key,
orden varchar (50),
eventDate varchar (50),
geom geometry);

When I run 

select distinct (geom,eventdate), geom
from datos
where orden='x';

it works OK. However, 

create view esfuerzo as 
select distinct (geom,eventdate), geom
from datos
where orden='x';

returns
ERROR: column "row" has pseudo-type record
Estado SQL: 42P16. How can I create such a view?

Best Answer

As @IanTurton pointed out, enclosing (multiple) column names in parenthesis will invoke a cast to RECORD.

Simply run

CREATE OR REPLACE VIEW esfuerzo AS
  SELECT DISTINCT
         eventdate,
         geom
  FROM   datos
  WHERE  orden = 'x'
;

or possibly better, the equivalent

CREATE OR REPLACE VIEW esfuerzo AS
  SELECT geom
  FROM   datos
  WHERE  orden = 'x'
  GROUP BY
         eventdate, geom
;

where you can choose the columns to select from the list passed to GROUP BY.

Related Question