Re: [SQL] Dublicates pairs in a table.
On Tuesday 17 Sep 2002 7:36 am, Ries van Twisk wrote: > Richard, > > do you suggest using a stored procedure to handle this? I do expect that > the table will be large (for me large is a around 1-2 records, the > table as more columns but I only need the restriction on c1 & c2) but I > dont expect lots of inserts and deletes. Data entry will be done using a > regular user but of course a update needs to be done within a acceptable > time so the user does not get annoyed. I'd go with the UNIQUE INDEX. You're unlikely to be able to write code any faster than the built-in indexing code. It's also simpler and easier for someone else to see what's going on. The only time the index will slow you down is on an insert/delete when the index needs to be updated as well as the table. However, without the index you'll need to scan the table to check for duplicates. For thousands of records that can only be slower. To put your mind at ease, I'd put together some test data and try it - nothing like seeing some real evidence. HTH - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to select and result row number??
> "Yudie" == Yudie <[EMAIL PROTECTED]> writes:
Yudie> what is the select statement so I can get result records like this
Yudie> no cityname
Yudie> --
Yudie> 1 NEW YORK
Yudie> 2 LOS ANGELES
Yudie> 3 HOUSTON
Yudie> 4
Yudie> 5 ...
This is not really a SQL type result; you are probably better of
writing something in a procedural language. That said, something like
this seems to work for 7.2:
create sequence temp_counter;
select nextval('temp_counter'), * from whatever;
If you have an 'order by', the above will not work. You could then
try either building a temporary table or using a subselect
select nextval('temp_counter'), * from (select order by ...);
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
[EMAIL PROTECTED] 76-15 113th Street, Apt 3B
[EMAIL PROTECTED] Forest Hills, NY 11375
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Join Question
My office is working on a fantasy football database and, unfortunately, I have been tagged as the DBA. I'm a bit weak on set theory but I'm trying. Right now I am trying to calculate up game scores into the database rather than running through code to do that. A baseline of my schema is that: + Each team has a schedule number associated with it. The schedule numbers are linked to the game schedule table. + There is a game schedule table based on schedule numbers. This schedule is fixed and doesn't change season to season. If you want to change who plays who you change the team schedule numbers. + GameScheduleDetails is a view joining the team info with the schedule so I can easily see who plays who. + There is a game roster table that lists which players each team played in a given week. + After running stats through there is a table that lists the points for each player per week. + GamePointDetails is a view joining the game rosters with the weeklyplayerpoints. + The games table is a historical table that shows the scores between two teams for a given week and season. The SQL to run the calcs is below. I know that what is happening is that the join between the two teams scores is generating 64 rows of player points for each team instead of eight (a game roster consists of eight players). That means that the join that is occuring is a natural join rather than the desired inner join which leads to a multiplicative row count of 8 players from team 1 x 8 players from team 2 which is 64 rows per team. Since each player score is being counted 8 times I am just dividing the sum of each team score by 8 and it is spitting out the correct numbers. But, it is not working "properly". It is getting the job done but I want to understand is how to make it work properly where I don't have to do the divide by 8; So my question is... can I get rid of the divide by 8 or am I stuck with it? rjsjr insert into games ( season, weeknum, teamid1, team1pointsscored, team1maxpointsfor, teamid2, team2pointsscored, team2maxpointsfor ) select SEASON_NUM as Season, WEEK_NUMas WeekNum, gsd.team1id as Team1ID, sum(gpd1.points) / 8as Team1Score, 0 as Team1Max, gsd.team2id as Team2ID, sum(gpd2.points) / 8as Team2Score, 0 as Team2Max from gamescheduledetails gsd inner join gamepointdetails as gpd1 on gpd1.teamid = gsd.team1id and gpd1.weeknum= gsd.weeknum inner join gamepointdetails as gpd2 on gpd2.teamid = gsd.team2id and gpd2.weeknum= gsd.weeknum where gsd.weeknum = WEEK_NUM and gpd1.leagueid = gpd2.leagueid and gpd1.season = gpd2.season and gpd1.season = SEASON_NUM group by gsd.team1id, gsd.team1name, gsd.team2id, gsd.team2name ; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How to select and result row number??
On Mon, Sep 16, 2002 at 11:12:06PM -0400, Bruce Momjian wrote:
> Yudie wrote:
> Good question. The only easy answer I have is the creation of a temp
> table with a SERIAL column:
>
> CREATE TEMP TABLE out (cnt SERIAL, other_cols...);
> INSERT INTO out SELECT ... ORDER BY col;
Hmm, this needs to be:
INSERT INTO out (ther_cols...) SELECT ... ORDER BY col;
So that the cnt column gets filled from the default.
> SELECT * FROM out;
On Tue, Sep 17, 2002 at 10:14:58AM -0400, Roland Roberts wrote:
> create sequence temp_counter;
> select nextval('temp_counter'), * from whatever;
>
> If you have an 'order by', the above will not work. You could then
> try either building a temporary table or using a subselect
>
> select nextval('temp_counter'), * from (select order by ...);
Approximately the same solution, but without saving the result in a temp
table.
Hmm, with the new dependency code, is the auto-sequence from a SERIAL
in a temp table also a temp sequence? It get's put in the temp schema,
right? Seems we have a workaround for those wanting numbered result
sets.
BTW, Bruce's solution could be useful for those times you want to batch a
potentially large return set, but can't use cursors - the temp table will
live as long as the connection does, so as long as your connection pool
doesn't play round robin games on you, you can use the result count from
the INSERT to calculate batch sizes, and use OFFSET and LIMIT on SELECTs
from the temp table.
Ross
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] How to select and result row number??
"Ross J. Reedstrom" <[EMAIL PROTECTED]> writes: > Hmm, with the new dependency code, is the auto-sequence from a SERIAL > in a temp table also a temp sequence? It get's put in the temp schema, > right? Seems we have a workaround for those wanting numbered result > sets. That's the hard way; just do CREATE TEMP SEQUENCE ... works in prior releases too ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Returning a reference to a cursor from a function
On Thursday 12 Sep 2002 7:12 pm, david williams wrote: > To anyone who can help me, > > I am new at Postgresql and am having some problems. > I went a stage further attempt to put this query into a function as such > CREATE FUNCTION getallusers() RETURN integer AS' > DECLARE > Liahona CURSOR FOR Select * from users; > BEGIN > > FETCH FORWARD ALL IN Liahona; > > CLOSE Liahona; > END; > 'language 'plpgsql'; Number of issues here - it's RETURNS on the first line, you haven't OPENed the cursor and I don't think you can use that form of FETCH in the function. Look at the Programmers Guide, ch 23.7 "Cursors" for details on how to do it. To get you going, here's one that returns a count of how many records were fetched from an indicated table. -- BEGIN function -- DROP FUNCTION foo_count(text); CREATE FUNCTION foo_count(text) RETURNS integer AS ' DECLARE my_name ALIAS FOR $1; csr1 refcursor; dummy RECORD; n int4; BEGIN n:=0; RAISE NOTICE ''counting table: %'',my_name; OPEN csr1 FOR EXECUTE ''SELECT * FROM '' || my_name; FETCH csr1 INTO dummy; WHILE (FOUND) LOOP n:=n+1; FETCH csr1 INTO dummy; END LOOP; CLOSE csr1; RETURN n; END; 'language 'plpgsql'; -- END function -- Put this in a text-file and use \i filename from psql to import the definition - makes it easier to debug. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] cannot delete bug
Dear Friends, I am not sure this due to my hardware problem or due to a bug in the postgres 7.2. kzdb=# select id from mytable where id=7224; id -- 7224 (1 row) kzdb=# delete from mytable where id=7224; ERROR: Relation 41073353 does not exist kzdb=# My simple solution is to dump the table as text then \copy back in the table again after dropping the old table. This had repeatly happend to me. I guess there is something very wrong somewhere, bug I don't know. If any one has any experience please let me know. Apprciate. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How to select and result row number??
Ross J. Reedstrom wrote:
> On Mon, Sep 16, 2002 at 11:12:06PM -0400, Bruce Momjian wrote:
> > Yudie wrote:
>
>
> > Good question. The only easy answer I have is the creation of a temp
> > table with a SERIAL column:
> >
> > CREATE TEMP TABLE out (cnt SERIAL, other_cols...);
> > INSERT INTO out SELECT ... ORDER BY col;
>
> Hmm, this needs to be:
>
> INSERT INTO out (ther_cols...) SELECT ... ORDER BY col;
>
> So that the cnt column gets filled from the default.
Yes, thanks for the fix.
> > create sequence temp_counter;
> > select nextval('temp_counter'), * from whatever;
> >
> > If you have an 'order by', the above will not work. You could then
> > try either building a temporary table or using a subselect
> >
> > select nextval('temp_counter'), * from (select order by ...);
>
> Approximately the same solution, but without saving the result in a temp
> table.
I thought about doing it this way. However, a subselect as a
pseudotable is not guaranteed to return the data in any specific order,
so I don't think this method work work reliably. At least that was my
assumption.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How to select and result row number??
Bruce Momjian <[EMAIL PROTECTED]> writes:
>>> select nextval('temp_counter'), * from (select order by ...);
>>
>> Approximately the same solution, but without saving the result in a temp
>> table.
> I thought about doing it this way. However, a subselect as a
> pseudotable is not guaranteed to return the data in any specific order,
> so I don't think this method work work reliably.
Say what? Given the ORDER BY in the subselect, it will.
Of course, you can't do any grouping or other processing at the outer
level, but the example as given is just fine.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to select and result row number??
Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> >>> select nextval('temp_counter'), * from (select order by ...);
> >>
> >> Approximately the same solution, but without saving the result in a temp
> >> table.
>
> > I thought about doing it this way. However, a subselect as a
> > pseudotable is not guaranteed to return the data in any specific order,
> > so I don't think this method work work reliably.
>
> Say what? Given the ORDER BY in the subselect, it will.
>
> Of course, you can't do any grouping or other processing at the outer
> level, but the example as given is just fine.
When you specify a table in FROM, there is no ordering to the table. Is
it guaranteed that a subquery in FROM _does_ have an ordering. Does
ANSI say it has an ordering? What if the subquery is involved in a
join?
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] cannot delete bug
Kemin Zhou <[EMAIL PROTECTED]> writes: > kzdb=# select id from mytable where id=7224; > id > -- > 7224 > (1 row) > kzdb=# delete from mytable where id=7224; > ERROR: Relation 41073353 does not exist Have you got any rules, triggers, or foreign keys linking to or from that table? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to select and result row number??
Bruce Momjian <[EMAIL PROTECTED]> writes: >> Say what? Given the ORDER BY in the subselect, it will. > When you specify a table in FROM, there is no ordering to the table. Is > it guaranteed that a subquery in FROM _does_ have an ordering. If you write ORDER BY, we'll implement it. > Does ANSI say it has an ordering? ANSI forbids ORDER BY in sub-selects, so they don't have to address the question. > What if the subquery is involved in a join? We are entitled to reorder its output for purposes of processing the join, then. But that's entirely irrelevant to the example as given. There is no reason to reorder the subselect output, and we won't. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
