Thread: On "linking" the types of two columns

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

One approach I am familiar with that avoids the need to change data types is using generic keys.
i.e. a integer sequence used as the default valve of a primary key.  All other data in the record
can have its data type changed without disturbing the relationship between tables.

Is this near to what you were looking for?

regards,

Richard Broersma Jr.

--- kynn@panix.com wrote:

>
>
>
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>

> 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

can i ask a question?  why are your column types
dynamic in nature?

my integers typcially stay as integers.  my text
typically stays as text.

you can also put in a feature request so the
developers can review it.

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

hi

kynn@panix.com schrieb:

> (PL/pgSQL has tablename.columnname%TYPE pseudo-types, but these are
> not available in PostgreSQL SQL.)
you could write a function in plpgsql.  the function is available from sql.

chris