How do I create row numbers in Redshift?

Question

I would like to create a table using a select statement and add a column with row numbers

So I'd like to write something like:

create table schema.table_w_rownumbers as 
select 
    identity(1, 1) as nrum,
    foo.*
from schema.initial_table as foo;

possible duplicate of [Select Row number in postgres](http://stackoverflow.com/questions/11952245/select-row-number-in-postgres) — usr, Jun 26 '14 at 16:56
Do you need row numbers or an identity column? Will you be inserting into table_w_rownumbers in the future and want the values to auto increment? — SQLChao, Jun 26 '14 at 17:01
@usr - you get this error with similar code ERROR: Ranking window functions require order by clause. — d_a_c321, Jun 26 '14 at 17:26
@JChao - I won't be inserting. My goal is to test whether two Redshift tables are *EXACTLY* identical as described here: http://stackoverflow.com/questions/24436540/how-do-i-tell-if-two-tables-in-redshift-are-exactly-identical — d_a_c321, Jun 26 '14 at 17:30

score 11 · Answer 1 · answered Jun 14 '17 at 23:20

11

This worked for me!

create table schema.table_w_rownumbers as 
select 
  row_number() over (partition by 1) as nrum,
  foo.*
from schema.initial_table as foo;

answered Jun 14 '17 at 23:20

user 923227

score 1 · Answer 2 · answered Jun 26 '14 at 17:44

1

Try this code. Make sure you change order by id to whatever it needs to be ordered by.

Create Table schema.table_w_rownumbers
AS
(
select row_number() over (order by id) as nrum, * schema.initial_table
);

answered Jun 26 '14 at 17:44

SQLChao

2

In this case, I don't want to order it by anything. I want the row numbers to correspond to the order that they are returned by the select statement. – d_a_c321 Jun 26 '14 at 18:50
Order is not guaranteed unless specified. So the set returned by the select statement could vary for example if an index was added/deleted/altered. Since you are comparing data what's wrong with ordering both? – SQLChao Jun 26 '14 at 19:00

2 Answers2