Обсуждение: [GENERAL] 9.6.1: INSERT with PK as serial
I have a table with the primary key type of serial. The initial data load has 40 lines, and each has a value for every column including sequential integers from 1 through 40 for the data. When I try to read it in using 'psql -d <database_name> -f <filename.sql>' I get a syntax error at the second word in the string that is column 2. The error message does not print the integer PK. If it matters, I have all 14 columns specified but not explicitly named in the INSERT INTO command. I've looked in the manual without learning how to INSERT data in a table with a serial PK and need to learn how to do this. Rich
On 17 January 2017 at 12:44, Rich Shepard <rshepard@appl-ecosys.com> wrote: > I have a table with the primary key type of serial. The initial data load > has 40 lines, and each has a value for every column including sequential > integers from 1 through 40 for the data. When I try to read it in using > 'psql -d <database_name> -f <filename.sql>' I get a syntax error at the > second word in the string that is column 2. The error message does not print > the integer PK. If it matters, I have all 14 columns specified but not > explicitly named in the INSERT INTO command. > > I've looked in the manual without learning how to INSERT data in a table > with a serial PK and need to learn how to do this. You may have more luck getting an answer if you include the statement causing the problem. If you're not specifying the column names, then the VALUES list must have the same number of values, in the same order as the columns defined on the table. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Tue, 17 Jan 2017, David Rowley wrote: > You may have more luck getting an answer if you include the statement > causing the problem. David, Here's an example: INSERT INTO companies VALUES (1,'AG Spray Inc.',,'PO Box 12129','Salem','OR','97309-0129','USA','503-371-7907','888-273-0937','info@agsprayinc.com',,'Chemicals','Opportunity'), and the associated error message: psql:companies.sql:1: ERROR: syntax error at or near "Spray" LINE 1: INSERT INTO companies VALUES (AG Spray Inc.,,PO Box 12129,Sa... > If you're not specifying the column names, then the VALUES list must > have the same number of values, in the same order as the columns > defined on the table. As I wrote in the original message, I did this. Rich
Rich Shepard <rshepard@appl-ecosys.com> writes:
> Here's an example:
> INSERT INTO companies VALUES
> (1,'AG Spray Inc.',,'PO Box
12129','Salem','OR','97309-0129','USA','503-371-7907','888-273-0937','info@agsprayinc.com',,'Chemicals','Opportunity'),
> and the associated error message:
> psql:companies.sql:1: ERROR: syntax error at or near "Spray"
> LINE 1: INSERT INTO companies VALUES (AG Spray Inc.,,PO Box 12129,Sa...
It looks like something deleted the quote marks. How are you entering
this SQL command, exactly?
Also, you can't just write double commas to leave out a item in the
value list. You could write DEFAULT there, ie
... VALUES (1,'AG Spray Inc.',DEFAULT,'PO Box 12129','Salem', ...
regards, tom lane
On Mon, 16 Jan 2017, Tom Lane wrote:
> It looks like something deleted the quote marks. How are you entering
> this SQL command, exactly?
Tom,
I noticed that, too. Here's the first line:
INSERT INTO companies
('comp_id','comp_name','addr1','addr2','city','comp_state','postcode','country','phone','fax','e_mail','url','industry','status','comment')
VALUES
(1,'AG Spray Inc.',' ','PO Box
12129','Salem','OR','97309-0129','USA','503-371-7907','888-273-0937','info@agsprayinc.com','','Chemicals','Opportunity',''),
Other than the comp_id PK column each column's data type is a delimited
string:
CREATE TABLE companies (
comp_id serial PRIMARY KEY,
comp_name varchar(64) NOT NULL,
addr1 varchar(64),
addr2 varchar(64),
city varchar(16),
comp_state char(2),
postcode varchar(9),
country varchar(12) DEFAULT 'USA' NOT NULL,
phone varchar(10),
fax varchar(10),
e_mail varchar(64),
url varchar(64),
industry varchar(24) NOT NULL
CONSTRAINT invalid_industry
CHECK (industry in ('Agriculture', 'Business, other', 'Chemicals',
'Energy', 'Law', 'Manufacturing', 'Mining', 'Municipalities',
'Ports/Marine Services', 'Transportation')),
status varchar(20) NOT NULL
CONSTRAINT invalid_status
CHECK (status in ('Client', 'Proposal submitted', 'Prospect',
'Referral', 'Opportunity', 'No further contact')),
comment text
);
> Also, you can't just write double commas to leave out a item in the
> value list. You could write DEFAULT there, ie
I tried with ,, and ,'', and ,' '. Same error each time. Also, listing
each column name does not fix the problem.
Thanks,
Rich
On 1/16/2017 4:30 PM, Rich Shepard wrote:
I noticed that, too. Here's the first line:
INSERT INTO companies
('comp_id','comp_name','addr1','addr2','city','comp_state','postcode','country','phone','fax','e_mail','url','industry','status','comment')
VALUES
(1,'AG Spray Inc.',' ','PO Box
12129','Salem','OR','97309-0129','USA','503-371-7907','888-273-0937','info@agsprayinc.com','','Chemicals','Opportunity',''),
the above isn't valid postgresql, as field names need to be in "doublequotes" not 'singlequotes'.
but thats not the INSERT statement your error logged...
psql:companies.sql:1: ERROR: syntax error at or near "Spray"
LINE 1: INSERT INTO companies VALUES (AG Spray Inc.,,PO Box 12129,Sa...
note no list of fields. note no PK value. AND no quotes around the values.
-- john r pierce, recycling bits in santa cruz
On 17 January 2017 at 13:30, Rich Shepard <rshepard@appl-ecosys.com> wrote:
> INSERT INTO companies
>
('comp_id','comp_name','addr1','addr2','city','comp_state','postcode','country','phone','fax','e_mail','url','industry','status','comment')
> VALUES
> (1,'AG Spray Inc.',' ','PO Box
>
12129','Salem','OR','97309-0129','USA','503-371-7907','888-273-0937','info@agsprayinc.com','','Chemicals','Opportunity',''),
single quotes are strings. double quotes may be used for identifiers.
In the column names list here you've mixed up the two.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On Monday, January 16, 2017, Rich Shepard <rshepard@appl-ecosys.com> wrote:
On Mon, 16 Jan 2017, Tom Lane wrote:It looks like something deleted the quote marks. How are you entering
this SQL command, exactly?
Tom,
I noticed that, too. Here's the first line:
INSERT INTO companies
('comp_id','comp_name','addr1','addr2','city','comp_state',' postcode','country','phone','f ax','e_mail','url','industry', 'status','comment')
VALUES
(1,'AG Spray Inc.',' ','PO Box
12129','Salem','OR','97309-0129','USA','503-371-7907','888- 273-0937','info@agsprayinc.com ','','Chemicals','Opportunity' ,''),
Identifiers, if quoted at all, must quoted using double-quotes. "comp_id","comp_name"...
David J.
On Mon, 16 Jan 2017, John R Pierce wrote: > the above isn't valid postgresql, as field names need to be in "doublequotes" > not 'singlequotes'. John, I thought I had changed them from single to double, yet missed seeing that I did not do so. Mea culpa! I know better than that. That fixed all but one error which I'll address. Thanks, Rich
On Monday, January 16, 2017, Rich Shepard <rshepard@appl-ecosys.com> wrote:
I have a table with the primary key type of serial. The initial data load
has 40 lines, and each has a value for every column including sequential
integers from 1 through 40 for the data.
Without any special effort on your part the first 40 records you try to insert using the default sequence are now going to fail with duplicate key errors.
David J.
On Mon, 16 Jan 2017, David G. Johnston wrote: > Without any special effort on your part the first 40 records you try to > insert using the default sequence are now going to fail with duplicate key > errors. David, Thank you. I added them while thrashing around looking for the source of the problem. It's now fixed. Much appreciated, Rich