Re: [SQL] Most efficient way to hard-sort records
main_table: id, name, position key_table: id, main_table_id, key, value Here is how I need to sort the records: SELECT * FROM main_table INNER JOIN key_table ON main_table.id = key_table.main_table_id WHERE key = 'param' ORDER BY value I currently collect all ids from main_table in sorted order and then update the position field for each row in the main_table one-by-one. Is there a better/faster/more efficient solution? A cheap solution if you don't care about the position value as long as sort order is ok. 1) # SELECT main_table.id into temp_table FROM main_table INNER JOIN key_table ON main_table.id = key_table.main_table_id ORDER BY value; 2) # update main_table set position = (select oid from temp_table where id = main_table.id ); I guess I'll get a set of consecutive oids by this. You can make the number begin at arbitrary number, by 2-a) # update main_table set position = ( (select oid::int4 from temp_table where id = main_table.id ) - (select min(oid::int4) from temp_table) + 1) ; I read that oid wraps around (after ~ billions) so you might want to check your current oid. Regards, Ben K. Developer http://benix.tamu.edu ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Returning String as Integer
My twopence. I just happened to have the same problem with iReports for a 10 digit number - it may be case specific but in my case Please, take care with your quote attributions. I've never asked such a question here and, in fact, I was answering it. The person who asked such a question was Kashmira Patel. Sorry, I apologize. Regards, Ben K. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Returning String as Integer
Em Sábado 06 Maio 2006 17:05, Ben K. escreveu: > Jorge Godoy <[EMAIL PROTECTED]> wrote: > > numbers. Is there any way to select a value from this column and return > > it as an integer? > > My twopence. I just happened to have the same problem with iReports for a > 10 digit number - it may be case specific but in my case Please, take care with your quote attributions. I've never asked such a question here and, in fact, I was answering it. The person who asked such a question was Kashmira Patel. Thanks, -- Jorge Godoy <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Returning String as Integer
Jorge Godoy <[EMAIL PROTECTED]> wrote: numbers. Is there any way to select a value from this column and return it as an integer? My twopence. I just happened to have the same problem with iReports for a 10 digit number - it may be case specific but in my case # select ... int4(id) ...; worked. I tried int8(id) but java didn't take it as Integer. (It took int8 as something like Long.) Regards, Ben ---(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] grant select on database demo to user
A. Kretschmer wrote: > Is there a way to grant to all tables, with a single grant? I know how > > > No, but you can use a little Script, please read: > http://people.planetpostgresql.org/greg/index.php?/archives/38-guid.html#extended > > HTH, Andreas > Thanks, What always happens is I create a new view, or replace an old view, and the grant step gets forgotten. Then I get angry users who can't see the view (I can see it, just they can't) -Bryce ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] problem with uniques and foreing keys
On Sat, 6 May 2006, kernel.alert kernel.alert wrote: > I create the follow tables... > > > > CREATE TABLE empresa ( > id_empresa integer NOT NULL primary key, > nombre varchar(45), > ); > CREATE TABLE casino ( > id_casino integer NOT NULL, > id_empresa integer REFERENCES empresa(id_empresa), > > nombre varchar(45), > > primary key(id_casino,id_empresa) > ); > CREATE TABLE maq_casino ( > id_empresa integer NOT NULL REFERENCES casino(id_empresa), > id_casino integer NOT NULL REFERENCES casino(id_casino), You probably want a table level constraint like: foreign key (id_casino, id_empresa) references casino(id_casino, id_empresa) That's not the same as two single-column constraints which is what you have above. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] problem with uniques and foreing keys
Sorry i had a mistake in the post it was with the definition of the primary key in the empresa table...I'm having the same error when i build the last table...thanks for your answers... Hi list...Please i have a problem with this... I create the follow tables...CREATE TABLE empresa ( id_empresa integer NOT NULL primary key, nombre varchar(45), primary key(id_empresa) );CREATE TABLE casino ( id_casino integer NOT NULL, id_empresa integer REFERENCES empresa(id_empresa), nombre varchar(45), primary key(id_casino,id_empresa));CREATE TABLE maq_casino ( id_empresa integer NOT NULL REFERENCES casino(id_empresa), id_casino integer NOT NULL REFERENCES casino(id_casino), ubicacion_sala varchar(45) default NULL, primary key(id_empresa,id_casino,id_tipo_maquina,id_maq_casino));When i'm gonna to create the last table i got this error: ERROR: no hay restriccion unique que coincida con las columnas dadas en la tabla referida <> That in english is like .. there is no a unique constraint with columns referred in casino table. Please where is the problem...Greetings ...Krackem
Re: [SQL] problem with uniques and foreing keys
On Sat, May 06, 2006 at 12:27:41 -0500, "kernel.alert kernel.alert" <[EMAIL PROTECTED]> wrote: > Hi list... > > Please i have a problem with this... > > I create the follow tables... > > > > CREATE TABLE empresa ( > id_empresa integer NOT NULL primary key, > nombre varchar(45), > ); > CREATE TABLE casino ( > id_casino integer NOT NULL, > id_empresa integer REFERENCES empresa(id_empresa), > > nombre varchar(45), > > primary key(id_casino,id_empresa) > ); > CREATE TABLE maq_casino ( > id_empresa integer NOT NULL REFERENCES casino(id_empresa), > id_casino integer NOT NULL REFERENCES casino(id_casino), > > ubicacion_sala varchar(45) default NULL, > primary key(id_empresa,id_casino,id_tipo_maquina,id_maq_casino) > ); > > > > When i'm gonna to create the last table i got this error: > > ERROR: no hay restriccion unique que coincida con las columnas dadas en la > tabla referida <> > > That in english is like .. there is no a unique constraint with columns > referred in casino table. > > > Please where is the problem... Unless there is a performance problem, id_empresa should not appear in the maq_casino table at all, since it is derivable from id_casino. Is id_empresa really allowed to be NULL in casino? If there is an actual performance problem and you really need to have it in the mag_casino table, then you want to add another unique key (id_casino, id_empresa) in casino and then change casino_mag to have a foreign key reference on (id_casino, id_impresa) to casino instead of the individual references you have now. If id_empresa in casino_mag is not supposed to be tied to the value of id_casino (which I doubt is the case), then it should be referencing empresa instead of casino. ---(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] problem with uniques and foreing keys
On Saturday 06 May 2006 01:27 pm, "kernel.alert kernel.alert" <[EMAIL PROTECTED]> thus communicated: --> Hi list... --> --> Please i have a problem with this... --> --> I create the follow tables... --> --> --> --> CREATE TABLE empresa ( --> id_empresa integer NOT NULL primary key, --> nombre varchar(45), --> ); --> CREATE TABLE casino ( --> id_casino integer NOT NULL, --> id_empresa integer REFERENCES empresa(id_empresa), --> --> nombre varchar(45), --> --> primary key(id_casino,id_empresa) --> ); --> CREATE TABLE maq_casino ( --> id_empresa integer NOT NULL REFERENCES casino(id_empresa), --> id_casino integer NOT NULL REFERENCES casino(id_casino), --> --> ubicacion_sala varchar(45) default NULL, --> primary key(id_empresa,id_casino,id_tipo_maquina,id_maq_casino) --> ); --> --> --> --> When i'm gonna to create the last table i got this error: --> --> ERROR: no hay restriccion unique que coincida con las columnas dadas en la --> tabla referida <> --> --> That in english is like .. there is no a unique constraint with columns --> referred in casino table. --> --> --> Please where is the problem... --> --> Greetings ... --> --> --> -- The columns referenced in the maq_casino table must have UNIQUE constraints on them in their table definition as in: id_casino integer UNIQUE NOT NULL, ^^^ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] problem with uniques and foreing keys
Hi list...Please i have a problem with this...I create the follow tables...CREATE TABLE empresa ( id_empresa integer NOT NULL primary key, nombre varchar(45),);CREATE TABLE casino ( id_casino integer NOT NULL, id_empresa integer REFERENCES empresa(id_empresa), nombre varchar(45), primary key(id_casino,id_empresa));CREATE TABLE maq_casino ( id_empresa integer NOT NULL REFERENCES casino(id_empresa), id_casino integer NOT NULL REFERENCES casino(id_casino), ubicacion_sala varchar(45) default NULL, primary key(id_empresa,id_casino,id_tipo_maquina,id_maq_casino));When i'm gonna to create the last table i got this error: ERROR: no hay restriccion unique que coincida con las columnas dadas en la tabla referida <>That in english is like .. there is no a unique constraint with columns referred in casino table. Please where is the problem...Greetings ...Krackem
Re: [SQL] Returning String as Integer
On Fri, May 05, 2006 at 18:52:19 -0300, Jorge Godoy <[EMAIL PROTECTED]> wrote: > Em Sexta 05 Maio 2006 18:37, Kashmira Patel (kupatel) escreveu: > > Hi all, > >I have a table with a column of type 'text'. It mainly contains > > numbers. Is there any way to select a value from this column and return > > it as an integer? > > testing=# select '123'::integer; > int4 > -- > 123 > (1 registro) Note that that isn't the same thing. The '123' above is not treated as being of type text. If you actually wanted to test this conversion you would want to do: bruno=> select '123'::text::integer; int4 -- 123 (1 row) ---(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] Most efficient way to hard-sort records
Hi, Miroslav, Miroslav Šulc schrieb: > I have a table with cca 100,000 records. I need to hard-sort the records > by a key from second table. By hard-sorting I mean storing position of > each row in the main table. Here is what my tables look like: > > main_table: id, name, position > key_table: id, main_table_id, key, value > > Here is how I need to sort the records: > SELECT * FROM main_table > INNER JOIN key_table ON main_table.id = key_table.main_table_id > WHERE key = 'param' > ORDER BY value > > I currently collect all ids from main_table in sorted order and then > update the position field for each row in the main_table one-by-one. Is > there a better/faster/more efficient solution? Create an SQL function that selects the sort value from the key table when given id as parameter, and then create a functional index on the table, and CLUSTER the table on the index. Scratch-Code (untested): CREATE FUNCTION getvalue (ID int4) RETURNS int4 AS " SELECT value FROM key_table WHERE value=$1 LIMIT 1" LANGUAGE SQL STRICT; CREATE INDEX main_table_order_idx ON main_table (getvalue(id)); CLUSTER main_table_order_idx ON main_table; HTH, Markus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Most efficient way to hard-sort records
Hello, I have a table with cca 100,000 records. I need to hard-sort the records by a key from second table. By hard-sorting I mean storing position of each row in the main table. Here is what my tables look like: main_table: id, name, position key_table: id, main_table_id, key, value Here is how I need to sort the records: SELECT * FROM main_table INNER JOIN key_table ON main_table.id = key_table.main_table_id WHERE key = 'param' ORDER BY value I currently collect all ids from main_table in sorted order and then update the position field for each row in the main_table one-by-one. Is there a better/faster/more efficient solution? Thank you for your suggestions. -- Miroslav Šulc begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Schodov=C3=A1 309/10;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
