- changed status to closed
insert error
hello;
i am getting the following error. I'm not familiar with libzdb and can't get past the problem.
Version: latest
OS: SuSE linux 12 x64 sp5
Compile:
aws> ./configure --without-sqlite --without-mysql --without-oracle --enable-optimized --enable-protected --with-postgresql=/opt/postgresql-14.1/bin/pg_config
aws>make
aws>sudo make install
Database: PostgreSQL 15
Code;
....
sprintf(datatarih,"'%04i-%02i-%02i %02i:%02i:00'",yil,ay,gun,saat,dakika);
sprintf(sql1,"INSERT INTO %s(DATATARIH,ISTNO,YIL,AY,GUN,SAAT,DAKIKA,KAYIT_TURU,ORT_RUZGAR_YONU,ORT_RUZGAR_HIZI,MAKS_RUZGAR_YONU,MAKS_RUZGAR_HIZI,MAKS_RUZGAR_ZAMANI,TOP_YAGIS,SICAKLIK,NEM,ISBA_SICAKLIGI,BUHAR_BASINCI,AKTUEL_BASINC,DENIZE_INDIRGENMIS_BASINC,BUHARLASMA_MIKTARI,ORT_KURESEL_GUNES_RADYASYONU,GUNESLENME_SURESI,TOP_YAGIS_AGIRLIKLI,TOP_YAGIS_KEFELI2,DIREKT_GUNESLENME_SIDDETI,DIFUZ_GUNESLENME_SIDDETI,UVA_RADYASYON_SIDDETI,UVB_RADYASYON_SIDDETI,DENIZ_SUYU_SICAKLIGI,DALGA_YONU,DALGA_PERIYODU,DALGA_YUKSEKLIGI,ORT_ONMETRE_RUZGAR_HIZI,MAKS_ONMETRE_RUZGAR_HIZI,YATAY_GORUS_MESAFESI,METEOROLOJIK_HADISE,NRT_DUZELTILMIS_YAGIS,PWS_HALIHAZIR_YAGIS,ACIK_YUZEY_BUHARLASMASI,REZERVE1,REZERVE2,REZERVE3,REZERVE4) ",istBilgi.tblname_1dk);
sprintf(sql2,"%s VALUES(%s,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%s,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i,%i) ON CONFLICT DO NOTHING;",sql1,datatarih,istno,yil,ay,gun,saat,dakika,kayit_turu,ort_ruzgar_yonu,ort_ruzgar_hizi,maks_ruzgar_yonu,maks_ruzgar_hizi,maks_ruzgar_zamani,top_yagis_kefeli1,sicaklik,nem,isba_sicakligi,buhar_basinci,aktuel_basinc,denize_indirgenmis_basinc,buharlasma_miktari,ort_kuresel_gunes_radyasyonu,guneslenme_suresi,top_yagis_agirlikli,top_yagis_kefeli2,direkt_guneslenme_siddeti,difuz_guneslenme_siddeti,uva_radyasyon_siddeti,uvb_radyasyon_siddeti,deniz_suyu_sicakligi,dalga_yonu,dalga_periyodu,dalga_yuksekligi,ort_ruzgar_hizi_10m,maks_ruzgar_hizi_10m,yatay_gorus_mesafesi,meteorolojik_hadise,nrt_duzeltilmis_yagis,pws_halihazir_yagis,acik_yuzey_buharlasmasi,rezerve1,rezerve2,rezerve3,rezerve4);
printf("\\n\\n%s\\n\\n",sql2);
PreparedStatement_T pst = Connection_prepareStatement(cn1, sql2);
ResultSet_T rst = PreparedStatement_executeQuery(pst);
...
insert SQL command:
INSERT INTO awsd.public.awos_data(DATATARIH,ISTNO,YIL,AY,GUN,SAAT,DAKIKA,KAYIT_TURU,ORT_RUZGAR_YONU,ORT_RUZGAR_HIZI,MAKS_RUZGAR_YONU,MAKS_RUZGAR_HIZI,MAKS_RUZGAR_ZAMANI,TOP_YAGIS,SICAKLIK,NEM,ISBA_SICAKLIGI,BUHAR_BASINCI,AKTUEL_BASINC,DENIZE_INDIRGENMIS_BASINC,BUHARLASMA_MIKTARI,ORT_KURESEL_GUNES_RADYASYONU,GUNESLENME_SURESI,TOP_YAGIS_AGIRLIKLI,TOP_YAGIS_KEFELI2,DIREKT_GUNESLENME_SIDDETI,DIFUZ_GUNESLENME_SIDDETI,UVA_RADYASYON_SIDDETI,UVB_RADYASYON_SIDDETI,DENIZ_SUYU_SICAKLIGI,DALGA_YONU,DALGA_PERIYODU,DALGA_YUKSEKLIGI,ORT_ONMETRE_RUZGAR_HIZI,MAKS_ONMETRE_RUZGAR_HIZI,YATAY_GORUS_MESAFESI,METEOROLOJIK_HADISE,NRT_DUZELTILMIS_YAGIS,PWS_HALIHAZIR_YAGIS,ACIK_YUZEY_BUHARLASMASI,REZERVE1,REZERVE2,REZERVE3,REZERVE4) VALUES('2023-06-11 00:00:00',18438,2023,6,11,0,0,1,258,6,267,7,'2023-06-11 00:00:00',-8888,172,84,143,163,-8888,-9999,-9999,-8888,-8888,0,-8888,-8888,-9999,-8888,-8888,-8888,-8888,-8888,-8888,-9999,-9999,-9999,-9999,0,-9999,-8888,-8888,-8888,-8888,-8888);
ERROR:
SQLException:
raised in _executeQuery at src/db/postgresql/PostgresqlPreparedStatement.c:187
Note: ON CONFLICT DO NOTHING : ignore duplicate rows
without “ON CONFLICT DO NOTHING”
SQLException: ERROR: duplicate key value violates unique constraint "idx_awos_dakikalik"
DETAIL: Key (datatarih, istno)=(2023-06-11 00:00:00, 18438) already exists.
raised in _executeQuery at src/db/postgresql/PostgresqlPreparedStatement.c:187
Comments (3)
-
repo owner -
reporter Thank you for the response.
-
Ps. To get a quick overview see https://www.tildeslash.com/libzdb/#api and scroll down to examples. Two things to note: 1) You perform an SQL INSERT statement but call
PreparedStatement_executeQuery
which expect a SQL SELECT statement. In this case you should instead callPreparedStatement_execute()
. 2) Since you don’t use any wildcard parameters (?), just useConnection_execute()
instead. SinceConnection_execute()
takes a variable number of parameters likesprintf()
you can just write something like this (simplified):Connection_execute(con, "INSERT INTO awsd.public.awos_data (ISTNO,YIL,AY..) VALUES(%s,%d,%d,%d,%d..) ON CONFLICT DO NOTHING;", istno,yil,ay,gun...);
- Log in to comment
Your prepared statement does not include any parameters. In this case use Connection_execute instead. PS. Please don't use this issue tracker for support questions. Use stackoverflow or something else instead.