[SQL]
Hi, Regarding temp tables in the same connection session. If there is SQL procedure creating temp tables, process temp tables and drop all the temp tables finally. This SQL procedure can't run twice in the same session. Because all the temp tables are referred to the first physical temp tables. I posted this question couple months ago and the final reply is that in the future version, all the sql procedures, which use temp tables, will be forced to re-compile though it's been used in the session. Now, my question is, if it has been done, do I still need to drop all the temp tables before I re-run the same SQL procedure in the same session? Jack ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Double quotes?
Hi everyone, I have a column with type text and am trying to pump in a larger amount of text that has double quotes and also commas in between. I got an error when I tried to do a simple INSERT. I then tried to escape the double quote with a backslash but that didn't work. The manual for the 7.1 database says that I can escape charactes by using the \xxx where xxx is the octal value of the character. Is this the only way or have I missed out on something simple and just keep making a small mistake? Thanks in advance, Archie ---(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] Double quotes?
The information you gave is not very helpful. An INSERT command, which caused the error, would help. Anyway, did you enclose the text by single quotes? AFAIK, you do not have to escape double quotes. Regards, Christoph > > Hi everyone, > > I have a column with type text and am trying to pump in a larger amount of > text that has double quotes and also commas in between. I got an error > when I tried to do a simple INSERT. I then tried to escape the double > quote with a backslash but that didn't work. The manual for the 7.1 > database says that I can escape charactes by using the \xxx where xxx is > the octal value of the character. Is this the only way or have I missed > out on something simple and just keep making a small mistake? > > Thanks in advance, > > Archie > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Double quotes?
> The information you gave is not very helpful. > An INSERT command, which caused the error, would help. > Anyway, did you enclose the text by single quotes? > AFAIK, you do not have to escape double quotes. > Found the problem. Yes I enclosed the text in single quotes, and the text also included a single quote that I managed to miss to escape. Thanks anyway, Archie ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] INSERT only under certain conditions (SELECT)
create an uniq index on the columns, # create unique index index_name on table (col1,col2,col3,...) hope it helps. regds mallah On Monday 08 July 2002 06:48, Joachim Trinkwitz wrote: > Hi, > > I want to insert a row only under condition that there isn't already > another row with similar values -- something like a INSERT INTO > ... WHERE NOT EXISTS (SELECT ...)? > > Hoping for help, > joachim > > > > ---(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 -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] INSERT only under certain conditions (SELECT)
On Mon, Jul 08, 2002 at 03:18:33 +0200, Joachim Trinkwitz <[EMAIL PROTECTED]> wrote: > Hi, > > I want to insert a row only under condition that there isn't already > another row with similar values -- something like a INSERT INTO > ... WHERE NOT EXISTS (SELECT ...)? You can do something like the following: insert into tab (col1, col2, col3) select 'val1', 'val2', 'val3' where not exists (select * from tab where col1 = 'val1' and col2 = 'val2' and col3 = 'val3'); ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Boolean to int
Perhaps the 'create rule' is in the way, you could try something like: CASE WHEN document_online THEN 1 ELSE 0 END, CASE WHEN document_valid 1 ELSE 0 END But if that doesnt work, I'm out of options aswel. You could try the "instead query" separately to test whether the query itself is correct. Goodluck > -Oorspronkelijk bericht- > Van: Stephane Schildknecht [mailto:[EMAIL PROTECTED]] > Verzonden: vrijdag 5 juli 2002 15:39 > Aan: Arjen van der Meijden > CC: [EMAIL PROTECTED] > Onderwerp: Re: [SQL] Boolean to int > > > Le jeu 04/07/2002 à 23:32, Arjen van der Meijden a écrit : > > How about this hint in the postgresql-manual: > > > >"Tip: Values of the boolean type cannot be cast > directly to other > > types (e.g., CAST (boolval AS integer) does not work). This can be > > accomplished using the CASE expression: CASE WHEN boolval > THEN 'value if > > true' ELSE 'value if false' END. See also Section 4.12. " > > > > For more information: > > http://www.postgresql.org/idocs/index.php?datatype-boolean.html > > and > > http://www.postgresql.org/idocs/index.php?functions-conditional.html > > I tried that : > > CREATE RULE boolean_return AS ON SELECT TO DOCUMENT DO INSTEAD > SELECT > document_id, > workflow_id, > type_document_id, > image_id, > theme_id, > document_version, > document_surtitre, > document_titre, > document_chapeau, > document_synthese, > document_corps, > document_pdf, > document_date_creation, > document_mot_clef, > document_online, > document_valid, > CASE document_online WHEN TRUE THEN 1 >WHEN FALSE THEN 0 > END, > CASE document_valid WHEN TRUE THEN 1 > WHEN FALSE THEN 0 > END > FROM document; > > And, that doesn't work either... > > psql:cnambo_proc_stock.sql:76: ERROR: select rule's target > list has too many entries > > > > > -- > _Stéphane SCHILDKNECHT___ > | AurorA-SAS 69-71, Av. Pierre Grenier 92100 BOULOGNE | > | Tel : 01.58.17.03.20 Fax : 01.58.17.03.21 | > | mailto:[EMAIL PROTECTED] - ICQ : 142504394 | "Free > |Markets have taught that innovation is best when | > | ideas flow freely." Adam Smith | > |_| > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] manipulating the database in plsql
Is there any way to work with tables etc, in plsql? Can i get a get a database handle to the local database? If not, is there any chance for this to be implemented in the near future? thanks ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Problem on PostgreSQL (error code, store procedures)
Hello, I would like to know if there are any global variables storing the error code or the number of rows affected after each execution of the SQL statement. Also, for Postgresql function, I have checked that I can only call the function by: select function(parms) or by EXECUTE PROCEDURE function(parms) in the trigger action. I wonder whether there is any other method for calling the function which is similar to the stored procedures in the MS SQL server? Thank a lot! Carmen __ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] newbie question
Hi I am going to learn MySql 'cause I have to, but would it be better, or let's say, more interesting, to learn postgressql? Is it newer, more common, etc.? greets mirco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] newbie question
On Sun, Jul 07, 2002 at 11:59:51PM +0200, Mirco D'Angelo wrote: > Hi > > I am going to learn MySql 'cause I have to, but would it be better, or let's > say, more interesting, to learn postgressql? Is it newer, more common, etc.? PostgreSQL is certainly more interesting, more useful, more feature-complete (as far as Relational database servers go), more fun and what you could learn with PostgreSQL you could take to other database servers who are out to be real database servers, not the case with MySQL. AFAIK, MySQL is more common. -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 Why trying to DRINK and DRIVE, while you can SMOKE and FLY? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Bad SUM result
That is because your query is generating a cartesian product. Try: SELECT ( SELECT SUM(totalprice) FROM invoices WHERE custnumber = '1' ) - ( SELECT SUM(paymentamount) FROM payments WHERE custnumber = '1' ) Roy Souther wrote: > > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > I have an invoice database that has two tables one for invoices and one for > payments. I want to get the account balance for a client by subtracting the > sum of all payments from the sum off all invoices for that client. > > Here is the SQL that I thought should work. > SELECT SUM(t0.totalprice)-SUM(t1.paymentamount) FROM invoices t0, payments t1 > WHERE t0.custnumber='1' AND t1.custnumber='1' > > It works fine if there is only one invoice and one payment but as soon as > there is more then one of either it screws up. For each match found in > payments the invoice sum is added to the total. So if client 1 purchased a > $100 item then maid a $10 payment the SQL would return the balance of $90 > just fine. When the client makes a second payment of $15 the balance is $75 > but this SQL returns ($100+$100)-($10+$15) = $175. A third payment of $1 > would return ($100+$100+$100)-($10+$15+$1) = $274. > > Could some one explain this to me and recommend an SQL command that would work > please? I could do this using a temp table but that would be very messy as I > would really like it to be a single SQL command. > - -- > Roy Souther <[EMAIL PROTECTED]> > http://www.SiliconTao.com > > Linux: May the source be with you. > > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.0.6 (GNU/Linux) > Comment: For info see http://www.gnupg.org > > iEYEARECAAYFAj0oo9MACgkQCbnxcmEBt43qFQCgtjCs7khKGH+2LYd78O9mA3h4 > vDQAn0GkKkuYl1Kybgm/ITO4LbO1WWLX > =1G4R > -END PGP SIGNATURE- > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] newbie question
On Sunday 07 Jul 2002 10:59 pm, Mirco D'Angelo wrote: > Hi > > I am going to learn MySql 'cause I have to, but would it be better, or > let's say, more interesting, to learn postgressql? Is it newer, more > common, etc.? Mysql is more common on web-hosting platforms, but you can find PostgreSQL if you look. PostgreSQL's ancestors go back further than MySQL - both get updated regularly, so "newer" doesn't mean much. Mysql is easier to setup initially, but both systems require knowledge to tune properly. Both have a lot of online documentation - I'm not sure that one is better than the other. The mailing lists for PostgreSQL are very helpful and contain some very experienced people. PostgreSQL offers some more advanced features. Once you are used to these features it is irritating to find them missing in MySql. For some applications they are pretty much mandatory. PostgreSQL also tends to fit standards more than MySQL. Where I use MySQL behind a website I tend to use PostgreSQL to manage the data here during development. I'd recommend installing both if you can. Try to make sure anything you write works on both and check the documentation for where both diverge from standards. - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] manipulating the database in plsql
On Sun, Jul 07, 2002 at 11:40:19AM -0700, teknokrat wrote: > Is there any way to work with tables etc, in plsql? Can i get a get a Yes. > database handle to the local database? If not, is there any chance for > this to be implemented in the near future? I don't know what you mean. A PL/pgSQL function is already part of the database it belongs to. It doesn't need a handle. -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 All true wisdom is found in taglines. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] INSERT only under certain conditions (SELECT)
Maybe I should explain my problem a little bit: simplified, I have two tables, L and T, (which values references to L): L: art|kvvnr|semester ---+-+ 7.0| 4151| 2002ws 7.0| 4326| 2002ws 6.1| 4200| 2002ws 7.0| 4151| 2001ws (combination of kvvnr and semester is unique) T: id |semester|kvvnr ++- 123| 2002ws |4151 123| 2001ws |4151 Now I want to allow a user with id 123 only to insert a row in T if he doesn't choose values from L with same 'art' and 'semester' values -- in the examples user 123 has already chosen a kvvnr from semester 2002ws in category 'art', so he shouldn't be allowed to insert the values of the second row in table L. I hope, someone has followed this complicated thing up to here and there is a solution for my problem. Greetings and thanks for your answers by now and further on, joachim ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] bit field changes in 7.2.1
Peter Eisentraut wrote:
> Kevin Brannen writes:
>
>
>>EXCEPT that now fails in 7.2.1 (I just upgraded this afternoon). It
>>forces me to use "b'00'" instead of "b'0'::bit(6)".
>
>
> Which is a problem why?
Because in the real system, it will be b'0'::bit(64) or b'001::bit(64)
or etc. A bit nasty to type isn't it. :-) And of course that's for 1
table, in another place it be 40 bits wide, and in yet a third 96 bits
wide. So a simple mechanism to make it the correct size (for
comparisons) is very useful (to maintain my sanity. :-)
...
>
>
> In your case the solution is to type the six zeroes.
>
> The comment referred to cases where the results of computations needed to
> be forced to the right length, in which case you could use something like
>
> substring(computation() || b'00' for 6)
>
> The question whether the constant should go before or after the
> computation, and whether it should be zeros or ones is a matter of taste,
> which is why an example has been omitted.
>
I'm still not sure I see the problem, but that doesn't really matter.
If that's the explanation, then *that* should have been included with
the note, IMO. OTOH, I still don't see where that helps me. How does
it give me a string of bits like b'0'::64 does, or b'1'::64 does? And
something like:
select substring(1 || b'00' for 6);
assuming some computation returns a "1", tells me:
ERROR: Unable to identify an operator '||' for types 'integer' and
'bit' You will have to retype this query using an explicit cast
In an email, Chris suggested using rpad(), which looks good but doesn't
quite work, unless I'm overlooking something (if so, please point it
out!). When I try it in my query I get:
select ... from ...
where sp.bitmask != cast(rpad('0',6,'0') as bit(6));
ERROR: Cannot cast type 'text' to 'bit'
I guess my issue is that when some feature is taken out, there needs to
be another way to do it, AND it needs to be documented. Or just leave
it in and flag it as an extension.
;-)
I guess I'll just have to read the bits in from the DB reference table,
then store and manipulate them all in Perl...sigh...
Thanks for the effort though!
Kevin
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Rule WHERE condition problem
I have a table with a reference constraint and an ON DELETE SET NULL action. When this action triggers, I also want to update another field in the table, actually a timestamp which should be set to NOW(). After reading some documentation it would seem a rule is the easiest way to accomplish this. However, I ran into a problem with this: CREATE TABLE a ( id INT PRIMARY KEY ); CREATE TABLE b ( id INT REFERENCES a ON DELETE SET NULL, time TIMESTAMP DEFAULT 'infinity' ); INSERT INTO a VALUES (1); INSERT INTO b VALUES (1); CREATE RULE b_id_null AS ON UPDATE TO b WHERE new.time='infinity' AND old.id IS NOT NULL AND new.id IS NULL DO UPDATE b SET time=NOW() where id=old.id; DELETE FROM a WHERE id=1; I would now expect a to by empty and b to contain a single row with id=NULL and time=NOW(). However, this is what I get: ERROR: query rewritten 10 times, may contain cycles ERROR: query rewritten 10 times, may contain cycles It would seem that my WHERE clause is not checked before the action is run. Is this simply not implemented (yet, hopefully)? Thanks. --- Kristian ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] manipulating the database in plsql
Am Sonntag, 7. Juli 2002 20:40 schrieb teknokrat: > Is there any way to work with tables etc, in plsql? Can i get a get > a database handle to the local database? If not, is there any > chance for this to be implemented in the near future? I guess you mean plpgsql. you dont need a database handle inside plpgsql. you just can do SQL operations or do other stuff like inserting, updating and so on (sometimes you need the plpgsql PERFORM statement). look at the programmers guide about procedural languages (plpgsql.html) janning ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] newbie question
Transactions (well, recently mysql allows them but using propietary extensions), foreign key relationships, subqueries, stored procedures/triggers. MySQL lacks all of these. On the other side, postgres is only ported on *nix platforms, but you can put cygwin/cygipc on Windows, if that is the case. And mysql is "friendlier", that means wrong column value types (INT_VALUE = '5') and double quotes working as simple quotes, so it's easier to start with. As far as I can remember. Mirco D'Angelo wrote: >Hi > >I am going to learn MySql 'cause I have to, but would it be better, or let's >say, more interesting, to learn postgressql? Is it newer, more common, etc.? > >greets >mirco > > > > > >---(end of broadcast)--- >TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > > > > > > > ---(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
