That makes it possible to do something like this: The RETURNING 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. When you run a DELETE statement, the RETURNING clause will show you the values for the row or rows that were removed from the table. Then, you can get the value, or values for multi-row inserts, that were generated.įor an UPDATE statement, the RETURNING clause will return the new values for the row, especially useful if you’ve done calculations on a column or columns to see the values that resulted. So, in the example of an INSERT, you wouldn’t see anything other than what you supplied, except where there are defaults such as a sequence number on an identity column. Basically, this returns as a result set, the data that was created or modified. One piece of behavior that’s common across all the standard data manipulation commands is the RETURNING clause. In addition to these behaviors, you also have the ONLY clause to deal with inheritance. Then I used the WHERE clause to define the join criteria between the tables, and the filtering criteria for the band_name. So here I used an alias on both the antennabands table and the bands table. ![]() The core behavior of INSERT is very much as you would expect coming from a SQL Server background: We may as well start by adding data to a table. The rest of the code from this article is in the 09_DataManipulation folder. ![]() The objects and database you will need can be created/reset using the CreateDatabase.sql script, then adding sample data using the SampleData.sql script. If you wish to execute the code or look at the data structures, the code is in my ScaryDBA/LearningPostgreSQL repository here. In the sample database I’ve created as a part of this ongoing series, I created a couple of schemas and organized my tables within them. However, as with so much of what I’ve learned in PostgreSQL, there are quite a few interesting wrinkles that are different to my “SQL Server” eyeballs. PostgreSQL makes use of standard SQL for operations like INSERT, UPDATE and DELETE. After all, a database is only useful if there’s information stored within. Now, it’s time to put some of that information to work and begin the process of manipulating data within the database. So far in the series I’ve shown how to create databases, tables, constraints, indexes and schema.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |