{"id":97011,"date":"2023-06-12T10:19:05","date_gmt":"2023-06-12T10:19:05","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=97011"},"modified":"2024-04-16T19:18:34","modified_gmt":"2024-04-16T19:18:34","slug":"manipulating-data-in-postgresql-learning-postgresql-with-grant","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/manipulating-data-in-postgresql-learning-postgresql-with-grant\/","title":{"rendered":"Manipulating Data In PostgreSQL: Learning PostgreSQL with Grant"},"content":{"rendered":"

This is part of an ongoing series of post by Grant as he takes his vast knowledge of SQL Server and applies it to adding PostgreSQL and shares it with you so you can skip learn from his triumphs and mistakes. For more you can go to the Learning PostgreSQL with Grant<\/a> series home page<\/strong><\/em><\/p>\n\n

So far in the series I\u2019ve shown how to create databases, tables, constraints, indexes and schema. Now, it\u2019s time to put some of that information to work and begin the process of manipulating data within the database. After all, a database is only useful if there\u2019s information stored within. PostgreSQL makes use of standard SQL for operations like INSERT<\/code>, UPDATE<\/code> and DELETE<\/code>. However, as with so much of what I\u2019ve learned in PostgreSQL, there are quite a few interesting wrinkles that are different to my \u201cSQL Server\u201d eyeballs.<\/p>\n

In the sample database I\u2019ve created as a part of this ongoing series, I created a couple of schemas and organized my tables within them. If you wish to execute the code or look at the data structures, the code is in my ScaryDBA\/LearningPostgreSQL<\/code> repository here<\/a>. The objects and database you will need can be created\/reset using the\u00a0CreateDatabase.sql<\/code>\u00a0script, then adding sample data using the SampleData.sql<\/code> script. The rest of the code from this article is in the\u00a0 09_DataManipulation folder.<\/p>\n

INSERT<\/h2>\n

We may as well start by adding data to a table. The core behavior of INSERT<\/code> is very much as you would expect coming from a SQL Server background:<\/p>\n

INSERT\r\n\tINTO\r\n\tradio.radios\r\n(radio_name,\r\n\tmanufacturer_id,\r\n\tconnectortype_id,\r\n\tdigitalmode_id)\r\nVALUES\r\n('GD-88',\r\n7,\r\n3,\r\n2);<\/pre>\n
The basic behavior is pretty straightforward. You define the statement: INSERT INTO (<\/code>Note, INTO <\/code>is not optional in PostgreSQL.)<\/code>. Then you tell it which table you\u2019re addressing, including the schema: radio.radios<\/code>. Yeah, like SQL Server you can leave the schema off and the PostgreSQL engine will figure things out for you. Don\u2019t do that. It\u2019s a good practice, from the get-go, to define your tables including their schema. You avoid issues down the line by developing that habit early.<\/p>\n
Then you list the columns. Like how SQL Server code (and most RDBMS code) works, you can skip columns that have a default (or allow NULL<\/code> values). In this case, there is a radio_id<\/code> column that\u2019s a sequence, so I don\u2019t have to supply it in the column list. Then, you define the VALUES<\/code> as shown. The VALUES<\/code> clause is where you specify the values that will be set for the column when the row is inserted.<\/p>\n
If you wanted to add multiple rows at once, it\u2019s very similar syntax to what you\u2019d see in SQL Server.<\/p>\n
INSERT\r\nINTO\r\nradio.radios\r\n(radio_name,\r\nmanufacturer_id,\r\nconnectortype_id,\r\ndigitalmode_id)\r\nVALUES\r\n('FT300DR',\r\n1,\r\n4,\r\n1),\r\n\r\n--added a second row\r\n('IC-V86',\r\n2,\r\n2,\r\nNULL);<\/pre>\n
I simply added a comma delimited set of parentheses to the VALUES<\/code>. You can also see how I dealt with a NULL<\/code> value by simply using the defined key word.<\/p>\n
As with SQL Server, you can also do an INSERT<\/code> statement with the source of rows based on a SELECT<\/code>:<\/p>\n
INSERT\r\nINTO\r\nradio.radiobands \r\n(radio_id,\r\nband_id)\r\nSELECT\r\n9,\r\nband_id\r\nFROM\r\nradio.radiobands\r\nWHERE\r\nradio_id = 2;<\/pre>\n
In this case, the radio I added earlier has the same two bands as another radio, so I can use the values from that known radio to add to the radio.radiobands<\/code> table.<\/p>\n
Finally, you can use the default value that is defined for a column (or for a column that accepts NULL<\/code>), by using the key word DEFAULT<\/code>:<\/p>\n
INSERT\r\nINTO\r\nradio.connectortypes (connectorytype_id,\r\nconnectortype_name)\r\nVALUES\r\n(DEFAULT,\r\n'F-Type Male');<\/pre>\n
In this case, since connectortype_id<\/code> is managed by the identity<\/code> property, we automatically get a value, so using DEFAULT<\/code> let\u2019s us still list the column, but we don\u2019t have to supply a value (and in this case, we couldn\u2019t anyway).<\/p>\n
If there are default values for all the columns, you could write the INSERT<\/code> like this:<\/p>\n
INSERT\r\nINTO radio.connectorytypes\r\nDEFAULT VALUES;<\/pre>\n
Although, on this database, that will cause an error since there is no default value defined for the connectorytype_name<\/code>.<\/p>\n
Overall, this behavior is exactly what I would have expected. In fact, it\u2019s largely the same as what I\u2019m used to in SQL Server. There are some differences though.<\/p>\n
One small difference is that the INTO<\/code> keyword, has to be a part of the syntax. Whereas, you can cheat and leave that off in SQL Server. Personally, I tend to use it because that\u2019s how I learned to use T-SQL and I think the INTO<\/code> adds clarity. PostgreSQL enforces its use because that is part of the ANSI standard, which PostgreSQL follows more closely than most other database systems.<\/p>\n
Another useful feature that is not a part of SQL Server is the ability to use OVERRIDING<\/code> to add your own data rather than let the system generate it for you.<\/p>\n
For example, if wanted to specify an identity value for a column, rather than let the identity mechanism generate it for me, I could do this:<\/p>\n
INSERT INTO\r\nradio.bands\r\n(band_id,\r\nband_name,\r\nfrequency_start_khz,\r\nfrequency_end_khz,\r\ncountry_id)\r\n\r\nOVERRIDING SYSTEM VALUE\r\n\r\nVALUES (10,\r\n'6 Meters',\r\n50000,\r\n54000,\r\n1);<\/pre>\n
The OVERRIDING SYSTEM VALUE<\/code> clause lets me add my own value to the band_id<\/code> column, bypassing the identity property for that column. That can\u2019t be done within the INSERT<\/code> statement in T-SQL but instead requires changing a setting prior to running the INSERT<\/code>. This is clearly a lot easier.<\/p>\n
Finally, there\u2019s one really neat trick that you can\u2019t do in SQL Server, ON CONFLICT<\/code>:<\/p>\n
INSERT\r\nINTO\r\nradio.antenna \r\n(antenna_name,\r\nmanufacturer_id,\r\nconnectortype_id)\r\nVALUES ('rubber duck',\r\n2,\r\n2)\r\n\r\nON\r\nCONFLICT (antenna_name,\r\nmanufacturer_id)\r\nDO\r\nUPDATE\r\nSET\r\nconnectortype_id = excluded.connectortype_id;<\/pre>\n
Basically, this code will add the defined antenna, or it will update an existing antenna, but only for the row where we violated the unique index that exists on the table on the two columns, antenna_name and manufacturer_id. In short, it\u2019s a way to do a MERGE<\/code> statement without doing a MERGE<\/code> statement. I\u2019m not sure if, like MERGE<\/code> in SQL Server, there are performance implications. You can also use the clause DO NOTHING<\/code> to prevent a response to the conflict.<\/p>\n
This is most of the syntax for the INSERT<\/code> statement, but not everything. If you want to see more, you can see there are a few additional features here in the PostgreSQL INSERT statement documentatio<\/a>n.<\/p>\n
UPDATE<\/h2>\n
When it\u2019s time to change data in the database, you\u2019re going to use the UPDATE<\/code> statement, as you\u2019d expect:<\/p>\n
UPDATE\r\nradio.bands\r\nSET\r\nband_name = '70 CM'\r\nWHERE\r\nband_id = 2;<\/pre>\n
The statement is UPDATE<\/code>. You then supply the table, and yes, just as you can only add data to a single table at a time, you can only modify data to a single table at a time. The SET<\/code> command then lets you pick and choose which columns you\u2019re going to modify. Finally, the WHERE<\/code> clause is used to filter the data to only modify the row or rows, you\u2019re interested in.<\/p>\n
Leaving off the WHERE<\/code> clause will modify all data in the table.<\/p>\n
You can also use the FROM<\/code> clause to modify data between tables something like this:<\/p>\n
UPDATE\r\nradio.bands\r\nSET\r\nband_name = r.radio_name\r\nFROM\r\nradio.radios AS r\r\nWHERE\r\nband_id = r.radio_id;<\/pre>\n
One additional note on UPDATE<\/code>. Since it\u2019s possible to use table inheritance within PostgreSQL, an additional clause can be added to UPDATE<\/code> statements to ensure that only the table specified has data modified within it. (A discussion about table inheritance is beyond this article, but the PostgreSQL documentation has a straightforward explanation here<\/a>):<\/p>\n
UPDATE ONLY\r\nradio.bands\r\nSET\r\nband_name = r.radio_name\r\nFROM\r\nradio.radios AS r\r\nWHERE\r\nband_id = r.radio_id;<\/pre>\n
Except for the addition of the ONLY clause, this UPDATE is the same as the one above. However, now, if radio.bands<\/code> was inherited from another table, this statement ensures that only the specific table specified is affected.<\/p>\n
DELETE<\/h2>\n
With the DELETE<\/code> command, we finally have a bit more deviation from the standards, although, the standards are there as well. A simple statement like this will remove all data in a table:<\/p>\n
DELETE\r\nFROM\r\nradio.bands;<\/pre>\n
Just as with SQL Server, you can also use the TRUNCATE<\/code> command to remove data from a table and it is faster. TRUNCATE<\/code> does have limitations (permission and will not work with FOREIGN KEY<\/code> constraints), for example. In fact, if you attempt to execute this DELETE<\/code> statement you will get an error because it causes a foreign key constraint error.<\/p>\n
It does have benefits for larger updates such as removing all row versions without the VACUUM<\/code> process needing to execute (For more details on the VACCUM<\/code> process, check out Henrietta Dombrovskaya\u2019s post here<\/a>).<\/p>\n
If you want to get specific, you take advantage of the WHERE<\/code> clause:<\/p>\n
DELETE\r\nFROM\r\nradio.antenna\r\nWHERE\r\nantenna_id = 42;<\/pre>\n
That\u2019s about what I\u2019d expect. One point, just as you had to keep the INTO<\/code> clause for an INSERT<\/code>, you must use the FROM<\/code> keyword in DELETE<\/code>. And if you want to reference another table, you don\u2019t simply start writing JOIN<\/code> statements as I would in T-SQL. Instead, you must insert the USING<\/code> clause:<\/p>\n
DELETE\r\nFROM\r\nradio.antennabands AS ab\r\nUSING radio.bands AS b\r\nWHERE\r\nab.band_id = b.band_id\r\nAND b.band_name = '6 Meters';<\/pre>\n
So here I used an alias on both the antennabands<\/code> table and the bands<\/code> table. Then I used the WHERE<\/code> clause to define the join criteria between the tables, and the filtering criteria for the band_name<\/code>.<\/p>\n
In addition to these behaviors, you also have the ONLY<\/code> clause to deal with inheritance.<\/p>\n
The RETURNING Clause<\/h2>\n
One piece of behavior that\u2019s common across all the standard data manipulation commands is the RETURNING<\/code> clause. Basically, this returns as a result set, the data that was created or modified. So, in the example of an INSERT<\/code>, you wouldn\u2019t see anything other than what you supplied, except where there are defaults such as a sequence number on an identity<\/code> column. Then, you can get the value, or values for multi-row inserts, that were generated.<\/p>\n
For an UPDATE<\/code> statement, the RETURNING<\/code> clause will return the new values for the row, especially useful if you\u2019ve done calculations on a column or columns to see the values that resulted.<\/p>\n
When you run a DELETE<\/code> statement, the RETURNING<\/code> clause will show you the values for the row or rows that were removed from the table.<\/p>\n
The RETURNING<\/code> clause can be put to really interesting types of use, especially since you can use common table expressions (CTEs) with all these data manipulation queries. That makes it possible to do something like this:<\/p>\n
WITH addant AS\r\n(\r\n\/* create a new antenna row *\/\r\nINSERT\r\nINTO\r\nradio.antenna (antenna_name,\r\nmanufacturer_id,\r\nconnectortype_id)\r\nVALUES('Rubber duck',\r\n1,\r\n2)\r\nRETURNING antenna_id)\r\n\/*\r\ntake the new antenna_id and create\r\na new antennabands row \r\n*\/\r\nINSERT\r\nINTO\r\nradio.antennabands \r\n(antenna_id,\r\nband_id)\r\nSELECT\r\naa.antenna_id,\r\n1\r\nFROM\r\naddant AS aa;<\/pre>\n
The WITH<\/code> clause defines a rowset, addant<\/code>, which is just the RETURNING<\/code> value of the antenna_id<\/code> generated from the INSERT<\/code> statement that defines the CTE. I can then use that value to add another row to another table, all as part of a single statement. This opens up a lot of possibilities of stacking statement that can run as one single statement.<\/p>\n
MERGE<\/h2>\n
As in SQL Server, the MERGE<\/code> command gives you the ability to combine INSERT<\/code>, UPDATE<\/code> and DELETE<\/code> operations in various combinations into a single statement. When using MERGE<\/code>, you are going to be evaluating conditions to determine behaviors. This means you\u2019ll always have a target table where the actions are going to occur. In addition, you need to have an evaluation data source. This can be a table, or a set of tables defined in a sub-select, whatever you need to evaluate the necessary actions.<\/p>\n
From there, you can define WHEN MATCHED<\/code> for conditions that require a matched value, or WHEN NOT MATCHED<\/code> for those other conditions. The order you define them in, is the order in which they\u2019ll be evaluated.<\/p>\n
Worth noting, the MERGE<\/code> command is new to PostgreSQL 15.<\/p>\n
Here\u2019s an example. I\u2019ll build out some data in a temporary table, and then use the logic to add the data, update it, or delete it, based on information being passed:<\/p>\n
CREATE TEMPORARY TABLE radioupdates (radio_name varchar(100),\r\nmanufacturer_id int,\r\nconnectortype_id int,\r\ndelete_flag int);\r\nINSERT\r\nINTO\r\nradioupdates (radio_name,\r\nmanufacturer_id,\r\nconnectortype_id,\r\ndelete_flag)\r\nVALUES\r\n('UV5R',\r\n2,\r\n3,\r\n0),\r\n('UV5R',\r\n3,\r\n3,\r\n0),\r\n('UV5R',\r\n1,\r\n3,\r\n1);\r\n<\/pre>\n
Then I will use the following MERGE<\/code> statement to merge the values in the temp table into the radios table<\/p>\n
MERGE\r\nINTO\r\nradio.radios AS r\r\nUSING radioupdates AS ru\r\nON\r\nru.radio_name = r.radio_name\r\nAND ru.manufacturer_id = r.manufacturer_id\r\nWHEN NOT MATCHED THEN\r\nINSERT\r\nVALUES(DEFAULT,\r\nru.radio_name,\r\nru.manufacturer_id,\r\nNULL,\r\nru.connectortype_id,\r\nNULL)\r\nWHEN MATCHED\r\nAND ru.delete_flag = 0 THEN\r\nUPDATE\r\nSET\r\nconnectortype_id = ru.connectortype_id\r\nWHEN MATCHED\r\nAND ru.delete_flag = 1 THEN\r\nDELETE;<\/pre>\n
The trick is just to get the logic right. In my MERGE<\/code> statement, the logic is: if there are no matches based on the ON<\/code> criteria, WHEN NOT MATCHED<\/code>, I\u2019ll INSERT<\/code> the row. Then, if it matches, but it\u2019s not flagged to be a DELETE<\/code>, it does an UPDATE<\/code> operation on the values from the source. Otherwise, if it matches and it\u2019s flagged for deleting, it gets deleted.<\/p>\n
You can also specify DO NOTHING<\/code> for the outcome of evaluations. The ONLY<\/code> key word can be used to deal with inheritance as mentioned in the UPDATE<\/code> section. You can even use OVERRIDING<\/code> in the INSERT<\/code> clause. In short, most of the behaviors we\u2019ve gone over through this article are available in MERGE<\/code>.<\/p>\n
Just remember, the old approach was to just use the ON CONFLICT<\/code> clause to achieve an UPSERT<\/code> (UPDATE<\/code> or INSERT<\/code>) command. That method was not as powerful as MERGE<\/code> when you need the extra complexity, for example, removing data from the target.<\/p>\n
For complete details about the MERGE<\/code> statement, here is the link to the PostgreSQL documentation<\/a>.<\/p>\n
Conclusion<\/h2>\n
Manipulating data within PostgreSQL is one of the easiest things I\u2019ve learned so far. Mostly, broad strokes, it\u2019s the same as with SQL Server. While there are a few details different in some areas, mostly, this is the kind of behavior I expected. I really like how you can use common table expressions with these commands. I also like how RETURNING<\/code> works to allow you to do some really customized behaviors. Overall, there\u2019s quite a bit of useful functionality within PostgreSQL when it comes to directly manipulating data.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
So far in the series I\u2019ve shown how to create databases, tables, constraints, indexes and schema. Now, it\u2019s time to put some of that information to work and begin the process of manipulating data within the database. After all, a database is only useful if there\u2019s information stored within. PostgreSQL makes use of standard SQL……<\/p>\n","protected":false},"author":221792,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143534],"tags":[158977,158976,159066],"coauthors":[6785],"class_list":["post-97011","post","type-post","status-publish","format-standard","hentry","category-featured","category-postgresql","tag-learningpostgresqlwithgrant","tag-planetpostgresqlgrantfritchey","tag-postgresql-101-webinar-sidebar"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97011","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=97011"}],"version-history":[{"count":10,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97011\/revisions"}],"predecessor-version":[{"id":102165,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97011\/revisions\/102165"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=97011"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=97011"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=97011"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=97011"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}