By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,542 Members | 1,434 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,542 IT Pros & Developers. It's quick & easy.

Foreign keys and pg_user table

P: n/a
C G
Dear All,

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."

Is there another way of doing what I want?

Many thanks

Colin

__________________________________________________ _______________
Stay in touch with absent friends - get MSN Messenger
http://www.msn.co.uk/messenger
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #2

P: n/a

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:

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

Is there another way of doing what I want?



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.