Re: [SQL] plpgsql functions and NULLs
This sounds like a perfect candidate for a LEFT OUTER JOIN. See: http://www.postgresql.org/docs/7.4/static/queries-table- expressions.html#QUERIES-FROM Yours would looks something like: SELECT * FROM ... LEFT JOIN candidate AS c ON <...>.omcr_id = c.omcr_id AND ... -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Jan 30, 2005, at 1:41 PM, Don Drake wrote: OK, I have a function that finds records that changed in a set of tables and attempts to insert them into a data warehouse. There's a large outer loop of candidate rows and I inspect them to see if the values really changed before inserting. My problem is that when I look to see if the row exists in the warehouse already, based on some IDs, it fails when an ID is NULL. The ID is nullable, so that's not a problem. But I'm forced to write an IF statement looking for the potential NULL and write 2 queries: IF omcr_id is null select * from WHERE omcr_id is NULL AND ... ELSE select * from WHERE omcr_id=candidate.omcr_id AND END IF; IF FOUND ... Is there a way to do the lookup in one statement?? This could get ugly quick. I'm using v7.4. Thanks. -Don -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ 312-560-1574 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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] plpgsql functions and NULLs
My outer query to get the candidates has an outer join, that works just fine and I get the null OMCR_ID's. It's when I have to query the dimension table (no joins) to see if a row exists with a (sometimes) null OMCR_ID I'm forced to write 2 queries, when I think I should only have to write one. Thanks. -Don On Mon, 31 Jan 2005 14:25:03 -0600, Thomas F. O'Connell <[EMAIL PROTECTED]> wrote: > This sounds like a perfect candidate for a LEFT OUTER JOIN. See: > > http://www.postgresql.org/docs/7.4/static/queries-table- > expressions.html#QUERIES-FROM > > Yours would looks something like: > > SELECT * > FROM ... > LEFT JOIN candidate AS c > ON <...>.omcr_id = c.omcr_id > AND ... > > -tfo > > -- > Thomas F. O'Connell > Co-Founder, Information Architect > Sitening, LLC > http://www.sitening.com/ > 110 30th Avenue North, Suite 6 > Nashville, TN 37203-6320 > 615-260-0005 > > On Jan 30, 2005, at 1:41 PM, Don Drake wrote: > > > OK, I have a function that finds records that changed in a set of > > tables and attempts to insert them into a data warehouse. > > > > There's a large outer loop of candidate rows and I inspect them to see > > if the values really changed before inserting. > > > > My problem is that when I look to see if the row exists in the > > warehouse already, based on some IDs, it fails when an ID is NULL. > > The ID is nullable, so that's not a problem. > > > > But I'm forced to write an IF statement looking for the potential NULL > > and write 2 queries: > > > > IF omcr_id is null > >select * from > >WHERE omcr_id is NULL > >AND ... > > ELSE > >select * from > >WHERE omcr_id=candidate.omcr_id > >AND > > END IF; > > > > IF FOUND > > ... > > > > Is there a way to do the lookup in one statement?? This could get ugly > > quick. I'm using v7.4. > > > > Thanks. > > > > -Don > > > > -- > > Donald Drake > > President > > Drake Consulting > > http://www.drakeconsult.com/ > > 312-560-1574 > > > > ---(end of > > broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ 312-560-1574 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] plpgsql functions and NULLs
As far as I know, you didn't post your actual table definitions (or full queries) earlier, so I'm not exactly sure what you mean. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Jan 31, 2005, at 3:06 PM, Don Drake wrote: My outer query to get the candidates has an outer join, that works just fine and I get the null OMCR_ID's. It's when I have to query the dimension table (no joins) to see if a row exists with a (sometimes) null OMCR_ID I'm forced to write 2 queries, when I think I should only have to write one. Thanks. -Don ---(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] plpgsql functions and NULLs
On Sun, 30 Jan 2005, Don Drake wrote: > OK, I have a function that finds records that changed in a set of > tables and attempts to insert them into a data warehouse. > > There's a large outer loop of candidate rows and I inspect them to see > if the values really changed before inserting. > > My problem is that when I look to see if the row exists in the > warehouse already, based on some IDs, it fails when an ID is NULL. > The ID is nullable, so that's not a problem. > > But I'm forced to write an IF statement looking for the potential NULL > and write 2 queries: > > IF omcr_id is null >select * from >WHERE omcr_id is NULL >AND ... > ELSE >select * from >WHERE omcr_id=candidate.omcr_id >AND > END IF; Hmm, perhaps some form like: WHERE not(candidate.omcr_id is distinct from omcr_id) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] number os commands inside transaction block
Dear Friends how i can increse the number of commands in an transaction block i use postgres 7.4.5 on linux Regards Luiz - Original Message - From: "Stephan Szabo" <[EMAIL PROTECTED]> To: "Don Drake" <[EMAIL PROTECTED]> Cc: Sent: Monday, January 31, 2005 7:31 PM Subject: Re: [SQL] plpgsql functions and NULLs On Sun, 30 Jan 2005, Don Drake wrote: OK, I have a function that finds records that changed in a set of tables and attempts to insert them into a data warehouse. There's a large outer loop of candidate rows and I inspect them to see if the values really changed before inserting. My problem is that when I look to see if the row exists in the warehouse already, based on some IDs, it fails when an ID is NULL. The ID is nullable, so that's not a problem. But I'm forced to write an IF statement looking for the potential NULL and write 2 queries: IF omcr_id is null select * from WHERE omcr_id is NULL AND ... ELSE select * from WHERE omcr_id=candidate.omcr_id AND END IF; Hmm, perhaps some form like: WHERE not(candidate.omcr_id is distinct from omcr_id) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.2 - Release Date: 28/1/2005 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] number os commands inside transaction block
On Mon, Jan 31, 2005 at 07:54:45PM -0200, Luiz Rafael Culik Guimaraes wrote: > > how i can increse the number of commands in an transaction block What do you mean? What problem are you trying to solve? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] plpgsql functions and NULLs
You learn something new everyday. I've never seen that syntax before, and it works like a charm!! Thanks a ton. -Don On Mon, 31 Jan 2005 13:31:34 -0800 (PST), Stephan Szabo <[EMAIL PROTECTED]> wrote: > > On Sun, 30 Jan 2005, Don Drake wrote: > > > OK, I have a function that finds records that changed in a set of > > tables and attempts to insert them into a data warehouse. > > > > There's a large outer loop of candidate rows and I inspect them to see > > if the values really changed before inserting. > > > > My problem is that when I look to see if the row exists in the > > warehouse already, based on some IDs, it fails when an ID is NULL. > > The ID is nullable, so that's not a problem. > > > > But I'm forced to write an IF statement looking for the potential NULL > > and write 2 queries: > > > > IF omcr_id is null > >select * from > >WHERE omcr_id is NULL > >AND ... > > ELSE > >select * from > >WHERE omcr_id=candidate.omcr_id > >AND > > END IF; > > Hmm, perhaps some form like: > > WHERE not(candidate.omcr_id is distinct from omcr_id) > > -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ 312-560-1574 ---(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] number os commands inside transaction block
Hi Michael Fuhr how i can increse the number of commands in an transaction block What do you mean? What problem are you trying to solve? i´m trying to solve the follow message current transaction is aborted, queries ignored until end of transaction block some one tell me this is defined inside postgres sources i recive this message when i execute an certain number of queries inside an begin/commit block Regards Luiz ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] number os commands inside transaction block
On Mon, 2005-01-31 at 16:29, Luiz Rafael Culik Guimaraes wrote: > Hi Michael Fuhr > >> how i can increse the number of commands in an transaction block > > > > What do you mean? What problem are you trying to solve? > > iÂm trying to solve the follow message > current transaction is aborted, queries ignored until end of transaction > block > > some one tell me this is defined inside postgres sources > i recive this message when i execute an certain number of queries inside an > begin/commit block This is normal postgresql behaviour, and can't really be changed. However, with the advent of savepoints in 8.0, it is now possible to detect such errors and roll back so you can then continue. Without using savepoints with rollback to the given save point, however, there's not much way to get around it. It sounds to me like you're getting an error somewhere in your scripts you need to look into. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] number os commands inside transaction block
On Mon, Jan 31, 2005 at 08:29:42PM -0200, Luiz Rafael Culik Guimaraes wrote: > > i´m trying to solve the follow message > current transaction is aborted, queries ignored until end of transaction > block A previous command in the transaction has failed; no more commands will be executed until you issue a ROLLBACK (or a COMMIT, but the transaction will be rolled back due to the error). If you're doing error checking on every command then you should be able to discover which command failed. PostgreSQL 8.0 has savepoints so you can roll back part of a transaction and continue after an error, but that might not be what you need. > some one tell me this is defined inside postgres sources > i recive this message when i execute an certain number of queries inside an > begin/commit block Transactions can have 2^32 - 1 (4294967295) commands, so I'd be surprised if you were hitting that limit. If you were, you should see the following error: cannot have more than 2^32-1 commands in a transaction -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] plpgsql functions and NULLs
On Mon, 31 Jan 2005, Don Drake wrote: > You learn something new everyday. I've never seen that syntax before, > and it works like a charm!! Actually, now that I think about it, I wonder if that's a good thing to use because I don't think that'll use indexes to do the search. You may want to do some testing to see how it runs for you. > On Mon, 31 Jan 2005 13:31:34 -0800 (PST), Stephan Szabo > <[EMAIL PROTECTED]> wrote: > > > > On Sun, 30 Jan 2005, Don Drake wrote: > > > > > OK, I have a function that finds records that changed in a set of > > > tables and attempts to insert them into a data warehouse. > > > > > > There's a large outer loop of candidate rows and I inspect them to see > > > if the values really changed before inserting. > > > > > > My problem is that when I look to see if the row exists in the > > > warehouse already, based on some IDs, it fails when an ID is NULL. > > > The ID is nullable, so that's not a problem. > > > > > > But I'm forced to write an IF statement looking for the potential NULL > > > and write 2 queries: > > > > > > IF omcr_id is null > > >select * from > > >WHERE omcr_id is NULL > > >AND ... > > > ELSE > > >select * from > > >WHERE omcr_id=candidate.omcr_id > > >AND > > > END IF; > > > > Hmm, perhaps some form like: > > > > WHERE not(candidate.omcr_id is distinct from omcr_id) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] plpgsql functions and NULLs
I'm constraining on other columns as well and it's still picking up the index. Thanks again. -Don On Mon, 31 Jan 2005 16:32:02 -0800 (PST), Stephan Szabo <[EMAIL PROTECTED]> wrote: > On Mon, 31 Jan 2005, Don Drake wrote: > > > You learn something new everyday. I've never seen that syntax before, > > and it works like a charm!! > > Actually, now that I think about it, I wonder if that's a good thing to > use because I don't think that'll use indexes to do the search. You may > want to do some testing to see how it runs for you. > > > On Mon, 31 Jan 2005 13:31:34 -0800 (PST), Stephan Szabo > > <[EMAIL PROTECTED]> wrote: > > > > > > On Sun, 30 Jan 2005, Don Drake wrote: > > > > > > > OK, I have a function that finds records that changed in a set of > > > > tables and attempts to insert them into a data warehouse. > > > > > > > > There's a large outer loop of candidate rows and I inspect them to see > > > > if the values really changed before inserting. > > > > > > > > My problem is that when I look to see if the row exists in the > > > > warehouse already, based on some IDs, it fails when an ID is NULL. > > > > The ID is nullable, so that's not a problem. > > > > > > > > But I'm forced to write an IF statement looking for the potential NULL > > > > and write 2 queries: > > > > > > > > IF omcr_id is null > > > >select * from > > > >WHERE omcr_id is NULL > > > >AND ... > > > > ELSE > > > >select * from > > > >WHERE omcr_id=candidate.omcr_id > > > >AND > > > > END IF; > > > > > > Hmm, perhaps some form like: > > > > > > WHERE not(candidate.omcr_id is distinct from omcr_id) > -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ 312-560-1574 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] BLOBs vs BYTEA
Hi, Apologies if I've already sent a post asking this but I'm not sure if it actually went through last time as it doesn't seem to be in my sent items box. Anyway, I was wondering which would be a better way to store a large amount of files each a few megabytes in size. There could be hundreds of thousands of files altogether. If stored as BYTEAs this would put them all in a single table. Would this effect performance considerablely? I assume if there were thousands then it would. But if the data is stored as BLOBs then aren't they store inside the database just in another table? Wouldn't this also be undesirable. Would it be better to store them normally on the file system and just provide the path and file name in the database. Obviously this wont provide any security or backup but would it make sense to do it this way with such a large amount of data? Thanks. -Sam ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] plpgsql functions and NULLs
Don Drake <[EMAIL PROTECTED]> writes: > On Mon, 31 Jan 2005 16:32:02 -0800 (PST), Stephan Szabo > <[EMAIL PROTECTED]> wrote: >> Actually, now that I think about it, I wonder if that's a good thing to >> use because I don't think that'll use indexes to do the search. You may >> want to do some testing to see how it runs for you. > I'm constraining on other columns as well and it's still picking up the index. Stephan is right that an IS DISTINCT FROM construct is not considered indexable. So it's only your other constraints that are being used with the index. You need to think about whether the other constraints are selective enough to yield adequate performance. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] BLOBs vs BYTEA
Sam Adams wrote: Anyway, I was wondering which would be a better way to store a large amount of files each a few megabytes in size. There could be hundreds of thousands of files altogether. If stored as BYTEAs this would put them all in a single table. Would this effect performance considerablely? I assume if there were thousands then it would. But if the data is stored as BLOBs then aren't they store inside the database just in another table? Wouldn't this also be undesirable. Would it be better to store them normally on the file system and just provide the path and file name in the database. Obviously this wont provide any security or backup but would it make sense to do it this way with such a large amount of data? This is one of those age old debates. I've done both, and I'll tell you that in my experience it is less of a headache to store the files in the filesystem (which is what filesystems are designed for) and store the metadata in the database. Dennis Sacks [EMAIL PROTECTED] ---(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: [PERFORM] [SQL] OFFSET impact on Performance???
Hi, What you could do is create a table containing all the fields from your SELECT, plus a per-session unique ID. Then you can store the query results in there, and use SELECT with OFFSET / LIMIT on that table. The WHERE clause for this temp-results table only needs to contain the per-session unique id. This of course gives you a new problem: cleaning stale data out of the temp-results table. And another new problem is that users will not see new data appear on their screen until somehow the query is re-run (... but that might even be desirable, actually, depending on how your users do their work and what their work is). And of course better performance cannot be guaranteed until you try it. Would such a scheme give you any hope of improved performance, or would it be too much of a nightmare? cheers, --Tim -Original Message- From: [EMAIL PROTECTED] on behalf of Andrei Bintintan Sent: Wed 1/26/2005 11:11 AM To: [EMAIL PROTECTED]; Greg Stark Cc: Richard Huxton; pgsql-sql@postgresql.org; pgsql-performance@postgresql.org Subject: Re: [PERFORM] [SQL] OFFSET impact on Performance??? The problems still stays open. The thing is that I have about 20 - 30 clients that are using that SQL query where the offset and limit are involved. So, I cannot create a temp table, because that means that I'll have to make a temp table for each session... which is a very bad ideea. Cursors somehow the same. In my application the Where conditions can be very different for each user(session) apart. The only solution that I see in the moment is to work at the query, or to write a more complex where function to limit the results output. So no replace for Offset/Limit. Best regards, Andy. - Original Message - From: "Greg Stark" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: "Richard Huxton" ; "Andrei Bintintan" <[EMAIL PROTECTED]>; ; Sent: Tuesday, January 25, 2005 8:28 PM Subject: Re: [PERFORM] [SQL] OFFSET impact on Performance??? > > Alex Turner <[EMAIL PROTECTED]> writes: > >> I am also very interesting in this very question.. Is there any way to >> declare a persistant cursor that remains open between pg sessions? >> This would be better than a temp table because you would not have to >> do the initial select and insert into a fresh table and incur those IO >> costs, which are often very heavy, and the reason why one would want >> to use a cursor. > > TANSTAAFL. How would such a persistent cursor be implemented if not by > building a temporary table somewhere behind the scenes? > > There could be some advantage if the data were stored in a temporary table > marked as not having to be WAL logged. Instead it could be automatically > cleared on every database start. > > -- > greg > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] same question little different test MSSQL vrs Postgres
Quoting Dennis Sacks <[EMAIL PROTECTED]>: > Bruno Wolff III wrote: > >On Tue, Jan 25, 2005 at 21:21:08 -0700, > > Dennis Sacks <[EMAIL PROTECTED]> wrote: > >>One of the things you'll want to do regularly is run a "vacuum analyze". > >>You can read up on this in the postgresql docs. This is essential to the > >>indexes being used properly. At a bare minimum, after you import a large > >>amount of data, you'll want to run vacuum analyze. > > > Good point! Analyze after bulk inserts, vacuum analyze after > updates/deletes and inserts. :) Hmmm ... in performance tests of bulk inserts into a table with six indexes, I found that without vacuum analyze (rather than analyze), insertion slowed down albeit something less than linearly. Table of 6M rows, about 3GB (including index files). This is 7.4.1 on SuSE Linux, RAID5 Xeon(sigh) 2.8GHz 4GB nothing else running. The inserts were always done with an existing record check (LEFT JOIN ... WHERE joinkey IS NULL). -- "Dreams come true, not free." ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Need SELECT rights to UPDATE/DELETE WHERE?
PostgreSQL 8.0.0 on WinXP Pro, libpq In my hands it looks like a user with INSERT/DELETE/UPDATE rights on table1 cannot do "update table1 set field1=xx where field2=yy" without also being granted select rights. However, the user can do "update table1 set field1=xx". Is this right? Any explanations why it should be like this and any suggestions on how to get around it without granting select is greatly appreciated. KP ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Postgresql number of command
Hi... People I would like to know if inside of a function that has been called by a trigger, what instruction the SQL called, i do not wanna know if it´s a insert or a update or a delete (tg_op)... i need the full instruction. For example: I wanna know the number of the instruction: update telefone set numero = '-'; Thanks, and sorry if it´s not here to put the message... just say me where i need to write this question... Thank you very much Wilton Ruffato Wonrath -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.4 - Release Date: 25/1/2005 ---(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]
Hi, I need a script or a program that convert and esport my data from sql database to a file in the iso2709 format thanks bye
[SQL] error in function!!
Hi,
i have a problem with the following
sentence:
CREATE OR REPLACE FUNCTION
tschema.sp_actualizar_contacto(p_idpers
text,
p_nombre
text,
p_apellido
text,
p_titulo
text,
p_fecnac
text,
p_codedociv integer,
p_sexo text,
p_codpais
integer,
p_pw text,
p_empr text,
p_cargo text,
p_pwempr
text,
p_aniv text,
p_prof text,
p_trab text,
p_fecgen text,
p_fecing
text,
p_fuente
text,
p_codupload
integer,
cli_codigo integer ) RETURNS text as '
DECLARE
c_codigo
integer;
c_sinc
varchar;
c_idpers ALIAS for
$1; c_nombre
ALIAS for $2; c_apellido
ALIAS for $3;
c_titulo ALIAS for
$4; c_fecnac
ALIAS for $5; c_codedociv ALIAS
for $6;
c_sexo ALIAS for
$7; c_codpais ALIAS
for $8;
c_pw
ALIAS for $9;
c_empr ALIAS for
$10;
c_cargo ALIAS for
$11; c_pwempr
ALIAS for $12;
c_aniv ALIAS for
$13;
c_prof ALIAS for
$14;
c_trab ALIAS for
$15; c_fecgen
ALIAS for $16;
c_fecing ALIAS for
$17; c_fuente
ALIAS for $18; c_codupload ALIAS
for $19; cli_Codigo ALIAS
for $20;BEGIN select
nextval(''seq_tbu_contacto_cont_codigo'') into
c_codigo;
Insert into tbu_contacto (cont_codigo,
cont_idpers,cont_nombre,cont_apellido,cont_titulo,cont_fecnac,cont_codedociv,cont_sexo,cont_codpais,cont_pw,cont_empr,cont_cargo,cont_pwempr,cont_aniv,cont_prof,cont_trab,cont_fecgen,cont_fecing,cont_fuente,cont_sinc,cont_codupload)
values
(c_codigo,c_idpers,c_nombre,c_apellido,c_titulo,to_timestamp(c_fecnac,''-mm-dd
HH:MM:SS''),c_codedociv,c_sexo,1,c_pw,c_empr,c_cargo,c_pwempr,c_aniv,c_prof,c_trab,to_timestamp(c_fecgen,''-mm-dd
HH:MM:SS''),to_timestamp(CURRENT_TIMESTAMP,''-mm-dd HH:MM:SS'')
,c_fuente,''S'',c_codupload);
return ''OK:''||c_codigo;
END;' LANGUAGE 'plpgsql'
this is the error in pgadmin III on windows
(postgresql 8.0):
ERROR: function
tschema.sp_actualizar_contacto(integer, "unknown", "unknown", "unknown",
"unknown", "unknown", "unknown", integer, "unknown", "unknown", "unknown",
"unknown", "unknown", "unknown", "unknown", "unknown", "unknown", "unknown",
"unknown", integer, integer) does not existHINT: No function matches
the given name and argument types. You may need to add explicit type
casts.
I need to know if the "insert sentence" was
sucesfull, how do i do?
help me with this please,
thanks!!
Re: [SQL] SQL Query Performance tips
Hi PFC Thanks for this! It has sped up complete - now in fact there is no delay! A few tweaks had to be made to the code ; here it is: select breakdown.alignment, sum(cnt) as num FROM ( (select alignment.name as class, count(1) as cnt from weapons, alignment where weapons.level < (select level_id from users where uid = $userid) and cost = 0 and alignment.id = weapons.align_id group by alignment.name) UNION ALL select b.class as class, count(1) as cnt from weapons w, user_weapons uu, alignment b where tu.weaponid = uu.weaponid and uu.user_id = ($userid) and b.id = tu.id group by b.class ) as breakdown group by breakdown.class; Essentially you had skipped a few brackets and I had forgotten to note one of the tables. It works! Thanks a lot!! Mike ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] error in function!!
On Jan 31, 2005, at 1:59 PM, Ing. Jhon Carrillo wrote: ERROR: function tschema.sp_actualizar_contacto(integer, "unknown", "unknown", "unknown", "unknown", "unknown", "unknown", integer, "unknown", "unknown", "unknown", "unknown", "unknown", "unknown", "unknown", "unknown", "unknown", "unknown", "unknown", integer, integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. I need to know if the "insert sentence" was sucesfull, how do i do? This message is telling you that your function call using tschema.sp_actualizar_contacto was not correct. You must have all parameters in the function call and they must all be the correct type. So the message above says the first parameter passed was an integer, but your function expects the first parameter to be text. Note that you can remove all of the ALIAS declarations and use parameter name directly. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL]
hi, I'm not familiar with iso2709 but there is a program called Octopus that may do what you want. It's open source software and can be found at octopus.enhydra.org - worth a try anyway. Regards Iain - Original Message - From: Matteo Braidotti To: pgsql-sql@postgresql.org Sent: Wednesday, January 26, 2005 11:28 PM Subject: [SQL] Hi, I need a script or a program that convert and esport my data from sql database to a file in the iso2709 format thanks bye
Re: [SQL] Need SELECT rights to UPDATE/DELETE WHERE?
[EMAIL PROTECTED] writes: > In my hands it looks like a user with INSERT/DELETE/UPDATE rights on table1 > cannot do "update table1 set field1=xx where field2=yy" without also being > granted select rights. However, the user can do "update table1 set field1=xx". > Is this right? Yes. Otherwise you can use UPDATEs to infer something about the content of the table, eg do update table1 set field1 = field1 where field2 = yy and note the result count to find out whether there are any rows with field2 = yy. If you didn't give the other guy SELECT rights then presumably you do not want him to be able to infer any such thing. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] BLOBs vs BYTEA
O Dennis Sacks έγραψε στις Jan 31, 2005 : > Sam Adams wrote: > > >Anyway, I was wondering which would be a better way to store a large > >amount of files each a few megabytes in size. There could be hundreds of > >thousands of files altogether. If stored as BYTEAs this would put them > >all in a single table. Would this effect performance considerablely? I > >assume if there were thousands then it would. But if the data is stored > >as BLOBs then aren't they store inside the database just in another > >table? Wouldn't this also be undesirable. Would it be better to store > >them normally on the file system and just provide the path and file name > >in the database. Obviously this wont provide any security or backup but > >would it make sense to do it this way with such a large amount of data? > > > > > This is one of those age old debates. I've done both, and I'll tell you > that in my experience it is less of a headache to store the files in the > filesystem (which is what filesystems are designed for) and store the > metadata in the database. I think if you are doing replication or write in java, you'll be much happier with bytea. > > Dennis Sacks > [EMAIL PROTECTED] > > ---(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 > -- -Achilleus ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] [SQL] OFFSET impact on Performance???
As I read the docs, a temp table doesn't solve our problem, as it does not persist between sessions. With a web page there is no guarentee that you will receive the same connection between requests, so a temp table doesn't solve the problem. It looks like you either have to create a real table (which is undesirable becuase it has to be physicaly synced, and TTFB will be very poor) or create an application tier in between the web tier and the database tier to allow data to persist between requests tied to a unique session id. Looks like the solutions to this problem is not RDBMS IMHO. Alex Turner NetEconomist On Wed, 26 Jan 2005 12:11:49 +0200, Andrei Bintintan <[EMAIL PROTECTED]> wrote: > The problems still stays open. > > The thing is that I have about 20 - 30 clients that are using that SQL query > where the offset and limit are involved. So, I cannot create a temp table, > because that means that I'll have to make a temp table for each session... > which is a very bad ideea. Cursors somehow the same. In my application the > Where conditions can be very different for each user(session) apart. > > The only solution that I see in the moment is to work at the query, or to > write a more complex where function to limit the results output. So no > replace for Offset/Limit. > > Best regards, > Andy. > > > - Original Message - > From: "Greg Stark" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Cc: "Richard Huxton" ; "Andrei Bintintan" > <[EMAIL PROTECTED]>; ; > > Sent: Tuesday, January 25, 2005 8:28 PM > Subject: Re: [PERFORM] [SQL] OFFSET impact on Performance??? > > > > > > Alex Turner <[EMAIL PROTECTED]> writes: > > > >> I am also very interesting in this very question.. Is there any way to > >> declare a persistant cursor that remains open between pg sessions? > >> This would be better than a temp table because you would not have to > >> do the initial select and insert into a fresh table and incur those IO > >> costs, which are often very heavy, and the reason why one would want > >> to use a cursor. > > > > TANSTAAFL. How would such a persistent cursor be implemented if not by > > building a temporary table somewhere behind the scenes? > > > > There could be some advantage if the data were stored in a temporary table > > marked as not having to be WAL logged. Instead it could be automatically > > cleared on every database start. > > > > -- > > greg > > > > > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
