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"

      id   unique

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.


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'

Not the answer you're looking for? Browse other questions tagged or ask your own question.