Customize Consent Preferences

We use cookies to help you navigate efficiently and perform certain functions. You will find detailed information about all cookies under each consent category below.

The cookies that are categorized as "Necessary" are stored on your browser as they are essential for enabling the basic functionalities of the site. ... 

Always Active

Necessary cookies are required to enable the basic features of this site, such as providing secure log-in or adjusting your consent preferences. These cookies do not store any personally identifiable data.

No cookies to display.

Functional cookies help perform certain functionalities like sharing the content of the website on social media platforms, collecting feedback, and other third-party features.

No cookies to display.

Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics such as the number of visitors, bounce rate, traffic source, etc.

No cookies to display.

Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.

No cookies to display.

Advertisement cookies are used to provide visitors with customized advertisements based on the pages you visited previously and to analyze the effectiveness of the ad campaigns.

No cookies to display.

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