PostgreSQL/Insert Delete Update/Insert — различия между версиями

Текущая версия на 10:14, 26 мая 2010

Содержание

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=#

PostgreSQL/Insert Delete Update/Insert — различия между версиями

Текущая версия на 10:14, 26 мая 2010

Содержание

Fill the columns from the left with as many values as are given, and the rest will be defaulted

Insert date data to table

Insert only an ID: only one column

Inserts a single row into the employees table

List the columns explicitly

Omit values

Request default values explicitly, for individual columns

Request default values explicitly for the entire row

The data values are listed in the order in which the columns appear in the table, separated by commas

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты