Inserting data to PostgreSQL

When a table is created, there is no data in it. The first thing to do before a database can be useful is to insert data into it. The data is conceptually inserted one line at a time. We can of course insert multiple rows at a time, but there is no way to insert less than one row of data. Even if you only know the values of several fields, you must also create a complete line.

Use the Insert command to create a new row. This command requires a table name and a field value.

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

Here is an example of inserting a row into a table:

INSERT into Products VALUES (1, ' Cheese ', 9.99);

The data values are listed in the order in which the fields appear in the table, separated by commas. Typically, data values are literals (constants), but scalar expressions are also allowed.

The disadvantage of the syntax above is that you must know the order of the fields in the table. You can also explicitly list the fields to avoid this problem. For example, the following two commands have the same effect as the one above:

Insert INTO Products (product_no, name, Price) VALUES (1, ' Cheese ', 9.99); Insert to products (name, price, product_no) V Alues (' Cheese ', 9.99, 1);

Many users think it is a good practice to explicitly list field names.

If you don't know the values of all the fields, you can omit some of them. At this point, these unknown fields will be populated with their default values. Like what:

Insert INTO Products (product_no, name) VALUES (1, ' Cheese '); Insert to products values (1, ' Cheese ');

The second form is an extension of PostgreSQL. It fills the field from left to right with the given value as much as possible, leaving the remaining padding to the default value.

To maintain clarity, you can also explicitly use default values for separate fields or entire rows:

Insert INTO Products (product_no, name, price) VALUES (1, ' Cheese ', default); Insert to products default values;

You can insert multiple lines in one command:

INSERT into Products (product_no, name, price) VALUES    (1, ' Cheese ', 9.99), (    2, ' Bread ', 1.99),    (3, ' Milk ', 2.9 9);

tip: To insert large amounts of data at once, you can look at the copy command. It is not as flexible as the insert command, but more efficient.

Inserting data to PostgreSQL