>> >> That said, you could also use the value in pg_user.usesysid as a >> unique ID, rather than the account name (if you want to distinguish >> between different users with the same login name, over a period of >> time where users were removed and re-added). > > Perhaps I should just not use a foreign key, and accept that users may > be removed, and do a LEFT JOIN to pg_user and SELECT > COALESCE(pg_use.username, 'user removed'). Since I imagine that > removing users will rarely or never occur in practice, this may be the > thing to do. Then again, perhaps I'm "prematurely pessimising" by not > just doing the simple and obvious thing and using a text column as > described in my original mail. I'd like to do whatever is considered > canonical, but I'm not sure what that is in this case. > A text column should be perfectly fine, and is the simplest implementation. But it does have a few possible issues that you need to keep in mind (besides things like users being removed). For instance, if PostgreSQL users are renamed (eg: ALTER USER name RENAME TO newname), then your text column becomes incorrect. If you want to record additional user info (besides what PostgreSQL already tracks), I'd suggest creating your own users table, and inside there add a (non foreign key reference) entry for pg_user.usesysid, as well as any other application-specific details. Then, you link other tables to your table (with foreign key reference), instead of using text columns. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
