tableplus check unique constraint postgresql

WHERE c.altitude > 500 and c.tableoid = p.oid (If you try to reproduce this example, you will probably get different numeric OIDs.) By doing a join with pg_class you can see the actual table names:

There is a system column called tableoid in each table which can tell you the originating table: In some cases you may wish to know which table a particular row originated from. Many of the commands that we have already discussed - SELECT, UPDATE and DELETE - support the ONLY keyword. Here the ONLY keyword indicates that the query should apply only to cities, and not any tables below cities in the inheritance hierarchy. On the other hand, the following query finds all the cities that are not state capitals and are situated at an altitude over 500ft: Given the sample data from the PostgreSQL tutorial (see Seção 2.1), this returns: For example, the following query finds the names of all cities, including state capitals, that are located at an altitude over 500ft: In PostgreSQL, a table can inherit from zero or more other tables, and a query can reference either all rows of a table or all rows of a table plus all of its descendant tables. State capitals also have an extra column, state, that shows their state.

In this case, the capitals table inherits all the columns of its parent table, cities. We define the capitals table so that it inherits from cities: However, what happens when we want to ask for data about a city, regardless of whether it is a capital or not? The inheritance feature can help to resolve this problem. This can be done by creating two tables, one for state capitals and one for cities that are not capitals. We want to be able to quickly retrieve the capital city for any particular state.

Each state has many cities, but only one capital. Let's start with an example: suppose we are trying to build a data model for cities. (SQL:1999 and later define a type inheritance feature, which differs in many respects from the features described here.) PostgreSQL implements table inheritance, which can be a useful tool for database designers.

Tableplus check unique constraint postgresql