"Kim Yunhan" <[EMAIL PROTECTED]> writes:
> I want to query this...
> --> SELECT * FROM bbs ORDER BY ref desc, step ASC LIMIT 12;
> this query doesn't refer the index that made by this query.
> --> CREATE INDEX idx_bbs ON bbs (ref, step);
Well, no. The ordering the query is asking for has nothi
I want to query this...
--> SELECT * FROM bbs ORDER BY ref desc, step ASC LIMIT 12;
this query doesn't refer the index that made by this query.
--> CREATE INDEX idx_bbs ON bbs (ref, step);
but, i change the query that "ref desc" to "ref asc".
then query refer the index, and i can see a result
You can use two quote characters to get a single quote in the quoted
string, so ''month''
On Thu, 8 Feb 2001, Hubert Palme wrote:
> Stephan Szabo wrote:
> >
> > Functional indexes cannot currently take constant values to the function,
> > so it's complaining about the constant 'month'. The cu
Hi,
How might I insert a new row into a table and return the id of the new row
all in the same SQL statement? The id is generated by a sequence. Up to
now I've been getting the nextval of the sequence first and then inserting
with the id in a second SQL exec. Is there a faster way (in a general
c
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Tom Lane writes:
>> I am inclined to keep our options open by forbidding EXECUTE 'SELECT
>> INTO ...' for now. That's more than a tad annoying, because that leaves
>> no useful way to do a dynamically-built SELECT, but if we don't forbid
>> it I thin
Tom Lane writes:
> I am inclined to keep our options open by forbidding EXECUTE 'SELECT
> INTO ...' for now. That's more than a tad annoying, because that leaves
> no useful way to do a dynamically-built SELECT, but if we don't forbid
> it I think we'll regret it later.
You can always use CREAT
Brice Ruth <[EMAIL PROTECTED]> writes:
> As for vacuum analyze - prior to running into these problems, I deleted
> all data from the database (using delete from ) and then ran
> vacuumdb -a, after which I loaded the data into the tables using 'copy
> ... from' - there have been no updates to the d
Um, no.
You should run vacuum analyze AFTER you loaded up the data, otherwise,
your table statistics will be all wrong (it'll contain 'empty table'
statistics).
-alex
On Thu, 8 Feb 2001, Brice Ruth wrote:
> Stephan,
>
> Here is what EXPLAIN shows:
>
> NOTICE: QUERY PLAN:
>
> Sort (cost=0.0
Tom, Jan,
> This is completely pointless, AFAICS. If you don't know what table
> is to be selected from, then you can't do *any* semantic checking or
> planning in advance, so you might as well just do the entire processing
> at runtime. That's exactly what EXECUTE does. I don't see any
> func
Ross,
Thanx to Stephan's help, I found out that after loading the tables w/
data, I had to run vacuum analyze to inform the optimizer of the amount
of data in the table (amongst other things, I imagine). After running
that on all the tables, the query performs fine.
-Brice
"Ross J. Reedstrom"
Brice,
This sounds like a problem with you postgresql install or your HDD
rather than a problem with your query ... which appears to be fine.
I would reccomend taking it up with PGSQL Inc. (or Great Bridge)
pay-for support if this is a commercial project.
Stephan,
Here is what EXPLAIN shows:
NOTICE: QUERY PLAN:
Sort (cost=0.02..0.02 rows=1 width=64)
-> Nested Loop (cost=0.00..0.01 rows=1 width=64)
-> Seq Scan on tblmedcond (cost=0.00..0.00 rows=1 width=36)
-> Seq Scan on tblsidedruglink (cost=0.00..0.00 rows=1 width=28)
Brice -
What does EXPLAIN say for that query? With empty tables, I get two index scans,
a merge join, and two sorts. I'm guessing wildly that you've got a non-optimal
sort strategy happening somehow, given the four fold ORDER BY clause.
Ross
Here's the empty version:
NOTICE: QUERY PLAN:
Sort
On Wed, Feb 07, 2001 at 10:38:53AM -0600, Brice Ruth wrote:
> Is there a simple (unix) command I can run on text files to convert
> cr/lf to lf? The way I did it seemed pretty ass-backward to me (not to
> mention time consuming).
>
> -Brice
On many systems (linux at least) there is a command do
After you load the data, you need to run vacuum analzye. That'll
get statistics on the current data in the table. Of course, I'm
not sure that'll help in this case.
On Thu, 8 Feb 2001, Brice Ruth wrote:
> Stephan,
>
> Here is what EXPLAIN shows:
>
> NOTICE: QUERY PLAN:
>
> Sort (cost=0.0
All right ... after reading up on the documentation for vacuum, I
understand why that's necessary. I've run vacuum analyze on all the
tables, now. Here are the more realistic results from explain:
NOTICE: QUERY PLAN:
Sort (cost=62.46..62.46 rows=14 width=64)
-> Nested Loop (cost=0.00..62
What does explain show for the query and have you run
vacuum analyze recently on the tables?
On Thu, 8 Feb 2001, Brice Ruth wrote:
> The following query:
>
> SELECT
> tblSIDEDrugLink.DrugID,
> tblSIDEDrugLink.MedCondID,
> tblMedCond.PatientName AS MedCondPatientName,
>
> Josh Berkus wrote:
>> If you think that's the best way. What we're really all wanting is a wy
>> in PL/pgSQL to pass a parameter as an object name. Doing it *without*
>> using EXECUTE would be even better than modifying EXECUTE to accomdate
>> SELECT ... INTO variable.
>>
>> If we can write q
Jan Wieck <[EMAIL PROTECTED]> writes:
>> I am inclined to keep our options open by forbidding EXECUTE 'SELECT
>> INTO ...' for now. That's more than a tad annoying, because that leaves
>> no useful way to do a dynamically-built SELECT, but if we don't forbid
>> it I think we'll regret it later.
Josh Berkus wrote:
> Tom, Jan, Michael,
>
> > While I have not looked closely, I seem to recall that plpgsql handles
> > INTO by stripping that clause out of the statement before it's passed to
> > the SQL engine. Evidently that's not happening in the EXECUTE case.
> >
> > Jan, do you agree this
Hello,
I have upgraded my DB to 7.0.3, but there is still the problem. I think that
it may be a bug.
Joining 3 tables was not possible...
It says "fa1 should be in aggregate too" !
While joining 2 tables gives wrong results ...
ie, if the results should be like this:
fa1 sum
-
Tom Lane wrote:
> Michael Ansley <[EMAIL PROTECTED]> writes:
> > CREATE FUNCTION table_count(varchar) RETURNS integer AS '
> > DECLARE
> > SQL varchar;
> > RES integer;
> > BEGIN
> > SQL = ''SELECT * INTO temp1 FROM '' || $1;
> > EXECUTE SQL;
> > SELECT count(*) INTO RES FROM temp1;
> > RETU
Title: RE: [SQL] Re: PL/pgsql EXECUTE 'SELECT INTO ...'
What I wrote wasn't about temp tables, it was about selecting into plpgsql variables. It would appear that Jan's syntax gets around this problem.
MikeA
-Original Message-
From: Jan Wieck [mailto:[EMAIL PROTECTED]]
Sent: 08 Fe
Tom Lane wrote:
> I have looked a little bit at what it'd take to make SELECT INTO inside
> an EXECUTE work the same as it does in plain plpgsql --- that is, the
> INTO should reference plpgsql variables, not a destination table.
> It looks to me like this is possible but would require some nontri
Josh Berkus wrote:
> Tom et al.
>
> Discovered this quirk in foriegn keys:
>
> In the preliminary version of a database, I added foriegn
> key constraints to a number of tables, linking them to a
> column in a shared reference table (status.status) that was
> only one-half of a composite primary k
Ken Corey wrote:
> Wow! Answering emails on a Sunday? Someone should be giving you an award or
> something.
>
> On Sunday 04 February 2001 8:13 pm, you wrote:
> > Ken Corey <[EMAIL PROTECTED]> writes:
> > > When the select at the bottom of this email is executed, I'm getting the
> > > message:
>
26 matches
Mail list logo
