Обсуждение: Definging columns for INSERT statements
I have > 100 rows to add to a table using INSERT INTO statements. I want the
PK to be the next value in the sequence. Would this be the appropriate
syntax for the columns to be entered?
INSERT INTO people (person_nbr DEFAULT('people_person_nbr_seq'),lname,fname,job_title DEFAULT
'Contact',company_nbr,loc_nbr,direct_phone,cell_phone,email,activeDEFAULT('true')) VALUES
( ...
TIA,
Rich
On Wed, 12 Jun 2024, Rich Shepard wrote:
> VALUES (nextval('people_person_nbr_seq'), ...
Correction.
Rich
On Wed, Jun 12, 2024 at 2:11 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:
I have > 100 rows to add to a table using INSERT INTO statements. I want the
PK to be the next value in the sequence. Would this be the appropriate
syntax for the columns to be entered?
The whole point of the server is to parse text and tell you if it has syntax errors and then, if not, execute what you gave it.
INSERT INTO people (person_nbr DEFAULT('people_person_nbr_seq'),lname,fname,job_title DEFAULT 'Contact',company_nbr,loc_nbr,direct_phone,cell_phone,email,active DEFAULT('true')) VALUES
Not sure where you got the idea that something besides a plain column name can appear in the optional parentheses after the table name.
David J.
On Wed, 12 Jun 2024, David G. Johnston wrote:
>> INSERT INTO people (person_nbr
>> DEFAULT('people_person_nbr_seq'),lname,fname,job_title DEFAULT
>> 'Contact',company_nbr,loc_nbr,direct_phone,cell_phone,email,active
>> DEFAULT('true')) VALUES
>>
>
> https://www.postgresql.org/docs/current/sql-insert.html
>
> Not sure where you got the idea that something besides a plain column name
> can appear in the optional parentheses after the table name.
David,
I've not used nextval() before which is why I asked.
Thanks,
Rich
On 6/12/24 14:11, Rich Shepard wrote:
> I have > 100 rows to add to a table using INSERT INTO statements. I want
> the
> PK to be the next value in the sequence. Would this be the appropriate
> syntax for the columns to be entered?
>
> INSERT INTO people (person_nbr
> DEFAULT('people_person_nbr_seq'),lname,fname,job_title DEFAULT
> 'Contact',company_nbr,loc_nbr,direct_phone,cell_phone,email,active
> DEFAULT('true')) VALUES
> ( ...
Assuming 'people_person_nbr_seq' is the sequence attached to person_nbr
and the other DEFAULTs are the column defaults then the syntax would be:
INSERT INTO people
(person_nbr, lname, fname, job_title, company_nbr,loc_nbr, direct_phone,
cell_phone,email, active)
VALUES
(DEFAULT, 'klaver', 'adrian', DEFAULT, 1, 1, '555-1234', '555-4321',
'adrian.klaver@aklaver.com', DEFAULT);
If they are not the column defaults then just supply the literal value.
>
> TIA,
>
> Rich
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On Wed, 12 Jun 2024, Adrian Klaver wrote: > Assuming 'people_person_nbr_seq' is the sequence attached to person_nbr > and the other DEFAULTs are the column defaults then the syntax would be: > > INSERT INTO people > (person_nbr, lname, fname, job_title, company_nbr,loc_nbr, direct_phone, > cell_phone,email, active) > VALUES > (DEFAULT, 'klaver', 'adrian', DEFAULT, 1, 1, '555-1234', '555-4321', > 'adrian.klaver@aklaver.com', DEFAULT); > > If they are not the column defaults then just supply the literal value. Adrian, No matter how many postgres docs and web pages I read I didn't find an example as explicit as yours. Columns other than the person_nbr were not defined with defaults and I expected to enter them for each row. Many thanks for another valuable lesson. Best regards, Rich
On 6/12/24 16:24, Rich Shepard wrote: > On Wed, 12 Jun 2024, Adrian Klaver wrote: > >> Assuming 'people_person_nbr_seq' is the sequence attached to person_nbr >> and the other DEFAULTs are the column defaults then the syntax would be: >> >> INSERT INTO people >> (person_nbr, lname, fname, job_title, company_nbr,loc_nbr, >> direct_phone, cell_phone,email, active) >> VALUES >> (DEFAULT, 'klaver', 'adrian', DEFAULT, 1, 1, '555-1234', '555-4321', >> 'adrian.klaver@aklaver.com', DEFAULT); >> >> If they are not the column defaults then just supply the literal value. > > Adrian, > > No matter how many postgres docs and web pages I read I didn't find an > example as explicit as yours. Columns other than the person_nbr were not > defined with defaults and I expected to enter them for each row. The 'Examples' section at the bottom of this page: https://www.postgresql.org/docs/current/sql-insert.html is a useful resource. > > Many thanks for another valuable lesson. > > Best regards, > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, 12 Jun 2024, Adrian Klaver wrote: > The 'Examples' section at the bottom of this page: > https://www.postgresql.org/docs/current/sql-insert.html > is a useful resource. Adrian, That's good to know. Thank you. Regards, Rich