Re: [GENERAL] [SQL] check data for datatype
I guess that could need something like (untested) delete from bigtable text_column !~ '^[0-9][0-9]*$'; HTH Gerardo - Mensaje original - De: Suresh Raja suresh.raja...@gmail.com Para: pgsql-general@postgresql.org, pgsql-...@postgresql.org Enviados: Viernes, 27 de Marzo 2015 15:08:43 Asunto: [SQL] check data for datatype Hi All: I have a very large table and the column type is text. I would like to convert in numeric. How can I find rows that dont have numbers. I would like to delete those rows. Thanks, -Suersh Raja -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to see where SQL is better than PLPGSQL
Hi all. I see an entire database, with all the stored procedures writen in plpgsql. Off course, many (if not all) of that SP are simple inserts, updates, selects and so on. So, i want to test and show the differences between doing the same function in pgpgsql vs. plain sql. Im getting statistics (via collectd if that matters) and doing a modified version of the pgbench tests, just using pl (and sql) functions instead of the plain query: \setrandom delta -5000 5000 BEGIN; SELECT pgbench_accounts_upd_pl(:delta, :aid); SELECT get_pgbench_accounts_pl(:aid); SELECT pgbench_tellers_upd_pl(:delta, :tid); SELECT pgbench_branches_upd_pl(:delta, :bid); select pgbench_history_ins_pl(:tid, :bid, :aid, :delta); END; At first, pgbench is showing a difference between the pl and de sql versions: (pl.scripts own the PL version, sql.script owns the SQL version of the test) (This is a tiny netbook, with a dual core procesor) gherzig@via:~ pgbench -c 2 -C -T 300 -f pl.script -U postgres test duration: 300 s number of transactions actually processed: 13524 tps = 45.074960 (including connections establishing) tps = 75.260741 (excluding connections establishing) gherzig@via:~ pgbench -c 2 -C -T 300 -f sql.script -U postgres test starting vacuum...end. duration: 300 s number of transactions actually processed: 15125 tps = 50.412852 (including connections establishing) tps = 92.058245 (excluding connections establishing) So yeah, it looks like the SQL version is able to do a 10% more transactions. However, i was hoping to see anothers efects of using sql (perhaps less load avg in the SQL version), at the OS level. So, finnaly, the actual question: ¿Wich signals should i monitor, in order to show that PGPLSQL uses more resources than SQL? Thanks! Gerardo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [SQL] how to see where SQL is better than PLPGSQL
Hi all. I see an entire database, with all the stored procedures writen in plpgsql. Off course, many (if not all) of that SP are simple inserts, updates, selects and so on. So, i want to test and show the differences between doing the same function in pgpgsql vs. plain sql. Im getting statistics (via collectd if that matters) and doing a modified version of the pgbench tests, just using pl (and sql) functions instead of the plain query: \setrandom delta -5000 5000 BEGIN; SELECT pgbench_accounts_upd_pl(:delta, :aid); SELECT get_pgbench_accounts_pl(:aid); SELECT pgbench_tellers_upd_pl(:delta, :tid); SELECT pgbench_branches_upd_pl(:delta, :bid); select pgbench_history_ins_pl(:tid, :bid, :aid, :delta); END; At first, pgbench is showing a difference between the pl and de sql versions: (pl.scripts own the PL version, sql.script owns the SQL version of the test) (This is a tiny netbook, with a dual core procesor) gherzig@via:~ pgbench -c 2 -C -T 300 -f pl.script -U postgres test duration: 300 s number of transactions actually processed: 13524 tps = 45.074960 (including connections establishing) tps = 75.260741 (excluding connections establishing) gherzig@via:~ pgbench -c 2 -C -T 300 -f sql.script -U postgres test starting vacuum...end. duration: 300 s number of transactions actually processed: 15125 tps = 50.412852 (including connections establishing) tps = 92.058245 (excluding connections establishing) So yeah, it looks like the SQL version is able to do a 10% more transactions. However, i was hoping to see anothers efects of using sql (perhaps less load avg in the SQL version), at the OS level. So, finnaly, the actual question: ¿Wich signals should i monitor, in order to show that PGPLSQL uses more resources than SQL? It is hard question. It is invisible feature of SQL proc - inlining. What I know, a SQL function is faster than PLpgSQL function, when it is inlined. But there is nothing visible metric, that inform you about inlining. Regards Pavel Thanks Pavel! Im not (directly) concerned about speed, im concerned about resources usage. May be there is a value that shows the PGSQL machine necesary for plpgsql execution Thanks again for your time. Gerardo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
