Relative Relocating

We wish to relocate one or more columns relative to the current column locations while keeping all other column locations relatively unchanged.

We will cover the following scenarios:

  • Relative to Beginning: Move one or more columns to the beginning ( the left of the table) while leaving the rest of the columns where they are.
  • Relative to End: Move one or more columns to the end ( the right of the table) while leaving the rest of the columns where they are.

Relative to Beginning

We wish to move one or more columns to the beginning (the left of the table) while leaving the rest of the columns where they are.

In this example, we wish to move columns col_3 and col_4 of the table table_1 to be the leftmost columns.

SELECT col_3,
       col_4,
       * EXCEPT (col_3, col_4)

FROM table_1;

Here is how this works:

  • To move one or more columns to the front of a table, we specify their names first in the desired order, and then we use * EXCEPT () to include the rest of the columns. See Exclude Columns.
  • The output table will be a copy of the input table table_1 with columns col_3 and col_4 moved to the first and second leftmost positions respectively.

Relative to End

We wish to move one or more columns to the end (the right of the table) while leaving the rest of the columns where they are.

In this example, we wish to move columns col_1 and col_2 of the table table_1 to be the right columns.

SELECT * EXCEPT (col_1,col_2),
       col_1,
       col_2
FROM table_1;

Here is how this works:

  • To move one or more columns to the end of a table, we select all columns and exclude their names using * EXCEPT (). See Exclude Columns.
  • We specify the columns we wish to move to the end of the table after that in the desired order.
  • The output table will be a copy of the input table table_1 with columns col_1 and col_2 moved to the first and second rightmost positions respectively.
SQL
I/O