Скачать или смотреть How to Conditionally Insert Rows in PostgreSQL with INSERT ... SELECT

Learn how to conditionally insert rows into a PostgreSQL table by using the `INSERT ... SELECT` statement effectively.
---
This video is based on the question https://stackoverflow.com/q/65162997/ asked by the user 'lpetrucci' ( https://stackoverflow.com/u/8685682/ ) and on the answer https://stackoverflow.com/a/65163027/ provided by the user 'Abelisto' ( https://stackoverflow.com/u/593144/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Postgres: Conditionally adding to table

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Conditionally Insert Rows in PostgreSQL

PostgreSQL is a powerful tool for database management, but it can be tricky when it comes to conditional operations, especially with inserts. One common issue developers face is the need to insert a row only when a certain condition is met. This can lead to confusion, particularly for those accustomed to using traditional control flow commands like IF...ELSE. In this post, we will explore how to achieve a conditional insert in PostgreSQL without relying on these constructs.

Understanding the Problem

You may find yourself in a situation where you want to insert a new record into a table, but only if certain criteria are not met. For example, in a voting system, you might want to ensure that a user can only vote once within a specific time frame. Here is a scenario laid out clearly:

Suppose you have a "Vote" table and you want to:

Insert a vote into the table for a user (with an ID of 1) and a server (with an ID of 1).

Prevent the insertion if that user has already cast a vote between two given dates.

Attempting to use procedural code such as IF...ELSE, as seen in the original user's query, will lead to a syntax error because PostgreSQL does not support IF statements in standard SQL for such cases. Instead, we can use a more efficient approach.

Solution: Using INSERT ... SELECT

The solution to this problem lies in the use of the INSERT ... SELECT statement combined with a WHERE NOT EXISTS clause. This allows you to check for the condition you need in a single query before deciding whether to insert the record or not. Here's how it works step by step:

Example Query

To conditionally insert a row only if the check passes, you can use the following SQL command:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the Query

INSERT INTO "Vote" ("authorId", "serverId"): This part specifies that you want to insert values for the columns authorId and serverId in the Vote table.

SELECT 1, 1: Here, we are preparing to insert the values for the authorId and serverId. In this case, we simply want to insert 1 for both.

WHERE NOT EXISTS (...): This is the crucial part where you check your condition:

Subquery: The subquery selects records from the Vote table to see if any vote (with authorId = 6667) exists between 2020-09-01 and 2020-10-01.

If this subquery does not return any results (i.e., the user has not voted in that timeframe), the main insert will proceed.

If the subquery finds a matching record, the insert will be skipped.

Conclusion

By using the INSERT ... SELECT statement along with WHERE NOT EXISTS, you can elegantly handle conditional inserts in PostgreSQL without resorting to procedural code or dealing with syntax errors. This approach is efficient and concise, allowing developers to maintain clean and understandable SQL commands.

If you find yourself needing to conditionally modify data in your PostgreSQL database, remember that leveraging the power of SQL can save you a lot of time and headache. Happy querying!