Shulou(Shulou.com)06/01 Report--

Editor to share with you how to achieve PostgreSQL temporary table, I hope you will learn something after reading this article, let's discuss it together!

When it comes to temporary tables, students who use MYSQL may not be very common, especially in the Internet field, where it is flawed to use temporary tables in the master-slave replication environment of MYSQL (this is also involved in the program, which is more complicated to explain). Today's topic is Postgresql's temporary table, Postgresql's temporary table itself is isolated, unlike ORACLE, PostgreSQL's temporary table itself is more thorough, after the failure of SESSION, the definition of the table will disappear, ORACLE is not, the definition of the table will not disappear. This is also the difference between the two databases on temporary tables.

We can download and do a test, and we open two windows.

Besides that, we're opening a window.

First of all, it is proved that the temporary tables in each session are independent and cannot be seen in other SESSION.

In addition, the temporary table in PostgreSQL has some convenient settings that specify when the temporary table disappears or cleans up the data when it is created.

The relevant postgresql can be set in commit, for example

ON COMMIT DELETE ROWS;ON COMMIT DROP;ON COMMIT PRESERVE ROWS

These three represent different meanings, one is in SESSION, if the COMMIT deletes the rows in the temporary table directly, the DROP deletes the table directly after commit, and finally, even commit retains the table until the end of the session.

In addition, parallel scanning in POSTGRESQL is not valid for temporary tables. Another interesting thing is that if you create temporary tables and entity tables with the same name in the same transaction, you access and manipulate temporary tables first.

Another thing to discuss is how much temporary tables help us in complex transactions.

Temporary tables can reduce query complexity and performance problems caused by multi-table association.

For example, a temporary table can decompose the access and query to a large table during the program's quick call to the stored procedure, and store the intermediate results in the temporary table instead of associating multiple large tables. If we only need to query 1% of the records in the large table, at the same time, 1% or less of the data of the large table can be stored in the temporary table through conditions, and related operations such as join, aggregation, and so on It will greatly reduce the possibility of lock waiting, deadlock, and so on.

In addition, unlike some databases, PG's temporary table is created in the database you are currently operating on and is named with a t (in this case, the name of the physical storage space in the temporary table).

So making better use of history tables can raise your skills such as stored procedures and even programming to a higher level. of course, if you abuse history tables and use them in inappropriate scenarios, you will get half the result with twice the effort.

After reading this article, I believe you have a certain understanding of "how to achieve temporary tables in PostgreSQL". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.