CODEWITHSUNDEEP
sqlpostgresqlview
code base 5000
code base 5000

Reputation: 4102

Add non-existing column to view

Is it possible to add a SERIAL column to an existing view if that field does not exist in the source table? I want to get an autoincremental ID field named OID that cannot be NULL.

CREATE OR REPLACE VIEW public.osm_polygon AS 

SELECT planet_osm_polygon.osm_id::text AS osm_id, 
    planet_osm_polygon.tags -> 'osm_timestamp'::text AS osm_timestamp, 
    planet_osm_polygon.tags -> 'name:en'::text AS name_en, 
    planet_osm_polygon.access, planet_osm_polygon.admin_level, 
    planet_osm_polygon.aerialway, planet_osm_polygon.aeroway, 
    planet_osm_polygon.amenity, planet_osm_polygon.area, 
    planet_osm_polygon.barrier, planet_osm_polygon.bicycle, 
    planet_osm_polygon.brand, planet_osm_polygon.bridge, way
    planet_osm_polygon.boundary, planet_osm_polygon.building FROM planet_osm_polygon
WHERE st_isvalid(planet_osm_polygon.way) AND planet_osm_polygon.way && st_makeenvelope((-17.5)::double precision, 3.0::double precision, 8.7::double precision, 26.9::double precision, 4326);

So that is my view.

I need to add a column that contains an auto-incremental field as Long Integers or Serial that cannot be NULL. Any suggestions?

Upvotes: 0

Views: 759

Answers (1)

Houari
Houari

Reputation: 5621

Using row_number ?

CREATE OR REPLACE VIEW public.osm_polygon AS 

SELECT planet_osm_polygon.osm_id::text AS osm_id, 
planet_osm_polygon.tags -> 'osm_timestamp'::text AS osm_timestamp, 
planet_osm_polygon.tags -> 'name:en'::text AS name_en, 
planet_osm_polygon.access, planet_osm_polygon.admin_level, 
planet_osm_polygon.aerialway, planet_osm_polygon.aeroway, 
planet_osm_polygon.amenity, planet_osm_polygon.area, 
planet_osm_polygon.barrier, planet_osm_polygon.bicycle, 
planet_osm_polygon.brand, planet_osm_polygon.bridge, way,
planet_osm_polygon.boundary, planet_osm_polygon.building,
row_number() over() as row_num
 FROM planet_osm_polygon
WHERE st_isvalid(planet_osm_polygon.way) AND planet_osm_polygon.way && st_makeenvelope((-17.5)::double precision, 3.0::double precision, 8.7::double precision, 26.9::double precision, 4326);

Upvotes: 1

Related Questions