PostgreSQL – Difference Between INSERT … SELECT and SELECT INTO

insertpostgresqlselect

Let's say I have a table item and an other one versionned_item. From time to time, I want to copy data from item to versionned_item. What I did is

   WITH
     subset AS (SELECT ...)
     INSERT INTO versionned_item SELECT ... FROM subset

But in the documentation I came across the SELECT INTO statement that seems to do the exact same thing so I am wondering if there are differences / good practices about those 2 kind of inserts.

Best Answer

SELECT INTO is usually used to select specific set of data into a table, esp., during scenarios when the data in the table is the priority and not the constraints. It automatically creates a table if there is no such table already. But, INSERT INTO is used when you already have a table that has specific defined constraints and need to add data from a different table.

Related Question