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?
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.
You can't use a pseudo-type as a column type. Since geom and eventdate are different types I suspect you get a sort of undefined type in the results. You may be able to cast it to something specific or could maybe try:
create view esfuerzo as
select distinct (st_astext(geom),eventdate), geom
from datos
where orden='x';
to make the result a varchar.
TEXT: CREATE VIEW esfuerzo AS SELECT (geom, eventdate)::TEXT ... (which will be translated into ... ROW(geom, eventdate)::TEXT as "row" ...). Or, better, create a specific type to be able to keep the column types.
The correct syntax is SELECT DISTINCT ON (geom, eventdate) geom, eventdate FROM datos WHERE orden='x'. By omitting on, Postgres thinks you are looking for a composite type, rather than essentially just a GROUP BY geom, evendate. – John Powell Jul 3 at 15:28
SELECT DISTINCT ON (geom, eventdate) geom, eventdate FROM datos WHERE orden='x'. By omitting on, Postgres thinks you are looking for a composite type, rather than essentially just a GROUP BY geom, evendate.