I recently had to do a data migration where I needed to populate a new table with some data from another table.
Rather than perform some sort of export, transform, and import, it was much easier to do this via a select query within Postgres itself.
Although you can use the star operator, it’s safer and smarter to name fields specifically, in order. In addition, you should manually verify your select query first.
In my case, there were a set of a few fields I wanted to insert, and converted into a select query, looked like this:
SELECT date_added, description, last_updated, name, type FROM information;
This gave me all of the fields I wanted, and I verified that the data looked good.
Next, I added this to an insert query:
INSERT INTO information_subset (date_added, description, last_updated, name, type) SELECT date_added, description, last_updated, name, type FROM information;
Although it’s something that seems like it might be complex, inserting into one table using data from another is quite simple in Postgres and most other databases.