How to add ROW_NUMBER() in a view?

Question

In PostgreSQL 8.4 I want to create a view from 3 tables with id. So I want to have this structure in my view:

num serial,
name_dispatcher character varying(250)
the_geom geometry

I can select name_dispatcher and the_geom from tables:

 CREATE VIEW lineView
      AS SELECT 'name' AS name_dispatcher, the_geom
      FROM line1
      UNION
      SELECT 'name' AS name_dispatcher, the_geom
      FROM line2
      UNION
      SELECT 'name' AS name_dispatcher, the_geom
      FROM line3

How to create the num column in the view?

UPDATE

I found a solution:

ROW_NUMBER() OVER(ORDER BY lineView.voltage)

But I don't know how to use it in ALTER VIEW. How do I put it in there?

Side note: Be aware that `UNION` can be an expensive operation, because it eliminates duplicates. If you know that no duplicates will be in your final result, or you're okay with duplicates existing, switching to `UNION ALL` may be beneficial. — Damien_The_Unbeliever, Jan 18 '13 at 08:59

Erwin Brandstetter · Accepted Answer · 2013-01-18T08:54:34.603

You can't use ALTER VIEW to drop or add columns. I quote the manual on ALTER VIEW:

ALTER VIEW changes various auxiliary properties of a view. (If you want to modify the view's defining query, use CREATE OR REPLACE VIEW.)

But a simple CREATE OR REPLACE VIEW won't cut it. Another quote from the manual:

The new query must generate the same columns that were generated by the existing view query

So DROP and CREATE the view:

DROP VIEW lineview;

CREATE VIEW lineview AS
SELECT *, row_number() OVER(ORDER BY ???) AS num
FROM (
   SELECT 'name' AS name_dispatcher, the_geom
   FROM line1

   UNION
   SELECT 'name' AS name_dispatcher, the_geom
   FROM line2

   UNION
   SELECT 'name' AS name_dispatcher, the_geom
   FROM line3
   ) x

I use a subquery because I assume you want to add row_number() to all rows. Your question is vague in that respect.
If you just want a unique id in no particular order, use row_number() OVER().

@KliverMax: That's short for `AS x` and is a table alias for the sub-select. SQL requires one. — Erwin Brandstetter, Jan 18 '13 at 08:48

How to add ROW_NUMBER() in a view?

1 Answers1

Linked