unsuscribe - Mailing list pgsql-sql

unsuscribe
----- Original Message -----
From: pgsql-sql-digest <owner-pgsql-sql-digest@hub.org>
To: <pgsql-sql-digest@hub.org>
Sent: Monday, August 16, 1999 5:00 AM
Subject: pgsql-sql-digest V1 #321


>
> pgsql-sql-digest        Sunday, August 15 1999        Volume 01 : Number
321
>
>
>
> Index:
>
> select [for update]??
> Re: [SQL] Intentionally inserting duplicates without aborting
> Re: [SQL] Multiple values for a field
> Re: [SQL] err: select f() from i where (f()) in (select f() from x group
by j);
> Agregate Problem?
> Re: [SQL] Agregate Problem?
> Re: [SQL] Multiple values for a field
> Re: [SQL] Agregate Problem?
>
> ----------------------------------------------------------------------
>
> Date: Sun, 15 Aug 1999 01:51:29 -0400
> From: Matthew Hagerty <matthew@venux.net>
> Subject: select [for update]??
>
> Greetings,
>
> Can someone explain to me how to use a "select for update"?
>
> Thanks,
> Matthew
>
> ------------------------------
>
> Date: Sun, 15 Aug 1999 16:06:06 +0300
> From: Herouth Maoz <herouth@oumail.openu.ac.il>
> Subject: Re: [SQL] Intentionally inserting duplicates without aborting
>
> At 18:59 +0300 on 12/08/1999, disser@sdd.hp.com wrote:
>
>
> > What I would like to do (and have manage to kludge together in Perl
> > DBI) is to attempt to insert (item_id, state, today's date) into
> > item_hist, and in the cases where that item/state combo exists
> > already, the insert will fail.  However, if I am using {AutoCommit =>
> > 0}, the whole transaction bombs, so I can only get away with this if
> > I'm AutoCommit'ing.
> >
> > Any thoughts on how I can do this with AutoCommit => 0?  I would
> > rather not have to look up all the status rows to figure out if an
> > error will occur.
>
> Are you saying that inserting a (some_item_id, some_state, some_date) and
> waiting for an error is more efficient than doing a SELECT 1 FROM ...
WHERE
> item_id = some_item_id, state = some_state and seeing whether or not you
> got any rows? It shouldn't be much of a difference, since both operations
> simply go through the index.
>
> In any case, if what you want to do is to insert a bulk of data, and only
> have the non-duplicates be inserted, then perhaps you should create a temp
> table of ited id and state, insert all the data into it (using COPY for
> faster insertions) and then:
>
> INSERT INTO item_history
> SELECT item_id, state, now() FROM temp_table t
> WHERE NOT EXISTS (
>   SELECT * FROM item_history i
>   WHERE  i.item_id = t.item_id AND i.state = t.state
> );
>
> Or maybe you meant that the insertion is done directly from the table
item?
> Then you don't need a temporary table. The general looks are the same,
> though you would probably want to add things to the WHERE clause.
>
> Herouth
>
> - --
> Herouth Maoz, Internet developer.
> Open University of Israel - Telem project
> http://telem.openu.ac.il/~herutma
>
> ------------------------------
>
> Date: Sun, 15 Aug 1999 16:26:31 +0300
> From: Herouth Maoz <herouth@oumail.openu.ac.il>
> Subject: Re: [SQL] Multiple values for a field
>
> At 18:11 +0300 on 13/08/1999, Mike Field wrote:
>
>
> > Do  you see any problem if I put the actual names of the authors,  comma
> > separated, in the author field (for example: Mike Field, Tom  Lane, ZZ
Top)?
> > I would do the same for my six fields which may have multiple  values
> > (author, region, subject, related_projects, doc_type, translations) I
have
> > about 550 documents to put into the database.
>
> Repeating groups are harder to update. What if you found out that you made
> a spelling mistake in some region, and you have to go through the entire
> table, find it in each of the strings, and replace with the correct
> version. What if you wanted to drop an author? You have to select it, fix
> and update. Too much work.
>
> > I know using text  fields would take up more space relative to just
using a
> > number to refer to  the author... but using a number makes me perform
more
> > loops and thus, more  processing time.
>
> An alternative may be to create an aggregate function that will give you
> the comma-separated list you desired upon request:
>
> CREATE AGGREGATE textcat_all
> (
> basetype = text,
> sfunc1 = textcat,
> stype1 = text
> );
>
> Then you can do something like:
>
> SELECT da.docid, textcat_all( a.Author_name || ',' )
> FROM docs_authors da, authors a
> WHERE da.authorid = a.id
> GROUP BY da.docid;
>
> This will return a comma-separated list of author names for each doc id,
> assuming the actual author name is in a separate table. There is an extra
> comma at the end, but I don't suppose that's too much of a problem.
>
> Herouth
>
> - --
> Herouth Maoz, Internet developer.
> Open University of Israel - Telem project
> http://telem.openu.ac.il/~herutma
>
> ------------------------------
>
> Date: Sun, 15 Aug 1999 16:40:36 +0300
> From: Herouth Maoz <herouth@oumail.openu.ac.il>
> Subject: Re: [SQL] err: select f() from i where (f()) in (select f() from
x group by j);
>
> At 03:13 +0300 on 14/08/1999, Chad Miller wrote:
>
>
> > If I create a view, with
> >
> > > create view foo as select timestamp, sum(num) from timelines where
> >(name='foo' or name='bar' or name='baz') group by timestamp;
> >
> > I get:
> > Table    = foo
> >
+-----------------------------+----------------------------------+-------+
> > |              Field          |              Type                |
Length|
> >
+-----------------------------+----------------------------------+-------+
> > | timestamp                   | datetime                         |     8
|
> > | sum                         | int2                             |     2
|
> >
+-----------------------------+----------------------------------+-------+
> >
> > (Note the fields' naming)  Still -- I'd like a more elegant way to do
this,
> > than creating a view.
> >
> > I came up with:
> >
> > > select timestamp, max(sum) from timelines where (timestamp, sum) in
> >(select timestamp, sum(num) from timelines where (name='foo' or
name='bar'
> >or name='baz') group by timestamp);
> >
> > ...which returns
> >
> > < ERROR:  attribute 'sum' not found
>
> The way to get rid of names that would make your life hard is to put field
> aliases in the CREATE VIEW:
>
> create view foo as select timestamp as ts_col, sum(num) as sum_col
> from timelines, where ....;
>
> Anyway, it's not the problem here. The problem is that you selected from
> timelines instead of from foo.
>
> As for a more elegant way of doing the same task, I'm not entirely sure,
> because I don't have the latest PostgreSQL here, but here is a general
idea:
>
> SELECT timestamp, sum( num ) as the_sum
> FROM timelines
> WHERE (name='foo' or name='bar' or name='baz')
> GROUP BY timestamp
> ORDER BY the_sum DESC
> LIMIT 1;
>
> Herouth
>
> - --
> Herouth Maoz, Internet developer.
> Open University of Israel - Telem project
> http://telem.openu.ac.il/~herutma
>
> ------------------------------
>
> Date: Sun, 15 Aug 1999 09:01:36 -0500
> From: "Len Morgan" <len-morgan@crcom.net>
> Subject: Agregate Problem?
>
> I am having a problem with queries that have an aggregate (sum in this
case)
> when no rows meet my criteria.  I still get back a row with null values
for
> all fields.  Is this the correct behavior?  I am trying to sum up daily
> sales quanties by items and if I don't have any sales on a particular day,
I
> want to print nothing.  I thought I could do this by detecting a 0 row
count
> but since I have the aggregates, I still 1 row.
>
> Example:
>
> SELECT itemno,sum(qty) FROM sales WHERE sale_date = 'now()::date'
>
> I would expect to get back zero rows if there were no sales today but I
get
> back 1.  Of course I also get back 1 row if I only sold 1 item.  Is this a
> bug or a "feature?"
>
> Thanks
>
> Len Morgan
>
> ------------------------------
>
> Date: Sun, 15 Aug 1999 17:27:57 +0300
> From: Herouth Maoz <herouth@oumail.openu.ac.il>
> Subject: Re: [SQL] Agregate Problem?
>
> At 17:01 +0300 on 15/08/1999, Len Morgan wrote:
>
>
> >
> > SELECT itemno,sum(qty) FROM sales WHERE sale_date = 'now()::date'
> >
> > I would expect to get back zero rows if there were no sales today but I
get
> > back 1.  Of course I also get back 1 row if I only sold 1 item.  Is this
a
> > bug or a "feature?"
>
> This is a correct behaviour when there is no GROUP BY. PostgreSQL does
this
> in GROUP BY, too, unfortunately. But it's not a big problem - you merely
> have to check whether the returned row has a NULL value instead of the
sum.
>
> Herouth
>
> - --
> Herouth Maoz, Internet developer.
> Open University of Israel - Telem project
> http://telem.openu.ac.il/~herutma
>
> ------------------------------
>
> Date: Sun, 15 Aug 1999 10:36:26 -0500
> From: "Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>
> Subject: Re: [SQL] Multiple values for a field
>
> On Sun, Aug 15, 1999 at 04:26:31PM +0300, Herouth Maoz wrote:
> >
> > An alternative may be to create an aggregate function that will give you
> > the comma-separated list you desired upon request:
> >
> > CREATE AGGREGATE textcat_all
> > (
> > basetype = text,
> > sfunc1 = textcat,
> > stype1 = text
> > );
> >
> > Then you can do something like:
> >
> > SELECT da.docid, textcat_all( a.Author_name || ',' )
> > FROM docs_authors da, authors a
> > WHERE da.authorid = a.id
> > GROUP BY da.docid;
> >
> > This will return a comma-separated list of author names for each doc id,
> > assuming the actual author name is in a separate table. There is an
extra
> > comma at the end, but I don't suppose that's too much of a problem.
> >
>
> And can be solved as so:
> SELECT da.docid, rtrim(textcat_all( a.Author_name || ',' ), ',') ...
>
> Excellent tip, Herouth. This one goes in the 'keepers' bag.
>
> Ross
> - --
> Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
> NSBRI Research Scientist/Programmer
> Computer and Information Technology Institute
> Rice University, 6100 S. Main St.,  Houston, TX 77005
>
> ------------------------------
>
> Date: Sun, 15 Aug 1999 11:48:41 -0400
> From: Tom Lane <tgl@sss.pgh.pa.us>
> Subject: Re: [SQL] Agregate Problem?
>
> > At 17:01 +0300 on 15/08/1999, Len Morgan wrote:
> >> SELECT itemno,sum(qty) FROM sales WHERE sale_date = 'now()::date'
>
> This is invalid as it stands --- you need "GROUP BY itemno" because of
> the aggregate function.  (IIRC, Postgres versions before 6.5 were a bit
> lax about checking for that error.)
>
> >> I would expect to get back zero rows if there were no sales today but I
get
> >> back 1.  Of course I also get back 1 row if I only sold 1 item.  Is
this a
> >> bug or a "feature?"
>
> Herouth Maoz <herouth@oumail.openu.ac.il> writes:
> > This is a correct behaviour when there is no GROUP BY. PostgreSQL does
this
> > in GROUP BY, too, unfortunately.
>
> I believe we finally agreed that producing an empty row when there is a
> GROUP BY is a bug (there was some debate about it).  I intend to change
> that behavior for 6.6, but haven't actually done it yet...
>
> regards, tom lane
>
> ------------------------------
>
> End of pgsql-sql-digest V1 #321
> *******************************
>