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
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=#
Insert date data to table
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=#
Insert only an ID: only one column
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=#
Inserts a single row into the employees table
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=#
List the columns explicitly
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=#
Omit values
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=#
Request default values explicitly, for individual columns
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=#
Request default values explicitly for the entire row
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=#
The data values are listed in the order in which the columns appear in the table, separated by commas
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=#