Foreign keys and pg_user table

On Thu, 11 Dec 2003, C G wrote:

I'm trying to create a table where the username and email can only be
inserted into the table if the username is already in pg_user. The method
I'm trying is:

CREATE user(
usename name,
email text,
FOREIGN KEY usename REFERENCES (pg_user)
);

but I get told that "...pg_user is not a table."

Which is true, it's a view. However, even were it a table,
it's a system table and references to them are not allowed (in recent
versions it'll fail to make the constraint, in older versions it would
make the constraint but it wouldn't work).

Is there another way of doing what I want?

You could potentially do the insert/update on user time check in a custom
trigger. That wouldn't prevent you from dropping a user later that was
referenced, however.

Well, it's not a table! It's a view:

dbs=# \d pg_user
View "pg_catalog.pg_user"
Column | Type | Modifiers
-------------+---------+-----------
usename | name |
usesysid | integer |
usecreatedb | boolean |
usesuper | boolean |
usecatupd | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
View definition:
SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb,
pg_shadow.usesuper, pg_shadow.usecatupd, '********'::text AS passwd,
pg_shadow.valuntil, pg_shadow.useconfig
FROM pg_shadow;

So, you really want to use the pg_shadow table.

C G wrote: