PostgreSQL/Insert Delete Update/Insert
Содержание
- 1 Fill the columns from the left with as many values as are given, and the rest will be defaulted
- 2 Insert date data to table
- 3 Insert only an ID: only one column
- 4 Inserts a single row into the employees table
- 5 List the columns explicitly
- 6 Omit values
- 7 Request default values explicitly, for individual columns
- 8 Request default values explicitly for the entire row
- 9 The data values are listed in the order in which the columns appear in the table, separated by commas
Fill the columns from the left with as many values as are given, and the rest will be defaulted
<source lang="sql">
postgres=# postgres=# postgres=# CREATE TABLE products ( postgres(# product_no integer, postgres(# name text, postgres(# price numeric postgres(# ); CREATE TABLE postgres=# -- Fill the columns from the left with as many values as are given, and the rest will be defaulted. postgres=# INSERT INTO products VALUES (1, "Cheese"); INSERT 0 1 postgres=# postgres=# postgres=# select * from products;
product_no | name | price
+--------+-------
1 | Cheese |
(1 row) postgres=# postgres=# drop table products; DROP TABLE postgres=# postgres=# postgres=#
</source>
Insert date data to table
<source lang="sql">
postgres=# postgres=# CREATE TABLE weather ( postgres(# city varchar(80), postgres(# temp_lo int, -- low temperature postgres(# temp_hi int, -- high temperature postgres(# prcp real, -- precipitation postgres(# date date postgres(# ); CREATE TABLE postgres=# postgres=# postgres=# INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) postgres-# VALUES ("San Francisco", 43, 57, 0.0, "1994-11-29"); INSERT 0 1 postgres=# postgres=# INSERT INTO weather (date, city, temp_hi, temp_lo) postgres-# VALUES ("1994-11-29", "Hayward", 54, 37); INSERT 0 1 postgres=# postgres=# postgres=# drop table weather; DROP TABLE postgres=#
</source>
Insert only an ID: only one column
<source lang="sql">
postgres=# postgres=# CREATE TABLE employee ( postgres(# ID int, postgres(# name varchar(10), postgres(# salary real, postgres(# start_date date, postgres(# city varchar(10), postgres(# region char(1) postgres(# ); CREATE TABLE postgres=# postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (1, "Jason", 40420, "02/01/94", "New York", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (2, "Robert",14420, "01/02/95", "Vancouver","N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (3, "Celia", 24020, "12/03/96", "Toronto", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (4, "Linda", 40620, "11/04/97", "New York", "N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (5, "David", 80026, "10/05/98", "Vancouver","W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (6, "James", 70060, "09/06/99", "Toronto", "N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (7, "Alison",90620, "08/07/00", "New York", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (8, "Chris", 26020, "07/08/01", "Vancouver","N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (9, "Mary", 60020, "06/09/02", "Toronto", "W"); INSERT 0 1 postgres=# postgres=# select * from employee;
id | name | salary | start_date | city | region
+--------+--------+------------+-----------+--------
1 | Jason | 40420 | 1994-02-01 | New York | W 2 | Robert | 14420 | 1995-01-02 | Vancouver | N 3 | Celia | 24020 | 1996-12-03 | Toronto | W 4 | Linda | 40620 | 1997-11-04 | New York | N 5 | David | 80026 | 1998-10-05 | Vancouver | W 6 | James | 70060 | 1999-09-06 | Toronto | N 7 | Alison | 90620 | 2000-08-07 | New York | W 8 | Chris | 26020 | 2001-07-08 | Vancouver | N 9 | Mary | 60020 | 2002-06-09 | Toronto | W
(9 rows) postgres=# postgres=# -- Insert only an ID postgres=# postgres=# INSERT INTO employee (id) VALUES (108); INSERT 0 1 postgres=# postgres=# select * from employee;
id | name | salary | start_date | city | region
+--------+--------+------------+-----------+--------
1 | Jason | 40420 | 1994-02-01 | New York | W 2 | Robert | 14420 | 1995-01-02 | Vancouver | N 3 | Celia | 24020 | 1996-12-03 | Toronto | W 4 | Linda | 40620 | 1997-11-04 | New York | N 5 | David | 80026 | 1998-10-05 | Vancouver | W 6 | James | 70060 | 1999-09-06 | Toronto | N 7 | Alison | 90620 | 2000-08-07 | New York | W 8 | Chris | 26020 | 2001-07-08 | Vancouver | N 9 | Mary | 60020 | 2002-06-09 | Toronto | W 108 | | | | |
(10 rows) postgres=# postgres=# drop table employee; DROP TABLE postgres=# postgres=#
</source>
Inserts a single row into the employees table
<source lang="sql">
postgres=# postgres=# CREATE TABLE employee ( postgres(# ID int, postgres(# name varchar(10), postgres(# salary real, postgres(# start_date date, postgres(# city varchar(10), postgres(# region char(1) postgres(# ); CREATE TABLE postgres=# postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (1, "Jason", 40420, "02/01/94", "New York", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (2, "Robert",14420, "01/02/95", "Vancouver","N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (3, "Celia", 24020, "12/03/96", "Toronto", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (4, "Linda", 40620, "11/04/97", "New York", "N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (5, "David", 80026, "10/05/98", "Vancouver","W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (6, "James", 70060, "09/06/99", "Toronto", "N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (7, "Alison",90620, "08/07/00", "New York", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (8, "Chris", 26020, "07/08/01", "Vancouver","N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (9, "Mary", 60020, "06/09/02", "Toronto", "W"); INSERT 0 1 postgres=# postgres=# select * from employee;
id | name | salary | start_date | city | region
+--------+--------+------------+-----------+--------
1 | Jason | 40420 | 1994-02-01 | New York | W 2 | Robert | 14420 | 1995-01-02 | Vancouver | N 3 | Celia | 24020 | 1996-12-03 | Toronto | W 4 | Linda | 40620 | 1997-11-04 | New York | N 5 | David | 80026 | 1998-10-05 | Vancouver | W 6 | James | 70060 | 1999-09-06 | Toronto | N 7 | Alison | 90620 | 2000-08-07 | New York | W 8 | Chris | 26020 | 2001-07-08 | Vancouver | N 9 | Mary | 60020 | 2002-06-09 | Toronto | W
(9 rows) postgres=# postgres=# -- Inserts a single row into the employees table: postgres=# postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (99, "M", 20, "06/09/02", "Toronto", "W"); INSERT 0 1 postgres=# postgres=# select * from employee;
id | name | salary | start_date | city | region
+--------+--------+------------+-----------+--------
1 | Jason | 40420 | 1994-02-01 | New York | W 2 | Robert | 14420 | 1995-01-02 | Vancouver | N 3 | Celia | 24020 | 1996-12-03 | Toronto | W 4 | Linda | 40620 | 1997-11-04 | New York | N 5 | David | 80026 | 1998-10-05 | Vancouver | W 6 | James | 70060 | 1999-09-06 | Toronto | N 7 | Alison | 90620 | 2000-08-07 | New York | W 8 | Chris | 26020 | 2001-07-08 | Vancouver | N 9 | Mary | 60020 | 2002-06-09 | Toronto | W 99 | M | 20 | 2002-06-09 | Toronto | W
(10 rows) postgres=# postgres=# drop table employee; DROP TABLE postgres=# postgres=#
</source>
List the columns explicitly
<source lang="sql">
postgres=# CREATE TABLE products ( postgres(# product_no integer, postgres(# name text, postgres(# price numeric postgres(# ); CREATE TABLE postgres=# -- List the columns explicitly postgres=# postgres=# INSERT INTO products (product_no, name, price) VALUES (1, "Cheese", 9.99); INSERT 0 1 postgres=# INSERT INTO products (name, price, product_no) VALUES ("Cheese", 9.99, 1); INSERT 0 1 postgres=# postgres=# postgres=# select * from products;
product_no | name | price
+--------+-------
1 | Cheese | 9.99
1 | Cheese | 9.99
(2 rows) postgres=# postgres=# drop table products; DROP TABLE postgres=# postgres=# postgres=#
</source>
Omit values
<source lang="sql">
postgres=# postgres=# postgres=# CREATE TABLE products ( postgres(# product_no integer, postgres(# name text, postgres(# price numeric postgres(# ); CREATE TABLE postgres=# -- Omit values postgres=# postgres=# INSERT INTO products (product_no, name) VALUES (1, "Cheese"); INSERT 0 1 postgres=# postgres=# postgres=# select * from products;
product_no | name | price
+--------+-------
1 | Cheese |
(1 row) postgres=# postgres=# drop table products; DROP TABLE postgres=# postgres=# postgres=# postgres=#
</source>
Request default values explicitly, for individual columns
<source lang="sql">
postgres=# postgres=# postgres=# CREATE TABLE products ( postgres(# product_no integer, postgres(# name text, postgres(# price numeric postgres(# ); CREATE TABLE postgres=# -- Request default values explicitly, for individual columns postgres=# INSERT INTO products (product_no, name, price) VALUES (1, "Cheese", DEFAULT); INSERT 0 1 postgres=# postgres=# postgres=# select * from products;
product_no | name | price
+--------+-------
1 | Cheese |
(1 row) postgres=# postgres=# drop table products; DROP TABLE postgres=# postgres=# postgres=#
</source>
Request default values explicitly for the entire row
<source lang="sql">
postgres=# CREATE TABLE products ( postgres(# product_no integer, postgres(# name text, postgres(# price numeric postgres(# ); CREATE TABLE postgres=# -- Request default values explicitly for the entire row: postgres=# INSERT INTO products DEFAULT VALUES; INSERT 0 1 postgres=# postgres=# postgres=# select * from products;
product_no | name | price
+------+-------
| |
(1 row) postgres=# postgres=# drop table products; DROP TABLE postgres=# postgres=#
</source>
The data values are listed in the order in which the columns appear in the table, separated by commas
<source lang="sql">
postgres=# postgres=# -- Inserting Data postgres=# postgres=# -- The data values are listed in the order in which the columns appear in the table, separated by commas. postgres=# postgres=# CREATE TABLE products ( postgres(# product_no integer, postgres(# name text, postgres(# price numeric postgres(# ); CREATE TABLE postgres=# INSERT INTO products VALUES (1, "Cheese", 9.99); INSERT 0 1 postgres=# postgres=# select * from products;
product_no | name | price
+--------+-------
1 | Cheese | 9.99
(1 row) postgres=# postgres=# drop table products; DROP TABLE postgres=#
</source>
