Re: [SQL] Insert only if not found
am Thu, dem 05.04.2007, um 10:38:21 +0200 mailte Shavonne Marietta Wijesinghe
folgendes:
> Hello everyone..
>
> Here is the result of my Insert. and also the insert works ok.
>
> INSERT INTO MOD48_02 (ID, TE_INDI, TE_COGNOME, TE_NOME, TE_SESSO, TE_ATTNASC,
> TE_LUONASC, TE_DTNASC, TE_PROVSTATO, TE_PROV, TE_PATERNITA, TE_RICHIESTA,
> USERNAME, DATE_INSERTED, TIME_INSERTED) VALUES ('127001200745101033',
> 'england', 'bloom', 'orlando', 'M', 'ww', 'florida', '05/06/1984', 'america',
> 'UK', 'xx', 'no', 'demo', '05/04/2007', '10.10.33');
> But before i insert this record i want to check if the it hasn't been inserted
> before. I want to use the following fields as the riefferent. TE_COGNOME,
> TE_NOME, TE_SESSO, TE_DTNASC
>
> And if there is no record to match those 4 fields i want to insert it else
> just
> a message saying that the record has been inserted b4.
>
> any idea to start with?
Yes, read this:
http://ads.wars-nicht.de/blog/archives/11-Avoid-Unique-Key-violation.html
I think, you can change this for your request.
(but without the message, if you really need this message, you should use
a function and RAISE a NOTICE as the message)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(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] slow query
"A. Kretschmer" <[EMAIL PROTECTED]> writes: > am Thu, dem 05.04.2007, um 1:27:25 -0400 mailte Tom Lane folgendes: >> I'm betting the problem is poor vacuuming practice leading to lots of >> dead space. There's no way it takes 22 sec to read 10 rows if the >> table is reasonably dense. > This was my first thought, but: > ,[ Quote ] > | I've tried > | vacuuming this table many time > ` [ shrug... ] If the damage is already done, lazy VACUUM won't fix it. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Insert only if not found
wow thanks. i managed to paste it to fit my code..
yea ^^
Shavonne Wijesinghe
www.studioform.it
- Original Message -
From: "A. Kretschmer" <[EMAIL PROTECTED]>
To:
Sent: Thursday, April 05, 2007 10:53 AM
Subject: Re: [SQL] Insert only if not found
am Thu, dem 05.04.2007, um 10:38:21 +0200 mailte Shavonne Marietta
Wijesinghe folgendes:
Hello everyone..
Here is the result of my Insert. and also the insert works ok.
INSERT INTO MOD48_02 (ID, TE_INDI, TE_COGNOME, TE_NOME, TE_SESSO,
TE_ATTNASC,
TE_LUONASC, TE_DTNASC, TE_PROVSTATO, TE_PROV, TE_PATERNITA, TE_RICHIESTA,
USERNAME, DATE_INSERTED, TIME_INSERTED) VALUES ('127001200745101033',
'england', 'bloom', 'orlando', 'M', 'ww', 'florida', '05/06/1984',
'america',
'UK', 'xx', 'no', 'demo', '05/04/2007', '10.10.33');
But before i insert this record i want to check if the it hasn't been
inserted
before. I want to use the following fields as the riefferent. TE_COGNOME,
TE_NOME, TE_SESSO, TE_DTNASC
And if there is no record to match those 4 fields i want to insert it
else just
a message saying that the record has been inserted b4.
any idea to start with?
Yes, read this:
http://ads.wars-nicht.de/blog/archives/11-Avoid-Unique-Key-violation.html
I think, you can change this for your request.
(but without the message, if you really need this message, you should use
a function and RAISE a NOTICE as the message)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(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
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
[SQL] Insert only if not found
Hello everyone..
Here is the result of my Insert. and also the insert works ok.
INSERT INTO MOD48_02 (ID, TE_INDI, TE_COGNOME, TE_NOME, TE_SESSO, TE_ATTNASC,
TE_LUONASC, TE_DTNASC, TE_PROVSTATO, TE_PROV, TE_PATERNITA, TE_RICHIESTA,
USERNAME, DATE_INSERTED, TIME_INSERTED) VALUES ('127001200745101033',
'england', 'bloom', 'orlando', 'M', 'ww', 'florida', '05/06/1984', 'america',
'UK', 'xx', 'no', 'demo', '05/04/2007', '10.10.33');
But before i insert this record i want to check if the it hasn't been inserted
before. I want to use the following fields as the riefferent. TE_COGNOME,
TE_NOME, TE_SESSO, TE_DTNASC
And if there is no record to match those 4 fields i want to insert it else just
a message saying that the record has been inserted b4.
any idea to start with?
Shavonne Wijesinghe
Re: [SQL] Insert only if not found
I think i spoke to soon. It works. But if i change a letter from capital to
simple it inserts my record 2 times. So i have 2 records for "Shavonne" and
"shavonne". So i thought i would do the select in uppercase.
INSERT INTO MOD48_02 (ID, TE_INDI, TE_COGNOME, TE_NOME, TE_SESSO,
TE_ATTNASC, TE_LUONASC, TE_DTNASC, TE_PROVSTATO, TE_PROV, TE_PATERNITA,
TE_RICHIESTA, USERNAME, DATE_INSERTED, TIME_INSERTED) SELECT
'127001200745114035', '', 'chan', 'micia', 'F', '', '', '01/05/2006', '',
'', '', '', 'demo', '05/04/2007', '11.40.35' WHERE NOT EXISTS (SELECT
upper(TE_COGNOME), upper(TE_NOME), upper(TE_SESSO), TE_DTNASC FROM MOD48_02
WHERE TE_COGNOME='CHAN' AND TE_NOME='MICIA' AND TE_SESSO='F' AND
TE_DTNASC='01/05/2006');
but this doesn't work. I don't know why. If i try only the part
SELECT upper(TE_COGNOME), upper(TE_NOME), upper(TE_SESSO), TE_DTNASC FROM
MOD48_02
it returns me the fields in UPPERCASE. It's really strange why it doens't
work all together.
Anyone?
- Original Message -
From: "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]>
To:
Sent: Thursday, April 05, 2007 11:24 AM
Subject: Re: [SQL] Insert only if not found
wow thanks. i managed to paste it to fit my code..
yea ^^
Shavonne Wijesinghe
www.studioform.it
- Original Message -
From: "A. Kretschmer" <[EMAIL PROTECTED]>
To:
Sent: Thursday, April 05, 2007 10:53 AM
Subject: Re: [SQL] Insert only if not found
am Thu, dem 05.04.2007, um 10:38:21 +0200 mailte Shavonne Marietta
Wijesinghe folgendes:
Hello everyone..
Here is the result of my Insert. and also the insert works ok.
INSERT INTO MOD48_02 (ID, TE_INDI, TE_COGNOME, TE_NOME, TE_SESSO,
TE_ATTNASC,
TE_LUONASC, TE_DTNASC, TE_PROVSTATO, TE_PROV, TE_PATERNITA,
TE_RICHIESTA,
USERNAME, DATE_INSERTED, TIME_INSERTED) VALUES ('127001200745101033',
'england', 'bloom', 'orlando', 'M', 'ww', 'florida', '05/06/1984',
'america',
'UK', 'xx', 'no', 'demo', '05/04/2007', '10.10.33');
But before i insert this record i want to check if the it hasn't been
inserted
before. I want to use the following fields as the riefferent.
TE_COGNOME,
TE_NOME, TE_SESSO, TE_DTNASC
And if there is no record to match those 4 fields i want to insert it
else just
a message saying that the record has been inserted b4.
any idea to start with?
Yes, read this:
http://ads.wars-nicht.de/blog/archives/11-Avoid-Unique-Key-violation.html
I think, you can change this for your request.
(but without the message, if you really need this message, you should use
a function and RAISE a NOTICE as the message)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(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
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
---(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] Insert only if not found
am Thu, dem 05.04.2007, um 11:47:35 +0200 mailte Shavonne Marietta Wijesinghe
folgendes:
> I think i spoke to soon. It works. But if i change a letter from capital to
> simple it inserts my record 2 times. So i have 2 records for "Shavonne" and
> "shavonne". So i thought i would do the select in uppercase.
>
> INSERT INTO MOD48_02 (ID, TE_INDI, TE_COGNOME, TE_NOME, TE_SESSO,
> TE_ATTNASC, TE_LUONASC, TE_DTNASC, TE_PROVSTATO, TE_PROV, TE_PATERNITA,
> TE_RICHIESTA, USERNAME, DATE_INSERTED, TIME_INSERTED) SELECT
> '127001200745114035', '', 'chan', 'micia', 'F', '', '', '01/05/2006', '',
> '', '', '', 'demo', '05/04/2007', '11.40.35' WHERE NOT EXISTS (SELECT
> upper(TE_COGNOME), upper(TE_NOME), upper(TE_SESSO), TE_DTNASC FROM MOD48_02
> WHERE TE_COGNOME='CHAN' AND TE_NOME='MICIA' AND TE_SESSO='F' AND
> TE_DTNASC='01/05/2006');
>
>
> but this doesn't work. I don't know why. If i try only the part
You have an error ;-)
Compare the strings in ther WHERE-clause. An example:
test=# create table no_dupes (id int, name text);
CREATE TABLE
test=*# commit;
COMMIT
test=# insert into no_dupes select 1, 'Andreas' where not exists (select id,
lower(name) from no_dupes where id=1 and lower(name)=lower('Andreas'));
INSERT 0 1
test=*# insert into no_dupes select 1, 'Andreas' where not exists (select id,
lower(name) from no_dupes where id=1 and lower(name)=lower('Andreas'));
INSERT 0 0
test=*# select * from no_dupes ;
id | name
+-
1 | Andreas
(1 row)
test=*# insert into no_dupes select 1, 'Andreas' where not exists (select id,
name from no_dupes where id=1 and lower(name)=lower('Andreas'));
INSERT 0 0
test=*# select * from no_dupes ;
id | name
+-
1 | Andreas
(1 row)
test=*#
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] slow query
On Thu, Apr 05, 2007 at 01:47:03AM -0400, Tom Lane wrote: > [ shrug... ] If the damage is already done, lazy VACUUM won't fix it. > Also, if there are enough open transactions at any one time and sufficient churn in the table, lazy VACUUM may not be able to keep up. (We had that experience with a table that was updated _very very_ often. The answer turned out to be to update less often. Aggregating queries that could use an index over a large number of "expired" rows worked better than seqscans over large numbers of dead tuples.) A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] LOG: unexpected EOF on client connection
Hi list, I am doing a benchmark in my database but it is spent too much time. I was looking for my log file but I could not understad clearly what it says. The log is like this: INT: Consider increasing the configuration parameter "checkpoint_segments". LOG: checkpoints are occurring too frequently (4 seconds apart) HINT: Consider increasing the configuration parameter "checkpoint_segments". LOG: checkpoints are occurring too frequently (5 seconds apart) HINT: Consider increasing the configuration parameter "checkpoint_segments". LOG: checkpoints are occurring too frequently (5 seconds apart) HINT: Consider increasing the configuration parameter "checkpoint_segments". LOG: checkpoints are occurring too frequently (8 seconds apart) HINT: Consider increasing the configuration parameter "checkpoint_segments". NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "branches_pkey" for table "branches" NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "tellers_pkey" for table "tellers" NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "accounts_pkey" for table "accounts" ERROR: conversion between latin9 and LATIN1 is not supported STATEMENT: set client_encoding to 'latin9' FATAL: sorry, too many clients already LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection Does anyone have any suggestion ? -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Another date range join problem
Hi folks, me again. I'm back with my availability and roster tables again. I have: Table "public.availability" Column |Type ---+ aid | serial primary key asid | integer asdate| date afdate| date Table "public.roster_staff" Column| Type | Modifiers -+-+--- rsdate | date| not null rsgid | integer | rsgsid | integer | rssid | integer | I want to do a join giving the details from the availability with a count of roster_staff records within the range asdate->afdate for each staff member (asid <-> rssid) e.g. availability 1 1 2007-04-01 2007-04-01 2 1 2007-04-10 2007-04-15 roster_staff 2007-04-01 4 5 1 2007-04-11 4 6 1 2007-04-13 4 3 1 2007-04-14 5 5 1 2007-04-15 5 6 1 giving 1 1 2007-04-01 2007-04-01 1 2 1 2007-04-10 2007-04-15 4 -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] LOG: unexpected EOF on client connection
<[EMAIL PROTECTED]> writes: > FATAL: sorry, too many clients already You need a larger max_connections setting. > LOG: unexpected EOF on client connection I think pgbench just dies ungracefully if it gets a connection failure. regards, tom lane ---(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] Generating dates prior to generate_series
On Wed, 2007-04-04 at 07:00, Roger Tannous wrote: > I'm using PostgreSQL version 7.3.2, and generate_series() is not > available, so this is a function to generate a series dates. > > The function goes backwards if the second argument is less than the first > one. Check the two select statements at the end. As someone who is still responsible for the care and feeding of a 7.4 database, thanks for the function. You really should upgrade to the latest 7.3.xx version, as there were many data loss bugs in earlier 7.3.xx versions that have been fixed. ---(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] SOLVED - Another date range join problem
I've managed to come up with a solution that works, and embellished it to give exactly what I want. create view availability_details as select aid, asid, asdate, afdate, adays, count(rsgid) as allocated, adays-count(rsgid) as afree from ( select aid, asid, asdate, afdate, coalesce(adays,afdate-asdate+1) as adays, rsgid from availability a left join roster_staff r on r.rsdate >= a.asdate and r.rsdate <= a.afdate ) as list group by aid, asid, asdate, afdate, adays order by asid, asdate; select * from availability_details ; aid | asid | asdate | afdate | adays | allocated | afree -+--+++---+---+--- 8 |1 | 2007-03-29 | 2007-04-04 | 7 | 1 | 6 4 |1 | 2007-04-06 | 2007-04-09 | 4 | 0 | 4 5 |1 | 2007-04-14 | 2007-04-15 | 2 | 2 | 0 6 |1 | 2007-04-21 | 2007-04-22 | 2 | 0 | 2 1 | 28 | 2007-03-01 | 2007-03-01 | 1 | 0 | 1 2 | 28 | 2007-03-02 | 2007-03-07 | 6 | 0 | 6 (6 rows) -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] auto vacuuming
Hi all is there a way i can find if the pg_autovacuum module is installed on my server?? The postgres version im using is 8.1.4 I tried searching the contrib module and dint see the pg_autovacuum directory there I checked m source Thanks, Sumeet
Re: [SQL] auto vacuuming
Sumeet escribió: > Hi all is there a way i can find if the pg_autovacuum module is installed on > my server?? > The postgres version im using is 8.1.4 > I tried searching the contrib module and dint see the pg_autovacuum > directory there pg_autovacuum no longer exists. Autovacuum is now integrated into the core. So the way to find if it's installed is "yes". -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] auto vacuuming
Then do i need to still provide the auto vacumming options in the postgres.conf file or these options are automatically taken care of. Thanks, Sumeet. On 4/5/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote: Sumeet escribió: > Hi all is there a way i can find if the pg_autovacuum module is installed on > my server?? > The postgres version im using is 8.1.4 > I tried searching the contrib module and dint see the pg_autovacuum > directory there pg_autovacuum no longer exists. Autovacuum is now integrated into the core. So the way to find if it's installed is "yes". -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Thanks, Sumeet Ambre Master of Information Science Candidate, Indiana University.
Re: [SQL] auto vacuuming
Sumeet escribió: > Then do i need to still provide the auto vacumming options in the > postgres.conf file or these options are automatically taken care of. You have to enable the autovacuum setting in postgresql.conf. It is not enabled by default. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Insert only if not found
It's also desirable that you use TRIM along with UPPER or LOWER. Roger. Don't pick lemons. See all the new 2007 cars at Yahoo! Autos. http://autos.yahoo.com/new_cars.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
