Empowering you to understand your world

PostgreSQL Tutorial: How To Update Records In A PostgreSQL Database

By Nicholas Brown

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’) in your PostgreSQL database.

How To Log Into PostgreSQL In Ubuntu

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.

If you want to update many PostgreSQL records at once, it will automatically do so if more than one record matches your criteria. 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 PostgreSQL tutorial without practice, now would it? Create a new table in your database 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  August 17, 2019. 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 in PostgreSQL, 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 how you drop a table in PostgreSQL Two measly words! To ensure that that the fruits table is no longer there, you can just type ‘\dt‘ as I showed you earlier to list relations (list tables) in your PostgreSQL database.

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, '2019-08-17');

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’.

PostgreSQL Tutorials: How To Rename A Column

Leave a Reply

Subscribe to our newsletter
Get notified when new content is published