[SQL] restircting rows
hi all, my query return 100 rows but if i am intrested only from 40-60 rows then how to restrict. for example select * from students (returns 100 rows).but i am intrested only rows from 40 to 60.
Re: [SQL] passing array(java) to postgre sql function
Hi, Bond,
bond wrote:
> public Object getArray(){
> ArrayList temp = new ArrayList();
> temp.add("");
> temp.add("");
> temp.add("T");
> temp.add("Q");
> return temp.toArray();
> }
> After this i am passing this.getArray() values to database.
As a first guess, have a look at java.sql.Array interface.
And maybe the people on the [email protected] mailing list know
better. :-)
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] restircting rows
am 22.02.2006, um 9:06:57 - mailte padmanabha konkodi folgendes: > > > hi all, > > my query return 100 rows but if i am intrested only from 40-60 rows then how > to restrict. > > for example > > select * from students (returns 100 rows).but i am intrested only rows from > 40 to 60. select * from students limit 20 offset 40; HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(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] restircting rows
padmanabha konkodi wrote: hi all, my query return 100 rows but if i am intrested only from 40-60 rows then how to restrict. for example select * from students (returns 100 rows).but i am intrested only rows from 40 to 60. SELECT * FROM student ORDER BY something LIMIT 20 OFFSET 40; Be aware that this will have to fetch 60 rows and throw the first 40 away. -- Richard Huxton Archonet Ltd ---(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] restircting rows
On Feb 22, 2006, at 18:06 , padmanabha konkodi wrote: my query return 100 rows but if i am intrested only from 40-60 rows then how to restrict. for example select * from students (returns 100 rows).but i am intrested only rows from 40 to 60. See LIMIT and OFFSET. http://www.postgresql.org/docs/8.1/interactive/sql-select.html#SQL-LIMIT Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Feature, my misconception or bug??
In PgSQL 7.4.12, foodb=# SELECT '255.255.255.255/24'::cidr; ERROR: invalid cidr value: "255.255.255.255/24" DETAIL: Value has bits set to right of mask. foodb=# SELECT '255.255.255.255/25'::cidr; cidr 255.255.255.255/25 (1 row) foodb=# in this case ... has no bits set to right of 8 LSB ^ ?? -- -Achilleus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Feature, my misconception or bug??
Am Mittwoch, 22. Februar 2006 13:04 schrieb Achilleus Mantzios: > foodb=# SELECT '255.255.255.255/24'::cidr; > ERROR: invalid cidr value: "255.255.255.255/24" > DETAIL: Value has bits set to right of mask. > in this case > ... > has no bits set to right of > 8 LSB ^ I'm sure you are aware that "1" is a set bit, so which part are you not understanding? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] Feature, my misconception or bug??
Hi, Peter, Peter Eisentraut wrote: > Am Mittwoch, 22. Februar 2006 13:04 schrieb Achilleus Mantzios: > >>foodb=# SELECT '255.255.255.255/24'::cidr; >>ERROR: invalid cidr value: "255.255.255.255/24" >>DETAIL: Value has bits set to right of mask. > > >>in this case >>... >>has no bits set to right of >> 8 LSB ^ > > > I'm sure you are aware that "1" is a set bit, so which part are you not > understanding? I guess he's confused by the fact that /24 complains about bits 25-31 beeing set, while /25 does not complain aobut bigs 26-31 beeing set. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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] Feature, my misconception or bug??
Achilleus Mantzios wrote: > In PgSQL 7.4.12, > > foodb=# SELECT '255.255.255.255/24'::cidr; > ERROR: invalid cidr value: "255.255.255.255/24" > DETAIL: Value has bits set to right of mask. > foodb=# > > SELECT '255.255.255.255/25'::cidr; > cidr > > 255.255.255.255/25 > (1 row) This one is refused in 8.1, so I guess that's a fixed bug. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Feature, my misconception or bug??
O Peter Eisentraut έγραψε στις Feb 22, 2006 : > Am Mittwoch, 22. Februar 2006 13:04 schrieb Achilleus Mantzios: > > foodb=# SELECT '255.255.255.255/24'::cidr; > > ERROR: invalid cidr value: "255.255.255.255/24" > > DETAIL: Value has bits set to right of mask. > > > in this case > > ... > > has no bits set to right of > > 8 LSB ^ > In the above statement there was a "?", which is missing here. It should read: "in this case ... has no bits set to right of 8th Least Significant Bit???" (with a ^ showing the position where the mask ends, making it obviously visible to the ones who use text base MUAs in fixed width font terminals). In other words, why doesnt the system produce an error for the second query as well??? > I'm sure you are aware that "1" is a set bit, so which part are you not > understanding? Others (Marcus) have reported that the second query SELECT '255.255.255.255/25'::cidr; indeed produces an error in 8.1; so i guess its a bug in the 7.4.x series. > > -- -Achilleus ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] 'locking' the SELECTs based on indices...
I have a table, like this: CREATE TABLE bla (id int4, code_id int4, code_value varchar, CONSTRAINT bla_pk PRIMARY KEY (id)) And, i have index on the table: CREATE INDEX bla_idx1 ON bla(code_id) Now, when I do this from one connection (psql shell, for instance): [A]BEGIN TRANSACTION; [A]SELECT * FROM bla WHERE code_id = 1 FOR UPDATE; and then, from another psql i do: [B]SELECT * FROM bla WHERE code_id = 1 FOR UPDATE the second SELECT will wait untill I rollback or commit first transaction. That is cool. But, if I do second SELECT like this: [C]SELECT * FROM bla WHERE code_id = 2 FOR UPDATE I will get the rows. If I erase the index bla_idx1, then [C] select will wait, same as [B] select will wait. Is there a way to have this behaviour with the index on column code_id? Mario -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Update in all tables
Hello everybody I need to update a field with the same value in the tables of my data base but this field exists in almost all tables and has the same value, I don't want to code a script, so my question is if there is some way to update that field with a query and affects all the tables that contain the field? Thanks in advanced, regards!!! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Update in all tables
Am Mittwoch, 22. Februar 2006 19:08 schrieb Judith: >Hello everybody I need to update a field with the same value in the > tables of my data base but this field exists in almost all tables and > has the same value, I don't want to code a script, so my question is if > there is some way to update that field with a query and affects all the > tables that contain the field? no. but you can do with a trigger ON UPDATE but what is so evil about a script like this: #!/bin/sh TABLES="A B C D" for TAB in $TABLES do psql -c "UPDATE $TAB set field = 'new' where field = 'old'" mydbname; done ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] 'locking' the SELECTs based on indices...
Mario Splivalo <[EMAIL PROTECTED]> writes: > Now, when I do this from one connection (psql shell, for instance): > [A]BEGIN TRANSACTION; > [A]SELECT * FROM bla WHERE code_id = 1 FOR UPDATE; > and then, from another psql i do: > [B]SELECT * FROM bla WHERE code_id = 1 FOR UPDATE > the second SELECT will wait untill I rollback or commit first > transaction. That is cool. > But, if I do second SELECT like this: > [C]SELECT * FROM bla WHERE code_id = 2 FOR UPDATE > I will get the rows. Well, of course. Why would you want something different? Why do you think the table's indexes should have anything to do with it? If you want a full-table lock then some form of LOCK TABLE seems like the proper answer. SELECT FOR UPDATE is designed to lock the specified rows, no more. > If I erase the index bla_idx1, then [C] select will wait, same as [B] > select will wait. I don't think so. If it does, it's a bug; please provide a reproducible test case. regards, tom lane ---(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] Update in all tables
On Wed, 2006-02-22 at 12:08, Judith wrote: >Hello everybody I need to update a field with the same value in the > tables of my data base but this field exists in almost all tables and > has the same value, I don't want to code a script, so my question is if > there is some way to update that field with a query and affects all the > tables that contain the field? Are those fields all dependent FK fields? If so, then declaring those foreign keys as on update cascade is all you need. If they're not related that way, then you'll have to script it. If you need them all to change at the same time (or all roll back in the event of an update failure) then you can wrap the changes in a transaction (begin/end pair) and it'll be atomic. ---(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
[SQL] Update in all tables
Hello everybody I need to update a field with the same value in the tables of my data base but this field exists in almost all tables and has the same value, I don't want to code a script, so my question is if there is some way to update that field with a query and affects all the tables that contain the field? Thanks in advanced, regards!!!
Re: [SQL] Update in all tables
On Wed, Feb 22, 2006 at 11:59:06AM -0600, Judith Altamirano Figueroa wrote: > Hello everybody I need to update a field with the same value in the > tables of my data base but this field exists in almost all tables and > has the same value, I don't want to code a script, so my question is if > there is some way to update that field with a query and affects all the > tables that contain the field? No, sorry. While we're at it, though, if you have the same field in several tables, it's a good sign that your database is badly normalised. You shouldn't have to update more than one table (which is why there isn't a way to do this automatically). A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Update in all tables
On Wed, 2006-02-22 at 15:13, Andrew Sullivan wrote: > On Wed, Feb 22, 2006 at 11:59:06AM -0600, Judith Altamirano Figueroa wrote: > > Hello everybody I need to update a field with the same value in the > > tables of my data base but this field exists in almost all tables and > > has the same value, I don't want to code a script, so my question is if > > there is some way to update that field with a query and affects all the > > tables that contain the field? > > No, sorry. While we're at it, though, if you have the same field in > several tables, it's a good sign that your database is badly > normalised. You shouldn't have to update more than one table (which > is why there isn't a way to do this automatically). Unless they're all FK/PK pairs, in which case you on update / delete cascade FKs. ---(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] Update in all tables
Scott Marlowe wrote: > On Wed, 2006-02-22 at 15:13, Andrew Sullivan wrote: > > > No, sorry. While we're at it, though, if you have the same field in > > several tables, it's a good sign that your database is badly > > normalised. You shouldn't have to update more than one table (which > > is why there isn't a way to do this automatically). > > Unless they're all FK/PK pairs, in which case you on update / delete > cascade FKs. It's entirely too bad this has to be designed into the DDL for the table. It'd be nice if you could specify "CASCADE" on UPDATE or DELETE queries. I don't see that it's any more of a gun than ON UPDATE/ON DELETE CASCADE in the foreign key definition. -Owen ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Feature, my misconception or bug??
O Markus Schaber έγραψε στις Feb 22, 2006 : > Achilleus Mantzios wrote: > > In PgSQL 7.4.12, > > > > foodb=# SELECT '255.255.255.255/24'::cidr; > > ERROR: invalid cidr value: "255.255.255.255/24" > > DETAIL: Value has bits set to right of mask. > > foodb=# > > > > SELECT '255.255.255.255/25'::cidr; > > cidr > > > > 255.255.255.255/25 > > (1 row) > > This one is refused in 8.1, so I guess that's a fixed bug. Thanx Marcus, it appears to have been fixed in http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/network.c.diff?r1=1.53;r2=1.54;f=h > > HTH, > Markus > > > -- -Achilleus ---(end of broadcast)--- TIP 6: explain analyze is your friend
