Date: June 2010
When new columns are added, they’re always placed at the end of the table. It’s possible to move existing columns within a table using an ALTER TABLE statement. For example, we can move the column that occupies the first position in a table to the last position. This article explains how columns can be moved, how we can see what the position of a column is, and what the difference is between the logical and the physical position of a column.
To illustrate all this, we use the following two tables. Both are described in my book The SQL Guide to Pervasive PSQL:
The TEAMS table:
TEAMNO PLAYERNO DIVISION
------ -------- --------
1 6 first
2 27 second
The MATCHES table:
MATCHNO TEAMNO PLAYERNO WON LOST
------- ------ -------- --- ----
1 1 6 3 1
2 1 6 2 3
3 1 6 3 0
4 1 44 3 2
5 1 83 0 3
6 1 2 1 3
7 1 57 3 0
8 1 8 0 3
9 2 27 3 2
10 2 104 3 2
11 2 112 2 3
12 2 112 1 3
13 2 8 0 3
Let’s begin by explaining what we mean by the first and the last position. Each column has both a logical and a physical position. The logical position is the position of the column shown when you use * in the SELECT clause. For example, if we process the following statement:
SELECT *
FROM TEAMS
the columns in the TEAMS table are displayed in the order TEAMNO, PLAYERNO, and DIVISION, because this is the order in which the columns were entered in the CREATE TABLE statement.
In essence, the logical position of a column is its default position when SELECT * is used. This position doesn’t depend on its physical position. The physical position of a column is the actual position of the column in the file. A column’s logical and physical position may be different. When a table has just been created, the logical position will always be the same as its physical position. The ALTER TABLE statement can change both types of positions which may result in the case where the two positions for a column are no longer the same.
Example 1: Move the column PLAYERNO in the MATCHES table from the third to the fifth logical position. Next, show that the PLAYERNO column has indeed been moved.
ALTER TABLE MATCHES
( PSQL_MOVE COLUMN PLAYERNO TO 5 )
SELECT *
FROM MATCHES
WHERE MATCHNO = 1
The result is:
MATCHNO TEAMNO WON LOST PLAYERNO
------- ------ --- ---- --------
1 1 0 1 6
Explanation: The result shows that PLAYERNO has been moved to the end. The column position you provide must be a whole number greater than 0 and smaller than or equal to the position of the last column. In the previous ALTER TABLE statement we were not allowed to use position 6 or higher, because the MATCHES table has just five columns. However, the column hasn’t been moved physically. In the file, all player numbers are still in the second position. In the book, a view is created that can be used to query column related data. The definition of this view looks like this:
CREATE VIEW COLUMNS (TABLE_NAME, COLUMN_NAME, COLUMN_NO, DATA_TYPE,
CHAR_LENGTH, PRECISION, SCALE, NULLABLE) AS
SELECT UPPER(TAB.XF$NAME), UPPER(COL.XE$NAME),
(SELECT COUNT(*)
FROM X$FIELD AS F
WHERE F.XE$FILE = TAB.XF$ID
AND F.XE$OFFSET <= COL.XE$OFFSET
AND F.XE$DATATYPE NOT IN (227,255)),
CASE XE$DATATYPE
WHEN 0 THEN 'STRING'
WHEN 1 THEN
CASE XE$SIZE
WHEN 1 THEN 'TINYINT'
WHEN 2 THEN 'SMALLINT'
WHEN 4 THEN 'INTEGER'
WHEN 8 THEN 'BIGINT'
ELSE 'UNKNOWN INTEGER'
END
WHEN 2 THEN
CASE XE$SIZE
WHEN 4 THEN 'REAL'
WHEN 8 THEN 'DOUBLE'
ELSE 'UNKNOWN REAL'
END
WHEN 3 THEN 'DATE'
WHEN 4 THEN 'TIME'
WHEN 5 THEN 'DECIMAL'
WHEN 8 THEN 'NUMERIC'
WHEN 9 THEN
CASE XE$SIZE
WHEN 4 THEN 'BFLOAT4'
WHEN 8 THEN 'BFLOAT8'
ELSE 'UNKNOWN BFLOAT'
END
WHEN 11 THEN 'VARCHAR'
WHEN 14 THEN
CASE XE$SIZE
WHEN 1 THEN 'UNSIGNED1 BINARY'
WHEN 2 THEN 'UNSIGNED2 BINARY'
WHEN 4 THEN 'UNSIGNED4 BINARY'
WHEN 8 THEN 'UNSIGNED8 BINARY'
ELSE 'UNKNOWN UNSIGNED BINARY'
END
WHEN 15 THEN
CASE XE$SIZE
WHEN 2 THEN 'AUTOINC2'
WHEN 4 THEN 'AUTOINC4'
ELSE 'UNKNOWN AUTOINC'
END
WHEN 16 THEN 'BIT'
WHEN 17 THEN 'NUMERICSTS'
WHEN 18 THEN 'NUMERICSA'
WHEN 19 THEN 'CURRENCY'
WHEN 20 THEN 'TIMESTAMP'
WHEN 21 THEN 'CLOB/BLOB'
WHEN 25 THEN 'WSTRING'
WHEN 26 THEN 'WZTRING'
WHEN 27 THEN 'GUID'
WHEN 30 THEN 'DATETIME'
ELSE CONCAT('DATATYPE UNKNOWN = ',
CAST(XE$DATATYPE AS CHAR(3)))
END,
XE$SIZE, XE$SIZE, XE$DEC,
CASE
WHEN XE$FLAGS & 4 = 4 THEN 'YES'
ELSE 'NO'
END
FROM X$FIELD AS COL, X$FILE AS TAB
WHERE COL.XE$FILE = TAB.XF$ID
AND TAB.XF$FLAGS & 16 <> 16
AND COL.XE$DATATYPE NOT IN (227,255)
If we query this view, we will see that PLAYERNO is still in that second position. The reason is that this COLUMNS view displays the physical position of each column and not the logical one. If we want to change the physical position of a column we have to add the term PSQL_PHYSICAL to the ALTER TABLE statement; see the next example.
Example 2: Move the column PLAYERNO in the TEAMS table from the second to the last physical position.
ALTER TABLE TEAMS
( PSQL_MOVE COLUMN PLAYERNO TO PSQL_PHYSICAL 3 )
SELECT *
FROM TEAMS
The result is:
TEAMNO DIVISION PLAYERNO
------ -------- --------
1 first 6
2 second 27
Explanation: This result obviously demonstrates that if we change the physical position, we also change the logical position. If you don’t want the logical position to change, you will need a second ALTER TABLE statement to move it back logically. Note that adding the term PSQL_POSITION has no effect on the statement’s result.
Example 3: Show the physical and logical positions of all the columns in the MATCHES table after the move described in Example 2.
SELECT UPPER(TAB.XF$NAME) AS TABLE_NAME, UPPER(COL.XE$NAME) AS COLUMN_NAME,
(SELECT COUNT(*)
FROM X$FIELD AS F
WHERE F.XE$FILE = TAB.XF$ID
AND F.XE$OFFSET <= COL.XE$OFFSET
AND F.XE$DATATYPE NOT IN (227,255)) AS PHYSICAL_ORDER,
IFNULL ((SELECT A.XA$ATTRS
FROM X$ATTRIB AS A
WHERE A.XA$TYPE='L'
AND A.XA$ID = COL.XE$ID),
(SELECT COUNT(*)
FROM X$FIELD AS F
WHERE F.XE$FILE = TAB.XF$ID
AND F.XE$OFFSET <= COL.XE$OFFSET
AND F.XE$DATATYPE NOT IN (227,255))) AS LOGICAL_ORDER
FROM X$FIELD AS COL, X$FILE AS TAB
WHERE COL.XE$FILE = TAB.XF$ID
AND TAB.XF$FLAGS & 16 <> 16
AND COL.XE$DATATYPE NOT IN (227,255)
AND UPPER(TAB.XF$NAME) = 'MATCHES'
The result is:
TABLE_NAME COLUMN_NAME PHYSICAL_ORDER LOGICAL_ORDER
---------- ----------- -------------- -------------
MATCHES MATCHNO 1 1
MATCHES TEAMNO 2 2
MATCHES PLAYERNO 3 5
MATCHES WON 4 3
MATCHES LOST 5 4
Explanation: The last three rows in the result show that the logical positions of the last three columns are different than their physical positions.
To summarize, columns can be moved in a table. If we do that we have to be aware that there is a difference between logical and physical positions.