PostgreSQL/Insert Delete Update/Insert

Материал из SQL эксперт
Версия от 10:14, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

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=#
Источник — «http://sqle.ru/index.php?title=PostgreSQL/Insert_Delete_Update/Insert&oldid=4729»