Use of database views

You can create a view over a SELECT query, which gives a name to the query that you can refer to like an ordinary table. Views are very useful for example to filter and sort data, without having to create a new table.

Views can be used in almost any place a real table can be used.

Create a view

Create a view
  1. Click on the "New Layer".
  2. Click on the "Database view".
  3. Give the new view a name.
  4. Input the SELECT SQL, which will define the view.
  5. Click "Create."

Both a table and a view must have a primary key. GC2 detects primary keys on tables, but views do not have primary keys, so GC2 falls back on the field "gid". This means that the view should have a field "gid" with unique values. It must also have a geometry field, so it appears in the list of layers (there is no requirement for the naming of geometry fields). If a table is created through GC2, the table will have "gid" as the primary key. So a SELECT like this will work:


If * is not used in the query, the gid field and geometry field must be queried:

SELECT gid,the_geom FROM foo WHERE bar=1

There may also be made a "gid" field with "As" syntax. Here a view with one single point:

SELECT 1 As gid, ST_SetSRID (ST_Point(-123.365556, 48.428611),4326)::geometry(Point,4326) AS the_geom



Management of views

Management of views

Views behave like tables in nearly all contexts. In order to know the views from real tables, the views is given a blue background in the layer list.

A difference between views and tables is that data views can not be edited.

Get the definition of a existing view

Get the definition of a existing view
  1. Select a view-layer in the list so that the background becomes gray.
  2. Click Advanced.
  3. The SELECT SQL that defines the view, can be seen in the field View definition.


Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.