[SQL] Altering pg_conndefaults

how do i alter values of pg_conndefaults? example user=user1 to user=user2 thanks in advance ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that yo

Re: [SQL] exists

I'm running 7.1.3. What does 'rows=1' mean? The number of rows returned or the number postgres has to look through? Tom Lane wrote: > Joseph Shraibman <[EMAIL PROTECTED]> writes: > >>Why does explain show more than one row, even if there is a LIMIT = 1? >> > > What version are you runni

Re: [SQL] exists

Joseph Shraibman <[EMAIL PROTECTED]> writes: > Why does explain show more than one row, even if there is a LIMIT = 1? What version are you running? I get results like regression=# explain select * from tenk1 limit 1; NOTICE: QUERY PLAN: Limit (cost=0.00..0.03 rows=1 width=148) -> Seq Scan

Re: [SQL] exists

Why does explain show more than one row, even if there is a LIMIT = 1? -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAI

Re: [SQL] exists

Joseph Shraibman <[EMAIL PROTECTED]> writes: > Then why does the explain say rows=1363 ? That's the estimate of how many rows the inner SELECT would return, if left free to return them all. You should get the same row count estimate (though quite possibly a different plan) if you just do an EXPL

Re: [SQL] Should I worry?

"Josh Berkus" <[EMAIL PROTECTED]> writes: > Do I need to worry about this: > pq_flush: send() failed: Broken pipe > ... which appears in the log intermittently? Looks like the trace of a client disconnecting ungracefully (mid-query). If you're not aware of any client-side failures in your setu

Re: [SQL] exists

Then why does the explain say rows=1363 ? I don't mean to nitpick here, but maybe this is the symptom of a larger problem. Tom Lane wrote: > Joseph Shraibman <[EMAIL PROTECTED]> writes: > >>Well the total cost should be at least as big as the sub-costs, no? >> > > Not if the sub-plan in questi

[SQL] Should I worry?

Folks: Do I need to worry about this: pq_flush: send() failed: Broken pipe ... which appears in the log intermittently? __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] a

Re: [SQL] exists

Joseph Shraibman <[EMAIL PROTECTED]> writes: > Well the total cost should be at least as big as the sub-costs, no? Not if the sub-plan in question is for an EXISTS. The sub-plan cost is stated in terms of cost to retrieve all rows --- but the outer level EXISTS isn't going to retrieve all rows,

Re: [SQL] exists

> Stephan Szabo wrote: > > On Tue, 21 Aug 2001, Joseph Shraibman wrote: > > > > > >>Thank you, I was missing the parens. > >> > >>If I do an explain I see: > >> > >>-> Index Scan using m_u_and_p_key on m (cost=0.00..3035.22 rows=1363 width=12) > >> > >> > >>even if I put a limit 1 on the sele

Re: [SQL] exists

Stephan Szabo wrote: > On Tue, 21 Aug 2001, Joseph Shraibman wrote: > > >>Thank you, I was missing the parens. >> >>If I do an explain I see: >> >>-> Index Scan using m_u_and_p_key on m (cost=0.00..3035.22 rows=1363 width=12) >> >> >>even if I put a limit 1 on the select. Why is that? >> >

Re: [SQL] exists

Stephan Szabo wrote: >>Limit (cost=48.39..48.39 rows=1 width=70) >> -> Sort (cost=48.39..48.39 rows=2 width=70) >> -> Hash Join (cost=18.46..48.38 rows=2 width=70) >> -> Index Scan using u_p_key on u (cost=0.00..27.66 rows=48 width=28) >> -> Hash (c

Re: [SQL] exists

On Tue, 21 Aug 2001, Joseph Shraibman wrote: > Thank you, I was missing the parens. > > If I do an explain I see: > > -> Index Scan using m_u_and_p_key on m (cost=0.00..3035.22 rows=1363 width=12) > > > even if I put a limit 1 on the select. Why is that? Is that the inner query (on the ex

[SQL] Re: Getting 'n-1'th record.

SELECT * FROM table ORDER BY field DESC LIMIT 1 OFFSET 1; This way you don't need to know the value of "n" in advance. The descending ORDER BY is to indicate a reversal of your intended ordering, so as to make the n-1'th record the second record. Note that getting the n-1'th record from an unorde

Re: [SQL] exists

Thank you, I was missing the parens. If I do an explain I see: -> Index Scan using m_u_and_p_key on m (cost=0.00..3035.22 rows=1363 width=12) even if I put a limit 1 on the select. Why is that? Stephan Szabo wrote: > On Mon, 20 Aug 2001, Joseph Shraibman wrote: > > >>I want to select a b

Re: [SQL] Primary vs Unique Index

Darcy, > It has to do somewhat with database theory. There is a basic > distinction > between a unique index and a primary key. Ideally, the primary key > should > never change but a unique key can as long as the new value is also > unique. > PostgreSQL doesn't enforce this (I think it should)

Re: [SQL] Getting 'n-1'th record.

Bhuvan, > An sql query results with 'n' records. OK. > I need ONLY the 'n-1'th record. You're making this much harder than it needs to be. If you want the "nth" record, then you have to be supplying the database with an ORDER BY. For the next-to-last record, simply reverse the ORDER BY and take

[SQL] Re: split/explode functions

Not amongst the builtin functions. You will need to create your own using a procedural language. Easiest is plperl, since Perl already has a very functional "split" function. - Original Message - From: "omid omoomi" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, August 20, 20

Re: [SQL] Getting 'n-1'th record.

Bhuvan A <[EMAIL PROTECTED]> writes: > I need ONLY the 'n-1'th record. See the 'LIMIT' and 'OFFSET' clauses in SELECT. Note the caveat that you'd better ORDER the rows to be sure you know which is the n-1'th. Having done an ORDER BY, you could simplify your life by reversing the ordering and cho

Re: [SQL] database location question

Carolyn Lu Wong <[EMAIL PROTECTED]> writes: > I'm using V6.5.3. You really oughta update ;-) > Is there a way to move the database to another directory location > instead in /var/lib/pgsql? Sure, just shut down the postmaster, physically move the entire data tree (use 'cp -p -r' or 'tar' or som

[SQL] RE: Sequential select queries...??

If you want to know for each individual one wether both equal or not you could do: SELECT *,NOT ((name=description) IS NULL) AND (name=description) AS both FROM t WHERE name='bleh' OR description='bleh'; If you wanted totals of the same you could do: SELECT count(*) AS tot,NOT ((name=description)

Re: [SQL] Primary vs Unique Index

Thus spake Gonzo Rock > Why would one need a Primary Key... which can only be declared at table creation if >one can create a Unique Index post table creation? > > ie: I deleted my primary key... is that a big deal? What's the purpose of the >Primary Key if it's function can be duplicated with

Re: [SQL] Getting 'n-1'th record.

Hi, It is a not a clean job but how about having a view like this : create view foo_view as select * from yourtable order by oid desc limit 2 ; and then making your select like this: select * from foo_view order by oid limit 1; hope that helps Omid >From: Bhuvan A <[EMAIL PROTECTED]> >To: [EMA

[SQL] Getting 'n-1'th record.

hi all, consider below.. An sql query results with 'n' records. OK. I need ONLY the 'n-1'th record. HOW CAN I GET THIS? Thankx in advance! == Q: What's the difference between the 1950's and the 1980's?

Re: [SQL] is it possible to use arrays in plpgsql function??

hi, I had the same question a couple of days ago and I received some good helps. look at the archives... regards Omid >From: Bhuvan A <[EMAIL PROTECTED]> >To: [EMAIL PROTECTED] >Subject: [SQL] is it possible to use arrays in plpgsql function?? >Date: Tue, 21 Aug 2001 11:24:08 +0530 (IST) > > >hi