[SQL] transaction block causing trouble
Hey I hope someone can help me here. I'm doing some little test on PostgreSQL 7.3.5 and am experiencing some weird behaviour. I create some tables and add values to it using insert into select from. After I am done with that, I create unique constraints on the tables. The reason to do that afterwards was to speed up the process a bit, but that doesn't seem to help anything. Anywayz, all goes well if I don't mention anything about transactions in my file and feed it to pgsql, which will autocommit after each line, if I'm not mistaken. However if I put begin; and commit; before and after the complete bunch of lines, pgsql suddenly encounters an error: psql:test.sql:1346: NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index 'rkb_unique_head' for table 'rkb' psql:test.sql:1346: ERROR: Cannot create unique index. Table contains non-unique values psql:test.sql:1350: ERROR: current transaction is aborted, queries ignored until end of transaction block psql:test.sql:1356: ERROR: current transaction is aborted, queries ignored until end of transaction block COMMIT The test.sql file contains: begin; drop table rka; drop table rkb; drop table tapestry; commit; begin; create table RKA( head int, tail int); create table RKB( head int, tail int); create table tapestry( attr0 int, attr1 int); ... some 1024 inserts like insert into RKA values (0, 726); insert into RKB select head+0, tail+0 from RKA; insert into RKB select head+8192, tail+8192 from RKA; ... this continued till there are 102400 rows in RKB update RKB set tail=(tail*37) % 102400; update RKB set tail=(tail*31) % 102400; update RKB set tail=(tail*29) % 102400; update RKB set tail=(tail*23) % 102400; update RKB set tail=(tail*19) % 102400; update RKB set tail=(tail*17) % 102400; update RKB set tail=(tail*13) % 102400; update RKB set tail=(tail*11) % 102400; update RKB set tail=(tail*7) % 102400; -- build unique constraints alter table RKA add constraint rka_unique_head unique (head); alter table RKB add constraint rkb_unique_head unique (head); insert into tapestry select R0.head, R0.tail from RKB R0; -- the insert will only take place if the above unique constraint was -- satisfied -- build unique constraint alter table tapestry add constraint tapestry_unique_attr0 unique (attr0); commit; Why does the whole thing run luckily without transactions, and complains about a duplicate entry when in an transaction block? Also when using less insert statements for RKB the transaction does not succeed. When specifying unique when creating the tables, and skipping the add constraint part at the bottom, the whole transaction does succeed. my file can be found gzipped at http://www.cwi.nl/~fabian/test.sql.gz Any help is appreciated for I'm clueless about this issue :) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] UPDATE with WHERE clause on joined table
Hi, I'm only very rarely using SQL, so please forgive me if I show any obvious signs of ignorance... I've got three tables "customer", "address" and "country". I want to set the "language" attribute on "customer" on rows returned by a SELECT such as this: SELECT title, first_names, last_name, email, language, country.country_name FROM ((customer JOIN address ON customer.eoid = address.eoid_customer) JOIN country ON address.eoid_country = country.eoid) WHERE email LIKE '%.es' AND country.country_name = 'SPAIN'; That is, I want to set the "language" to 'Spanish' where the "customer.email" is like '%.es' and where "country.country_name" is 'SPAIN'. I've tried all sorts of places to put the JOIN and the WHERE clauses within the UPDATE statement, but I just don't get it. I'd be most grateful for any help... TIA Fabian P.S.: One of my sorry attempts looked like this - which updates all rows in "customer" so I figure the WHERE clause is not where it should be: UPDATE customer SET language = 'Spanish' FROM ((customer AS customer_address JOIN address ON customer_address.eoid = address.eoid_customer) JOIN country ON address.eoid_country = country.eoid) WHERE customer.email LIKE '%.es' AND country.country_name = 'SPAIN'); ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] [SOLVED] UPDATE with WHERE clause on joined table
Am 29.07.2006 um 01:30 schrieb Erik Jones: Fabian Peters wrote: Hi, I'm only very rarely using SQL, so please forgive me if I show any obvious signs of ignorance... I've got three tables "customer", "address" and "country". I want to set the "language" attribute on "customer" on rows returned by a SELECT such as this: SELECT title, first_names, last_name, email, language, country.country_name FROM ((customer JOIN address ON customer.eoid = address.eoid_customer) JOIN country ON address.eoid_country = country.eoid) WHERE email LIKE '%.es' AND country.country_name = 'SPAIN'; That is, I want to set the "language" to 'Spanish' where the "customer.email" is like '%.es' and where "country.country_name" is 'SPAIN'. I've tried all sorts of places to put the JOIN and the WHERE clauses within the UPDATE statement, but I just don't get it. I'd be most grateful for any help... TIA Fabian P.S.: One of my sorry attempts looked like this - which updates all rows in "customer" so I figure the WHERE clause is not where it should be: UPDATE customer SET language = 'Spanish' FROM ((customer AS customer_address JOIN address ON customer_address.eoid = address.eoid_customer) JOIN country ON address.eoid_country = country.eoid) WHERE customer.email LIKE '%.es' AND country.country_name = 'SPAIN'); The FROM clause is where you put relations other than the one you are updating. Try this: UPDATE customer SET language='Spanish' FROM address ad, country co WHERE customer.eoid=ad.eoid_customer AND ad.eoid_country=co.eoid AND co.country_name='SPAIN' AND customer.email LIKE '%.es'; Note that for demonstration purposes I've aliased the join tables and that (unfortunately) you can't alias the update table. erik jones <[EMAIL PROTECTED]> software development emma(r) Erik, thanks a lot! Works like a charm and is so much more concise than my attempts. cheers Fabian ---(end of broadcast)--- TIP 6: explain analyze is your friend
