Search code examples
postgresqlwindow-functions

I have a problem with the result of a postgresql window function


i have this postgresql table in the 'inv' schema:

CREATE TABLE vouchers (
    id BIGSERIAL,
    proveedor VARCHAR,
    numero_serie VARCHAR,
    numero_correlativo INT,
    oficina VARCHAR,
    fecha_operacion DATE,
    numero_operacion INT,
    hora_operacion TIME with time zone,
    importe DEC(9,2),
    adquiriente VARCHAR,
    cliente VARCHAR
);

and this rows:

My csv file with data This data is rdy to import without headers, column order is:

proveedor,
numero_serie,
numero_correlativo,
oficina,
fecha_operacion,
numero_operacion,
hora_operacion,
importe;

The problem is, when i execute

SELECT id,proveedor,LAG (id,1) OVER (PARTITION BY fecha_operacion ORDER BY fecha_operacion) as result FROM inv.vouchers

the query results with null rows like this

results

I'm quite confused, I don't know what could be happening

The expected result is:

| id | proveedor | id |
|----|-----------|----|
|   1|kenthiva   |null|
|   2|tisocni    |   1|
|   3|kenthiva   |   2|
|   4|cherrys    |   3|
|   5|cherrys    |   4|
|   6|cherrys    |   5|
|   7|tisocni    |   6|
|   8|cherrys    |   7|
|   9|kenthiva   |   8|
|  10|kenthiva   |   9|
.
.
.
| 358|cach       | 357|
| 359|consulceliz| 358|
| 360|kenthiva   | 359|
| 361|cach       | 360|

Solution

  • The error was here SELECT id,proveedor,LAG (id,1) OVER (PARTITION BY fecha_operacion ORDER BY fecha_operacion) as result FROM inv.vouchers, in the 'PARTITION BY' clause, this partition by fecha_operacion. I removed that and solve this.