Message board

This is probably well know already, but it is worth posting for those who happen to come accross this post.

The scenario ...

You have a table called photos. It has many columns including "photoid", "title" and many more.  Now you want to create a table just for photo photo titles called "photo_titles". Your objective is to have a new "id" column along with the photid and title columns from the photos table.

You have heard of CREATE TABLE x AS SELECT * FROM y

We use the same concept but generate the id on the fly.  Here is how.

CREATE TABLE  photo_titles AS SELECT row_number() OVER ( ) AS id, photoid, title FROM photos WHERE title  != '';

Then you get ....

 id   | photoid |                                      title                                                                
-------+---------+-----------------------------------------------------------------------------------------
    1 |  333212 | Agnosco
    2 |  339133 | Horsing Around
    3 |  341838 | from my window.....
    4 |  348703 | Liberty ship
    5 |  515326 | Neon Boneyard Las Vegas
    6 |  602497 | Photo - 102497
    7 |  334449 | The Welcoming Path
    8 |  403089 | Photo - 103089
    9 |  527580 | blur
   10 |  100248 | Photo - 100248