public inbox for [email protected]
help / color / mirror / Atom feedDefinging columns for INSERT statements
8+ messages / 3 participants
[nested] [flat]
* Definging columns for INSERT statements
@ 2024-06-12 21:11 Rich Shepard <[email protected]>
0 siblings, 3 replies; 8+ messages in thread
From: Rich Shepard @ 2024-06-12 21:11 UTC (permalink / raw)
To: [email protected]
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
( ...
TIA,
Rich
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Defining columns for INSERT statements
@ 2024-06-12 21:36 Rich Shepard <[email protected]>
parent: Rich Shepard <[email protected]>
2 siblings, 0 replies; 8+ messages in thread
From: Rich Shepard @ 2024-06-12 21:36 UTC (permalink / raw)
To: [email protected]
On Wed, 12 Jun 2024, Rich Shepard wrote:
> VALUES (nextval('people_person_nbr_seq'), ...
Correction.
Rich
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Definging columns for INSERT statements
@ 2024-06-12 22:12 David G. Johnston <[email protected]>
parent: Rich Shepard <[email protected]>
2 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2024-06-12 22:12 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: [email protected]
On Wed, Jun 12, 2024 at 2:11 PM Rich Shepard <[email protected]>
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
>
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 J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Definging columns for INSERT statements
@ 2024-06-12 22:43 Rich Shepard <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Rich Shepard @ 2024-06-12 22:43 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Definging columns for INSERT statements
@ 2024-06-12 23:05 Adrian Klaver <[email protected]>
parent: Rich Shepard <[email protected]>
2 siblings, 1 reply; 8+ messages in thread
From: Adrian Klaver @ 2024-06-12 23:05 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; [email protected]
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',
'[email protected]', DEFAULT);
If they are not the column defaults then just supply the literal value.
>
> TIA,
>
> Rich
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Definging columns for INSERT statements
@ 2024-06-12 23:24 Rich Shepard <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Rich Shepard @ 2024-06-12 23:24 UTC (permalink / raw)
To: [email protected]
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',
> '[email protected]', 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Definging columns for INSERT statements
@ 2024-06-12 23:38 Adrian Klaver <[email protected]>
parent: Rich Shepard <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Adrian Klaver @ 2024-06-12 23:38 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; [email protected]
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',
>> '[email protected]', 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
[email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Definging columns for INSERT statements
@ 2024-06-13 12:37 Rich Shepard <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Rich Shepard @ 2024-06-13 12:37 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-06-13 12:37 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-12 21:11 Definging columns for INSERT statements Rich Shepard <[email protected]>
2024-06-12 21:36 ` Re: Defining columns for INSERT statements Rich Shepard <[email protected]>
2024-06-12 22:12 ` David G. Johnston <[email protected]>
2024-06-12 22:43 ` Rich Shepard <[email protected]>
2024-06-12 23:05 ` Adrian Klaver <[email protected]>
2024-06-12 23:24 ` Rich Shepard <[email protected]>
2024-06-12 23:38 ` Adrian Klaver <[email protected]>
2024-06-13 12:37 ` Rich Shepard <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox