PostgreSQL/Insert Delete Update/Update

Материал из SQL эксперт
Перейти к: навигация, поиск

A simple UPDATE: change one cell

   <source lang="sql">

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

      </source>
   
  


Update a single row

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

      </source>
   
  


Update in a slice

   <source lang="sql">

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

      </source>
   
  


Update two columns in one single statement

   <source lang="sql">

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

      </source>
   
  


Updating entire columns

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

      </source>
   
  


Updating Several Columns

   <source lang="sql">

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

      </source>
   
  


Using expression in update statement

   <source lang="sql">

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

      </source>
   
  


Using UPDATE with several Employees

   <source lang="sql">

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

      </source>
   
  


Using user defined function to update table data

   <source lang="sql">

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

      </source>