Basic concepts of the PostgreSQL table

A table in a relational database is very similar to a table on paper: It consists of rows and columns. The number and order of fields are fixed, and each field has a name. The number of rows is variable (it reflects the amount of data stored at a given moment). SQL does not have any commitment to the order of rows in a table. When a table is read, the rows appear in an unspecified order unless you explicitly require sorting. The content is described in http://www.infocool.net/PostgreSQL/index.htm. In addition, SQL does not give the row a unique identity, so we are likely to have several identical rows in a table. This is an inevitable result of the underlying mathematical model of SQL, but this is usually not what we would like to see. The section later in this chapter will discuss how to deal with this problem.

Each field has a data type. The data type controls the set of all possible values for a field and controls the semantics of the data in the field so that it can be used for calculations. For example, a field declared as a numeric type does not accept arbitrary text strings, and the data stored in this field can be used for mathematical calculations. In contrast, a field that is declared as a string type accepts almost any type of data, but they cannot be mathematically computed (although operations such as String joins can be performed).

PostgreSQL contains a set of built-in data types that can be clipped, and these types are suitable for many applications. Users can also define their own data types. Common data types are: integer for integers, numericfor possible fractions, textfor strings, date for dates, time for times , the timestampused for timestamps.

To create a table, you can use the CREATE TABLE command. In this command, you must declare at least one name for the new table, as well as the names of the fields and their data types. Like what:

CREATE TABLE my_first_table (    first_column text,    second_column integer);

This creates a table named my_first_table with two fields. The first field is named first_column, the data type is text, the second field is Second_column, and the data type is integer. The type name is usually also an identifier (with some exceptions). Note that the field list is comma delimited and enclosed in parentheses.

Of course, the front is just a very fictional example. In general, you give tables and fields a meaningful name to express what kind of data they store, so let's give a more realistic example:

CREATE TABLE Products (    product_no integer,    name text, price    numeric);

The numeric type can store fractional portions, and the amount is likely to have such fractional parts.

Tip: If you create many tables that are related to each other, it's a good idea to choose a consistent naming pattern for your tables and fields. For example, the name of a table can be uniformly selected singular or plural, both of which are supported by theorists of this kind.

There is a limit to the number of fields a table can contain. Depending on the type of field, this number may be between 250 and 1600. However, no matter which side of the number, if you design a table containing so many fields seems to be very unlikely to happen, otherwise it is a design problematic performance.

If you no longer need a table, you can delete it with the drop TABLE command. Like this:

DROP table My_first_table;drop table products;

Attempting to delete a nonexistent table is an error. However, in the SQL script file, we usually delete it unconditionally before creating the table and ignore the error message, so the script succeeds regardless of the table memory being deleted. Of course you can also use the DROP TABLE IF EXISTS to avoid error messages, but this does not conform to the SQL standard.

Using the tools discussed so far we can create a fully functional table. The remainder of this chapter is about adding attributes to the table definition, guaranteeing data integrity, security, or convenience.

Basic concepts of the PostgreSQL table