Re: [SQL] ignore unique violation OR check row exists
Samuel Gendler wrote: On Wed, Jan 4, 2012 at 1:57 AM, Zdravko Balorda mailto:zdravko.balo...@siix.com>> wrote: Take it out of transaction. Why is there a transaction in the first place? If transaction is needed, ok, but take these inserts out and everything will work as it should. Ignoring UNIQUE VIOLATION or any other error defeats the very purpose of transaction. That's why you can't ignore it. Unfortunately, bulk inserts are much slower when they don't occur in a transaction. Try inserting 1 million rows with auto commit enabled vs 1 million rows in 1 transaction, or even 10 or 100 transactions. The difference is enormous. The bulk insert into an unconstrained table and then pulling just the new rows over into the destination table in a single transaction is definitely the most effective way to do this. I do a lot of bulk inserts. What helps is dropping indexes before insert and recreating it after. Probably you need to better organize data to avoid having primary keys on a table with a lots of data. Zdravko -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ignore unique violation OR check row exists
Andreas Kretschmer wrote: rverghese wrote: I want to insert a bunch of records and not do anything if the record already exists. So the 2 options I considered are 1) check if row exists or insert and 2) ignore the unique violation on insert if row exists. Any opinions on whether it is faster to INSERT and then catch the UNIQUE VIOLATION exception and ignore it in plpgsql versus check if row exists and INSERT if it doesn't. I can't seem to ignore the UNIQUE VIOLATION exception via php, since it is a plpgsql command, so if I have to do the check and insert, alternatively i have a function that tries to insert and then ignores the violation. I was wondering if one way was better than the other. Thanks Take it out of transaction. Why is there a transaction in the first place? If transaction is needed, ok, but take these inserts out and everything will work as it should. Ignoring UNIQUE VIOLATION or any other error defeats the very purpose of transaction. That's why you can't ignore it. Zdravko -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] inheritance
Hi, I am new to postgress inheritance. Once a parent row is inserted can I later reconnect child rows so that thay get adopted by parent row? Regards, Zdravko. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] PHP pg_escape_string
Hi, I have a PHP/PGSQL question: there are both pg_(un)escape_bytea() functions but only one pg_escape_string()... I wonder if I may be missing something here? Thank you for any explanation, Zdravko. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Alter Table/Indexing
Hi, I wonder does ATER TABLE TYPE, SET, depends on indexes, like INSERT does in a sense it may be faster to drop and recreate index than sorting after every row inserted. Does changing type or setting default on an indexed column require sorting? Thanks, Zdravko -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] cast bool/int
CASE WHEN column='t' THEN 1 ELSE 0 END Or just CASE WHEN column THEN 1 ELSE 0 END. In the mean time I've got an elegant solution: alter ... ... column TYPE smallint USING column::boolean::int::smallint; It works, you wouldn't beleive it. ;) Zdravko -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] cast bool/int
Hi, I need a casting operator from boolean to integer, tu put in ALTER TABLE statment after USING. Any ideas? Thanks. Zdravko -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] alter table on a large db
Hi, I need to make some ALTER TABLEs. It takes about 30min to copy this quite large databse, bat several ours to run a bunch of ALTER TABLE statements. Is there any way to make it faster? I wonder what could possibly alter table be doing all this time. Regards, Zdravko. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] count(distinct)
Hi, this is probably an old issue but I'm not all that experienced. I wonder if an index can be accessed rather directly, as to speed up a query like "select count(distinct())", by simply calculating the number of branches (leaves) an index has. Or at least to skip sorting. Best regards, Zdravko -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
