What is a "pseudocolumn"? Accidently coded "SELECT $FOO.." and got the error "Invalid pseudocolumn "$FOO".

Question

I can't find any documentation for them. Is it something I should know?

Edit: this is a MS SQL Server specific question.

Answer 1

A simple Google search brings up this from Oracle's reference:

A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values.

I think that the error you got is simply because there is no column $FOO, so the query parser tests to see if there's a psuedocolumn named $FOO as a fallback. And since there is no pseudocolumn named "$FOO" (and there are no other fallback) you get the error "Invalid pseudocolumn $FOO". This is a guess, though. I'm no expert when it comes to databases.

Answer 2

Pseudocolumns are virtual columns that are available in special cases. In an Oracle database, there's a ROWNUM pseudocolumn that will give you the row number. SQL server, as far as I know, doesn't actually support pseudocolumns but there are errors and stored procedures that refer to pseudo columns, probably for Oracle migration.

Answer 3

One example of a pseudo-column is ROWID in Informix. It is a 32-bit number that can be used to find the data page more quickly than any other way (subject to caveats, such as the table is not fragmented) because it is basically the page address for the data. You can do SELECT * FROM SomeTable and it won't show up; you can do SELECT ROWID, * FROM SomeTable and it will show up in your data. Because it is not actually stored on disk (you won't see the ROWID on the disk with the data, though the ROWID tells you where to look on the disk for the data), it is a pseudo-column. There can be other pseudo-columns associated with tables - they tend to be similarly somewhat esoteric.

They can also be called hidden columns, particularly if they are (contrary to pseudo-columns) actually stored in the database, but are not selected by *; you have to specifically request the column to see it.

Answer 4

I believe that pseudocolumns are used for, say, if you have a table with columns of width and height, you can have a pseudocolumn called area that is defined as width*height. Area isn't updatable or assignable, it is merely calculated for you when you want it.