PostgreSQL – Create view with autoincremental column

postgresqlpostgresql-9.2view

I have a PostgreSQL table, and I need to create a view with a new column. This column needs to be an auto-incremental column starting at 1 and going to N.

Is this possible to do without effecting the original schema of the legacy data structure?

Best Answer

As @deszo said user OVER()

create view foo as (
    select row_number() over (order by field), field, field2, field3 from bar
)

Related Solutions

Sql-server – How to create a view that refresh automatically

Use WITH SCHEMABINDING in the view

CREATE VIEW ExampleDBaseII
WITH SCHEMABINDING 
AS
SELECT T.ID, Cast(T.Name AS Varchar) as Name, Cast(T.City AS Varchar) as City,
FROM Team T
GO

This will disallow any changes to the underling tables that could affect the view

It also requires the use of qualifiers (schema, alias) and disallows the use of SELECT *.
Which is a good thing (SO link)

Sql-server – Create index view with self join

If all you need is to speed up your query, you can create the following indexed view:

Select ParentDetailID ,
       SUM(Quantity) as RemainQuantity,
       COUNT_BIG(*) as CountBig
From SAM.Detail 
Group By ParentDetailID

You can join it you Detail table, and your query might run faster.

Best Answer

Related Solutions

Sql-server – How to create a view that refresh automatically

Sql-server – Create index view with self join

Related Question