POSTGRESQL Query and insert based on results

All we need is an easy explanation of the problem, so here it is.

In this case I’m trying to perform a SELECT based query followed by an insert of new records into the same table based on those results. For example a simple table:

Table "samples"

Fields: 
      id   unique
      name 
      locations
      randoma
      randomb
      randomc

To be clear my objective from the selection is to create a new sample with a given name and id for each location, but the rest of the fields can be left to default. This is the selection portion:

SELECT locations FROM samples WHERE name = 'example';

The command chain below works for a single record, but I need the query and creation to be iterative. Aforementioned attempt:

INSERT INTO samples (nextval('my_sequence'), 'newsample', (SELECT locations FROM samples WHERE name = 'example');

I’ve haven’t found a solution yet and would appreciate any admonishment on this PSQL simplicity.

I realize that this is a poor example in terms of database normalization, but it’s merely for effect.

Best regards and thanks in advance.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Your insert syntax is incorrect, it should look like insert into <table> select <something> from <table> — not like insert into <table> (something, something, (select .... And don’t sprinkle parentheses at random — they are meaningful.

INSERT INTO samples 
  SELECT nextval('my_sequence'), 'newsample', locations 
  FROM samples WHERE name = 'example'

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply