Re: [SQL] Same question about PostgreSql
> More than Interbase :-) More than MS SQL Server. Maybe less than > Oracle or MySQL; it's open to debate. Um. In my experience, PostgreSQL is more stable than MySQL. MySQL dies on me all the time, but I've never had it happen with PostgreSQL. (7.0.3, currently.) -- johs ---(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
[SQL] Using Random Sequence as Key
Hello, I was wondering if noone can help me maybe someone could at least give me some directions where to look for info or where to ask: I wanted to index a table by a random key. Exemplifying, when a insert is made, the id value is automatically filled with a random number between 1 and 9. I'm pretty new in pgsql so I could use a howto or something, and I repeat, if you don't know how, directions to where I can find info on that or where I can ask someone that might know would be enough. Thank you ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Using Random Sequence as Key
Bernardo de Barros Franco writes:
> I wanted to index a table by a random key. Exemplifying, when a insert is
> made, the id value is automatically filled with a random number between
> 1 and 9.
=> create table test (id int default random() * 8 + 1, content text);
CREATE
=> insert into test (content) values ('hi');
INSERT 36163 1
=> insert into test (content) values ('there');
INSERT 36164 1
=> insert into test (content) values ('blah');
INSERT 36165 1
=> select * from test;
id | content
---+-
61616 | hi
72605 | there
83469 | blah
(3 rows)
Verifying the default expression for correct boundaries is left as an
exercise. ;-)
--
Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] RTREE on points
Julian Scarfe wrote: > > explain select * from nodes where box(node,node) @ '((1,1),(3,3))'::box; > NOTICE: QUERY PLAN: > Seq Scan on nodes (cost=0.00..1.10 rows=1 width=28) > this should work, assuming you have enough points to make a difference (in the optimizer's mind, at least). the optimizer still doesn't do a great job of knowing when it's best to use an index, although, in your sample, there's no way it would ever be cheaper to use an index. there's simply not enough data there. you can test to see if an index can be used by a query by shutting off the sequential scans (set enable_seqscan=off) and retrying the query. essentially, this forces it to use an index scan if at all possible. -- Jeff Hoffmann PropertyKey.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Using Random Sequence as Key
Bernardo de Barros Franco writes:
> But my only question would be, in the example quoted would id be really the
> table index and is it unique?
Making unique *and* random numbers is a pretty complex (and slow) thing to
do in general. Consider redesigning your application.
>
> Thank you
>
> Quoting:
> >Bernardo de Barros Franco writes:
> >
> > > I wanted to index a table by a random key. Exemplifying, when a insert
> >is
> > > made, the id value is automatically filled with a random number between
> > > 1 and 9.
> >
> >=> create table test (id int default random() * 8 + 1, content
> >text);
> >CREATE
> >=> insert into test (content) values ('hi');
> >INSERT 36163 1
> >=> insert into test (content) values ('there');
> >INSERT 36164 1
> >=> insert into test (content) values ('blah');
> >INSERT 36165 1
> >=> select * from test;
> > id | content
> >---+-
> > 61616 | hi
> > 72605 | there
> > 83469 | blah
> >(3 rows)
> _
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
>
>
--
Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Range of Serial values
Yes, there is. When you create a serial column a sequence is created, which does the counting. You can create a serial column, drop the associated sequence and create a new one with the command: "CREATE SEQUENCE seqname START 1000". See also "\h CREATE SEQUENCE". HTH, Poul L. Christansen cbell wrote: > > Hello everyone, > > when creating a serial column, is there a way to specify which number it > will start counting from? For example, if you wanted all Serial ID's to > start at 1000 and count up from there, with no numbers below that. > > Thanks, > Chris. > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] RULE ... TO table.column
Saluton, I am using , | SELECT version(); | version | | PostgreSQL 7.0.2 on i586-pc-linux-gnulibc1, compiled by gcc 2.95.1 | (1 row) ` , and upgrading to 7.1 is not possible (it was hard enough to get the admin to upgrade from 6.5.3). While the docs say that I can create a rule on table.column, I get the error message: , | litdb=> create rule no_update_on_journal_title__rl as | litdb-> on update to journal.title do instead nothing; | ERROR: attribute level rules currently not supported ` Am I doing something wrong here, or do I have to work around this via ... where old.title = new.title ... (would this work?), or should I use a before-trigger? Are ``attribute level rules'' supported in 7.1 (so I can at least put a note into the code)? Thanks in advance, Albert. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Range of Serial values
Hello everyone, when creating a serial column, is there a way to specify which number it will start counting from? For example, if you wanted all Serial ID's to start at 1000 and count up from there, with no numbers below that. Thanks, Chris. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Using Random Sequence as Key
On Sun, Apr 15, 2001 at 10:58:40PM -0300, Bernardo de Barros Franco wrote: > I wanted to index a table by a random key. Exemplifying, when a insert is > made, the id value is automatically filled with a random number between > 1 and 9. I'm pretty new in pgsql so I could use a howto or Bernardo, Do you really need the keys to be random numbers? Can't the keys be sequential numbers, like 1,2,3,4...? That'd be a lot easier to make unique, and as far as the user is concerned, it'll be random for him/her. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Oh my GOSH! A Prompt!!! HELP A PROMPT ! HELP ! ---(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] Range of Serial values
Hi,
I believe you could also create the sequence, then update it with
setval('', );
Regards and best wishes,
Justin Clift
"Poul L. Christiansen" wrote:
>
> Yes, there is.
>
> When you create a serial column a sequence is created, which does the
> counting.
>
> You can create a serial column, drop the associated sequence and create
> a new one with the command:
> "CREATE SEQUENCE seqname START 1000".
>
> See also "\h CREATE SEQUENCE".
>
> HTH,
> Poul L. Christansen
>
> cbell wrote:
> >
> > Hello everyone,
> >
> > when creating a serial column, is there a way to specify which number it
> > will start counting from? For example, if you wanted all Serial ID's to
> > start at 1000 and count up from there, with no numbers below that.
> >
> > Thanks,
> > Chris.
> >
> > ---(end of broadcast)---
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Range of Serial values
Thank you Poul and Justin, I'll give it a try!!! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Using Random Sequence as Key
Bernardo de Barros Franco writes: > Hello, I was wondering if noone can help me maybe someone could at least > give me some directions where to look for info or where to ask: > I wanted to index a table by a random key. As others have pointed out, making a unique random primary key is tough. What I do for my cookie on my web based login system is have two fields in my database, one's the "id SERIAL", the other is a "magiccookie CHAR(16)" which I populate with 16 random characters on the initial insert. My cookie is then of the format "id/magiccookie". In my login verification code I split on the "/" character and query on "WHERE id=$id AND magiccookie=$magiccooke". Even though the "id" field is encoded in the cookie in plain text a cracker can't just guess at the user id number because that 16 character magiccookie needs to match as well. This also lets me be pretty loose about the id information, I can use it in other public places, because only the magiccookie needs to be restricted to being known by the logged in user. Dan ---(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] RTREE on points
Julian Scarfe wrote: > > It hadn't occured to me that the index would simply not be used and I'm > grateful for the pointer to the appropriate variable. i wouldn't recommend turning off sequential scans for day-to-day usage, but it certainly can be useful for debugging and testing. if you have specific queries that you need optimized, you can do that, but the whole point of cost estimates is to give a good estimate of what a normal query would return, so if you have a lot of ad-hoc queries, it's probably better to just trust the system. > Nevertheless, wouldn't... > > CREATE INDEX test_rtree ON nodes USING RTREE (node); > (which fails) > > ...be a lot simpler than... > > CREATE INDEX test_rtree ON nodes USING RTREE (box(node,node)); > (which succeeds, as above) > > ? yes, it does seem like a little more work, but there doesn't seem to be a lot of usage of the geometric functions by the developers to look at missing features -- they're mostly just reactive to problems. i really have never dug into tweaking access methods to get this to work, but i would imagine it's not that hard to implement. -- Jeff Hoffmann PropertyKey.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] RTREE on points
Jeff Hoffmann <[EMAIL PROTECTED]> writes: > yes, it does seem like a little more work, but there doesn't seem to be > a lot of usage of the geometric functions by the developers to look at > missing features -- they're mostly just reactive to problems. Jeff's correct, none of the core developers have much time to spend on adding features to the geometric datatypes. If someone else wants to step up to the plate, though, contributions are welcome ;-) The procedure for adding a new index opclass is somewhat documented for btree opclasses. To add an rtree opclass you'd be adding a different set of operators and support functions, which set isn't documented anywhere that I know of; you'd have to look at the existing examples to figure out what is needed. BTW, you should also look at the GIST stuff and figure out whether it might not be better to develop a GIST opclass instead of rtree. In the long run I suspect GIST will be better supported than rtree, since it's more general. regards, tom lane ---(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 Random Sequence as Key
Thank you it was pretty much something like that. I need to make a sale
table but the sale code is a random number. Since it is going to be used in
all queries and it is unique, it might as well be my table key (index). I
could make a index and other field to be the sale code but then I would have
2 different indexes, the table index and the sale code that would be used
for all queries.
But my only question would be, in the example quoted would id be really the
table index and is it unique?
Thank you
Quoting:
>Bernardo de Barros Franco writes:
>
> > I wanted to index a table by a random key. Exemplifying, when a insert
>is
> > made, the id value is automatically filled with a random number between
> > 1 and 9.
>
>=> create table test (id int default random() * 8 + 1, content
>text);
>CREATE
>=> insert into test (content) values ('hi');
>INSERT 36163 1
>=> insert into test (content) values ('there');
>INSERT 36164 1
>=> insert into test (content) values ('blah');
>INSERT 36165 1
>=> select * from test;
> id | content
>---+-
> 61616 | hi
> 72605 | there
> 83469 | blah
>(3 rows)
_
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
---(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
[SQL] Re: Using Random Sequence as Key
Since I can't really redesign my application, I could index the table using a serial but still would need a field with the random and unique number. I tried using including a unique in : create table test (id int UNIQUE default random() * 8 + 1, content text); and it didn't return any errors, I'm just not sure if it actually worked. I needed the random field because if I use serial and the user gets a 34203 he's sure that 34202 exists, and that (probably, there where 34202 inserts before him (or at least an offset + some)). Using a random just makes the user totally blind. As I said I could use a serial for indexing the table but I NEED the random field and I need to to be unique since all the queries will be using it as a search parameter. If inserting this way is slow it's not such a big deal since it's a small db and inserts are seldom made. Thanks in advance for any help. Quoting: > On Sun, Apr 15, 2001 at 10:58:40PM -0300, Bernardo de Barros Franco wrote: > > > I wanted to index a table by a random key. Exemplifying, when a insert is > > made, the id value is automatically filled with a random number between > > 1 and 9. I'm pretty new in pgsql so I could use a howto or > > Bernardo, > > Do you really need the keys to be random numbers? Can't the keys be > sequential numbers, like 1,2,3,4...? That'd be a lot easier to make > unique, and as far as the user is concerned, it'll be random for him/her. > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: Using Random Sequence as Key
It is a good way to solve it but since I don't want/can't use cookies in my application I could use only the random key but it would have to be unique or the where key=$key could refer to multiple lines or I would have to ask for the user to input both the random and the serial number wich wouldn't be very good either. That's the reason I've being trying to make a random key wich would be unique so I could ask for the user to type his order code and then select info from orders where ordercode=$ordercode and not have the risk of getting multiple answers (more then one line with that ordercode) or someone typing his ordercode-1 and accessing someone elses form. Ok, if I don't use a password or other protection even with random users can keep trying to guess but I don't have much of a problem with someone accessing someone else's form, I just don't want the user to let's say type his ordercode and by mistake type the last char say 1 less then his own and access someone else form and be completely lost. With random that still can happen but it is so less likely that will do. Thank you Quoting: > Bernardo de Barros Franco writes: > > Hello, I was wondering if noone can help me maybe someone could at least > > give me some directions where to look for info or where to ask: > > I wanted to index a table by a random key. > > As others have pointed out, making a unique random primary key is > tough. What I do for my cookie on my web based login system is have > two fields in my database, one's the "id SERIAL", the other is a > "magiccookie CHAR(16)" which I populate with 16 random characters on > the initial insert. > > My cookie is then of the format "id/magiccookie". In my login > verification code I split on the "/" character and query on "WHERE > id=$id AND magiccookie=$magiccooke". Even though the "id" field is > encoded in the cookie in plain text a cracker can't just guess at the > user id number because that 16 character magiccookie needs to match as > well. > > This also lets me be pretty loose about the id information, I can use > it in other public places, because only the magiccookie needs to be > restricted to being known by the logged in user. ---(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] RTREE on points
Julian Scarfe wrote:
> >
> > explain select * from nodes where box(node,node) @ '((1,1),(3,3))'::box;
> > NOTICE: QUERY PLAN:
> > Seq Scan on nodes (cost=0.00..1.10 rows=1 width=28)
From: "Jeff Hoffmann" <[EMAIL PROTECTED]>
> this should work, assuming you have enough points to make a difference
> (in the optimizer's mind, at least). the optimizer still doesn't do a
> great job of knowing when it's best to use an index, although, in your
> sample, there's no way it would ever be cheaper to use an index.
> there's simply not enough data there. you can test to see if an index
> can be used by a query by shutting off the sequential scans (set
> enable_seqscan=off) and retrying the query. essentially, this forces it
> to use an index scan if at all possible.
And indeed it does, thank you, Jeff:
# set enable_seqscan=off;
SET VARIABLE
# explain select * from nodes where box(node,node) @ '((1,1),(3,3))'::box;
NOTICE: QUERY PLAN:
Index Scan using test_rtree on nodes (cost=0.00..2.02 rows=1 width=28)
It hadn't occured to me that the index would simply not be used and I'm
grateful for the pointer to the appropriate variable.
Nevertheless, wouldn't...
CREATE INDEX test_rtree ON nodes USING RTREE (node);
(which fails)
...be a lot simpler than...
CREATE INDEX test_rtree ON nodes USING RTREE (box(node,node));
(which succeeds, as above)
?
The latter feels contorted and possibly inefficient. After all, I don't
do...:
CREATE TABLE "nodes" (
"node" point,
"node_name" character varying(30)
);
INSERT INTO nodes VALUES ('(1,1)', 'a');
INSERT INTO nodes VALUES ('(1,2)', 'b');
INSERT INTO nodes VALUES ('(3,2)', 'c');
INSERT INTO nodes VALUES ('(5,4)', 'd');
INSERT INTO nodes VALUES ('(7,8)', 'e');
INSERT INTO nodes VALUES ('(11,10)', 'f');
INSERT INTO nodes VALUES ('(101,11)', 'g');
CREATE INDEX test_btree ON nodes USING BTREE (textcat(node_name,node_name));
...if I want to index by name? (even though in principle it would work)
Thanks for any guidance.
Julian Scarfe
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Cursors in plpgsql
Hi Is there a command in plpgsql similar to %NOTFOUND of oracle? i want to write a load script which takes each row from the temporary table, do some processing and insert into actual tables. Any idea how i can accomplish this??? thank you regards Preeti ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
