postgresqlpostgresql-9.1postgresql-9.3postgresql-9.4

How to increment rows by 1 from a certain range in postgresql


I have a table and multiple rows in it.I want to increment rows of port column by 1 which is above certain range.(this certain range i am getting from another query)

I tried using row_number() over() but first row which has to be incremented is incrementing by row_number instead of 1.

below is my query:

UPDATE criml.model_master mm SET port = row_number + 
(select prev from (SELECT id,name,port,LAG(port) OVER (ORDER BY id )  as prev,port - LAG(port) OVER (ORDER BY id )  as difference  from criml.model_master ) AS foo where difference >9 order by difference asc limit 1) 
FROM ( SELECT id, row_number() over () FROM criml.model_master ORDER BY 1) foo WHERE mm.id = foo.id and port not in (9000,9001) and port > 
(select prev from (SELECT id,name,port,LAG(port) OVER (ORDER BY id )  as prev,port - LAG(port) OVER (ORDER BY id )  as difference  from criml.model_master ) AS foo where difference >9 order by difference asc limit 1) ;

Below is the sample data:

id port
1 200
2 201
3 670

i want 3rd row to be 202 instead of 201 + 3 (row_number).Ignore the inner query which i am calculating for getting the range.Please assist.


Solution

  • The following query reassigns ports using the same criteria that was in the original post:

    WITH base_port AS (
      SELECT port
        FROM (SELECT id, port, lead(port) OVER (ORDER BY port) AS next_port
                FROM model_master) t
               WHERE t.next_port - t.port > 9
               ORDER BY port
               LIMIT 1),
    new_ports AS (
      SELECT mm.id, mm.port AS old_port, base_port.port + dense_rank() OVER (ORDER BY mm.port) AS new_port
        FROM base_port CROSS JOIN model_master mm
       WHERE mm.port > base_port.port)
    UPDATE model_master mm
       SET port = new_ports.new_port
      FROM new_ports
     WHERE mm.id = new_ports.id
       AND mm.port NOT IN (9001, 9002);
    

    The query preserves the relative order of the ports.

    Note: The UPDATE will break if there are enough ports above 9002 to cause new_port to have a value of 9001 or 9002. Additional logic can be added to prevent overlapping values.