A table constraint can refer to any column in the table.įor example, suppose that you had an orders table to track customer orders: CREATE TABLE orders
The difference between a column constraint and a table constraint is that a column constraint should refer only to the column to which it relates. When you define a CHECK() constraint for a table, you are telling PostgreSQL that any insertions or updates made to the table must satisfy the boolean-expression given within the constraint. The syntax for a CHECK() constraint is CHECK( boolean-expression ) You can also apply constraints to a table as a whole or to groups of columns within a table.įirst, let’s look at the CHECK() constraint. In Chapter 2, "Working with Data in PostgreSQL," we explored the various constraints that you can apply to a column: NOT NULL, UNIQUE, PRIMARY KEY, REFERENCES, and CHECK(). See the section titled "Using Views" in Chapter 1 for more information about views. If you find that you are creating a given temporary table over and over again, you might want to convert that table into a view. Quite often, you will find that a complex query can be formulated more easily by first extracting the data that interests you into a temporary table. You can use a temporary table to accumulate intermediate results. Starting with release 7.3, you can access the permanent table by including the name of the schema where the permanent table resides.Ī temporary table is like a scratch pad.
Movies=# CREATE TEMPORARY TABLE customers Prior to version 7.3, there was no way to get back to the permanent table except by dropping the temporary table: movies=# SELECT * FROM customers Ĭustomer_id | customer_name | phone | birth_date | balance If you now SELECT from or INSERT into the customers table, you will be working with the temporary table. Now you have two tables, each named customers. Notice that the only difference between this command and the command that you used to create the permanent customers table is the TEMPORARY keyword 3. For example, let’s create a temporary table that hides the permanent customers table: CREATE TEMPORARY TABLE customers ( If you create a temporary table with the same name as a permanent table, you are effectively hiding the permanent table. Because temporary tables are local to each session, you don’t have to worry about colliding with the name of a table created by another session. Temporary tables are also local to your session, meaning that other PostgreSQL sessions can’t see temporary tables that you create. A permanent table persists after you terminate your PostgreSQL session a temporary table is automatically destroyed when your PostgreSQL session ends. I mentioned earlier that the customers table is a permanent table. If you don’t specify a tablespace, PostgreSQL creates the table in the tablespace assigned to the schema (if you’re creating an index without specifying a tablespace, the index is created in the tablespace of the parent table). When you create a table (or an index), you can tell PostgreSQL to store the object in a specific tablespace by including a TABLESPACE tablespacename clause, like this: CREATE TABLE joes_video.customers(. You can’t give ownership to another user at the time you create the table, but you can change it later using the ALTER TABLE.OWNER TO command (described later).
If you want the table to be created in some other schema, you can prefix the table name with the schema qualifier, for example: CREATE TABLE joes_video.customers(. (If you are using a version older than 7.3, your copy of PostgreSQL does not support schemas). If you are using PostgreSQL 7.3 or later, the customers table is created in the first schema in your search path. When you execute this command, the customers table is created in the database that you are connected to. This means that you can’t create a table whose name is the same as an existing data type. When you create a table, PostgreSQL automatically creates a new data type 2 with the same name as the table. A table name must meet the naming criteria described earlier in this chapter. This command creates a permanent table named customers. Here is the command that you used to create the customers table: CREATE TABLE customers ( You’ve created some simple tables in the first two chapters it’s time to talk about some of the more advanced features of the CREATE TABLE command. Now let’s move down one level in the PostgreSQL storage hierarchy and talk about creating and dropping tables.
Tableplus create table how to#
The previous section described how to create and drop databases.
0 kommentar(er)
