[SQL] Querying multiple database
Hello everyone, Please can you point me to the contrib/dblink package links. What am actually looking for is query multiple database located on different machines. Is there any documentation for same which tells the metadata steps? Existing archive link if exists please can you point me same. Regards - Dev
[SQL] Setting a default value for a select statement without results
I have an UPDATE query with the following general structure: UPDATE table1 SET (SELECT field FROM table2 WHERE conditions ORDER BY order_field LIMIT 1) Is it possible to assign a default value in case no results are returned by the SELECT statement? Respectfully, Jorge Maldonado
Re: [SQL] Setting a default value for a select statement without results
Hi Jorge, Look on http://www.postgresql.org/docs/8.1/static/functions-conditional.html Thanks, Anton On Feb 6, 2013, at 0:23, JORGE MALDONADO wrote: > I have an UPDATE query with the following general structure: > > UPDATE table1 SET (SELECT field FROM table2 WHERE conditions ORDER BY > order_field LIMIT 1) > > Is it possible to assign a default value in case no results are returned by > the SELECT statement? > > Respectfully, > Jorge Maldonado -- 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] Setting a default value for a select statement without results
2013/2/6 JORGE MALDONADO : > I have an UPDATE query with the following general structure: > > UPDATE table1 SET (SELECT field FROM table2 WHERE conditions ORDER BY > order_field LIMIT 1) > > Is it possible to assign a default value in case no results are returned by > the SELECT statement? One option would be to do something with UNION along these lines: UPDATE table1 SET field = (SELECT field FROM table2 WHERE conditions UNION SELECT 'default_value' WHERE NOT EXISTS (SELECT field FROM table2 WHERE conditions ) ORDER BY order_field LIMIT 1 ) HTH Ian Barwick -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] index scan vs bitmap index scan
I have a table with with an index that is of type 'timestamp without time zone'. Multiple records are inserted per second so the index is not unique. This table experiences frequent inserts and updates. Bulk deletes are performed once per month. Slower than expected search times are experienced when performing queries that limit the result to a range of dates. It's always a simple range: 'where ts between A and B'. I copied the table to another system where no inserts/updates are taking place and the results are rendered much faster. I attribute some of this to the I/O load on the idle system compared to our production system. EXPLAIN shows that the difference is that on the idle system a 'bitmap index scan' is used. On the production system a 'index scan' is used. On the production system query times are greatly reduced when 'set enable_indexscans to off' is used. Both backends are PGSQL 9.0.4. The table has about 24 million records. What influences the use of a bitmap index scan vs index scan? Any pointers on what I can do to render faster performance without having to explicitly adjust enable_indexscan would be greatly appreciated. Thanks, Wayne -- 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] Setting a default value for a select statement without results
On 2013-02-06, JORGE MALDONADO wrote: > --f46d0401fb2fcb805e04d50354b1 > Content-Type: text/plain; charset=ISO-8859-1 > > I have an UPDATE query with the following general structure: > > UPDATE table1 SET (SELECT field FROM table2 WHERE conditions ORDER BY > order_field LIMIT 1) assuming you mean this, and you are happy with its performance. UPDATE table1 SET field1 = (SELECT field FROM table2 WHERE conditions ORDER BY order_field LIMIT 1) > Is it possible to assign a default value in case no results are returned by > the SELECT statement? use coalesce. UPDATE table1 SET field1 = coalesce( (SELECT field FROM table2 WHERE conditions ORDER BY order_field LIMIT 1) , default_value ) -- ⚂⚃ 100% natural -- 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] Querying multiple database
Am looking for pointers related to cross-database queries? Thanks in advance! On Tue, Feb 5, 2013 at 10:42 PM, Dev Kumkar wrote: > Hello everyone, > > Please can you point me to the contrib/dblink package links. > > What am actually looking for is query multiple database located on > different machines. Is there any documentation for same which tells the > metadata steps? > Existing archive link if exists please can you point me same. > > Regards - Dev > -- :o) dev
