geoserver sql view – syntax error

when trying to save an SQL view in geoserver that has SQL view parameters sometimes the error message “ERROR: syntax error at or near” is shown.

The reason for this is that the SQL query may be invalid if no default values are provided for the view parameters. In this case, it helps to supply them.

Another reason why this error may occur is that one is trying to save or refresh the view, but the view parameters are not actually enabled. It then helps to activate them by checking the checkbox next to the parameter.

No primary key when selecting PostGIS data from QGIS

When selecting data from PostGIS via QGIS using a custom query sometimes the error message “PostgreSQL layer has no primary key” is shown. The reason is that a query can sometimes not contain a series of unique values (e.g a join or select omitting the primary key).

What can be done is to create an artificial primary key in the query, like so:

row_number() OVER() as id

so for example:

select distinct  row_number() OVER() as id ,   nwpdata.station_id,geom from nwpdata where geom && ST_MakeEnvelope(119.66,21.49,122.78,25.48)