Subject: Definging columns for INSERT statements

From: Rich Shepard <[email-masked]>
Date: Wed, 12 Jun 2024 14:11:30 -0700 (PDT)

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

---

From: Rich Shepard <[email-masked]>
Date: Wed, 12 Jun 2024 14:36:51 -0700 (PDT)

On Wed, 12 Jun 2024, Rich Shepard wrote:

> VALUES (nextval('people_person_nbr_seq'), ...

Correction.

Rich

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 12 Jun 2024 15:12:53 -0700

> 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.

---

From: Rich Shepard <[email-masked]>
Date: Wed, 12 Jun 2024 15:43:12 -0700 (PDT)

> can appear in the optional parentheses after the table name.

David,

I've not used nextval() before which is why I asked.

Thanks,

Rich

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 12 Jun 2024 16:05:21 -0700

> 'Contact',company_nbr,loc_nbr,direct_phone,cell_phone,email,active 

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-masked]', DEFAULT);

If they are not the column defaults then just supply the literal value.

-- 
Adrian Klaver
[email-masked]

---

From: Rich Shepard <[email-masked]>
Date: Wed, 12 Jun 2024 16:24:56 -0700 (PDT)

> 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

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 12 Jun 2024 16:38:54 -0700

On 6/12/24 16:24, Rich Shepard wrote:
>> (person_nbr, lname, fname, job_title, company_nbr,loc_nbr, 
>> direct_phone, cell_phone,email, active)
>> VALUES

The 'Examples' section at the bottom of this page:

is a useful resource.

---

From: Rich Shepard <[email-masked]>
Date: Thu, 13 Jun 2024 05:37:17 -0700 (PDT)

Adrian,

That's good to know. Thank you.

Regards,

Rich