PostgreSQL Introduction: How To Update Records In A PostgreSQL Database

You can update the IMR18650 record that you created earlier using the UPDATE statement as shown below. Note that you can use the \dt command to list tables (called ‘relations’ by PostgreSQL) in your database.

UPDATE batteries

SET type = 'Lithium-Ion' WHERE model = 'IMR18650';

PostgreSQL should return the ‘UPDATE 1’ message, and that’s it! ‘UPDATE 1’ means that one record was updated.

Notice how I used the WHERE clause to help it find the correct record? This is why it is helpful to assign a unique ID number to each record, to reduce the risk of updating the wrong one. The ‘WHERE’ clause is just the SQL way of pointing to the record (or records) you want to perform a query on.

You may also want to update many records at once. For example, if you had typed ‘WHERE capacity >  8’, it would set the type field to ‘Lithium-Ion’ for every battery that has a capacity over 8 Wh. In this case, there are two batteries with capacities exceeding 8 Wh, so both records ‘type’ fields would be updated to ‘lithium-ion’.

Creating Another Table, and Exploring Other Features/Data Types

This wouldn’t be a good tutorial without practice, now would it? Create a new table called fruits. Include the fields name, received_from, price, and date_received. Remember, you don’t need to use quotes for the table or field names unless they contain uppercase letters. The price field will be of the ‘numeric’ data type because cents may be involved.

CREATE TABLE fruits (

name varchar(80),

received_from varchar(80),

price numeric,

date_received date

);

Exercise: Add a $2.50 orange which was received from Molly on January 18, 2017. The default date format is yyyy-mm-dd.

Whoops, we forgot to state whether we paid Molly for the orange! To get more practice, and learn how to use the DROP TABLE query/statement, let us erase that and create a new, much more useful table that helps us keep track of what we owe. Here’s how you delete a table in PostgreSQL:

DROP TABLE fruits;

That’s it! To ensure that that the fruits table is no longer there, you can just type ‘\dt’ as I showed you earlier.

We will add a column called ‘paid’ with a data type of numeric to keep track of how much we have paid Molly (and whoever else we add to this table in the future) so far, so we can determine whether or not we owe her anything (and also exactly how much) with the help of the WHERE clause. Bear in mind that dates should be in single quotes.

CREATE TABLE fruits (

name varchar(80),

received_from varchar(80),

price numeric,

paid numeric,

date_received date

);

When you’re done, add the orange again, except you would assign a value of 1 to paid, as we only paid Molly $1 so far, and still owe her. Please attempt to do it yourself before looking at the solution below.

INSERT INTO fruits VALUES('Orange', 'Molly', 2.50, 1, '2017-01-18');

Type SELECT * FROM fruits; to view your table. The ‘*’ isn’t considered ideal under all circumstances, it is just a quick way to check the contents of a small table like this. You will learn more about structuring SELECT statements later in this tutorial.

To gain more practice, and enable us to move onto the next phase of this tutorial, add some more records with the following values.

Name: Apple, received from: Gertrude, price: $4, amount paid: $4, date received: ‘2017-01-22’.

Name: Pear, received from: John, price: $3, amount paid: $1, date received: ‘2017-01-28’.

Facebook Comments

comments