I would like to be able to define the type of column A in table T to
be the same as the type of column B in table U, *whatever this type
may be*. In particular, if later I change the type of column U.B, I
don't want to have to explicitly change the type of column T.A.
I have asked many people about how to do this, and the consensus seems
to be that it can't be done, at least in PostgreSQL.
(PL/pgSQL has tablename.columnname%TYPE pseudo-types, but these are
not available in PostgreSQL SQL.)
I find the absence of this feature hard to understand.
After all, in a RDBMS, references between tables (which require that
the referring and referred-to columns have matching types) is not only
extremely common but downright *essential* to the whole relational
approach to organizing data.
(Another potential solution would be to use CREATE TYPE to create a
"composite type" having only one element. Then any changes could be
localized to changing the definition of the new type. But using a
composite type for what really should be a scalar type results in more
inconveniences than the whole scheme purports to solve.)
I'm sure I'm missing something huge, because this is just too perverse
otherwise.
What is it that I'm missing?
Is there a best practice to handle this extremely common problem?
Thanks!
kj