[SQL] ERROR: duplicate key violates unique constraint
Hi List, Its been a while since I worked with databases and I am running into the following which I have not been able to find the root cause for: I have the follow SQL statement: INSERT INTO reference VALUES (DEFAULT,'123','2',1); Which generates the following error: "ERROR: duplicate key violates unique constraint "reference_pkey"" And the table definition looks like: CREATE TABLE reference ( referencelist_nr serial unique, reference_text varchar(40) NOT NULL, reference_type integer NOT NULL, Topic_Id integer NOT NULL, PRIMARY KEY (referencelist_nr), FOREIGN KEY (Topic_Id) REFERENCES Topic (Topic_Id), FOREIGN KEY (reference_type) REFERENCES reference_type (reference_type_nr) ); It seems to me for some reason "DEFAULT" doesn't select the next SERIAL. If I run: INSERT INTO reference VALUES (14,'123','2',1); 14 being the next free integer it works fine. I have been adding data with "COPY" into this tabledoes that break something? Your help/tips/insights are appreciated. Solaris 10/Postgresql 8.1.4 Regards, ..Remco
[SQL] unsubscribe
unsubscribe _ FREE pop-up blocking with the new MSN Toolbar - get it now! http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] ERROR: duplicate key violates unique constraint
Use INSERT INTO reference(reference_text, reference_type, Topic_Id) VALUES (DEFAULT,'123','2',1); instead. Since referencelist_nr is a serial PostgreSQL will provide the next free integer itself. >>> "remco lengers" <[EMAIL PROTECTED]> 2007-03-20 10:11 >>> Hi List, Its been a while since I worked with databases and I am running into the following which I have not been able to find the root cause for: I have the follow SQL statement: INSERT INTO reference VALUES (DEFAULT,'123','2',1); Which generates the following error: "ERROR: duplicate key violates unique constraint "reference_pkey"" And the table definition looks like: CREATE TABLE reference ( referencelist_nr serial unique, reference_text varchar(40) NOT NULL, reference_type integer NOT NULL, Topic_Id integer NOT NULL, PRIMARY KEY (referencelist_nr), FOREIGN KEY (Topic_Id) REFERENCES Topic (Topic_Id), FOREIGN KEY (reference_type) REFERENCES reference_type (reference_type_nr) ); It seems to me for some reason "DEFAULT" doesn't select the next SERIAL. If I run: INSERT INTO reference VALUES (14,'123','2',1); 14 being the next free integer it works fine. I have been adding data with "COPY" into this tabledoes that break something? Your help/tips/insights are appreciated. Solaris 10/Postgresql 8.1.4 Regards, ..Remco
[SQL] unsubscribe
unsubscribe [EMAIL PROTECTED] _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] ERROR: duplicate key violates unique constraint
remco lengers wrote:
Hi List,
Its been a while since I worked with databases and I am running into the
following which I have not been able to find the root cause for:
I have the follow SQL statement:
INSERT INTO reference VALUES (DEFAULT,'123','2',1);
Which generates the following error:
"ERROR: duplicate key violates unique constraint "reference_pkey""
And the table definition looks like:
CREATE TABLE reference (
referencelist_nr serial unique,
reference_text varchar(40) NOT NULL,
reference_type integer NOT NULL,
Topic_Id integer NOT NULL,
PRIMARY KEY (referencelist_nr),
FOREIGN KEY (Topic_Id) REFERENCES Topic (Topic_Id),
FOREIGN KEY (reference_type) REFERENCES reference_type (reference_type_nr)
);
It seems to me for some reason "DEFAULT" doesn't select the next SERIAL.
It does, but if you've been manually setting that value then the SERIAL
doesn't know about it. Sequences are fast and light, but they can be so
because they don't have to check the table.
I have been adding data with "COPY" into this tabledoes that break
something?
There you go. After a bulk load you'll want to run something like:
SELECT setval('reference_referencelist_nr_seq', (SELECT
max(referencelist_nr)+1 FROM reference));
Have a look at pg_dump to see how it does this sort of thing.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] Select and Count
Hello
I have a postgresql table and i do a select via ASP
strSQL = "SELECT * FROM " & MioTabella & " WHERE TRIM(date_inserted) >= '" &
datainizio & "' AND TRIM(date_inserted) <= '" & datafine & "'"
oRs.open strSQL,oConn,3
schede = oRs.RecordCount
Do until oRs.EOF
sch_sin = cint(sch_sin) + cint(oRs("SCHE_SINGOLA").Value)
oRs.movenext
Loop
Then inside my asp page in the body i write the result. and this works
<%
Response.write schede
%>
Then i want to get the SUM of the values inserted in the SCHE_SINGOLA column
(defined as text)
<%
Response.Write sch_sin
%>
But here i get a type mismacth error..
What should i do?
Thanks
Shavonne Wijesinghe
www.studioform.it
Re: [SQL] Select and Count
On Tue, 20 Mar 2007, Shavonne Marietta Wijesinghe wrote:
Hello
I have a postgresql table and i do a select via ASP
strSQL = "SELECT * FROM " & MioTabella & " WHERE TRIM(date_inserted) >= '" & datainizio & "' AND
TRIM(date_inserted) <= '" & datafine & "'"
oRs.open strSQL,oConn,3
schede = oRs.RecordCount
Do until oRs.EOF
sch_sin = cint(sch_sin) + cint(oRs("SCHE_SINGOLA").Value)
oRs.movenext
Loop
I know nothing about ASP, but it looks like you are doing a SUM of an int
with, maybe, an array (don't know how ASP defines oRs().Value output).
Keep in mind that you are pulling all the columns of that table (as you
used a * in the column selection).
Just a guess, nothing more then that.
--
21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18
-
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática| '@' || 'unl.edu.ar';
Universidad Nacional| DBA, Programador,
del Litoral | Administrador
-
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] unsubscribe
Stefan Ionita wrote: > unsubscribe Marc, I would think that these list don't have the "administrativia" flag set in the configuration. This is in the "moderation" class. The help for this option is: If the administrivia setting is set to "yes", posted messages which match the patterns in the admin_body and admin_headers settings will be sent to the moderators for review. These two settings are used to keep commands, such as "remove" or "unsubscribe", from being posted to a mailing list. If the administrivia setting is set to "no", the admin_body and admin_headers settings will have no effect. The pgsql-es-ayuda list had it set to "no" (I just changed it), and it said This value was determined by the DEFAULT settings. So maybe the other lists are missing this setting. I remember Bruce complaining about this not long ago (and threatening with Mailman!?) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Select and Count
Don't know any ASP but maybe Response.Write only accepts strings. In
that case you would need to do something like
<%
Response.Write cStr(sch_sin)
%>
Maybe you can let the database do the summing:
<% option explicit %>
<%
Dim strSQL
Dim oRs
Dim oConn
oConn = ... 'Fill this in for yourself
strSQL = "SELECT COUNT(SCHE_SINGOLA) COU, SUM(SCHE_SINGOLA) SU FROM "
& MioTabella & " WHERE TRIM(date_inserted) >= '" & datainizio & "' AND
TRIM(date_inserted) <= '" & datafine & "'"
oRs.open strSQL,oConn,3
If oRs.RecordCount = 1 Then
Response.Write(oRs("COU"))
Response.Write(oRs("SU"))
Else
Response.Write("Something went wrong")
End If
%>
>>> Martin Marques 2007-03-20 12:55 >>>
On Tue, 20 Mar 2007, Shavonne Marietta Wijesinghe wrote:
> Hello
>
> I have a postgresql table and i do a select via ASP
>
> strSQL = "SELECT * FROM " & MioTabella & " WHERE TRIM(date_inserted)
>= '" & datainizio & "' AND TRIM(date_inserted) <= '" & datafine & "'"
>
> oRs.open strSQL,oConn,3
> schede = oRs.RecordCount
>
> Do until oRs.EOF
> sch_sin = cint(sch_sin) + cint(oRs("SCHE_SINGOLA").Value)
> oRs.movenext
> Loop
I know nothing about ASP, but it looks like you are doing a SUM of an
int
with, maybe, an array (don't know how ASP defines oRs().Value output).
Keep in mind that you are pulling all the columns of that table (as you
used a * in the column selection).
Just a guess, nothing more then that.
--
21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18
-
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática| '@' || 'unl.edu.ar';
Universidad Nacional| DBA, Programador,
del Litoral | Administrador
-
---(end of
broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[SQL] ERROR: invalid byte sequence for encoding "UTF8": 0x92
Hi, I am using postgres 8.2.3. I have recently converted my database from sql-ascii to UTF8. I have a portal which calls a perl program to insert the data into the database. While inserting, I am getting an error message DBD::Pg::st execute failed: ERROR: invalid byte sequence for encoding "UTF8": 0x92 [for Statement "INSERT INTO longdescs (bug_id, who, bug_when, thetext, isprivate) How can I set the client encoding in perl. Can somebody help me? Regards skarthi _ Get a FREE Web site, company branded e-mail and more from Microsoft Office Live! http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] ERROR: invalid byte sequence for encoding "UTF8": 0x92
I am using postgres 8.2.3. I have recently converted my database from sql-ascii to UTF8. I have a portal which calls a perl program to insert the data into the database. While inserting, I am getting an error message DBD::Pg::st execute failed: ERROR: invalid byte sequence for encoding "UTF8": 0x92 [for Statement "INSERT INTO longdescs (bug_id, who, bug_when, thetext, isprivate) Try to modify client_encoding in postgresql.conf to utf8 and restart your db. regards Claus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Substitute
Hello everybody somebody knows, how I can substitute in a query if a field contains a character that I want to be shown with other character? for example if I have a \n I want to shows me a ~ in the result of the select thanks in advanced!! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Substitute
Judith написа: > Hello everybody > > somebody knows, how I can substitute in a query if a field contains a > character that I want to be shown with other character? > > for example if I have a \n I want to shows me a ~ in the result of the > select > Look for 'replace' here - http://www.postgresql.org/docs/8.2/static/functions-string.html. -- Milen A. Radev ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Substitute
On Tue, Mar 20, 2007 at 05:54:47PM -0600, Judith wrote: > somebody knows, how I can substitute in a query if a field contains a > character that I want to be shown with other character? > > for example if I have a \n I want to shows me a ~ in the result of the > select One way is with translate(). See "String Functions and Operators" in the documentation for more information. http://www.postgresql.org/docs/8.2/interactive/functions-string.html -- Michael Fuhr ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [ADMIN] [SQL] ERROR: invalid byte sequence for encoding "UTF8": 0x92
On Tue, Mar 20, 2007 at 09:06:25PM +0100, Claus Guttesen wrote: > >DBD::Pg::st execute failed: ERROR: invalid byte sequence for encoding > >"UTF8": 0x92 > >[for Statement "INSERT INTO longdescs (bug_id, who, bug_when, thetext, > >isprivate) > > Try to modify client_encoding in postgresql.conf to utf8 and restart your > db. The data is apparently not UTF-8 so client_encoding needs to be set to something else. 0x92 is a hint that the encoding might be Windows-1252 or Windows-1250 since that byte represents the right single quotation mark in those encodings; that's a common character in data that originated in Windows. Also, client_encoding can be set by the client without having to restart the backend. -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] unsubscribe
unsubscribe Stefan Ionita ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] unsubscribe
unsubscribe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
