I have a very wierd requirement that i need to load a file containing full row duplicate as shown below in to a table without losing any value and it should also contain a primary key defined. hence we are planning to use a sequence number such that each record will be treated as a unique record and can also define primary key on the sequence number.
col 1 col 2 col 3 seq num
aaa bbb ccc 1
aaa bbb ccc 2
one option i found is to use row_number() over (order by col1, col2, col3).
Since the table contains nearly 50 columns this query looks awkward, i am sure there could be a way to simplify the query. Kindly help me on this.
I found sum(1) over( rows unbounded preceding ) to be an interersting substitute, but i would like to hear some experts advice on using this condition.
There were some exchanges of views on these two links . They may be what you are looking for.
Generated always as identity can be used, but you may not get it in sequence. it will be make the row unique and a good distribution.
col_name BIGINT GENERATED ALWAYS AS IDENTITY
(START WITH 1
INCREMENT BY 1
and make it as PI.
Thanks raja, those links are pretty useful. i decided to use sum(1) over (rows unbounded precedings) and it works well.
Shrini, looks like identity column cannot be used as a primary key. I tried creating primary key based on the column and end up with Illegal use of identity column. Otherwise i felt it as a good approach as well.