Postgres conditional update

Postgres conditional update DEFAULT

Conditional DELETE and INSERT in PostgreSQL

Introduction

We are comfortable with inserting/deleting record(s) into/from a table. But we sometimes get confused when we need to insert or delete records conditionally from a table. Let us look at an example.

Example scenario

Suppose we have a table, called ALL_EMPLOYEES, which contains all employees' information who are working or had ever worked for the company. Suppose we also have a table, called SEP_EMPLOYEES, which contains those employees' information who have separated from the company. Now, the company wants to clean up their database of all the separated employees' information from the ALL_EMPLOYEES table. In this case, we need to do a conditional delete from the ALL_EMPLOYEES table.

Delete Join support

PostgreSQL does not support the DELETE JOIN statement. But we can use the USING clause with the DELETE statement to have the same functionality achieved.

The syntax looks like:

In the above syntax:

  1. After the USING clause, specify the table expression, which can be one or even more tables (equivalent to join)
  2. In the WHERE clause, use the columns from the tables in the USING clause to join the data (equivalent to join condition).

Solution for our example scenario

To solve our problem we discussed, we need to use this DELETE with USING. First we need to create the tables as:

DROP TABLE IF EXISTS ALL_EMPLOYEES; CREATE TABLE ALL_EMPLOYEES( employee_id serial PRIMARY KEY, first_name varchar(50) NOT NULL, last_name varchar(50) NOT NULL, department varchar(15) NOT NULL ); DROP TABLE IF EXISTS SEP_EMPLOYEES; CREATE TABLE SEP_EMPLOYEES( employee_id INT NOT NULL UNIQUE );

Now let's feed some dummy data into these tables as:

INSERT INTO ALL_EMPLOYEES (first_name, last_name, department) VALUES ('Sabyasachi', 'Mukherjee', 'IT Analyst'); INSERT INTO ALL_EMPLOYEES (first_name, last_name, department) VALUES ('Stephen', 'Johnas', 'Senior Dev'); INSERT INTO ALL_EMPLOYEES (first_name, last_name, department) VALUES ('David', 'Willson', 'DBA'); INSERT INTO SEP_EMPLOYEES VALUES (1); INSERT INTO SEP_EMPLOYEES VALUES (2); INSERT INTO SEP_EMPLOYEES VALUES (4);

So now when we need to remove the data of SEP_EMPLOYEES from ALL_EMPLOYEES, we can write:

DELETE FROM ALL_EMPLOYEES USING SEP_EMPLOYEES WHERE SEP_EMPLOYEES.EMPLOYEE_ID = ALL_EMPLOYEES.EMPLOYEE_ID RETURNING ALL_EMPLOYEES.FIRST_NAME || ' ' || ALL_EMPLOYEES.LAST_NAME;

The resultant rows returns:

It shows the names which have been removed from the ALL_EMPLOYEES table. The record set in the ALL_EMPLOYEES table looks like:

Example Scenario

Well, now suppose we have this ALL_EMPLOYEES table containing the present address of employees, too. Assume that we get a batch of employees every day whose details need to be entered into this table. Now this batch contains both new employees joining the company and also employees requesting for a change in their postal address. In such case, we cannot simply run an INSERT query because that would create multiple employee ids for the same employee requesting an address update. So here the insert should be conditional.

PostgreSQL UPSERT support

UPSERT is basically INSERT+UPDATE, i.e., it says that if no similar record is found, then you may insert the record, otherwise update the matching existing record with the information provided. To take the benefit of this feature, we need to use the INSERT ON CONFLICT syntax which is like:

The target can be either a column name or a constraint name or a WHERE clause. On the other hand, the <action_to_be_performed> can be either the DO NOTHING or the DO UPDATE statement.

Solution for our example scenario

First, we need to re-create the table with the present address column as:

DROP TABLE IF EXISTS ALL_EMPLOYEES; CREATE TABLE ALL_EMPLOYEES( employee_id serial PRIMARY KEY, first_name varchar(50) NOT NULL, last_name varchar(50) NOT NULL, email_id varchar(50) NOT NULL UNIQUE, department varchar(15) NOT NULL, present_address varchar(255) NOT NULL );

Now let's insert few records into this table running the below queries:

INSERT INTO ALL_EMPLOYEES (first_name, last_name, email_id, department, present_address) VALUES ('Sabyasachi', 'Mukherjee', '[email protected]', 'IT Analyst', 'India, WB, Kolkata'); INSERT INTO ALL_EMPLOYEES (first_name, last_name, email_id, department, present_address) VALUES ('David', 'Willson', '[email protected]', 'DBA', 'USA, New Jersey'); INSERT INTO ALL_EMPLOYEES (first_name, last_name, email_id, department, present_address) VALUES ('John', 'Vancock', '[email protected]', 'Senior Dev', 'USA, San Francisco');

To ensure that only fresh records are inserted and existing records does an update in the present address column, we can write a query as:

INSERT INTO ALL_EMPLOYEES (first_name, last_name, email_id, department, present_address) VALUES ('Sabyasachi', 'Mukherjee', '[email protected]', 'Senior Dev', 'USA, Canada') ON CONFLICT (email_id) DO UPDATE SET present_address = present_address;

Now, suppose we get a batch of employees which contains 2 new employees (named: Stephen Havard and Anthony Wickwood) and 1 existing employee (named: Sabyasachi Mukherjee) requesting an address update, then the bulk INSERT ON CONFLICT queries generated would look like:

INSERT INTO ALL_EMPLOYEES (first_name, last_name, email_id, department, present_address) VALUES ('Sabyasachi', 'Mukherjee', '[email protected]', 'Senior Dev', 'USA, Canada') ON CONFLICT (email_id) DO UPDATE SET present_address = EXCLUDED.present_address; INSERT INTO ALL_EMPLOYEES (first_name, last_name, email_id, department, present_address) VALUES ('Stephen', 'Havard', '[email protected]', 'Junior Dev', 'Indonesia') ON CONFLICT (email_id) DO UPDATE SET present_address = EXCLUDED.present_address; INSERT INTO ALL_EMPLOYEES (first_name, last_name, email_id, department, present_address) VALUES ('Anthony', 'Wickwood', '[email protected]', 'Finance Auditor', 'USA, New Jersey') ON CONFLICT (email_id) DO UPDATE SET present_address = EXCLUDED.present_address;

So if we run these all queries in a batch, then there should be 2 new employees created while 1 employees address should be updated from India, WB, Kolkata to USA, Canada.

The result looks like:

In case we do not want to do anything for any existing employee, we can use DO NOTHING instead.

Note: We are using email_id as an unique identifier here to understand whether the employee is an existing or new since 2 employees cannot have same email_id in an organization.

Conclusion

In this article, we have learnt on how to insert/update data into a table and delete data from a table joining other table(s).

Sours: https://www.sqlservercentral.com/articles/conditional-delete-and-insert-in-postgresql

conditional updating in postgreSQL

I'm testing some conditional rendering to see if it behaves as expected on a small scale before implementing it on a larger table.

The goal is to create an update query that will only update the columns that a user has input new data in and if there is no data then nothing is done to those columns

here's my case when then test. The row with the null value was not updated 'newValue'

Here's a simple example of what I want to produce ($1 is the user input)

what I'm expecting to happen is the insert to create a row with the id of 1 and a val cell that equals null.

Then in the update query, I'm thinking the update will check if the stored data in val on row 1 is NOT equal to the input value, if that is true then the input value will get set to val for row 1.

Is that logic and syntax correct? If not, what is incorrect? Thanks!

asked Sep 26 '18 at 5:41

Austin CallaghanAustin Callaghan

8511 gold badge11 silver badge1010 bronze badges

Sours: https://stackoverflow.com/questions/52510655/conditional-updating-in-postgresql
  1. Animal cell jokes
  2. Rakuten unsubscribe
  3. Octave statistics package
  4. Concrete deck lighting

UPDATE

Name

UPDATE -- update rows of a table

Synopsis

[ WITH [ RECURSIVE ] [, ...] ] UPDATE [ ONLY ] [ * ] [ [ AS ] ] SET { = { | DEFAULT } | ( [, ...] ) = ( { | DEFAULT } [, ...] ) } [, ...] [ FROM ] [ WHERE | WHERE CURRENT OF ] [ RETURNING * | [ [ AS ] ] [, ...] ]

Description

changes the values of the specified columns in all rows that satisfy the condition. Only the columns to be modified need be mentioned in the clause; columns not explicitly modified retain their previous values.

There are two ways to modify a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the clause. Which technique is more appropriate depends on the specific circumstances.

The optional clause causes to compute and return value(s) based on each row actually updated. Any expression using the table's columns, and/or columns of other tables mentioned in , can be computed. The new (post-update) values of the table's columns are used. The syntax of the list is identical to that of the output list of .

You must have the privilege on the table, or at least on the column(s) that are listed to be updated. You must also have the privilege on any column whose values are read in the or .

Parameters

The clause allows you to specify one or more subqueries that can be referenced by name in the query. See Section 7.8 and SELECT for details.

The name (optionally schema-qualified) of the table to update. If is specified before the table name, matching rows are updated in the named table only. If is not specified, matching rows are also updated in any tables inheriting from the named table. Optionally, can be specified after the table name to explicitly indicate that descendant tables are included.

A substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. For example, given , the remainder of the statement must refer to this table as not .

The name of a column in . The column name can be qualified with a subfield name or array subscript, if needed. Do not include the table's name in the specification of a target column — for example, is invalid.

An expression to assign to the column. The expression can use the old values of this and other columns in the table.

Set the column to its default value (which will be NULL if no specific default expression has been assigned to it).

A list of table expressions, allowing columns from other tables to appear in the condition and the update expressions. This is similar to the list of tables that can be specified in the FROM Clause of a statement. Note that the target table must not appear in the , unless you intend a self-join (in which case it must appear with an alias in the ).

An expression that returns a value of type . Only rows for which this expression returns will be updated.

The name of the cursor to use in a condition. The row to be updated is the one most recently fetched from this cursor. The cursor must be a non-grouping query on the 's target table. Note that cannot be specified together with a Boolean condition. See DECLARE for more information about using cursors with .

An expression to be computed and returned by the command after each row is updated. The expression can use any column names of the or table(s) listed in . Write to return all columns.

A name to use for a returned column.

Outputs

On successful completion, an command returns a command tag of the form

UPDATE

The is the number of rows updated. If is 0, no rows matched the (this is not considered an error).

If the command contains a clause, the result will be similar to that of a statement containing the columns and values defined in the list, computed over the row(s) updated by the command.

Notes

When a clause is present, what essentially happens is that the target table is joined to the tables mentioned in the , and each output row of the join represents an update operation for the target table. When using you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

Because of this indeterminacy, referencing other tables only within sub-selects is safer, though often harder to read and slower than using a join.

Examples

Change the word to in the column of the table :

UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';

Adjust temperature entries and reset precipitation to its default value in one row of the table :

UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT WHERE city = 'San Francisco' AND date = '2003-07-03';

Perform the same operation and return the updated entries:

UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT WHERE city = 'San Francisco' AND date = '2003-07-03' RETURNING temp_lo, temp_hi, prcp;

Use the alternative column-list syntax to do the same update:

UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT) WHERE city = 'San Francisco' AND date = '2003-07-03';

Increment the sales count of the salesperson who manages the account for Acme Corporation, using the clause syntax:

UPDATE employees SET sales_count = sales_count + 1 FROM accounts WHERE accounts.name = 'Acme Corporation' AND employees.id = accounts.sales_person;

Perform the same operation, using a sub-select in the clause:

UPDATE employees SET sales_count = sales_count + 1 WHERE id = (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');

Attempt to insert a new stock item along with the quantity of stock. If the item already exists, instead update the stock count of the existing item. To do this without failing the entire transaction, use savepoints:

BEGIN; -- other operations SAVEPOINT sp1; INSERT INTO wines VALUES('Chateau Lafite 2003', '24'); -- Assume the above fails because of a unique key violation, -- so now we issue these commands: ROLLBACK TO sp1; UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003'; -- continue with other operations, and eventually COMMIT;

Change the column of the table in the row on which the cursor is currently positioned:

UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;

Compatibility

This command conforms to the standard, except that the and clauses are PostgreSQL extensions, as is the ability to use with .

According to the standard, the column-list syntax should allow a list of columns to be assigned from a single row-valued expression, such as a sub-select:

UPDATE accounts SET (contact_last_name, contact_first_name) = (SELECT last_name, first_name FROM salesmen WHERE salesmen.id = accounts.sales_id);

This is not currently implemented — the source must be a list of independent expressions.

Some other database systems offer a option in which the target table is supposed to be listed again within . That is not how PostgreSQL interprets . Be careful when porting applications that use this extension.

Sours: https://www.postgresql.org/docs/9.1/sql-update.html
Postgres Conditionals: How to Use Greatest and Least

Summary: in this tutorial, you will learn how to use the PostgreSQL statement to update existing data in a table.

Introduction to the PostgreSQL UPDATE statement

The PostgreSQL statement allows you to modify data in a table. The following illustrates the syntax of the statement:

Code language:SQL (Structured Query Language)(sql)

In this syntax:

  • First, specify the name of the table that you want to update data after the keyword.
  • Second, specify columns and their new values after keyword. The columns that do not appear in the clause retain their original values.
  • Third, determine which rows to update in the condition of the clause.

The clause is optional. If you omit the clause, the statement will update all rows in the table.

When the statement is executed successfully, it returns the following command tag:

The is the number of rows updated including rows whose values did not change.

Returning updated rows

The statement has an optional clause that returns the updated rows:

Code language:SQL (Structured Query Language)(sql)

PostgreSQL UPDATE examples

Let’s take some examples of using the PostgreSQL statement.

Setting up a sample table

The following statements create a table called and insert some data into it:

Code language:SQL (Structured Query Language)(sql)

The following statement returns the data from the table:

Code language:SQL (Structured Query Language)(sql)
PostgreSQL Update - Courses Table

1) PostgreSQL UPDATE – updating one row

The following statement uses the statement to update the course with id 3. It changes the from to .

Code language:SQL (Structured Query Language)(sql)

The statement returns the following message indicating that one row has been updated:

Code language:Shell Session(shell)

The following statement selects the course with id 3 to verify the update:

Code language:SQL (Structured Query Language)(sql)
PostgreSQL Update - update one row

2) PostgreSQL UPDATE – updating a row and returning the updated row

The following statement updates course id 2. It modifies of the course to and returns the updated course.

Code language:SQL (Structured Query Language)(sql)
PostgreSQL UPDATE and return updated row

Summary

  • Use the PostgreSQL statement to update data in one or more columns of a table.
  • Use the clause to return the updated rows from the statement
Sours: https://www.postgresqltutorial.com/postgresql-update/

Update postgres conditional

Use CASE to select columns in UPDATE query?

If you specify a column should be updated then it will always be updated, but you can change the value you put in conditionally and put back the original values depending on your conditions. Something like:

So if the conditions are not right for an update to a particular column you just feed back it's current value.

Do note that every row matched will see an update (even if all the columns end up getting set to the values they already have) unless you explicitly gate this circumstance in you filtering ON and WHERE clauses, which could be a performance problem (there will be a write, indexes will be updated, appropriate triggers will fire, ...) if not mitigated.

answered Apr 12 '13 at 11:28

David SpillettDavid Spillett

29k33 gold badges4242 silver badges8181 bronze badges

Sours: https://dba.stackexchange.com/questions/39815/use-case-to-select-columns-in-update-query/39821
Postgres Conditionals: How to Use Coalesce

How to Update a Column Based on a Filter of Another Column

In this tutorial, we’ll go over the various ways to update rows in a table using SQL progressing from more general updates to more specific methods.

Full Update

If every field needs to be updated to the same value, you can do that using a simple command.

Conditional Update

To do a conditional update depending on whether the current value of a column matches the condition, you can add a clause which specifies this. The database will first find rows which match the clause and then only perform updates on those rows.

To expand on this, you can add anything to the clause you like as long as it’s a valid expression. So to perform an update based on the value of another column in the same table, you could execute the following:

Since the clause can contain any valid expression, you also have the possibility to do updates where multiple columns meet the criteria

As you can see, you can expand the clause as much as you’d like in order to filter down the rows for updating to what you need.

Now what happens if you want to update rows in one table based on the condition of another table? This question leads to a few different ways you could do this.

Since the clause can contain any valid expression, you could use a subquery:

You can also use a subquery in the portion of the statement if you want to set the column to a value in another table

Perhaps an easier way is to specify multiple tables after the clause. Only the expression will perform updates but listing additional tables will allow the tables to be included.

Similarly to expanding the clause, the amount of tables can be expanded to include all the tables you need if you have multiple tables that need to be joined on.

Sours: https://chartio.com/learn/sql-tips/how-to-update-a-column-based-on-a-filter-of-another-column/

You will also like:

Modifying Rows with UPDATE

Once data has been inserted into rows within the database, those rows can have one or more of their column values modified through use of the SQL command. Column values may be updated either with constants, identifiers to other data sets, or expressions. They may apply to an entire column, or a subset of a column’s values through specified conditions. The command uses the following syntax:

UPDATE [ ONLY ] table SET column = expression [, ...] [ FROM source ] [ WHERE condition ]

The keyword may be used to indicate that only the table should be updated, and none of its sub-tables. This is only relevant if is inherited by any other tables.

[, ...]

The required clause is followed by an update expression for each column name that needs to have its values modified, separated by commas. This expression is always of the form where is the name of the column to be updated (which may not be aliased, or dot-notated), and where describes the new value to be inserted into the column.

The clause is a non-standard PostgreSQL extension that allows table columns from other data sets to update a column’s value.

The clause describes the upon which a row in will be updated. If unspecified, all values in will be modified. This may be used to qualify sources in the clause, as you would in a statement.

Example 4-53 demonstrates a simple statement. It instructs PostgreSQL to update the value in the table’s column with the floating-point constant value of 29.95. The clause constrains any modifications to rows that match the criteria described by it.

Example 4-53. A simple UPDATE

booktown=# SELECT retail FROM stock booktown-# WHERE isbn = '0590445065'; retail -------- 23.95 (1 row) booktown=# UPDATE stock booktown-# SET retail = 25.95 booktown-# WHERE isbn = '0590445065'; UPDATE 1 booktown=# SELECT retail FROM stock booktown-# WHERE isbn = '0590445065'; retail -------- 25.95 (1 row)

The resultant message from Example 4-53 indicates that one record was successfully updated. Even if the value that is modified is identical to the record previously stored, it is considered an update, and the database files on disk are still modified as a result of the statement.

If the clause is omitted, an statement will modify each of the values within the entire specified column. This is generally most useful when updating columns with an rather than a constant value. When an expression is specified in the clause, it is re-evaluated just before updating each row. Thus, each row is updated to a value determined dynamically by the interpreted expression’s value for each row. This is demonstrated in Example 4-54.

Example 4-54 demonstrates using an statement on the table’s column. It uses a mathematical expression to raise the retail price of each stocked book. The expression itself has several components, separated by parentheses to enforce order of execution.

The sub-expression determines the current profit margin of the book, which is then incremented by one tenth with the operator and a floatingpoint constant of 0.1. The syntax explicitly casts the floating point constant to a value of type This is necessary due to the result of the division sub-expression returning a value of type Finally, this new profit margin is multiplied by the base cost from the column, resulting in the new price with which the column should be updated.

Example 4-54. Updating entire columns

booktown=# SELECT isbn, retail, cost booktown-# FROM stock booktown-# ORDER BY isbn ASC booktown-# LIMIT 3; isbn | retail | cost ------------+--------+------- 0385121679 | 36.95 | 29.00 039480001X | 32.95 | 30.00 0394800753 | 16.95 | 16.00 (3 rows) booktown=# UPDATE stock booktown-# SET retail = booktown-# (cost * ((retail / cost) + 0.1::numeric)); UPDATE 16 booktown=# SELECT isbn, retail, cost booktown-# FROM stock booktown-# ORDER BY isbn ASC booktown-# LIMIT 3; isbn | retail | cost ------------+--------+------- 0385121679 | 39.85 | 29.00 039480001X | 35.95 | 30.00 0394800753 | 18.55 | 16.00 (3 rows)

Since the statement in Example 4-54 has no clause, all rows within the table are modified by this statement.

By separating assignment expressions in the clause with commas, you may execute updates to several columns of a table in a single statement. Example 4-55 illustrates updating both the and column of the table for the Publisher with the of 113.

Example 4-55. Using UPDATE on several columns

booktown=# UPDATE publishers booktown-# SET name = 'O\'Reilly & Associates', booktown-# address = 'O\'Reilly & Associates, Inc. ' booktown-# || '101 Morris St, Sebastopol, CA 95472' booktown-# WHERE id = 113; UPDATE 1 booktown=# SELECT name, substr(address, 1, 40) || '...' AS short_address booktown-# FROM publishers booktown-# WHERE id = 113; name | short_address -----------------------+--------------------------------------------- O'Reilly & Associates | O'Reilly & Associates, Inc. 101 Morris S... (1 row)

The statement in Example 4-55 shows both the and columns assigned through string constants. Notice that several backslashes within the string constants escape the input apostrophes. The statement following the update verifies that the desired information was updated.

Example 4-55 also demonstrates the use of the text concatenation operator, and the function, in practical usage. The column is set with two string constants that are attached through the operator in order to prevent the query from wrapping past the edge of the terminal. The function is then used in the verification to prevent the output from wrapping. Each of these are used here to maintain readability of the output (of course, you would not want to display only a substring of the address field if you were interested in verifying its complete contents).

Updating from Several Sources

PostgreSQL supports a powerful non-standard enhancement to the SQL statement in the form of the clause. By using the clause, you can apply your knowledge of the statement to draw input data from other existing data sets, such as tables, or sub-selects.

Example 4-56 uses an statement in conjunction with a clause to modify the row data within the table via the table. The clause describes the relationship between the table to be updated and its source. Wherever the column is found to match, the value in the table is modified to the value from the previously populated table.

Example 4-56. Using UPDATE with several sources

booktown=# UPDATE stock booktown-# SET retail = stock_backup.retail booktown-# FROM stock_backup booktown-# WHERE stock.isbn = stock_backup.isbn; UPDATE 16

The clause supports each of the syntax options described in the section titled Retrieving Rows with SELECT, enabling a wide variety of update methods from existing data sets. Further, as stated previously, sub-selects may be used as a data source to the clause, just as is possible with the command.

Get Practical PostgreSQL now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.

Start your free trial

Sours: https://www.oreilly.com/library/view/practical-postgresql/9781449309770/ch04s05.html


4930 4931 4932 4933 4934