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

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

Содержание

1 A simple UPDATE: change one cell
2 Update a single row
3 Update in a slice
4 Update two columns in one single statement
5 Updating entire columns
6 Updating Several Columns
7 Using expression in update statement
8 Using UPDATE with several Employees
9 Using user defined function to update table data

A simple UPDATE: change one cell

postgres=#
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=# -- A simple UPDATE
postgres=#
postgres=# SELECT name FROM employee WHERE id = 1;
 name
-------
 Jason
(1 row)
postgres=#
postgres=# UPDATE employee SET name = "newName" WHERE id = 1;
UPDATE 1
postgres=#
postgres=# SELECT name FROM employee WHERE id = 1;
  name
---------
 newName
(1 row)
postgres=#
postgres=#
postgres=#
postgres=# drop table employee;
DROP TABLE
postgres=#

Update a single row

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=#
postgres=# UPDATE employee SET id = 116 WHERE id = 1;
UPDATE 1
postgres=# SELECT * FROM employee WHERE id = 1;
 id | name | salary | start_date | city | region
----+------+--------+------------+------+--------
(0 rows)
postgres=#
postgres=#
postgres=#
postgres=#
postgres=#
postgres=# drop table employee;
DROP TABLE
postgres=#

Update in a slice

postgres=#
postgres=#
postgres=# CREATE TABLE sal_emp (
postgres(#    name            text,
postgres(#    pay_by_quarter  integer[],
postgres(#    schedule        text[][]
postgres(# );
CREATE TABLE
postgres=#
postgres=# INSERT INTO sal_emp
postgres-#    VALUES ("Bill",
postgres(#    ARRAY[10000, 10000, 10000, 10000],
postgres(#    ARRAY[["meeting", "lunch"], ["training", "presentation"]]);
INSERT 0 1
postgres=#
postgres=# INSERT INTO sal_emp
postgres-#    VALUES ("Carol",
postgres(#    ARRAY[20000, 25000, 25000, 25000],
postgres(#    ARRAY[["breakfast", "consulting"], ["meeting", "lunch"]]);
INSERT 0 1
postgres=#
postgres=# SELECT * FROM sal_emp;
 name  |      pay_by_quarter       |                 schedule
-------+---------------------------+-------------------------------------------
 Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)
postgres=#
postgres=# -- Update in a slice:
postgres=#
postgres=# UPDATE sal_emp SET pay_by_quarter[1:2] = "{27000,27000}" WHERE name = "Carol";
UPDATE 1
postgres=#
postgres=# select * from sal_emp;
 name  |      pay_by_quarter       |                 schedule
-------+---------------------------+-------------------------------------------
 Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
 Carol | {27000,27000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)
postgres=#
postgres=# drop table sal_emp;
DROP TABLE
postgres=#
postgres=#

Update two columns in one single statement

postgres=#
postgres=# CREATE TABLE products (
postgres(#    product_no integer,
postgres(#    name text,
postgres(#    price numeric
postgres(# );
CREATE TABLE
postgres=#
postgres=# INSERT INTO products (product_no, name, price) VALUES (1, "Java", 1234);
INSERT 0 1
postgres=# INSERT INTO products (product_no, name, price) VALUES (2, "SQL Server", 3421);
INSERT 0 1
postgres=# INSERT INTO products (product_no, name, price) VALUES (3, "Oracle", 7623);
INSERT 0 1
postgres=# INSERT INTO products (product_no, name, price) VALUES (4, "DB2", 9874);
INSERT 0 1
postgres=# INSERT INTO products (product_no, name, price) VALUES (5, "Access", 5);
INSERT 0 1
postgres=#
postgres=#
postgres=# select * from products;
 product_no |    name    | price
------------+------------+-------
          1 | Java       |  1234
          2 | SQL Server |  3421
          3 | Oracle     |  7623
          4 | DB2        |  9874
          5 | Access     |     5
(5 rows)
postgres=#
postgres=# UPDATE products SET product_no = 999, name = "new Name", price = 1 WHERE price > 0;
UPDATE 5
postgres=#
postgres=# select * from products;
 product_no |   name   | price
------------+----------+-------
        999 | new Name |     1
        999 | new Name |     1
        999 | new Name |     1
        999 | new Name |     1
        999 | new Name |     1
(5 rows)
postgres=#
postgres=# drop table products;
DROP TABLE
postgres=#
postgres=#

Updating entire columns

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=# -- Updating entire columns
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=# UPDATE employee SET salary = (salary * 2);
UPDATE 9
postgres=#
postgres=# SELECT * FROM employee;
 id |  name  | salary | start_date |   city    | region
----+--------+--------+------------+-----------+--------
  1 | Jason  |  80840 | 1994-02-01 | New York  | W
  2 | Robert |  28840 | 1995-01-02 | Vancouver | N
  3 | Celia  |  48040 | 1996-12-03 | Toronto   | W
  4 | Linda  |  81240 | 1997-11-04 | New York  | N
  5 | David  | 160052 | 1998-10-05 | Vancouver | W
  6 | James  | 140120 | 1999-09-06 | Toronto   | N
  7 | Alison | 181240 | 2000-08-07 | New York  | W
  8 | Chris  |  52040 | 2001-07-08 | Vancouver | N
  9 | Mary   | 120040 | 2002-06-09 | Toronto   | W
(9 rows)
postgres=#
postgres=#
postgres=#
postgres=# drop table employee;
DROP TABLE
postgres=#

Updating Several Columns

postgres=#
postgres=#
postgres=# CREATE TABLE employee (
postgres(#     ID         int,
postgres(#     name       varchar(40),
postgres(#     salary     real,
postgres(#     start_date date,
postgres(#     city       varchar(100),
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=# -- Updating Several Columns
postgres=#
postgres=# UPDATE employee
postgres-#         SET name = "O\"Reilly",
postgres-#             city = "newCity" || " and new town"
postgres-#         WHERE id = 1;
UPDATE 1
postgres=#
postgres=# SELECT * FROM employee WHERE id = 1;
 id |   name   | salary | start_date |         city         | region
----+----------+--------+------------+----------------------+--------
  1 | O"Reilly |  40420 | 1994-02-01 | newCity and new town | W
(1 row)
postgres=#
postgres=#
postgres=#
postgres=# drop table employee;
DROP TABLE
postgres=#

Using expression in update statement

postgres=#
postgres=# CREATE TABLE products (
postgres(#    product_no integer,
postgres(#    name text,
postgres(#    price numeric
postgres(# );
CREATE TABLE
postgres=#
postgres=# INSERT INTO products (product_no, name, price) VALUES (1, "Java", 1234);
INSERT 0 1
postgres=# INSERT INTO products (product_no, name, price) VALUES (2, "SQL Server", 3421);
INSERT 0 1
postgres=# INSERT INTO products (product_no, name, price) VALUES (3, "Oracle", 7623);
INSERT 0 1
postgres=# INSERT INTO products (product_no, name, price) VALUES (4, "DB2", 9874);
INSERT 0 1
postgres=# INSERT INTO products (product_no, name, price) VALUES (5, "Access", 5);
INSERT 0 1
postgres=#
postgres=#
postgres=# select * from products;
 product_no |    name    | price
------------+------------+-------
          1 | Java       |  1234
          2 | SQL Server |  3421
          3 | Oracle     |  7623
          4 | DB2        |  9874
          5 | Access     |     5
(5 rows)
postgres=#
postgres=# UPDATE products SET price = price * 1.10;
UPDATE 5
postgres=#
postgres=# select * from products;
 product_no |    name    |  price
------------+------------+----------
          1 | Java       |  1357.40
          2 | SQL Server |  3763.10
          3 | Oracle     |  8385.30
          4 | DB2        | 10861.40
          5 | Access     |     5.50
(5 rows)
postgres=#
postgres=# drop table products;
DROP TABLE
postgres=#
postgres=#

Using UPDATE with several Employees

postgres=#
postgres=#
postgres=# create table job(
postgres(#     ID          int,
postgres(#     title       varchar (10));
CREATE TABLE
postgres=#
postgres=#
postgres=# insert into job(ID, title) values(1,"Developer");
INSERT 0 1
postgres=# insert into job(ID, title) values(2,"Tester");
INSERT 0 1
postgres=# insert into job(ID, title) values(3,"Designer");
INSERT 0 1
postgres=# insert into job(ID, title) values(4,"Programmer");
INSERT 0 1
postgres=#
postgres=# select * from job;
 id |   title
----+------------
  1 | Developer
  2 | Tester
  3 | Designer
  4 | Programmer
(4 rows)
postgres=#
postgres=#
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=# -- Using UPDATE with several Employees
postgres=#
postgres=# UPDATE employee
postgres-#         SET salary = salary + 100
postgres-#         FROM job
postgres-#         WHERE job.id = employee.id;
UPDATE 4
postgres=#
postgres=# select * from employee;
 id |  name  | salary | start_date |   city    | region
----+--------+--------+------------+-----------+--------
  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
  1 | Jason  |  40520 | 1994-02-01 | New York  | W
  2 | Robert |  14520 | 1995-01-02 | Vancouver | N
  3 | Celia  |  24120 | 1996-12-03 | Toronto   | W
  4 | Linda  |  40720 | 1997-11-04 | New York  | N
(9 rows)
postgres=#
postgres=#
postgres=# drop table job;
DROP TABLE
postgres=# drop table employee;
DROP TABLE
postgres=#

Using user defined function to update table data

postgres=# CREATE TABLE emp (
postgres(#    name        text,
postgres(#    salary      numeric,
postgres(#    age         integer,
postgres(#    cubicle     point
postgres(# );
CREATE TABLE
postgres=#
postgres=# insert into emp (salary) values(100);
INSERT 0 1
postgres=# insert into emp (salary) values(200);
INSERT 0 1
postgres=# insert into emp (salary) values(300);
INSERT 0 1
postgres=#
postgres=# CREATE FUNCTION double_salary(emp) RETURNS numeric AS $$
postgres$#    SELECT $1.salary * 2 AS salary;
postgres$# $$ LANGUAGE SQL;
CREATE FUNCTION
postgres=#
postgres=# SELECT name, double_salary(ROW(name, salary*1.1, age, cubicle)) AS dream
postgres-#    FROM emp;
 name | dream
------+-------
      | 220.0
      | 440.0
      | 660.0
(3 rows)
postgres=#
postgres=# drop function double_salary(emp);
DROP FUNCTION
postgres=# drop table emp;
DROP TABLE
postgres=#
postgres=#

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

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

Содержание

A simple UPDATE: change one cell

Update a single row

Update in a slice

Update two columns in one single statement

Updating entire columns

Updating Several Columns

Using expression in update statement

Using UPDATE with several Employees

Using user defined function to update table data

Навигация

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

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

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты