[SQL] Generating a range of integers in a query
Hello, I've got an interesting problem: I need to select all possible values of an attribute that do /not/ occur in the database. This would be easy (in my case at least) if there were a way to generate a table containing all integers between 1 and n, where n is the result of a subquery. In my case, n will be at most a few hundred. I would like to be able to generate this table as a subquery. Any ideas? Thanks, -- Aaron Bingham Senior Software Engineer Cenix BioScience GmbH ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Generating a range of integers in a query
On Jul 13, 2005, at 6:13 PM, Aaron Bingham wrote: This would be easy (in my case at least) if there were a way to generate a table containing all integers between 1 and n, where n is the result of a subquery. In my case, n will be at most a few hundred. I would like to be able to generate this table as a subquery. Any ideas? Take a look at generate_series(), available in v8.0 and above. http://www.postgresql.org/docs/8.0/interactive/functions-srf.html Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Copy user privileges
Thanks for the suggestions but as you guessed I do need to keep the old user and also groups would be unsuitable as I don't want either user to be able to see data on the other database. Thought there would be some SQL statement I could run on the sys tables but I don't know enough about the internals to attempt it. Thanks again, Graham -Original Message- From: Michael Fuhr [mailto:[EMAIL PROTECTED] Sent: 12 July 2005 20:29 To: Tom Lane Cc: Graham Vickrage; [email protected] Subject: Re: [SQL] Copy user privileges On Tue, Jul 12, 2005 at 03:12:50PM -0400, Tom Lane wrote: > "Graham Vickrage" <[EMAIL PROTECTED]> writes: > > I want to keep all the privileges but assign them to a new user. > > > What is the best way of doing this? > > How about just renaming the old user to a new name? > > I don't think we have an ALTER command for that, but an UPDATE on > pg_shadow would get the job done just as well. What about ALTER USER RENAME TO? test=# CREATE USER user1; CREATE USER test=# CREATE TABLE foo (x integer); CREATE TABLE test=# GRANT SELECT ON foo TO user1; GRANT test=# \z foo Access privileges for database "test" Schema | Name | Type | Access privileges +--+---+-- public | foo | table | {postgres=arwdRxt/postgres,user1=r/postgres} (1 row) test=# ALTER USER user1 RENAME TO user2; ALTER USER test=# \z foo Access privileges for database "test" Schema | Name | Type | Access privileges +--+---+-- public | foo | table | {postgres=arwdRxt/postgres,user2=r/postgres} (1 row) Renaming the user is only useful if you no longer need the old user. If you need to keep the old user and copy its privileges, then consider granting privileges to groups instead of to users -- then you could just add the new user to a group. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Generating a range of integers in a query
I use the following function which returns a date series. You can modify it to
return an int series instead
create or replace function alldates(date,date) returns setof date
as
'
declare
s alias for $1;
e alias for $2;
d date;
begin
d := s;
while d <= e
LOOP
return next d;
select d + \'1 day\'::interval into d;
END LOOP;
return null;
end;
'
LANGUAGE 'plpgsql'
;
select * from alldates('2004-07-01','2004-08-10');
-- Original Message ---
From: Aaron Bingham <[EMAIL PROTECTED]>
To: [email protected]
Sent: Wed, 13 Jul 2005 11:13:06 +0200
Subject: [SQL] Generating a range of integers in a query
> Hello,
>
> I've got an interesting problem: I need to select all possible values
> of an attribute that do /not/ occur in the database.
>
> This would be easy (in my case at least) if there were a way to
> generate a table containing all integers between 1 and n, where n is
> the result of a subquery. In my case, n will be at most a few
> hundred. I would like to be able to generate this table as a
> subquery. Any ideas?
>
> Thanks,
>
> --
>
> Aaron Bingham
> Senior Software Engineer
> Cenix BioScience GmbH
>
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
--- End of Original Message ---
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Generating a range of integers in a query
On Wed, 2005-07-13 at 04:13, Aaron Bingham wrote: > Hello, > > I've got an interesting problem: I need to select all possible values > of an attribute that do /not/ occur in the database. > > This would be easy (in my case at least) if there were a way to > generate a table containing all integers between 1 and n, where n is > the result of a subquery. In my case, n will be at most a few > hundred. I would like to be able to generate this table as a > subquery. Any ideas? Take a look here: http://www.postgresql.org/docs/8.0/interactive/functions-srf.html specifically, the generate_series() function. ---(end of broadcast)--- TIP 1: 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] getting back autonumber just inserted
On Jul 7, 2005, at 4:14 PM, Theodore Petrosky wrote:
you have to use currval inside a transaction...
begin;
insert something that increments the counter;
select currval('sequence_name');
end;
using currval inside a transaction guarantees that the
value is correct for your insert statement and has not
changed by another insert statement.
your understanding of currval() is completely incorrect. no
transaction is required.
smime.p7s
Description: S/MIME cryptographic signature
[SQL] Sql Query help: Remove Sub Selects
Hi Gang,
I'm trying to optimize a query:
This query below returns something like this:
event_id | eu_tid | event_name | event_when | day | mon |
start | end|event_users |
contact_phone| contact_pager | num_opps
--++---++-+-+--+--++-+---+--
196651 | 1 | Show Event Type Color | Jul 06th, 2005 | 06 | 07 |
04:27 pm | 05:27 pm | {"Mickey Mouse","Donal Duck"} |
{555-555-,555-555-} | {}|0
203651 | 1 | Schedule Appt.| Jul 08th, 2005 | 08 | 07 |
02:35 pm | 03:35 pm | {"George Bush","Bill Clinton"} | {}
| {}|0
Instead of doing multiple selects using array_accum, I would like to use an
INNER JOIN
I'm at a stand-still. I just can't seem to get anything rolling.
Any help is greatly appreciated.
-- Start Query
SELECT eu.event_id,
eu.eu_tid,
e.event_name,
to_char(e.event_when, 'Mon DDth, ') AS event_when,
to_char(e.event_when, 'DD') AS day,
to_char(e.event_when, 'MM') AS mon,
to_char(e.event_when, 'HH:MI am') AS start,
to_char((e.event_when + e.duration), 'HH:MI am') AS end,
(SELECT array_accum(get_username(eu2.user_id)) FROM event_users eu2
inner join user_table ut9 ON ut9.user_id = eu2.user_id
WHERE eu2.event_id = e.event_id AND eu2.user_id != 4223651) AS
event_users,
(SELECT array_accum(ut.phone_nbr) FROM event_users eu2 INNER JOIN
user_table ut ON ut.user_id = eu2.user_id
WHERE eu2.event_id = e.event_id AND eu2.user_id != 4223651) AS
contact_phone,
(SELECT array_accum(ut.pager_ph_nbr) FROM event_users eu2 INNER JOIN
user_table ut ON ut.user_id = eu2.user_id
WHERE eu2.event_id = e.event_id AND eu2.user_id != 4223651) AS
contact_pager,
(SELECT count(*) FROM opp_events AS oe WHERE oe.event_id =
e.event_id) AS num_opps
FROM events AS e
INNER JOIN event_users AS eu
ON eu.event_id = e.event_id
AND eu.user_id = 4223651
LEFT JOIN event_repeats er
ON er.event_id = e.event_id
WHERE e.event_when BETWEEN '2005-07-03 0:00:00' AND '2005-07-09 23:59:59'
AND e.status != 0 AND er.repeat_type IS NULL
ORDER BY e.event_when ASC
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Dynamic Offset Determination
I came up with a clever solution to this myself...
I know the determined value for my CriteriaCol, so I can simply get the
count of all the rows with criteria <= my value...
SELECT count(*) FROM foo WHERE CriteriaCol<=constraint ORDER BY OrderCol;
Thanks,
Dave
---
Problem Statement:
I want to return n rows from a table.
These n rows are relative to an offset.
The part I can't figure out:
The offset needs to be determined from values in the rows.
Example:
Given a table foo:
CriteriaCol integer
OrderedCol integer
I can figure out the number of rows
SELECT COUNT(CriteriaCol) FROM foo;
How do I determine the offset?
SELECT {rownum?} FROM foo WHERE CriteriaCol = SomeValidValue;
With the rownum I can then determine the rows I want.
SELECT * FROM foo ORDER BY OrderedCol LIMIT 21 OFFSET (rownum-10);
Don't worry about boundary conditions, functions, etc, I can sort that out
once I know how to get the offset.
--
Searching the forums, the one potential hack I've found is:
Quote from: http://archives.postgresql.org/pgsql-sql/2003-04/msg00287.php
create temp sequence foo;
select nextval('foo'), * from
(select ... whatever ... order by something) ss;
drop sequence foo;
Presumably I would get my ordinal value from the sequence before dropping
it.
This seems like it would be a potentially slow process. Also, this
solution dates to 2003, so I thought it worth asking in case a better
solution has come along.
Thanks for suggestions/solutions
Dave
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] Dynamic Offset Determination
Problem Statement:
I want to return n rows from a table.
These n rows are relative to an offset.
The part I can't figure out:
The offset needs to be determined from values in the rows.
Example:
Given a table foo:
CriteriaCol integer
OrderedCol integer
I can figure out the number of rows
SELECT COUNT(CriteriaCol) FROM foo;
How do I determine the offset?
SELECT {rownum?} FROM foo WHERE CriteriaCol = SomeValidValue;
With the rownum I can then determine the rows I want.
SELECT * FROM foo ORDER BY OrderedCol LIMIT 21 OFFSET (rownum-10);
Don't worry about boundary conditions, functions, etc, I can sort that out
once I know how to get the offset.
--
Searching the forums, the one potential hack I've found is:
Quote from: http://archives.postgresql.org/pgsql-sql/2003-04/msg00287.php
create temp sequence foo;
select nextval('foo'), * from
(select ... whatever ... order by something) ss;
drop sequence foo;
Presumably I would get my ordinal value from the sequence before dropping
it.
This seems like it would be a potentially slow process. Also, this
solution dates to 2003, so I thought it worth asking in case a better
solution has come along.
Thanks for suggestions/solutions
Dave
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[SQL] ORDER records based on parameters in IN clause
Thanks!
This worked beautifully.
Here’s what I have:
SELECT a.listing_ id FROM cls.listings a
JOIN (SELECT listing_id,count(listing_id) AS
count FROM cls.tagslistings
WHERE cust_id =27
AND tag_id IN (SELECT tag_id FROM cls.tags
WHERE tag_name IN ('toys','263'))
GROUP BY listing_seq_id
) AS X ON X.listing_id= a.listing_id
ORDER by X.count DESC
R. Verghese
Author: Zac
Date: 2005-06-29 05:122005-06-29 12:12 -700UTC
To: pgsql-sql
Subject: Re: [SQL] ORDER
records based on parameters in IN clause
> SELECT
> table.*
> FROM
> table
> JOIN (SELECT id, count(id) AS count FROM... your subquery) AS x
> ORDER BY
> x.count
>
> Bye.
Sorry: I forgot join condition:
SELECT
table.*
FROM
table JOIN (SELECT id, count(id) AS count FROM... your subquery) AS x ON
(table.id = x.id)
ORDER BY
x.count
[SQL] Possible to use a table to tell what table to select from?
Hello I've been thinking on a potential problem I might get in a distant future, but once I started thinking on it I just as well had to check... Is it possible to have say ~ 6 tables, where the first one is a 'reference' to all the others (they all are with the same structure). The other 5 or so have information stored in importance/access/age, where one table will be only recent/important things, the other with a bit lower priority and so on until the last table that can be really huge and slow and used more like a storage ... Is it possible to write an SQL query to let the first table figure out what table one will do the actual select from? like the pseudo code: SELECT name,content FROM (SELECT tablename FROM table1 WHERE item_id="123456") Or is this a totally pointless thing, as the first table would need to be so large either way? (or only good for storing large blobs and such?) /Frank H ---(end of broadcast)--- TIP 1: 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] getting back autonumber just inserted
Sounds like M$ SuckQL's @@identity value. ;-) Larry On 7/7/05, Michael Fuhr <[EMAIL PROTECTED]> wrote: > On Fri, Jul 08, 2005 at 01:56:26AM +0200, PFC wrote: > > >Do you mean with lastval()? Here's what happens: > > > > Hm, interesting, you mean the return value of lastval() also depends > > if you set your constraints to deferred or immediate ? > > My mind's ablank trying to contrive a situation where that would > matter. Can you provide an example? > > In any case, I simply meant to point out that 8.1's lastval() will > be seductively convenient because you don't have to pass it a > sequence name, but the value it returns might not be the value you > want. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > ---(end of broadcast)--- TIP 1: 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] getting back autonumber just inserted
If you have a trigger on your table that inserts a record in a table
and shares the same sequence, what value do you get back, the
triggered curval, or the currently inserted one?
Being a lazy bum, this is why I still prefer the "get key - insert
record" approach. Less brain power required. ;-)
Larry
On 7/7/05, Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Thu, Jul 07, 2005 at 01:14:33PM -0700, Theodore Petrosky wrote:
> >
> > you have to use currval inside a transaction...
>
> Not true. Have you observed otherwise?
>
> > begin;
> > insert something that increments the counter;
> > select currval('sequence_name');
> > end;
> >
> > using currval inside a transaction guarantees that the
> > value is correct for your insert statement and has not
> > changed by another insert statement.
>
> currval() returns the most recently obtained value from the sequence
> in the current session, regardless of what other sessions are doing
> or whether the current session is in a transaction. See the
> documentation and the FAQ:
>
> http://www.postgresql.org/docs/8.0/static/functions-sequence.html
>
> "Notice that because this is returning a session-local value, it
> gives a predictable answer whether or not other sessions have
> executed nextval since the current session did."
>
> http://www.postgresql.org/docs/faqs.FAQ.html#4.11.3
>
> "4.11.3) Doesn't currval() lead to a race condition with other users?
>
> "No. currval() returns the current value assigned by your session, not by
> all sessions."
>
> You can do experiments to confirm what the documentation states.
> If you see different behavior then please put together a self-contained
> test case and report it as a bug.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] two sums in one query
I have assuemed that the row will having eighter Debit account or Credit account, the following Query will give sum of debit and credit accounts SELECT SUM( CASE WHEN COALESCE( DEBIT , 0 ) <> 0 THEN COALESCE( AMOUNT , 0 ) ELSE 0 END ) AS DEBIT_AMT , SUM( CASE WHEN COALESCE( CREDIT , 0 ) <> 0 THEN COALESCE( AMOUNT , 0 ) ELSE 0 END ) AS CREDIT_AMT FROM Regards, R.Muralidharan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Kenneth Gonsalves Sent: Friday, July 08, 2005 11:18 AM To: [email protected] Subject: [SQL] two sums in one query hi i have a table like this: serialno varchar, debit integer, credit integer, amount numeric the columns 'debit' and 'credit' refer to the acount number. I want to query the table to select all the rows where either debit = account or credit=account and to get two sums - one of the amounts where the account is on the debit side and the other on the credit side. Can i do this in one query? if so, how? -- regards kg http://www.livejournal.com/users/lawgon tally ho! http://avsap.org.in ಇಂಡ್ಲಿನಕ್ಸ வாழ்க! ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] function parameters : bug?
pg 8.0.3 This behaviour seems odd to me: CREATE TABLE mytable ( inta INTEGER ); CREATE OR REPLACE FUNCTION myfunction (inta integer) RETURNS integer AS $body$ begin insert into mytable(inta) values (inta); return(0); end; $body$ LANGUAGE 'plpgsql'; select myfunction(123); ERROR: syntax error at or near "$1" at character 22 QUERY: insert into myTable( $1 ) values ( $2 ) CONTEXT: PL/pgSQL function "myfunction" line 2 at SQL statement The problem is with the line in my myfunction: insert into mytable(inta) values (inta); The server doesn't like the field name being the same as the name of the function parameter. I don't see why, as I can see no opportunity for ambiguity. Problem vanishes with a different function parameter name: CREATE OR REPLACE FUNCTION myfunction (my_inta integer) RETURNS integer AS $body$ begin insert into myTable(inta) values (my_inta); return(0); end; $body$ LANGUAGE 'plpgsql' But, this means that if I change the design of my tables, I have to possibly change my function headers rather than merely the code inside them. regards Richard ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
