Tip: View the SQL query behind psql commands
If you want to view the SQL query used to construct the information returned from a psql command (which will help you learn the underlying information schema) then …
Unique constraint across two rows in PostgreSQL
You can use a unique index to ensure only one occurance of a value but what do you do if you want to limit uniqueness to two rows instead of one? PostgreSQL's partial indexing can help.