Search Postgresql Archives

Re: Inserting rows containing composite foreign keys

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Nelson Green, 25.11.2013 23:01:
> Hello,
> When inserting a record into the jobs table that references projects by name, do I have to query the projects table twice, 
> once to get the funding source number, and once to get the project sequence number, even though both results will 
> return the same row? Or put another way, is there a way to insert a row into the jobs table without having to 
> perform two sub-queries for the same row, thus avoiding this:
> 
> INSERT INTO jobs
>    VALUES ((SELECT fundsrc_number FROM projects
>             WHERE project_name = 'proj1-1'),
>            (SELECT project_seq FROM projects
>             WHERE project_name = 'proj1-1'),
>             1, 'job1-1.1', 'first project 1-1 job');
> 

Use an INSERT based on a SELECT, not based on VALUES:

  INSERT INTO projects (fundsrc_number, project_seq, project_name, project_desc)
  SELECT fundsrc_number, 1, 'proj1-1', 'first source01 project'
  FROM fundsrc
  WHERE fundsrc_name IN ('source01', 'source02');

  INSERT INTO jobs (fundsrc_number, project_seq, job_seq, job_name, job_desc)
  SELECT fundsrc_number, project_seq, 1, 'job1-1.1', 'first project 1-1 job'
  FROM projects
  WHERE project_name = 'proj1-1';


Note that it's good coding style to always specify the columns in an INSERT statement. 
It makes your statements more robust against changes.





-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux