Re: [SQL] Insert multiple Rows

[Oleg Samoylov]

Hi,

Saint X wrote:
Hi, I have a program that need to send a Postgresql database a lot of
rows, i'm doing it using FOR an INSERT, something like this
for i = 0 to 1000
{
 insert into table(item1) VALUES (i);
}
And so on, as you can imagine these consume a lot of resources and
move so slowly, that's why I'm looking for a command to send more than
one row at the time, something like
insert into table (item1) values ('1' / '2' / '3' / '4' ... / '1000');
You can do instead:

insert into table (item1) values ('1');
insert into table (item1) values ('2');

insert into table (item1) values ('1000');
As single query.

But better use COPY command or prepared statment.

Inserts slow commonly due to indexis and foreing keys.

--
Olleg Samoylov
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] using Avg()

[Christoph Haller]

>
> I have a table with a column named SwErr   (Switch Error) with int
values
> date with datetime values and SwID with char(3)
> I am trying to get a subset back where the folowing is true
>
> select the avg(SwErr) for the last 30 days where SwId = 'XX1'
> Select the value of todays value of SwErr where SwId = 'XX1'
> if todays value of SwErr is greater than 4 times the SwErr Average
return in
> the value
>
Looks like you could use a plpgsql function, something like (untested)

CREATE OR REPLACE FUNCTION
get_dated_SwErr(CHAR) RETURNS INTEGER AS '
DECLARE
 thisSwID   ALIAS FOR $1;
 todaysSwErrINT;
 avgSwErr   INT;
 avg4SwErr  INT;
 dateLimit  TIMESTAMP;
BEGIN

SELECT INTO dateLimit current_date - ''30 days''::interval ;

SELECT INTO todaysSwErr SwErr FROM 
 WHERE SwID = thisSwID AND SwDate = current_date ;

SELECT INTO avgSwErr AVG(SwErr)::int FROM 
 WHERE SwID = thisSwID AND SwDate BETWEEN current_date AND dateLimit ;

avg4SwErr := avgSwErr * 4 ;

IF todaysSwErr > avg4SwErr THEN
 RETURN todaysSwErr;
ELSE
 RETURN avgSwErr;
END IF;

END;
' LANGUAGE 'plpgsql' ;

then
SELECT SwID, get_dated_SwErr(SwID) FROM 
 WHERE SwID = 'XX1' AND SwDate = current_date ;
should bring up the result.

Regards, Christoph



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Gist indexes on int arrays

[Greg Stark]

Greg Stark <[EMAIL PROTECTED]> writes:

> Can I have a GiST index on (foo_id, attribute_set_array) and have it be just
> as fast at narrowing the search to just foo_id = 900 but also speed up the ~
> operation?

Hm, so if I understand what I'm reading I can do this if I load the btree_gist
contrib module as well. I'm still not sure whether it'll be worthwhile for
this application though.

I have a bit of a problem though. Is building GiST indexes supposed to take
much much longer than building btree indexes? It's been running nearly an hour
and it's still going. The hard drive is hardly moving so it seems to be all
cpu usage. I don't even see any pgsql_tmp usage.

db=# CREATE INDEX cache_gist_idx on cache using gist ( foo_id , attribute_set 
gist__int_ops);

postgres 30176 86.3 22.2 64896 57344 ?   R11:08  40:32 postgres: postgres slo 
[local] CREATE INDEX


I don't remember exact numbers but building the normal btree index took on the
order of 15m. This will have to be rebuilt nightly, an hour long index build
won't be practical.

--
greg


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] Forcing query to use an index

[Jean-Luc Lachance]

I beg to differ.

A NULL field means not set.

Having to use work around because the database does not index null is
one thing, but making it a general rule is not.

Having NULL indexed would also speed up things when "is null" is part af
the query.

Until then...

JLL



Greg Stark wrote:
> 
> One suggestion I'll make about your data model -- I'm not sure it would
> actually help this query, but might help elsewhere:
> 
>  WHERE ( C.Disabled > '2003-02-28'
>   OR C.Disabled IS NULL
>)
> 
> Don't use NULL values like this. Most databases don't index NULLs (Oracle) or
> even if they do, don't make "IS NULL" an indexable operation (postgres).
> There's been some talk of changing this in postgres but even then, it wouldn't
> be able to use an index for an OR clause like this.
> 
> If you used a very large date, like -01-01 as your "not deactivated" value
> then the constraint would be C.disabled > '2003-02-28' and postgres could use
> an index on "disabled".
> 
> Alternatively if you have a disabled_flag and disabled_date then you could
> have an index on disabled_flag,disabled_date and uhm, there should be a way to
> use that index though I'm not seeing it right now.
> 
> This won't matter at first when 99% of your customers are active. And ideally
> in this query you find some way to use an index to find "kate" rather than
> doing a fully table scan. But later when 90% of the clients are disabled, then
> in a bigger batch job where you actually want to process every active record
> it could prevent postgres from having to dig through a table full of old
> inactive records.
>

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Insert multiple Rows

[Gary Stainburn]

On Tuesday 04 Mar 2003 10:54 am, Oleg Samoylov wrote:
> Hi,
>
> Saint X wrote:
> > Hi, I have a program that need to send a Postgresql database a lot of
> > rows, i'm doing it using FOR an INSERT, something like this
> > for i = 0 to 1000
> > {
> >  insert into table(item1) VALUES (i);
> > }
> >
> > And so on, as you can imagine these consume a lot of resources and
> > move so slowly, that's why I'm looking for a command to send more than
> > one row at the time, something like
> >
> > insert into table (item1) values ('1' / '2' / '3' / '4' ... / '1000');
>
> You can do instead:
>
> insert into table (item1) values ('1');
> insert into table (item1) values ('2');
> 
> insert into table (item1) values ('1000');
>
> As single query.
>
> But better use COPY command or prepared statment.
>
> Inserts slow commonly due to indexis and foreing keys.

If you use a 'prepared' insert within a transaction, would that speed things 
up - maybe by defering index updates?
-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] SETOF

[Fernando]

Hi,
I am using Postgresql version 7.2.2 
I made a small function...

CREATE FUNCTION ejem1(varchar) RETURNS SETOF to varchar as' 
SELECT names from mi_tabla WHERE city = $1; ' 
language ' SQL '; 

  ejem1 

  Sergio 
  Carlos 
  Fernando 

When wanting to obtain several columns I do this... 

CREATE FUNCTION ejem2(varchar) RETURNS SETOF mi_tabla as' 
SELECT * from mi_tabla WHERE city = $1;' 
language ' SQL '; 

  ejem2 
---
  137956448 
  137956448 
  137956448 

The number of registries that return is the correct, the question is, because 
it does not return the fields of the table, and that is what in its place 
this showing to me... 
Greetings and thank you very much!   

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Convert a text list to text array? Was: [SQL] Denormalizing duringselect

[Guy Fraser]

The email at the bottom gave me an idea, but it doesn't quite work:

CREATE AGGREGATE accumulate(
  BASETYPE = text,
  SFUNC = textcat,
  STYPE = text,
  INITCOND = '' );
--
SELECT ('{' || ltrim(accumulate(',' || tablename),',') || '}') as cruft
 FROM pg_tables
  WHERE hasindexes = 'f';
   cruft
---
 {pg_xactlock,pg_listener}
(1 row)
This produces somthing that looks like it could be able to be converted into 
an array but I cant figure out how to make it work.



Guy

Edmund Lian wrote:
Jeff and Josh,

I found this example in "Practical PostgreSQL"... will it do the job?

"""
The following example defines an aggregate function named sum(), for
use with the text data type. This aggregate calls the
textcat(text,text) function built into PostgreSQL to return a
concatenated "sum" of all the text found in its input values:
booktown=# CREATE AGGREGATE sum ( BASETYPE = text,
booktown(#SFUNC = textcat,
booktown(#STYPE = text,
booktown(#INITCOND = '' );
CREATE
booktown=# SELECT sum(title || ' ') FROM books WHERE title ~ '^L';
...snip...

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] LIMIT and SUBQUERIES

[Tomasz Myrta]

Chris wrote:
Hi all,

This question may be a bit confusing, and it is entirely possible that
I am going about it the wrong way, but any suggestions would be much
appreciated.  I'm trying to query a table of records that has a
(simplified) structure like the following:
owner int
description text
amount double
I want to do a select that returns the TOP 5 records ordered by
amount, PER OWNER.  I can easily construct this SQL query, the problem
arises in the fact that I want to have groups of the top five per
owner (an owner can obviously have more than 5 records, but I just
want the top 5 for each).
So anyway, I have the query that is working - but it returns all
records for all owners, when what I really want to do is return the
top 5 per each owner.
Any suggestions?

Thanks
Chris
It's not too easy to do this for large tables. If your table isn't too 
big, you can try this:

select
  t1.owner,
  t1.description,
  t1.amount
from
 some_table t1
 join some_table t2 using (owner)
where t2.amount<=t1.amount
group by t1.owner,t1.description,t1.amount
having count(*)<=5
In English:
"For each owner return these amounts, for which there are no more then 4 
smaller amounts"
This query is simple, but needs 0.5*amounts^2 calculations for each owner.

Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly