PostgreSQL, Swap data of certain column in two rows

Question

I have complex function to swap data from same name column in different rows. I read data from first, store it in local temporary variables, read data from second row and if certain conditions are met (min/max), save it to first row and then save temporary variables to second row what is slow and error prone operation.

So I thought that maybe same result could be achieved with SQL only.

Here is sample data:

CREATE TEMP TABLE tbl(
id        int PRIMARY KEY,
doc_num   integer, 
doc_text  text 
);

INSERT INTO tbl VALUES
  (1, 1, 'First column text1'),
  (2, 2, 'First column text2'),
  (4, 3, 'First column text3'),
  (7, 4, 'First column text4');

Piont is to swap only 'doc_num' column data in desired direction which may be to up or down what I do with separate functions.

If I can write a simple query in english that will sound like this:

First query:

SWAP DOC_NUM in row 2 with DOC_NUM in row 3 IF DOC_NUM in row 3 IS <= MAX(DOC_NUM);

Second query:

SWAP DOC_NUM in row 3 with DOC_NUM in row 2 IF DOC_NUM in row 2 IS >= MIN(DOC_NUM);

Is those queries possible to write with PostgreSQL and how?

Here is real code (which is ugly) from a real program which "do a job" and need improvements.

 Private Sub DataGridView2_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DataGridView2.KeyDown

    'SWAP --------------------------------------
    If e.Control And e.KeyCode = Keys.Left Then
        Debug.Print("Swap left/down")

        Dim target_nrow As Integer
        Dim target_index As Integer
        Dim selected_nrow As Integer
        Dim selected_index As Integer
        Dim target_row As Integer = selected_row - 1
        Using conn As New NpgsqlConnection(String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", dbServer, dbPort, dbUser, dbPass, mydatabase))
            conn.Open()
            Dim t As NpgsqlTransaction = conn.BeginTransaction()

            Using cmd As New NpgsqlCommand( _
                  "SELECT cur_id, doc_num, nrow " & _
                  "FROM " & mytable & " " & _
                  "WHERE doc_num='" & active_doc.ToString & "' AND nrow='" & selected_row.ToString & "'", conn)

                Using dr As NpgsqlDataReader = cmd.ExecuteReader()
                    While dr.Read()
                        selected_index = CInt(dr(0))
                        selected_nrow = CInt(dr(2))
                    End While
                End Using
            End Using

            Using cmd As New NpgsqlCommand( _
                  "SELECT cur_id, doc_num, nrow " & _
                  "FROM " & mytable & " " & _
                  "WHERE doc_num='" & active_doc.ToString & "' AND nrow='" & target_row.ToString & "'", conn)

                Using dr As NpgsqlDataReader = cmd.ExecuteReader()
                    While dr.Read()
                        target_index = CInt(dr(0))
                        target_nrow = CInt(dr(2))
                    End While
                End Using
            End Using

            Dim updated_t As Integer = 0
            Using cmd As New NpgsqlCommand( _
                  "UPDATE " & mytable & " SET " & _
                  "nrow=" & selected_nrow & " " & _
                  "WHERE cur_id=" & target_index.ToString, conn)

                updated_t = CInt(cmd.ExecuteNonQuery())
                cmd.Dispose()
            End Using

            Dim updated_s As Integer = 0
            Using cmd As New NpgsqlCommand( _
                  "UPDATE " & mytable & " SET " & _
                  "nrow=" & target_nrow & " " & _
                  "WHERE cur_id=" & selected_index.ToString, conn)

                updated_s = CInt(cmd.ExecuteNonQuery())
                cmd.Dispose()
            End Using

            If updated_s > 0 And updated_t > 0 Then
                t.Commit()
            Else
                t.Rollback()
            End If

            t.Dispose()
            conn.Close()
            conn.Dispose()
        End Using

        Refreshlist(active_doc)
    End If

    If e.Control And e.KeyCode = Keys.Right Then
        Debug.Print("Swap right/up")

        'similar code to swap up again

    End If

Whole story is in question on how to make this shorter, faster and more elegant?

Answer 1

Example: swap doc_num for ids 2 and 4:

UPDATE tbl dst
SET doc_num = src.doc_num
FROM tbl src
WHERE dst.id IN(2,4)
AND src.id IN(2,4)
AND dst.id <> src.id -- don't try this at home!
        ;

SELECT * FROm tbl
ORDER BY id;

Result:

 id | doc_num |      doc_text      
----+---------+--------------------
  1 |       1 | First column text1
  2 |       3 | First column text2
  4 |       2 | First column text3
  7 |       4 | First column text4
(4 rows)

Answer 2

to swap data down you could try something like.:

with cte1 as (
    select
        row_number() over(order by id asc) as row_num,
        id, doc_num
    from tbl
    where id >= %your current id% order by id limit 2
)
update tbl as t set
    doc_num = coalesce(c2.doc_num, t.doc_num)
from cte1 as c1
    left outer join cte1 as c2 on c2.row_num <> c1.row_num
where t.id = c1.id;

see sql fiddle demo

Up will be the same but where id <= %your current id% order by id desc limit 2