misuse of window function row_number()

(1) By anonymous on 2022-04-11 07:49:33 [link] [source]

drop table if exists myTable;

create table myTable(recid int generated always as (row_number() over()) stored,c1 text);

insert into myTable(c1)
WITH thisTable(c1) as (Values('Mon'),('Tue'),('Wed'),('Thu'),('Fri'),('Sat'),('Sun'))
select * from thisTable;

select * from myTable;

And I get

Parse error: misuse of window function row_number()

I expected recid to be auto populated. I'd like to understand how this is mis-use. Any insights?

(2) By midijohnny on 2022-04-11 10:54:35 in reply to 1 [source]

This is because the generated column is trying to use a window function - which isn't possible; generated columns can only be defined in terms of the current row ; whereas a window functions look across multiple rows.

The notes for generated column state this:

The expression of a generated column may only reference constant literals and columns within the same row, and may only use scalar deterministic functions. The expression may not use subqueries, aggregate functions, window functions, or table-valued functions.

(3) By midijohnny on 2022-04-11 11:08:23 in reply to 2 [link] [source]

Possibly you can use the feature of SQLite that will automatically generate you a sequential integer in the primary key.

Like this:

CREATE TABLE mytable(recid INTEGER PRIMARY KEY NOT NULL,c1 TEXT);

INSERT INTO mytable(c1) -- note we don't specify recid here
VALUES('Mon'),('Tue'),('Wed'),('Thu'),('Fri'),('Sat'),('Sun');

SELECT * FROM mytable;

recid|c1
1|Mon
2|Tue
3|Wed
4|Thu
5|Fri
6|Sat
7|Sun

See the notes on (not, if possible!) using autoincrement.

(4) By anonymous on 2022-04-11 11:13:45 in reply to 3 [link] [source]

Thank you; that will do!