{"id":112596,"date":"2026-11-02T12:00:00","date_gmt":"2026-11-02T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=112596"},"modified":"2026-10-22T13:31:02","modified_gmt":"2026-10-22T13:31:02","slug":"how-to-insert-query-and-modify-data-in-postgresql-getting-started-with-postgresql-part-three","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/how-to-insert-query-and-modify-data-in-postgresql-getting-started-with-postgresql-part-three\/","title":{"rendered":"How to insert, query, and modify data in PostgreSQL (getting started with PostgreSQL, part three)"},"content":{"rendered":"\n

Once your PostgreSQL tables are built, the next step is putting them to work. This guide walks through the core data operations every PostgreSQL user needs: inserting records with INSERT<\/code>, retrieving them with SELECT<\/code>, filtering and combining tables with WHERE<\/code> and JOIN<\/code>, and modifying or removing data with UPDATE<\/code> and DELETE<\/code>. <\/strong><\/p>\n\n\n\n

You’ll also learn how PostgreSQL handles IDENTITY<\/code> columns, sequences, aliases, and referential integrity<\/strong>.<\/p>\n\n\n\n

\n
\n\n

Important note before you continue…<\/strong><\/p>\n\n\n\n

This is the third and final part in Grant Fritchey’s complete set of step-by-step walkthroughs (guides) for getting started with PostgreSQL, designed for any IT professional or hobbyist who wants to add PostgreSQL to their existing skillset. Click here for the full series<\/a>.<\/p>\n\n\n\n

Originally intended to be proctored classes given in a live, in-person environment, Grant has aimed to make the documentation and scripts clear enough to walk through them independently from home.<\/p>\n\n\n\n

Before getting started, please check that you have everything you need.<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n

How to insert (add) data to PostgreSQL<\/h2>\n\n\n\n

With tables in place, we can now add data our PostgreSQL<\/a> databases.<\/strong> First, I’ll run through a few manual INSERT<\/code> statements to show some of the behaviors. These scripts will be in addingdata_manual.sql<\/code> – however, most of the data will get added by running just one script (addingdata.sql<\/code>).<\/p>\n\n\n\n

Before we start, make sure you’re logged in to psql<\/code>: psql -d ham_shack -h localhost -U radiodev<\/code><\/p>\n\n\n\n

The PostgreSQL INSERT<\/code> statement<\/h3>\n\n\n\n

The basic behavior of the INSERT<\/code> statement is pretty common across most data platforms. You define the table<\/a> you’re going to add data to. You then supply a list of columns in that table. Finally, you supply values for those columns. <\/p>\n\n\n\n

\n
\n\n

The INSERT<\/code> statement across various data platforms: further reading<\/strong><\/p>\n\n\n\n

Working with the INSERT statement in SQL Server<\/a><\/p>\n\n\n\n

The Basics of Inserting Data into a SQL Server Table<\/a><\/p>\n\n\n\n

Introducing the MySQL INSERT statement<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n

Here’s an example for the gear.antenna<\/code> table:<\/p>\n\n\n\n

INSERT INTO gear.antenna \n(antenna_name)\nVALUES\n('Rubber duck');<\/pre><\/div>\n\n\n\n
This follows exactly what we described above. The only thing to point out here is that we didn’t include the antenna_id<\/code> column, since that’s set as an IDENTITY<\/code> column<\/a> and will, by default, create its own values. So, there’s no need to add it to the column list. <\/p>\n\n\n\n
However, if you want to override<\/em> the behavior of IDENTITY<\/code>, you can simply supply it. Just make sure you’re not violating the primary key<\/a> or you’ll get an error. Don’t<\/em> run this script, for example:<\/p>\n\n\n\n
INSERT INTO gear.antenna \n(antenna_id,antenna_name)\nVALUES\n(2,'Signal Stick');<\/pre><\/div>\n\n\n\n
While this would<\/em> technically work, it won’t reset the values on the IDENTITY<\/code> column – you’ll get an error at the next INSERT<\/code>. <\/p>\n\n\n\n
Instead, you have to manually<\/em> add data to an IDENTITY<\/code> column that is less than the current value of the SEQUENCE<\/code> (the underlying architecture of the IDENTITY<\/code> behavior). Otherwise, you’d have to manually update the latest value for the SEQUENCE<\/code>.<\/p>\n\n\n\n
You don’t have to supply the column names. You can leave them off the INSERT<\/code> statement entirely:<\/p>\n\n\n\n
INSERT INTO gear.antenna \nVALUES\n(DEFAULT, 'Signal Stick');<\/pre><\/div>\n\n\n\n
In order to skip the column list, you must have the right number of columns, in the right order, with the right data types, in your VALUES<\/code> list. If not, you’ll get an error. <\/p>\n\n\n\n
We also don’t want to mess up the IDENTITY<\/code> value, we have to use the DEFAULT<\/code> keyword, which triggers PostgreSQL to supply the value. For a very small table like gear.antenna<\/code> – which by default only needs a single column -this is fine. However, it’d likely cause issues with larger tables.<\/p>\n\n\n\n
You can also add more than one value – in this case, three rows are added to the table with:<\/p>\n\n\n\n
INSERT INTO gear.antenna \n(antenna_name)\nVALUES\n('ZBM2 All Weather Whip'),\n('Maximus'),\n('771');<\/pre><\/div>\n\n\n\n
Speaking of rows, the PostgreSQL documentation<\/a> uses both ‘tuple’ and<\/em> ‘row’ to describe rows. Tuple is a much more academic term but, to be clear, they both mean the same thing.<\/p>\n\n\n\n
Filling out the rest of the data<\/h3>\n\n\n\n
Let’s exit interactive psql<\/code> for the moment (using \\q<\/code>), and we’ll now load data into the remaining tables by running the addingdata.sql<\/code> script.<\/p>\n\n\n\n
First, copy it to the container volume. As covered previously in the guide, use the File app in Linux, File Explorer in Windows, or issue a command line<\/a> to copy the file. Once done, execute it through psql<\/code> by using
psql -d postgres -h localhost -U postgres -f \"\/var\/lib\/postgresql\/bu\/addingdata.sql\"<\/code><\/p>\n\n\n\n
Once complete, you’ll have a little bit of data in every table. One point from the script: the final way you can INSERT<\/code> data is to combine it with a SELECT<\/code>. The following query will run with the script, so no need to execute it. It shows how an INSERT<\/code> and a SELECT<\/code> can be combined:<\/p>\n\n\n\n
INSERT INTO gear.radio_antenna\n(radio_id, antenna_id)\nSELECT r.radio_id, a.antenna_id\nFROM gear.radio r\nCROSS JOIN gear.antenna a;<\/pre><\/div>\n\n\n\n
Since there’s no WHERE<\/code> clause and<\/em> it’s doing a CROSS JOIN<\/a><\/code>, every antenna is combined with every radio.<\/p>\n\n\n\n
And we’re done! Now that we have data in the tables, we can proceed to query<\/em> that data. <\/p>\n\n\n\n
How to query data in PostgreSQL<\/h2>\n\n\n\n
In this section, I’ll walk through several different PostgreSQL queries, showing a few of the distinctions in how they operate compared to other platforms. This won’t be a complete guide on querying PostgreSQL – rather, just introduction to the fundamentals. If you do<\/em> want more detail, click here for my full guide to querying PostgreSQL<\/a>.<\/p>\n\n\n\n
Before we start querying, be sure you’re logged in to psql<\/code> as the role radiodev<\/code>:
psql -d ham_shack -h localhost -U radiodev<\/code><\/p>\n\n\n\n
How to retrieve data from PostgreSQL with the SELECT<\/code> statement<\/h3>\n\n\n\n
PostgreSQL adheres fairly strictly to the ANSI SQL<\/a> standard, which includes the use of the SELECT<\/code> statement to retrieve data. <\/strong><\/p>\n\n\n\n
The most simple query is SELECT * FROM radio;<\/code>, in which the *<\/code> retrieves all columns. You have to define the tables you’re accessing through the FROM<\/code> clause of the query. However, this isn’t the best way to write queries. Why? <\/p>\n\n\n\n
First, and most importantly, I didn’t define the schema. It happens that the search_path<\/code> (defined earlier in the guide) put the gear<\/code> schema first – hence the correct table was found. <\/p>\n\n\n\n
Next, the use of *<\/code> is considered bad practice, since you’re not actually controlling specifically which columns you’re interested in, or their order. A better way to write the same query is:<\/p>\n\n\n\n
SELECT\n\tradio_id,\n\tradio_name,\n\tbrand\nFROM\n\tgear.radio;<\/pre><\/div>\n\n\n\n
Note that plenty of white space, tabs, carriage returns<\/a>, and spaces are ignored by the query engine in PostgreSQL. To make the code easier to read, I format it with the carriage returns, etc, but this is optional of course. You could write the query as one long line and it would work the same – it’s just more difficult to read:
SELECT radio_id,radio_name,brand FROM gear.radio;<\/code><\/p>\n\n\n\n
Here’s the output from any of the three queries defined so far:<\/p>\n\n\n\n
 radio_id | radio_name |       brand       \n----------+------------+-------------------\n        1 | Maverick   | BridgeCom Systems\n        2 | DM32-UV    | Baofeng\n        3 | FT3D       | Yaesu\n(3 rows)<\/pre><\/div>\n\n\n\n
The WHERE<\/code> clause in PostgreSQL<\/h3>\n\n\n\n
Another important point is to filter data through the use of the WHERE<\/code> clause. For example, if we want to only<\/em> retrieve information about the ‘Maverick’ radio, we could do:<\/p>\n\n\n\n
SELECT\n\tbrand\nFROM\n\tgear.radio\nWHERE\n\tradio_id = 1;<\/pre><\/div>\n\n\n\n
This leads to a much-reduced result set:<\/p>\n\n\n\n
       brand       \n-------------------\n BridgeCom Systems<\/pre><\/div>\n\n\n\n
The WHERE<\/code> clause supports logical operators as well. We could supply an OR<\/code> in the WHERE<\/code> clause like this:<\/p>\n\n\n\n
SELECT\n\tlast_name\nFROM\n\tpeople.operator\nWHERE\n\tfirst_name = 'Grant'\n\tOR first_name = 'Howard';<\/pre><\/div>\n\n\n\n
Resulting in:<\/p>\n\n\n\n
 last_name \n-----------\n Fritchey\n Hawkins<\/pre><\/div>\n\n\n\n
Unlike the integer value of 1<\/code> up above (for the strings<\/a>), we’ve used a single quote around<\/em> the string. It’s how PostgreSQL separates this from another object or function name<\/a>. The other logical operators are AND<\/code> and NOT<\/code>. You can make increasingly more complex queries with these.<\/p>\n\n\n\n
Before continuing with that, though, let’s add more tables to our queries through the JOIN<\/code> operator.<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Get started with PostgreSQL – free book download<\/h3>\n                
\n                                            ‘Introduction to PostgreSQL for the data professional’, written by Grant Fritchey and Ryan Booz, covers all the basics of how to get started with PostgreSQL.                                    <\/div>\n            <\/div>\n                                            Download your free copy<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
The JOIN<\/code> operator in PostgreSQL<\/h3>\n\n\n\n
PostgreSQL is an object-relational data store<\/a>, and a relational storage engine lives on the relationships between tables<\/em>.<\/strong> We defined those relationships when we created foreign keys and, to put tables together, we use the JOIN<\/code> operator.<\/strong> <\/p>\n\n\n\n
In addition to this, however, we need to define the owners. This is so the PostgreSQL optimizer<\/a> knows which table we’re pulling columns from. A query to retrieve radio and antenna names might look like:<\/p>\n\n\n\n
SELECT\n\tradio.radio_name,\n\tantenna.antenna_name\nFROM\n\tgear.radio\nJOIN gear.radio_antenna\nON\n\tradio.radio_id = radio_antenna.radio_id\nJOIN gear.antenna\nON\n\tradio_antenna.antenna_id = antenna.antenna_id\nWHERE\n\tradio.radio_id = 1;<\/pre><\/div>\n\n\n\n
There are several points to take from this. First, we’re supplying the table name for each column now. This has to happen because two tables contain a radio_id<\/code> column (in the form of radio<\/code> and radio_antenna<\/code>), and another two tables contain an antenna_id<\/code> column with antenna<\/code> and radio_antenna<\/code>. <\/p>\n\n\n\n
Second, we’ve added two more words to the query language: JOIN<\/code> and ON<\/code>. JOIN<\/code> defaults to INNER JOIN<\/a><\/code>. The INNER<\/code> is syntactic sugar<\/a> and not really needed. PostgreSQL supports LEFT<\/code> and RIGHT<\/code> joins as well. You can simply use LEFT JOIN<\/code> instead of the more traditional LEFT OUTER JOIN<\/code> to, again, eliminate syntactic sugar. <\/p>\n\n\n\n
A LEFT<\/code> or RIGHT<\/code> join will return NULL<\/a><\/code> values on non-matching rows. JOIN<\/code> is how you tell which tables you’re going to add to the query. Then, ON<\/code> is used to define the criteria for joining between the tables. In our case, we used the primary key from one table to join to the foreign key in the other.<\/p>\n\n\n\n
To my eye, that query is quite hard to read because of all the table names, which isn’t unusual. Thankfully, PostgreSQL supports the use of aliases. So, to see my radio contacts for example, I could query like this:<\/p>\n\n\n\n
SELECT\n\tol.callsign,\n\tol.first_name,\n\tc.contact_date,\n\tc.freq_mhz,\n\tr.radio_name\nFROM\n\tpeople.operator o\nJOIN people.contact c \nON\n\to.operator_id = c.call_operator_id\nJOIN people.operator ol \nON\n\tc.logged_operator_id = ol.operator_id\nJOIN gear.radio r \nON\n\tc.radio_id = r.radio_id\nWHERE\n\to.callsign = 'KC1KCE';<\/pre><\/div>\n\n\n\n
This query then puts it all together and we’re still appropriately using both the schema and<\/em> object ownership<\/a>. However, by defining aliases (people.operator o<\/code>), we can use a much shorter object “owner”<\/em>. <\/p>\n\n\n\n
Again, PostgreSQL doesn’t need to use the full syntax for aliases, people.operator AS o<\/code>. We’ve also crossed between schemas, referencing both gear<\/code> and people<\/code>. The results should look something like:<\/p>\n\n\n\n
 callsign | first_name |    contact_date     |  freq_mhz  | radio_name \n----------+------------+---------------------+------------+------------\n GG7WXYZ  | Reginald   | 2026-07-02 13:33:00 | 146.500000 | Maverick\n DD8AAAAB | Horst      | 2026-07-02 13:35:00 | 146.500000 | Maverick\n VK39AA   | GDay       | 2026-07-02 13:38:00 | 146.500000 | Maverick\n K5ABCD   | Howard     | 2026-07-02 13:39:00 | 146.500000 | Maverick<\/pre><\/div>\n\n\n\n
Let’s add one more bit of syntax to our querying: placing the data in order.<\/p>\n\n\n\n
How to use ORDER BY<\/code> to order data in PostgreSQL<\/h3>\n\n\n\n
It’s common to want to control the order of data, and PostgreSQL never determines the order unless you specify it. So, running one query on different days could return the data in a different order, depending on how it’s stored. This is why syntax to determine the order is so important.<\/strong><\/p>\n\n\n\n
For example, to see frequencies in the people.contact<\/code> table, we need to put them in order:<\/p>\n\n\n\n
SELECT\n\tc.freq_mhz,\n\tr.radio_name\nFROM\n\tpeople.contact c\nJOIN gear.radio r \nON\n\tc.radio_id = r.radio_id\nORDER BY\n\tc.freq_mhz;<\/pre><\/div>\n\n\n\n
ORDER BY<\/code> will, by default, place things in ascending<\/em> order:<\/p>\n\n\n\n
  freq_mhz  | radio_name \n------------+------------\n 146.500000 | Maverick\n 146.500000 | Maverick\n 146.500000 | Maverick\n 146.500000 | Maverick\n 147.500000 | DM32-UV\n 147.500000 | DM32-UV\n 147.500000 | DM32-UV\n 147.500000 | DM32-UV\n 446.000000 | FT3D\n 462.375000 | Maverick<\/pre><\/div>\n\n\n\n
From there, you can control the order and<\/em> the number of rows returned:<\/p>\n\n\n\n
SELECT\n\tc.freq_mhz,\n\tr.radio_name\nFROM\n\tpeople.contact c\nJOIN gear.radio r \nON\n\tc.radio_id = r.radio_id\nORDER BY\n\tc.freq_mhz DESC\nLIMIT 5;<\/pre><\/div>\n\n\n\n
I added DESC<\/code> after the column to tell it to go in descending<\/em> order. Then, using LIMIT 5<\/code> ensures that only five rows are returned:<\/p>\n\n\n\n
  freq_mhz  | radio_name \n------------+------------\n 462.375000 | Maverick\n 446.000000 | FT3D\n 147.500000 | DM32-UV\n 147.500000 | DM32-UV\n 147.500000 | DM32-UV<\/pre><\/div>\n\n\n\n
You can<\/em> use LIMIT<\/code> without an ORDER BY<\/code>but, again, you won’t be able to guarantee which five (or however many) rows you’ll see.<\/p>\n\n\n\n
That’s some of the very basics of querying PostgreSQL for you, but there’s so much more we didn’t<\/em> touch upon. As mentioned earlier, see my full guide<\/a> for more.<\/p>\n\n\n\n
As for what’s next, we’ll now take a look at data manipulation in PostgreSQL. By the end, you’ll know how to change and remove data from your PostgreSQL databases.<\/p>\n\n\n\n
How to modify\/manipulate data in PostgreSQL<\/h2>\n\n\n\n
As usual, make sure you’re connected to psql<\/code> before continuing: psql -d ham_shack -h localhost -U radiodev<\/code><\/p>\n\n\n\n
The UPDATE<\/code> statement in PostgreSQL<\/h3>\n\n\n\n
The UPDATE<\/code> statement syntax is extremely simple. You can only modify one table at a time, so you define the table you wish to change, then define the column(s) you’re changing. Finally, filter with a WHERE<\/code> clause:<\/p>\n\n\n\n
UPDATE\n\tpeople.operator\nSET\n\tlast_name = 'Smith'\nWHERE\n\tcallsign = 'GG7WXYZ';<\/pre><\/div>\n\n\n\n
<\/a>The last bit is the most important. You don’t have to use a WHERE<\/code> clause but, if we’d left it off this query, every value in the column last_name<\/code> would be changed to ‘Smith’. It’s not uncommon to do this intentionally, but it’s also very<\/em> easy to do so accidentally. <\/p>\n\n\n\n
So, BE CAUTIOUS! If your WHERE<\/code> clauses matches multiple rows in the table, multiple rows will be updated.<\/strong><\/p>\n\n\n\n
When you execute the query through psql<\/code>, you get a single line back – UPDATE 1<\/code>– letting you know what you did and how many rows it affected.<\/p>\n\n\n\n
If you want to update more than one column, simply add them to the SET<\/code> part of the statement:<\/p>\n\n\n\n
UPDATE \n\tpeople.operator\nSET\n\tfirst_name = 'Bob',\n\tlast_name = 'McCall'\nWHERE\n\tcallsign = 'VK39AA';<\/pre><\/div>\n\n\n\n
You can also add a FROM<\/code> clause to the query, with which you can then JOIN<\/code> to multiple tables. You’d do this to perform an UPDATE<\/code> based on other data within the database. However, you’re still only ever updating one table at a time. <\/p>\n\n\n\n
Finally, you can also use aliases for the table names – including the one being updated.<\/p>\n\n\n\n
The DELETE<\/code> statement in PostgreSQL<\/h3>\n\n\n\n
Let’s add a couple of rows, just so we can remove them later for a demonstration of how to delete data in PostgreSQL:<\/p>\n\n\n\n
INSERT\n\tINTO\n\tgear.antenna \n(antenna_name)\nVALUES\n('End fed half wave'),\n('Dipole'),\n('Fan');<\/pre><\/div>\n\n\n\n
Like UPDATE<\/code>, the DELETE<\/code> statement in PostgreSQL is a single-table operation.<\/strong> The syntax is quite simple as well:<\/p>\n\n\n\n
DELETE\nFROM\n\tgear.antenna\nWHERE\n\tantenna_name = 'Fan';<\/pre><\/div>\n\n\n\n
As with UPDATE<\/code>, if you leave off the WHERE<\/code> clause, wave goodbye to your data. When run, we get what we did, and the number of rows affected (similar to UPDATE<\/code>): DELETE 1<\/code><\/p>\n\n\n\n
But what if we wanted to get rid of the very first row in the antenna<\/code> table? We’d run:<\/p>\n\n\n\n
DELETE\nFROM\n\tgear.antenna \nWHERE\n\tantenna_id = 1;<\/pre><\/div>\n\n\n\n
…which should <\/em>return the error:<\/p>\n\n\n\n
ERROR:  update or delete on table \"antenna\" violates foreign key constraint \"radio_antenna_antenna_id_fkey\" on table \"radio_antenna\"\nDETAIL:  Key (antenna_id)=(1) is still referenced from table \"radio_antenna\".<\/pre><\/div>\n\n\n\n
Since we have referential integrity<\/a> in our database through the definition of foreign keys, we can’t just delete data that is in use; there’s a validation<\/em> before the DELETE<\/code> occurs.<\/p>\n\n\n\n
Let’s run one more DELETE<\/code> statement (just so we can see how more than one row is affected):<\/p>\n\n\n\n
DELETE\nFROM\n\tgear.antenna\nWHERE\n\tantenna_id > 5;<\/pre><\/div>\n\n\n\n
This returns DELETE 2<\/code>.<\/p>\n\n\n\n
You can also reference other tables in a DELETE<\/code> statement with the USING<\/code> clause. However, you’re again still only deleting from a single table<\/em>. The other tables are to provide a more sophisticated filtering mechanism based on data in the database.<\/p>\n\n\n\n
One final note on UPDATE<\/code> and DELETE<\/code>. In PostgreSQL, by default, when you modify or remove a row, you’re not really<\/em> doing either of those things. In the case of both statements, the previous row is marked as logically deleted<\/em>, and a new row is inserted into the table. No physical delete occurs.<\/p>\n\n\n\n
This is all part of the Multi-Version Concurrency Control (MVCC)<\/a>, the mechanism within PostgreSQL that makes it so that readers seldom block writers, and writers seldom block readers. A process called VACUUM<\/a><\/code> effectively ‘cleans up’ the logically-deleted rows, ultimately removing them from the system.<\/p>\n\n\n\n
From here, there’s just one more (somewhat optional) thing I want to teach: how to set up the Bluebox sample database in PostgreSQL. Let’s get into it.<\/p>\n\n\n\n
How to set up the Bluebox sample database in PostgreSQL (and why you should)<\/h2>\n\n\n\n
Ryan Booz<\/a> created a fantastic PostgreSQL sample database, Bluebox<\/a>. It represents a DVD rental kiosk (similar to the real one in the US, Redbox) and has a very sophisticated set of real data, tables, extensions, and more. It’s available as a dump that you can add to your own PostgreSQL cluster, or can be run as a container. <\/strong><\/p>\n\n\n\n
(In the container, pg_cron<\/a> (a scheduling utility) runs scripts that simulate a real load.)<\/em><\/p>\n\n\n\n
Before setting up Bluebox, I want to first show you how to clean up your PostgreSQL containers.<\/strong><\/p>\n\n\n\n
How to remove PostgreSQL containers<\/h3>\n\n\n\n
Containers are meant to live short lives. It’s normal to throw them out regularly and then start them back up again. Just remember, use volumes when you do so you can retain information once the container is gone.<\/p>\n\n\n\n
If you’re still logged in to psql,<\/code> run \\q<\/code>, then exit the Bash container with exit<\/code>.<\/p>\n\n\n\n
In PostgreSQL, you can only remove a container when it’s stopped, so step one is to stop the container<\/strong>:
docker stop StartingPostgreSQL<\/code><\/p>\n\n\n\n
With the container stopped, you can now remove it with docker rm StartingPostgreSQL<\/code>. This removes the container and everything<\/em> in it – the only exception being anything stored in the local folder that you used as a volume.<\/p>\n\n\n\n
The image will be retained unless you remove it too. To see the images on your machine, run docker images<\/code>. You should see one called postgres:latest<\/code> – the one we want to remove. To do so, use docker rmi postgres:latest.<\/code> <\/p>\n\n\n\n
And that’s it! Now, back to Bluebox.<\/p>\n\n\n\n
How to set up the Bluebox sample database in PostgreSQL<\/h3>\n\n\n\n
To set up Bluebox in PostgreSQL, first head to the official GitHub repo<\/a> if you haven’t already. Then, with whatever command line utility you’re using, navigate to the folder where you’re storing your Git repos, and clone the Bluebox one with git clone https:\/\/github.com\/ryanbooz\/bluebox-docker.git<\/code>.<\/p>\n\n\n\n
This will take several minutes, using around 338mb of storage. Once it’s done, change to the bluebox-docker<\/code> folder: cd bluebox-docker<\/code><\/p>\n\n\n\n
Then, it’s easy. Ryan even included a shell script: .\/start.sh<\/code><\/p>\n\n\n\n
The shell script walks you through selecting the version of PostgreSQL you need, and the port to install it to. If you’re still running the StartingPostgreSQL<\/code> container, it’ll be using port 5432, so stop the container or pick another port:<\/p>\n\n\n\n
Available versions: 14, 15, 16, 17, 18, 19-dev\nPostgres version [18]: 18\nHost port [5432]: \n\n----------------------------------------\nConfiguration:\n  Postgres version: 18\n  Host port:        5432\n  Project name:     bluebox-pg18\n----------------------------------------<\/pre><\/div>\n\n\n\n
Then, it will download the docker image, start the container, and load the database, all completely automatically. This step also takes several minutes on a good internet connection.<\/p>\n\n\n\n
When you’re done, you’ll have a running container with Bluebox in it. The roles for connecting are as follows:<\/a><\/p>\n\n\n\n
Role<\/th>Password<\/th>Purpose<\/th><\/tr><\/thead>
bb_app<\/td>app_password<\/td>Application queries<\/td><\/tr>
bb_admin<\/td>admin_password<\/td>Schema admin<\/td><\/tr>
postgres<\/td>password<\/td>Superuser (use sparingly!)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Ryan’s script even helpfully pops out a psql<\/code> connection string: psql -h localhost -p 5432 -U bb_admin -d bluebox<\/code><\/p>\n\n\n\n
And that’s it for the series! You have been introduced to PostgreSQL. Have fun!<\/p>\n\n\n\n
\n    
\n\n
Want to advance your PostgreSQL learning? Here’s what to read next on Simple Talk:<\/strong><\/p>\n\n\n\n
Learning PostgreSQL with Grant – the full series<\/a><\/p>\n\n\n\n
Ryan Booz’s PostgreSQL guides<\/a><\/p>\n\n\n\n
The full collection of PostgreSQL articles and guides on Simple Talk<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n
\n    
FAQs<\/h2>\n\n                        
1. How do you insert data into a PostgreSQL table?<\/h3>\n            
\n                
Use the INSERT INTO<\/code> statement, specifying the table name, an optional column list, and the values to add. If a column uses IDENTITY, you can omit it and let PostgreSQL auto-generate the value, or use the DEFAULT<\/code> keyword to skip it explicitly while still listing all columns.<\/p>\n            <\/div>\n                    
2. Can you insert multiple rows in a single PostgreSQL query?<\/h3>\n            
\n                
Yes. List multiple sets of values separated by commas within a single INSERT INTO ... VALUES<\/code> statement to add several rows at once.<\/p>\n            <\/div>\n                    
3. Why shouldn't you use SELECT * in PostgreSQL queries?<\/h3>\n            
\n                
SELECT *<\/code> retrieves every column in an undefined order, which can affect performance and readability. Explicitly naming columns gives you control over which data is returned and in what order.<\/p>\n            <\/div>\n                    
4. What's the difference between UPDATE and DELETE in PostgreSQL, and are they permanent?<\/h3>\n            
\n                
UPDATE<\/code> modifies existing column values, while DELETE<\/code> removes rows entirely \u2014 both operate on a single table at a time. Neither is truly immediate at the storage level: PostgreSQL’s MVCC system marks old rows as logically deleted and writes new ones, with the VACUUM<\/code> process later cleaning them up.<\/p>\n            <\/div>\n                    
5. Why does PostgreSQL block deleting a row referenced by a foreign key?<\/h3>\n            
\n                
PostgreSQL enforces referential integrity, so deleting a row that’s still referenced by another table (via a foreign key) will throw an error unless the dependent rows are removed or updated first.<\/p>\n            <\/div>\n                    
6. How do you control the order and number of rows returned in a PostgreSQL query?<\/h3>\n            
\n                
Use ORDER BY<\/code> to sort results (ascending by default, or DESC<\/code> for descending), and combine it with LIMIT<\/code> to cap the number of rows returned. Without ORDER BY<\/code>, row order isn’t guaranteed even when using LIMIT<\/code>.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
Learn how to insert, query, update, and delete data in PostgreSQL with real examples covering INSERT, SELECT, JOIN, WHERE, and more.…<\/p>\n","protected":false},"author":221792,"featured_media":105920,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143523,53,143534],"tags":[4168,4170,159410,158978,4150,4252],"coauthors":[6785],"class_list":["post-112596","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-featured","category-postgresql","tag-database","tag-database-administration","tag-gettingstartedpostgresgrant","tag-postgresql","tag-sql","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/112596","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221792"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=112596"}],"version-history":[{"count":13,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/112596\/revisions"}],"predecessor-version":[{"id":113988,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/112596\/revisions\/113988"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/105920"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=112596"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=112596"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=112596"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=112596"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}