Re: [SQL] More efficient DELETE ... ?
Just a reminder that this is taken care of. It only WARNs in cases where we suspect they really made a mistake. test=> select * from pg_class s where pg_class.oid =34; NOTICE: Adding missing FROM-clause entry for table pg_class > Bruce Momjian <[EMAIL PROTECTED]> writes: > > In fact, someone can just remove the #ifdef WARN_FROM defines around the > > code and regenerate the regression tests and all will stay in sync. > > IIRC, Thomas felt that his outer join changes were going to break those > warnings anyway --- that's why we had them commented out. No point in > trying to turn them on until the outer-join dust settles. > > regards, tom lane > > > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] question on diagnostics
I can confirm that this is now fixed, and will appear in 7.1 when it is released. test=> select * from a natural inner join b natural inner join c; a --- 1 (1 row) > > > oh, btw this select refused to use an SQL natural join among the 3 > > > relations - the server gives up and disconnects without warning. > > > > That sounds like a garden-variety bug. I'd be willing to look at it > > if I had a complete example to follow, but I don't want to try to > > reverse-engineer your table definitions... > > a join with 3 tables never seems to work: > > test=# create table a(a int); > CREATE > test=# create table b(a int); > CREATE > test=# create table c(a int); > CREATE > test=# insert into a values (1); > INSERT 23734 1 > test=# insert into b values (1); > INSERT 23744 1 > test=# insert into c values (1); > INSERT 23736 1 > test=# select * > test-# from a, b, c > test-# where a.a = b.a AND > test-# b.a = c.a; > a | a | a > ---+---+--- > 1 | 1 | 1 > (1 row) > test=# select * from a natural inner join b natural inner join c; > pqReadData() -- backend closed the channel unexpectedly. > This probably means the backend terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. > > > alex > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Something I'd like to try...
Can someone comment on this? > Hi, > > I just noticed that postgres doesn't totally support > column aliases on UPDATE statements, for example > > UPDATE EMPLOYEES SET > OFFICE_PHONE = UU.OFFICE_PHONE, > MOBILE_PHONE = UU.MOBILE_PHONE, > OFFICE_CD = UU.OFFICE_CD, > ABOUT_ME = UU.ABOUT_ME > FROM > UNCONFIRMED_UPDATES UU > WHERE > EMPLOYEES.EMPLOYEE_ID = UU.EMPLOYEE_ID AND > UU.UPDATE_ID = 'HJhjaJ023J19KJAqp' > > It is not currently possible to alias EMPLOYEES > so that the test can become > > E.EMPLOYEE_ID = UU.EMPLOYEE_ID > > Do the guru's think that this would be hard to add? Also, > is it desirable? > -- > > Niall Smart > > email: [EMAIL PROTECTED] > phone: (087) 8052390 > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] OID Perfomance - Object-Relational databases
[ Charset ISO-8859-1 unsupported, converting... ] > Tom, > > > The trouble with pg_dump -o is that after reload, the OID > > generator > > will be set to max(any OID in the dumped data). So a > > dump & reload > > doesn't do anything to postpone OID-wraparound Ragnarok. > > > > As for the likelihood of overflow, figure 4G / tuple > > creation rate > > for your installation (not database, but whole > > installation controlled > > by one postmaster). Unless your installation has just > > one active > > table, per-table sequence values look like a better bet. > > Somebody (urgently) needs to tell all of the above to Bruce > Momjian (I've cc'd him); his book-in-the-making points up > OID's as a convenient and universal way to identify and link > tuples (chapter 7) and doen't mention these problems. Who > can I bug about how useless the above makes OID's? > Well, you know, everyone complains about wrap-around, but no one has ever reported it happening. It is like the Y2K thing where everyone thought they would starve. Please, someone tell me they have had had OID rollover, and I will start doing something about it. Also, 500 million transactions a day? Seems impossible to me. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] OID Perfomance - Object-Relational databases
[ Charset ISO-8859-1 unsupported, converting... ] > Bruce, Tom, etc.: > > > > This is what I mean. Does the DB engine only recycle > > > *unused* OIDs (that is, does it check for teh continued > > > existance of a tuple with OID 198401)? If that's the > > > method, then there isn't really a problem even if I do > > use > > > OIDs as a primary index. None of my OIDs still in use > > will > > > be touched. > > > > > > No, it uses all oids, and can create duplicates. > > Does this mean that Tom's "Wraparound Ragnarok" is the > accurate scenario? Yes. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] OID Perfomance - Object-Relational databases
There is an FAQ item about oid's already on the web site. It may not be in 7.0.2. > Tom, Bruce, > > Thanks. I think that gives me a pretty clear picture. How can we > submit this whole OID thing to the PGSQL FAQ? Want me to write it up? > > -Josh Berkus > > P.S. BTW, my conclusion based on this discussion is that I will not use > the OIDs for indexing/linking. Instead, I will embrace Michael's > earlier suggestion and have already created universal_sq and started > migrating primary keys to that sequence. > > P.P.S. Thank you both for taking the time to hash out this issue. > > > -- > __AGLIO DATABASE SOLUTIONS___ > Josh Berkus >Complete information technology [EMAIL PROTECTED] > and data management solutions (415) 436-9166 >for law firms, small businesses fax 436-0137 > and non-profit organizations. pager 338-4078 > San Francisco > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Regular expression query
> Rodger Donaldson <[EMAIL PROTECTED]> writes:
> > SELECT url
> > FROM sites
> > WHERE url ~ url || '\\s+'
>
> > While this concatenation works with the LIKE directive (ie LIKE url || '%'),
> > postgresql barfs on it in a regexp with the error:
>
> > ERROR: Unable to identify an operator '||' for types 'bool' and 'unknown'
> > You will have to retype this query using an explicit cast
>
> LIKE and ~ do not have the same precedence. See
> http://www.postgresql.org/docs/postgres/operators.htm.
> ~ and || actually fall in the same category ("all other") and therefore
> are grouped left-to-right; so you're getting (url ~ url) || '...'.
>
> > The other aspect of this is that it seems that postgresql's regexp engine
> > doesn't understand some expected regexps; I've tried both escaped and
> > unescaped versions of, eg \w, \s, \n and so on a pg seems to ignore them.
>
> The regexp package we currently use implements POSIX 1003.2 regexps
> (see src/backend/regex/re_format.7). I believe there is an item on the
> TODO list about upgrading the regexp parser to something more modern
> ... feel free to hop on that project if it's bugging you ...
I bug Henry Spencer regularly. His new code is in TCL/TK, but has not
been released into any other code.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Variable-length Types
> KuroiNeko, > > > I remember from the old days of Delphi/InterBase, and even older days of > > Paradox, there were so called input masks, US phone code mask would be like > > Input masks still exist for some languages (VB, Paradox) but I've found > that even in those platforms that support them I tend to replace them > with custom functions, because actually using them is too buggy. You can use CHECK column constraints to enforce this using regular expressions in the CHECK. > > > I'm just not sure what to do if 8 or 9 digits are supplied? Maybe, reject, > > assuming that such things should be caught by UI, and if we get such a > > weird thing, there's something really really wrong? > > Or, if you're using an interface that doesn't readily support entry > validation (e.g. PHP) then you can nest functions and have the "Save" > button test for a reply indicating that something's wrong. > > To wit: > > Create Function save_candidate (Lots of candidate data variables) > RETURNS VARCHAR (100) > AS > Declare output_string VARCHAR(100) > Do a whole bunch of stuff > SELECT first_phone = phoneformat(first_phone) > IF first_phone = 'fail' ( > SELECT output_string = 'Bad phone number for Primary Phone' > RETURN output_string > ) > More code > > -Josh > > -- > __AGLIO DATABASE SOLUTIONS___ > Josh Berkus >Complete information technology [EMAIL PROTECTED] > and data management solutions (415) 436-9166 >for law firms, small businesses fax 436-0137 > and non-profit organizations. pager 338-4078 > San Francisco > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] nested transactions
Net yet. [ Charset ISO-8859-15 unsupported, converting... ] > Hi, > > Just out of curiousity, does Postgres support nested transactions? > > > - Bernie Content-Description: Card for Bernie Huang [ Attachment, skipping... ] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] problem with select where like ']'
The fact is, I can't figure out how to get there without the URL. > Joseph Shraibman <[EMAIL PROTECTED]> writes: > > WHAT mailing list archives? > > They aren't linked to anywhere on www.postgresql.org that I can find. > > Hmm. My bookmark is > > http://www.postgresql.org/lists/mailing-list.html > > Dunno how to get there from the site toplevel... > > regards, tom lane > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] problem with select where like ']'
> > http://www.postgresql.org/users-lounge/index.html has most of them. > Actually it's under General Info from the user's lounge. > > It was brought to my attention today that the list of archives was > incomplete so I'll be adding to it and adding another one (developer > and user). Don't get too attached to the url below, it will be going > away very soon. Oh, I see it now. Thanks. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] INSERT [IGNORE] INTO TABLE
The proper fix for this is subtransactions, which we will have someday. You do the INSERT in a subtransaction. > Edmar Wiggers wrote: > > > > If I try to insert some row which in the table already exist the > > > postgre don't insert it and tell some error. Everything ok. > > > I'll insert only if there is not the same (same key) record, > > > else don't insert and don't tell me errors about it. > > > In MySQL is a switch [IGNORE]. > > > > Not a good feature for me. What do you do if the record already exists? > > Update it? > > > > Check existence and then insert or update. If you want, I guess you could > > wrap that inside a stored procedure. > > Hi, here is an example of using function using pl/pgsql for inserting and > checking whether an instance exists or not. > CREATE FUNCTION add_new_user(text,text) RETURNS bool AS' DECLARE > oldUser RECORD; > USR ALIAS FOR $1; > PWORD ALIAS FOR $2; > > BEGIN > SELECT INTO oldUser * > FROM users > where username=USR AND password= PWORD; > IF FOUND > THEN > RETURN ''f''; > ELSE > INSERT INTO USERS(username,password) > values(USR,PWORD); > RETURN ''t''; > END IF; > > END;' > LANGUAGE 'plpgsql'; > > Regards. > Najm > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] trying to pattern match to a value contained in a column
> Beth Gatewood <[EMAIL PROTECTED]> writes:
> > I hope this isn't because I am using 6.3 (yes...I know it is very very
> > old but this is currently where the data is!)
>
> > here is the query:
> > select * from av34s1 where chromat ~~ ('%' || sample || '%');
> > ERROR: parser: syntax error at or near "||"
>
> I seem to recall that || (and most other operators) wasn't
> considered associative by the grammar way back when.
> Try a fully parenthesized expression:
Yes || had strange problems in those releases.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] postgres
> On Wed, Dec 13, 2000 at 04:44:55PM -0800, Josh Berkus wrote: > > Mr. Daoust, > > > > You have reached the PostgreSQL SQL developers mailing list. We are > > not PostgreSQL sales people, and we have no marketing information to > > sell you. Please have a clue. > > Errr... forgive me, but maybe we could be help the PostgreSQL team by > kindly directing people to their corporate website, so they can purchase > services that are going to fund the project. > As long as the project is happy I am happy. My guess is that corporate support is mostly for serious PostgreSQL users, not new people. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [Re: [SQL] postgres]
If the person really does want commercial support, there is a "Commercial Support" page on www.postgresql.org. > Thank you very much Reberto, > > It appears that your co-workers are not inerested in potential funding. > For the rude onesmaybe/perhaps people like myself were givin the email > address ever think of that. > > A potential client that is having second thoughts. > > > > > Roberto Mello <[EMAIL PROTECTED]> wrote: > On Wed, Dec 13, 2000 at 04:44:55PM -0800, Josh Berkus wrote: > > Mr. Daoust, > > > > You have reached the PostgreSQL SQL developers mailing list. We are > > not PostgreSQL sales people, and we have no marketing information to > > sell you. Please have a clue. > > Errr... forgive me, but maybe we could be help the PostgreSQL team by > kindly directing people to their corporate website, so they can purchase > services that are going to fund the project. > As long as the project is happy I am happy. > > Just some thoughts. > > -Roberto > > -- > Computer Science Utah State University > Space Dynamics Laboratory Web Developer > USU Free Software & GNU/Linux Clubhttp://fslc.usu.edu > My home page - http://www.brasileiro.net/roberto > > > ________ > Get your own FREE, personal Netscape WebMail account today at >http://home.netscape.com/webmail > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Best database structure for timely ordered values
> Reiner Dassing <[EMAIL PROTECTED]> writes: > > The primary index must be the epoch. > > As there will be no deletion from this data I fear - due to the > > internal representation of B-trees - the performance will degrade very > > soon. > > Nonsense. btree should work just fine for that. Use a timestamp > column for the primary key, and away you go. > > (Actually, time alone doesn't seem like it'd be necessarily unique, > so maybe you don't want to call it a primary key. But certainly > you can make a non-unique index on that column.) I assume the user is concerned about non-balanced btrees. Ours are auto-balancing. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Best database structure for timely ordered values
> > I assume the user is concerned about non-balanced btrees. > That is correct! > As I tested an example database with about 7 million entries on PostgreSQL V > 6.5.2 > and the result of > select count(*) from table; > tooks about 3 minutes I have this concern. > May be version 7.0.3 will give a better result. The larger problem is that count(*) doesn't use the index at all. It just does a sequential scan of the heap table. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Release date for 7.5?
I assume you mean 7.1. We are in beta now. That usually takes a month. [ Charset ISO-8859-1 unsupported, converting... ] > Hi all. > > I'm hoping to migrate from mysql to postgresql but need some of the features > in 7.5. Any idea when it will be out? > > Thanx, > Mike Diehl. > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Re: [SQL] PostgreSQL web page
> I have no graphical browser right now, and lynx isn't installed, so I'm a > bit at a disadvantage here ... but, pgsql-sql *should* be listed under > users-gallery, not devel ... devel is -hackers, -bugs, -ports, etc ... > > Beyond that, the web site is maintained by Vince ... but I thought the > Award was definitely up there already ... Looks OK here. Maybe a bad mirror. Do: http://www.postgresql.org/index.html to avoid any mirrors. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Bruce's Book and Built-in Functions
[ Charset ISO-8859-1 unsupported, converting... ] > Folks, > > Well, I now have a copy of Bruce's book on order. Bruce, if > your sales have been slow, don't let A-W blame it on the > online draft. According to Stacy's, it takes them an Sold 2,155 copies in the first month. I know they think that is a good sales number. Having it online is clearly a win. Frankly, I can't buy a book without seeing it. I need to know it is written in style that emphasizes concepts, rather than emphasizing examples. So, to choose a book, I either have to see it in a book store, or browse it online. > average of 7 working days to get Ingram to cough up a new > copy, which is twice the normal period for tech books. As a > result, they're out of stock a lot. They only printed 6k copies, so they went through >1/3 of the books in the first month. My guess is that they are trying to prioritize the distribution. > > Plus www.postgresql.org could do a little more to promote > the book. There's *still* nothing on the web site to tell > me the book's in print. HINT, HINT. Well, the web site mentions my book, and the book web page says it is in print. > > So, while I wait for my copy ... I can't find in the online > docs anywhere a comprehensive list of built-in functions. > You know, stuff like CURRVAL() and NOW(). Can anyone point > me to such a list? Guessing parameters is getting > frustrating! Sure, chapter 9 has it. See the online version. There is a huge table, though currval is mentioned in the sequences chapter because it is not like normal functions. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Selecting Current value from a sequence
> Najm
>
> CURRVAL('sequence_name')
>
> For this and other sequence and serial functions, please see the online
> version of Bruce's book.
>
> BTW, O'Reilly's "SQL in at Nutshell" also catalogs all PGSQL functions
> from ver. 6.5.
It does, or only the standard SQL functions? Do they have
PostgreSQL-specific stuff.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Selecting Current value from a sequence
See my book on sequences: http://www.postgresql.org/docs/awbook.html currval() is probably what you need. > Hi all, > It is a very simple but I am not able to recall how to do it I > just need to find out the current value of a seq. It is very simple > select statement but I can't recall it... Help me please before I get > myself get fired -:). > Regards, Najm > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] PostgreSQL HOWTO
> Hello, > > I do not see how it puts the Postgres community in a bad light, although I > do see how the author is a moron. Many of use fell is puts Linux in a bad light. :-) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] PostgreSQL HOWTO & LDp
> Hello, > > I am the Webmaster of the LDP... What should I know? OK, just read a little bit of it. After you stop laughing, come on back and we can talk. :-) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] PostgreSQL HOWTO
> Is everyone sure that this HOWTO is not a work of fiction sort of > like that report on the computer virus that will cause your > motherboard to catch fire and burn down your house in the middle of > the night? The HOWTO author really reminds me of Jesus Monroy, if anyone remembers him from the 386BSD Usenet groups. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Re: is there a mysql to postgresql sql converter?
FYI, 7.1 will have a MySQL dump converter in /contrib. [ Charset ISO-8859-1 unsupported, converting... ] > > On Sat, 18 Nov 2000 01:13:23 +0200, Max Fonin said: > > > Can give a link ? > > > > can do : > http://freshmeat.net/projects/mysql2pgsql/?highlight=convert+sql > > i havent tried their's > > did try yours > > it was nice to have the transactions! > > > > > On Fri, 17 Nov 2000 13:45:37 -0800 > > clayton cottingham <[EMAIL PROTECTED]> wrote: > > > > > Max Fonin wrote: > > > > > > > > Hi. > > > > > > > > I'm writing MySQL->Postgres dump converter. E.g. it will convert only >database dumps produced by mysqldump. > > > > It's almost ready, problems are ENUM and SET types. I have problems with >types emulation. > > > > Maybe someone help me guys ? > > > > > > > > Anyway, some half-working version attached. > > > > > > > > Max Rudensky. > > > > > > > > > > > >Name: my2pg.pl > > > >my2pg.plType: Perl Program (application/x-perl) > > > >Encoding: base64 > > > > > > > > > thats cool!! > > > > > > i thought i saw another converter in the last couple o months on > > > freshmeat too > > > > > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[SQL] Re: [GENERAL] MySQL -> Postgres dump converter
FYI, we have a MySQL Perl script in 7.1beta. Would you please check that and see if you have any enhancements? Thanks. > Hi. > > MySQL->Postgres dump converter is now available at > http://ziet.zhitomir.ua/~fonin/code/my2pg.pl. Still beta and > bugsome version but working, supports MySQL ENUMs, near the end > are SET emulation. Please help me to test. > > Max Rudensky. > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Use of INNER JOIN and others ??!?
> Is it possible to use the statements INNER JOIN, OUTER JOIN, LEFT JOIN > and RIGHT JOIN in the new version of PostgreSQL 7.1 ? Yes in 7.1beta. See FAQ 'outer' entry for a short description. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Upgrade to 7.0.3
> Currently I am running 6.5.3 should I upgrade to 7.0.3 -- Any advantages or > should I wait > > Maybe I should post this somewhere else but I thought I would ask here first Good question. 7.1 is near final release. You could try that or 7.0.3. Both are more reliable/faster than 6.5.3. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Bug with rules in 7.0.3?
> On Sat, 3 Feb 2001, Tom Lane wrote: > > > I get > > > > regression=# SELECT * FROM orders; > > order_id | menu_id | price > > --+-+--- > > 1 | 2 |-1 > > (1 row) > > > > which is the correct result given that rules are executed before the > > original query. (Which is why you need a trigger for this...) > > OK. > > I think that Bruce's book is inaccurate then. In section D.19 (p. 299), > also reproduced on the web at > http://www.postgresql.org/docs/aw_pgsql_book/node490.html, this example is > given: > > All new employees must make 5,000 or less: > > CREATE RULE example_5 AS > ON INSERT TO emp > WHERE new.salary > 5000 DO > UPDATE emp SET salary = 5000 > WHERE emp.oid = new.oid; I checked the current create_rule.sgml file, and this example query is no longer in the file. Not sure why it was removed, but it will not appear in 7.1. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Re: PL/PGSQL Cook Book
[ Charset ISO-8859-1 unsupported, converting... ] > On Friday 09 February 2001 12:23, [EMAIL PROTECTED] wrote: > > I've just spent the last day or two trying to get to grips with plpgsql and > > can't believe how abysmal the documetentation and examples are. I've been > > trawling through the mailist lists and I notice there was talk back in 1999 > > abouta PLPGSQL Cook Book - did anything come of this? > > a good body of open source plpgsql code is in the openacs distribution at > openacs.org. > > i agree that plpgsql is poorly documented, the only way i found to figure out > its capabilities were to scour the mailing lists, the openacs code, and the > plpgsql implementation source. any attempt to better this documentation would > IMO, be a worthwhile effort. I have added PL/PgSQL to the open 7.1 docs list. Let's see if someone takes a stab at it. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] PL/PGSQL Cook Book
Even a PL/PgSQL FAQ would be good. > mark proctor writes: > > > I've been trawling through the mailist lists and I notice there was > > talk back in 1999 abouta PLPGSQL Cook Book - did anything come of > > this? If no one is maintaining something like this and people think > > its a good idea I think we should have another crack at it. I'd be > > happy to maintain something like this and put it up on the web, > > although I'm only a newbie and would rely upon user contribution. > > Start writing, and send it to [EMAIL PROTECTED] It doesn't have > to be a great work of literature; incremental work is just fine. > > >How can I create Tree structures? > >Are recursive functions supported? > >Whats are the advanteds disadvantes of the different scipting langues PL/PGSQL, >PL/Perl, PL/Tcl? > >How do variable scopes work in PL/PGSQL? > >Can I output variables from a function to the command line for debugging >purposes? > >How to debug PL/PGSQL? > >Various examples for each of the statements > > All valid questions. > > -- > Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] C/C++ interface
Cursors and standard queries are pretty much the same, except the cursor
can control the rows returned.
> Hello,
>
> in the C interface documentation there is an example using:
>
> res = PQexec(conn, "DECLARE mycursor CURSOR FOR select * from
> pg_database");
> if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
> {
> fprintf(stderr, "DECLARE CURSOR command failed\n");
> PQclear(res);
> exit_nicely(conn);
> }
> PQclear(res);
> res = PQexec(conn, "FETCH ALL in mycursor");
>
> ...etc. So the statements are:
>
> DECLARE mycursor CURSOR FOR select * from pg_database;
> FETCH ALL in mycursor;
>
> What's the difference between this and simply doing:
> select * from pg_database;
>
> I tried this in psql, the result seemed the same.
>
> What I'm really using, however, is the C++ interface.
> Its documentation is not yet complete.
> There, too, I tried a version with and without cursor.
> The result seems to be the same, but returned int is always 0
> for the version without cursor, so I get no information whether
> the query succeeded.
>
> Is someone maintaining the C++ interface and its documentation?
>
>
> Thanks,
>
> Volker Paul
>
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Controlling Reuslts with Limit
> Hi, > I was reading through Bruce's on line . I found follwing bit unclear... > > "Notice that each query uses ORDER BY . Although this clause is not required, > LIMIT without ORDER BY returns random rows from the query, which would be > useless. " It means there is no guarantee which rows will be returned. You may get the rows you want, or you may not. Without the ORDER BY, the backend can return any five rows it wishes. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Controlling Reuslts with Limit
> I don't think it is actually random. It just that the order is not defined > and other events may change the order. I believe that without an ORDER BY > or other clauses that cause an index to be used that the database tends to > return rows in the order stored on disk. This order tends to be the order > in which rows were added. My observation is this ordering is faily stable > and it seems to survive a database reload. Just don't rely on it. There is > a CLUSTER command to change the physical ordering. Yes, usually it is the heap order, but if you do "col > 12" you may get it in index order by the column indexes, or you may not, depending on the constant, the size of the table, vacuum, vacuum analyze, etc. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[SQL] Re: [GENERAL] Debug messages in beta5
cont_id = old.cont_id
> and crt_by = old.crt_by
> and group_name = old.group_name;
>
>
>
> This seems to lack sufficient context to find exactly where they came
> from:
> ERROR: Rule WHERE condition may not contain references to other
> relations
>
> ERROR: select rule's target entry 27 has different type from attribute
> lquant
>
> However, based on the field name, I can tell the last one is coming from
> this view:
>
> create view mtr_reg_v_unpackaged as
>select m.*, (select coalesce(sum(l.quant),0)
> from pak_lnk l
> where m.ttype = l.ttype
> and m.ropnum = l.ropnum
> and m.inum = l.inum
> and m.minum = l.minum
> and m.bminum = l.bminum) as lquant,
> (select p.base||'('||p.parm||')' from prd_part_v_base p where
> p.pnum = m.pnum) as pname,
> (select p.descr from prd_part_v_base p where p.pnum = m.pnum)
> as descr
>from mtr_reg_v_or m, ord_hdr o
>where m.status = 'open'
>and m.ropnum = o.ordnum
>and o.type = 'ord'
>and m.rquant >
>(select coalesce(sum(l.quant),0) from pak_lnk l
> where m.ttype = l.ttype
> and m.ropnum = l.ropnum
> and m.inum = l.inum
> and m.minum = l.minum
> and m.bminum = l.bminum);
>
Content-Description: Card for Kyle
[ Attachment, skipping... ]
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Weird NOT IN effect with NULL values
> When doing a subselect with NOT IN, as in > > SELECT name > FROM customer > WHERE customer_id NOT IN ( > SELECT customer_id > FROM salesorder > ); > > (from Bruce Momjian's book) > > I get no rows if the result column returned by the subselect > contains NULL values. It works as expected if I remove the NULL values > from the result set. Is this behaviour correct and if so, why? > > I am using 7.1 beta 4. Read more in the book. It covers subqueries with nulls, bottom of pages 96. Not sure about web URL but it is in the subqueries section titled "NOT IN and Subqueries with NULL Values". -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Weird NOT IN effect with NULL values
> SELECT name > FROM customer > WHERE NOT EXISTS ( > SELECT customer_id > FROM salesorder > WHERE customer_id = customer.customer_id > ); > > Bruce, you may want to consider editing your next edition to include the > above modification. WHERE ... NOT IN is a bad idea for any subselect on > medium-large tables. FAQ item mentions this, and section 8.2 shows eqivalency at the end of the section. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] PL/SQL-to-PL/PgSQL-HOWTO beta Available
Can someone comment on this? Is it being merged into the main docs? > Hi all, > > I finished the beta version of my PL/SQL-to-PL/PgSQL-HOWTO last night > and put it in http://www.brasileiro.net/roberto/howto . > It explains basic differences between Oracle's PL/SQL and PG's > PL/PgSQL and how to port apps from one to the other. It also includes my > instr functions that mimick Oracle's counterpart (they are handy). > Please take a look and send me ([EMAIL PROTECTED]) any suggestions, > criticism, etc. I am almost done writing my PL/PgSQL documentation that > hopefully will make into the PG doc tree. > > -Roberto > > -- > Computer Science Utah State University > Space Dynamics Laboratory Web Developer > USU Free Software & GNU/Linux Clubhttp://fslc.usu.edu > My home page - http://www.brasileiro.net/roberto > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] random
> Jelle Ouwerkerk <[EMAIL PROTECTED]> writes: > > Also, is there a way to randomize the order of a result set? > > There's always > SELECT * FROM foo ORDER BY random(); > How does that work? test=> select random(); random --- 0.896045367650709 (1 row) However: test=> select * from pg_class order by random(); does return some output. Is it random, and if so, how? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Optimizing Query
Have you tried VACUUM ANALYZE and CLUSTER? > Any suggestions welcome! > > Here is my query: > > select k.*, c.category from knowledge k, kb_categories c , kbwords w0 , > kbwords w1 WHERE k.catid=c.catid AND ((k.kbid=w0.kbid and w0.wordid=42743) > AND (k.kbid=w1.kbid and w1.wordid=85369)) ORDER BY k.kbid DESC LIMIT 25; > > Now for the details > knowledge k = 3,150 records > kbwords = 2-field database (kbid, wordid), 825,748 records > > Each word in the knowledge base is stored in a database called wordindex, > which has 50,000 records or so. The system first explodes the query string > and pulls the word #s from this database, which is where we get 42743 and > 85369 above, "ASIA" and "CHILDREN" respectively.) The idea is then to pull > all the articles in the knowledge base which contain both of these words. > > Here is the EXPLAIN for the query: > > NOTICE: QUERY PLAN: > > Nested Loop (cost=0.00..527690060.67 rows=2878549 width=308) >-> Nested Loop (cost=0.00..9472443.40 rows=52582 width=304) > -> Nested Loop (cost=0.00..6278.63 rows=960 width=300) >-> Index Scan Backward using knowledge_kbid_key on > knowledge k (cost=0.00..1292.51 rows=2825 width=284) >-> Seq Scan on kb_categories c (cost=0.00..1.34 rows=34 > width=16) > -> Seq Scan on kbwords w0 (cost=0.00..9787.02 rows=5474 width=4) >-> Seq Scan on kbwords w1 (cost=0.00..9787.02 rows=5474 width=4) > > This takes quite a while to return results... prohibitively long. There are > indexes on k.catid, c.catid, k.kbid, w0.kbid, w0.wordid. Any suggestions > for further optimization would be very welcome. We get about 3,000 searches > on our database daily... > > Blessings, > Justin Long > > > > > Justin Long Network for Strategic Missions > [EMAIL PROTECTED] 1732 South Park Court > http://www.strategicnetwork.org Chesapeake, VA 23320, USA > Reality Check e-zine: [EMAIL PROTECTED] > > Law: Never retreat. Never surrender. Never cut a deal with a dragon. > Corollary: No armor? Unclean life? Then do not mess in the affairs > of dragons, for you are crunchy and taste good with ketchup. > > > > Justin Long Network for Strategic Missions > [EMAIL PROTECTED] 1732 South Park Court > http://www.strategicnetwork.org Chesapeake, VA 23320, USA > Reality Check e-zine: [EMAIL PROTECTED] > ____ > Law: Never retreat. Never surrender. Never cut a deal with a dragon. > Corollary: No armor? Unclean life? Then do not mess in the affairs > of dragons, for you are crunchy and taste good with ketchup. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] random
> Bruce Momjian <[EMAIL PROTECTED]> writes: > >> Jelle Ouwerkerk <[EMAIL PROTECTED]> writes: > > Also, is there a way to randomize the order of a result set? > >> > >> There's always > >> SELECT * FROM foo ORDER BY random(); > > > However: > > test=> select * from pg_class order by random(); > > does return some output. Is it random, and if so, how? > > Sure. The function is marked noncachable, so it gets evaluated > afresh at each row --- but only once per row, as the sort step doesn't > recompute any values. Should work just peachy, and it seems to do > so in a couple quick tests. But random returns a random value from 0-1, right? How does that work in ORDER BY? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] Optimizing Query
Yes. > On Mon, Mar 05, 2001 at 04:07:57PM -0500, Bruce Momjian allegedly wrote: > > Have you tried VACUUM ANALYZE and CLUSTER? > > I assume CLUSTER still drops all indexes except the one you're clustering > on? > > Mathijs > -- > It's not that perl programmers are idiots, it's that the language > rewards idiotic behavior in a way that no other language or tool has > ever done. > Erik Naggum > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] random
> Bruce Momjian <[EMAIL PROTECTED]> writes: > > But random returns a random value from 0-1, right? How does that work > > in ORDER BY? > > What's the problem? Each row gets a different random value, then we > sort. Oh, I see. Nifty. I am used to seeing a column name or number in ORDER BY. Why doesn't the parser see this as a column number? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] Quick question MySQL --> PgSQL
> > > there is one in contrib > > as well there is some through freshmeat.net There are two in /contrib. One from pgsql.com, and another from freshmeat.net: http://ziet.zhitomir.ua/~fonin/code/ I would like to see these merged someday. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] Query Limitations
> PostgreSQL 6.4 seems to have limitations in Query Length when I > > "CREATE VIEW" ... is this limit defined > > further, when I create a query on a query... it seems to compound > the queries and reach the limit sooner!! > > Is this limit programmable? > > Is it default higher in 7.0? Yes. > > What is the most stable 7.X release? 7.0.3 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] PL/pgSQL "compilation error"
> On Wed, Mar 14, 2001 at 10:33:49AM -0500, Bruce Momjian wrote: > > It is hard to test this without the table schema, but I think you are > > missing some END IF's in the code above. Those are not elif's, but > > actual new IF's that need their own END IF, I think. > > Oh wow - this is almost like going back to my COBOL days. > > IF NEW.status_id <> OLD.status_id THEN > IF new.status_id = 1 THEN > UPDATE artifact_counts_agg SET open_count=open_count+1 \ > WHERE group_artifact_id=new.group_artifact_id; > ELSE > IF new.status_id = 2 THEN > UPDATE artifact_counts_agg SET open_count=open_count-1 \ > WHERE group_artifact_id=new.group_artifact_id; > ELSE > IF new.status_id = 3 THEN > UPDATE artifact_counts_agg SET open_count=open_count-1,count=count-1 \ > WHERE group_artifact_id=new.group_artifact_id; > END IF; > END IF; > END IF; > END IF; > > Yes - nesting those IFs did the trick. Are there more examples of PL/pgSQL > anywhere beyond those on your website? > > Thanks, Bruce! > There is a PL/PgSQL HowTo that we are trying to integrate into the docs for 7.1. Anyone have a URL or status on that? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] PL/pgSQL "compilation error"
> IF NEW.status_id <> OLD.status_id THEN > IF new.status_id = 1 THEN > UPDATE artifact_counts_agg SET open_count=open_count+1 \ >WHERE group_artifact_id=new.group_artifact_id; > ELSE IF new.status_id = 2 THEN > UPDATE artifact_counts_agg SET open_count=open_count-1 \ >WHERE group_artifact_id=new.group_artifact_id; > ELSE IF new.status_id = 3 THEN > UPDATE artifact_counts_agg SET open_count=open_count-1,count=count-1 \ >WHERE group_artifact_id=new.group_artifact_id; > END IF; > END IF; >END IF; >RETURN NEW; It is hard to test this without the table schema, but I think you are missing some END IF's in the code above. Those are not elif's, but actual new IF's that need their own END IF, I think. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] PL/pgSQL "compilation error"
> Bruce Momjian writes: > > > There is a PL/PgSQL HowTo that we are trying to integrate into the docs > > for 7.1. Anyone have a URL or status on that? > > It's not a PL/pgSQL Howto, it's just a documentation how to port from > Oracle's PL/SQL. The status is "done". The author expressed interest in > more work on the PL/pgSQL documentation; we'll see what comes of it. Oh, that's great. Thanks. > http://www.postgresql.org/devel-corner/docs/postgres/plpgsql-porting.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] PL/pgSQL "compilation error"
> I'm happy to do my bit on the documentation side, but I don't want to end > up duplicating the cookbook - does anyone know the status of this? I'd > guess 90% of plpgsql functions will be on the same "theme" as something > in a cookbook so it's the perfect format for learning/cut & paste. I am told that the cookbook has been merged into the 7.1beta docs. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] Re: Use of the LIMIT clause ?
Is this a TODO item? Seems we should match MySQL, seeing as we took the feature syntax from them. [ Charset ISO-8859-1 unsupported, converting... ] > On Mon, Mar 12, 2001 at 09:21:58PM -0500, Tom Lane wrote: > > Spy <[EMAIL PROTECTED]> writes: > > > Tom Lane a ?crit : > > >> Is that actually how MySQL interprets two parameters? We treat them > > >> as count and offset respectively, which definition I thought was the > > >> same as MySQL's. > > > > > But MySQL's syntax is different, as found on > > > http://www.mysql.com/doc/S/E/SELECT.html : > > > "SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] > > > [SQL_BUFFER_RESULT] > > > [...] > > > [LIMIT [offset,] rows]" > > > > That's annoying; looks like we do it backwards from MySQL. Can anyone > > confirm that this is how MySQL behaves (maybe it's a typo on this > > documentation page)? > > Yes, it does behave as documented. > > > Should we consider changing ours if it is different? > > I don't know that it's worth it... it seems to inconvenience some > people either way. I may soon be moving a moderately complex system > from MySQL to Postgres and it wouldn't be the end of my world if > I had to reverse all the LIMITs. > > Richard > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Re: Use of the LIMIT clause ?
> Bruce Momjian <[EMAIL PROTECTED]> writes: > > Is this a TODO item? Seems we should match MySQL, seeing as we took the > > feature syntax from them. > > Question is, will more people be inconvenienced if we change it than if > we leave it alone? Seems like someone gets screwed either way ... No question about it. One solution would be to disable this syntax for one release and then fix it in the one after that. We already offer LIMIT/OFFSET, which is what they should be using anyway. If we can't remember, and no one reported the bug for many releases, odds are no one was using that more limited form anyway. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Maybe a Bug, maybe bad SQL
With 7.1beta, I get:
day | day
+-
2000-04-02 | 2
2000-04-01 | 1
2000-04-03 | 3
(3 rows)
> HI,
> I am running postgres version 7.0 and I do this...
>
> akrherz=# create table test (day date);
> CREATE
> akrherz=# insert into test values('2000_04_02');
> INSERT 13166281 1
> akrherz=# insert into test values('2000_04_01');
> INSERT 13166282 1
> akrherz=# insert into test values('2000_04_03');
> INSERT 13166283 1
> akrherz=# select day, date_part('day', day) AS day from test;
> day | day
> +-
> 2000-04-02 | 1
> 2000-04-01 | 1
> 2000-04-03 | 3
> (3 rows)
>
>
> Is this fixed in a newer version? Am I doing something wrong?
>
>
> TIA,
> Daryl Herzmann
>
>
> ---(end of broadcast)-------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
---(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] Maybe a Bug, maybe bad SQL
> version 7.0.3
>
> binary_data=# select day, date_part('day', day) AS day from test;
> day | day
> +-
> 02/04/2000 | 2
> 01/04/2000 | 1
> 03/04/2000 | 3
> (3 rows)
>
> binary_data=#
>
> Why does everyone reply to the person as well as CC to the list when the
> person is on the list anyhow?
Good question. They get the reply faster by sending it to them and the
list, and if someone later wants to reply only to the poster, they have
the email address right there.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
---(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] Maybe a Bug, maybe bad SQL
> Note also that it's a mailing list cultural thing: many lists operate > in a 'post only to the list' mode. Those of us on the pgsql lists do the > 'list and person' thing, in response to direct questions, for the reasons > Bruce and D'Arcy point out. Note that by knowing the reasons, one may > then make informed decisions, like my posting of this message directly > to the list only, since it's a peripheral issue and multiple people > are involved in the conversation. It's not uncommon, when debugging > a particular problem, or discussing implementation of a new feature, > to have a thread of discussion by CC'ing three or four developers, > plus the HACKERS list for archiving and general interest. My mailer would have trouble sending just to the list and not to both. To do list-only, the mailing list software would have to set the Reply-To to be to the list. Marc had it set up that way a few times, but most didn't like it. In fact, the big problem with that setup is that you can't easily reply just to the poster. Most mailers have a 'reply to user' and 'reply to group' mode. Reply to user goes only to the poster, while reply-to group goes to both. At least that is how I understand the issue. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] how do I check if a temporary table exists?
Multiple backends can create temp tables with the same name and each person will see their own version. > Thus spake datactrl > > How do I check if a temporary table exists? Searching pg_tables with a > > temporary table name we chose always fails. > > What do you mean? You know that it exists because you just created it > and the create succeeded. Can you describe a situation where you need > to know about a temp table and don't already know? > > It can't go into the pg_tables table because then it would be visible > to other processes. Are you perhaps misunderstanding temp tables? You > cannot see them except from the process that created them. > > -- > D'Arcy J.M. Cain| Democracy is three wolves > http://www.druid.net/darcy/| and a sheep voting on > +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. > > ---(end of broadcast)------- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Re: [HACKERS] triggered data change violation
> A "triggered data change violation" happens everytime you change twice > within a transaction a value (column) that is part of a foreign key > constraint (don't recall exactly which part). > > This error shouldn't really happen, but I recall there were some > implementation and definition problems with deferred constraints. > > ...FAQ alert... Yes, I just got it in the TODO list a few weeks ago: * INSERT & UPDATE/DELETE in transaction of primary key fails with deferredTriggerGetPreviousEvent or "change violation" [foreign] I personally think we could do better on the wording of that error message, at least until we get it fixed. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] serial type; race conditions
> Hi, > > I'm using serial fields to generate IDs for almost all object in my > database. I insert an empty row, get the CURRVAL() of the sequence > and then update to that value. > > I had understood (and now, I can't find the reference to back this up) > that serial is implemented in such a way that race conditions between > DB connections can't happen. > > Is this true? Safe. See FAQ item. currval is for your backend only. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] RE: pl/pgsql and returning rows
MY book in chapter 18 has a Pl/PgSQL function called change_statename that does insert/update automatically. http://www.postgresql.org/docs/awbook.html > As a workaround, you can insert your row into an existing table, then > retrieve it from there later. I think you need to enumerate all of the > fields, as in 'INSERT INTO table VALUES (ret.field1, > ret.field2,...ret.fieldn);'. At least, I haven't succeeded any other way. > Messy, but the best method available right now. > > > -Original Message- > > From: Richard Huxton [SMTP:[EMAIL PROTECTED]] > > Sent: Tuesday, March 27, 2001 2:27 AM > > To: [EMAIL PROTECTED]; wade > > Subject:Re: pl/pgsql and returning rows > > > > From: "wade" <[EMAIL PROTECTED]> > > > > > create function get_details(int4) returns details as ' > > > declare > > > ret details%ROWTYPE; > > > site_recrecord; > > > cntct contacts%ROWTYPE; > > > begin > > > select into site_rec * sites_table where id = $1 limit 1; > > > select into cntct * from contacts where id = site_rec.contact; > > > > > > -- and then i populate rows of ret. > > > ret.name := cntct.name; > > > ret.ip := site_rec.ip; > > > . > > > . > > > . > > > return ret; > > > end; > > > ' language 'plpgsql'; > > > > > > now the problem is when is when I do a: > > > SELECT get_details(55); > > > all i get is a single oid-looking return value: > > > get_details > > > - > > > 136295592 > > > (1 row) > > > > Sorry - you can't return a row from a function at the present time (except > > for trigger functions which are special) although I believe this is on the > > todo list for a later 7.x release. > > > > Just from the top of my head, you might try a view with a select rule, > > although I'm not completely clear what your objectives are. > > > > - Richard Huxton > > > > > > ---(end of broadcast)--- > > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] RE: pl/pgsql and returning rows
FYI, I am always looking for additional examples that I should add in the next edition. > Yes: good example! I keep a printed copy on my desk... :-) > > > -Original Message- > > From: Bruce Momjian [SMTP:[EMAIL PROTECTED]] > > Sent: Tuesday, March 27, 2001 3:52 PM > > To: Jeff Eckermann > > Cc: [EMAIL PROTECTED]; wade > > Subject:Re: [SQL] RE: pl/pgsql and returning rows > > > > MY book in chapter 18 has a Pl/PgSQL function called change_statename > > that does insert/update automatically. > > > > http://www.postgresql.org/docs/awbook.html > > > > > As a workaround, you can insert your row into an existing table, then > > > retrieve it from there later. I think you need to enumerate all of the > > > fields, as in 'INSERT INTO table VALUES (ret.field1, > > > ret.field2,...ret.fieldn);'. At least, I haven't succeeded any other > > way. > > > Messy, but the best method available right now. > > > > > > > -Original Message- > > > > From: Richard Huxton [SMTP:[EMAIL PROTECTED]] > > > > Sent: Tuesday, March 27, 2001 2:27 AM > > > > To: [EMAIL PROTECTED]; wade > > > > Subject:Re: pl/pgsql and returning rows > > > > > > > > From: "wade" <[EMAIL PROTECTED]> > > > > > > > > > create function get_details(int4) returns details as ' > > > > > declare > > > > > ret details%ROWTYPE; > > > > > site_recrecord; > > > > > cntct contacts%ROWTYPE; > > > > > begin > > > > > select into site_rec * sites_table where id = $1 limit 1; > > > > > select into cntct * from contacts where id = site_rec.contact; > > > > > > > > > > -- and then i populate rows of ret. > > > > > ret.name := cntct.name; > > > > > ret.ip := site_rec.ip; > > > > > . > > > > > . > > > > > . > > > > > return ret; > > > > > end; > > > > > ' language 'plpgsql'; > > > > > > > > > > now the problem is when is when I do a: > > > > > SELECT get_details(55); > > > > > all i get is a single oid-looking return value: > > > > > get_details > > > > > - > > > > > 136295592 > > > > > (1 row) > > > > > > > > Sorry - you can't return a row from a function at the present time > > (except > > > > for trigger functions which are special) although I believe this is on > > the > > > > todo list for a later 7.x release. > > > > > > > > Just from the top of my head, you might try a view with a select rule, > > > > although I'm not completely clear what your objectives are. > > > > > > > > - Richard Huxton > > > > > > > > > > > > ---(end of > > broadcast)--- > > > > TIP 1: subscribe and unsubscribe commands go to > > [EMAIL PROTECTED] > > > > > > ---(end of broadcast)--- > > > TIP 2: you can get off all lists at once with the unregister command > > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > > > > > > > > -- > > Bruce Momjian| http://candle.pha.pa.us > > [EMAIL PROTECTED] | (610) 853-3000 > > + If your life is a hard drive, | 830 Blythe Avenue > > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] serial type; race conditions
> How does currval work if you are not inside a transaction. I have > been experimenting with inserting into a table that has a sequence. > If the insert fails (not using a transaction) because of bad client input > then the next insert gets the proper next number in the sequence. If you are in a transaction, and the INSERT succeeds but the transaction rolls back, the sequence does not get reused. Each backend has a local variable that holds the most recent sequence assigned. That is how currval works. > > given sequence 1,2,3,4,5 exists > insert into table date 1/111/01 (obviously wrong) insert fails... > try again with good data, insert succeeds and gets number 6 in the > sequence. > > i'm getting what I want. A sequence number that does not increment > on a failed insert. However, how do I get the assigned sequence > number with currval when I am not using a transaction? What > happens when multiple users are inserting at the same time? > > I am trying to create a sequence with out any "missing" numbers. If > there is a failure to insert, and a sequence number is "taken". I want > the empty row. > > Thanks, it is getting clearer You really can't use sequences with no gaps. Sequence numbers are not _held_ until commit because it would block other backends trying to get sequence numbers. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] Inheritance Semantics
> Could someone (Chris Mead?) post an update on the status of fixing > PostgreSQL's inheritance semantics in the following ways: > > Has a decision been made to implementing true inheritance via INHERITS or an > alternative keyword? > > By true inheritance, I mean first and foremost that any query on a super-class > should query *all members* of that class by default regardless of which table > they are stored in. Any other behavior violates the very natural expectation > that a table called "PERSON" actually implements the class of all persons. > 7.1 does that already. > Second, for performance reasons, there needs to be a way for an index on a > parent class attribute to be shared by all the tables that implement or > inherit from that parent class. This is also necessary to enforce unique > constraints on all members of a class. That is on the TODO list, so I think we want it to happen. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Savepoints
> Does PostgreSQL currently support something like savepoints in Oracle? No, sorry, but we know we need them. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] RTREE on pointsy
I believe Marc install the GIST code into the tree long ago. > Jeff Hoffmann <[EMAIL PROTECTED]> writes: > > I know there are a couple of GiST examples in contrib (seg, cube & > > intarray), but i thought there used to be at least a gist_box_ops. > > I don't recall any such thing having been removed, but it does seem > peculiar that there are no GIST opclasses in the standard distribution. > How the heck did the GIST index code get developed/tested without some > opclasses? > > Anyone remember the history? AFAICT from the CVS logs, GIST was added > to the tree in mid 1996, but no opclasses for it were added at the time. > I'd go digging in the maillist archives, but www.postgresql.org is too > friggin' slow at the moment ... > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SET SEQSCAN TO OFF - error
> I have the command below like it is described in the documentation > (http://www.archonet.com/pgdocs/force-index.html) but it doens't work. > > SET SEQSCAN TO OFF; > ERROR: 'seqscan' is not a valid option name > > > Does anybody know why? > Try: test=> set enable_seqscan to off; SET VARIABLE -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] How to encode and decode password in pgsql !!
[ Charset ISO-8859-1 unsupported, converting... ] > D'Arcy, > > > You use the chkpass type that I just commited to the distribution > > (finally.) > > Here is an example of its usage. > > Does that mean that chkpass is part of 7.1, or only the current CVS? > It will appear in 7.1.1, to be released soon. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] Re: Use of the LIMIT clause ?
[ Charset ISO-8859-1 unsupported, converting... ] > On Mon, Mar 12, 2001 at 09:21:58PM -0500, Tom Lane wrote: > > Spy <[EMAIL PROTECTED]> writes: > > > Tom Lane a ?crit : > > >> Is that actually how MySQL interprets two parameters? We treat them > > >> as count and offset respectively, which definition I thought was the > > >> same as MySQL's. > > > > > But MySQL's syntax is different, as found on > > > http://www.mysql.com/doc/S/E/SELECT.html : > > > "SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] > > > [SQL_BUFFER_RESULT] > > > [...] > > > [LIMIT [offset,] rows]" > > > > That's annoying; looks like we do it backwards from MySQL. Can anyone > > confirm that this is how MySQL behaves (maybe it's a typo on this > > documentation page)? > > Yes, it does behave as documented. > > > Should we consider changing ours if it is different? > > I don't know that it's worth it... it seems to inconvenience some > people either way. I may soon be moving a moderately complex system > from MySQL to Postgres and it wouldn't be the end of my world if > I had to reverse all the LIMITs. > Added to TODO. If we took the feature from MySQL, seems we should match it. This will require a clear notice in the release notes: * Change LIMIT val,val to be offset,limit to match MySQL -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] PGAccess/pgplsql Blues
FYI, I could not reproduce this problem in the current CVS sources. I created a function 'select 1;' and saved it, then opened the function and added a comment line, saved that, and it worked fine. > Roberto, > > > IIRC, pgaccess does quote-escaping for you, so if you try to write > > "standard" PL/pgSQL (escaping single quotes), it'll barf this error. > > > > Just something to check. > > Thanks. This doesn't seem to be the case; it seems to be a translation > problem: > > 1. Test fn_save_order: it's working. > 2. Open fn_save_order in PGAccess. > 3. Add '--test comment' on its own line. > 4. Save fn_save_order. > 5. test it: "Parse Error at or near "" " > > Unfortunately, I can't afford to pay Constatin for debugging, so that's > where things stand ... > > -Josh > > > __AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 621-2533 > and non-profit organizations. San Francisco > > ---(end of broadcast)------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Hey! ORDER BY in VIEWS?
I think Tom fixed that in 7.1.X. That is why it now works. > Tom, Stephan, > > Hey! I thought you couldn't do ORDER BY in views ... yet I just did. > Is this a new thing, or am I just getting my Trasact-SQL and my > PostgreSQL mixed up again? > > -Josh > > > > __AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 621-2533 > and non-profit organizations. San Francisco [ Attachment, skipping... ] [ Attachment, skipping... ] [ Attachment, skipping... ] > > ---(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 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Hey! ORDER BY in VIEWS?
> But - this gives them in reverse timestamp order. So - wrap the query in > a view and then apply your own ORDER BY. > > Can't remember who came up with this (some evil genius :-) - but it > seemed to make sense so I stuck the example in my PostgreSQL notes. We kept rejecting the idea of ORDER BY in a view until someone suggested LIMIT and ORDER BY, at which point we had to enable it. I think Tom did the work. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] pl/pgsql - code review + question
> Josh - if I try and do OFFSET at the same time (presumably it's the same > change) do you fancy acting as a sanity test site? > Both LIMIT and OFFSET seem to have that restriction. I will add this to the TODO list. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] TODO List
> Bruce, > > while you're at TO-DO list additions: > > I'd like to have a construct like: > > loop > if then > next [loop]; > end if; > [more statements] > end loop; > > I want to be able to skip to the next iteration of the loop, if a certain > condition is met but I do not want to exit the loop all together. There > doesn't seem to be functionality for that right now. You want C 'continue' statement. Is that for Pl/pgSQL? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Who do I make _ not a wildcard?
I also think two underscores make a literal underscore. Same with %. > On Sat, 28 Jul 2001, Roy Souther wrote: > > > In PG the _ is a wildcard that means any singal char. I need to do a search > > for the actual _ char and not get back thousands of wrong matches. Is there > > and escape char that I could use? This needs to work with PG 7.0.3 & 7.1.2. > > \\_ should work for a literal escape. > At least on current sources you can do something like: > like 'blah!_%' escape '!' > where ! becomes the escape character for the string. > > > > ---(end of broadcast)----------- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] Fuzzy matching?
> > See /contrib/soundex. > > Sorry, missed that -- I only looked in the Documentation :( > I guess it's not there because it is a contrib. FWIW, both Oracle and MSSQL > have a built-in soundex function. > > In any case, metaphone is reportedly more accurate (at least for English > words) than soundex, and levenshtein offers an entirely different and > interesting approach. Any interest in having all three of these in the > backend? Sure. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Fuzzy matching?
> Sounds like you want something along the lines of soundex or metaphone? I > don't see either function in PostgreSQL, but take a look at the PHP manual > to see examples: http://www.php.net/manual/en/function.soundex.php , > http://www.php.net/manual/en/function.metaphone.php > See /contrib/soundex. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] where'd the spaces come from
Does anyone have a fix for this? > From: "Gary Stainburn" <[EMAIL PROTECTED]> > > > psql -c "select t.tregion || '/' || to_char(t.tnumber,'000') || '-' || > > to_char(m.mnumber,'00') as unitno from teams t, members m > > where m.mteam = t.tid;" > >unitno > > - > > SW/ 041- 03 > > SW/ 041- 05 > > Looks like a buglet in to_char()s handling of numbers (unless I > misunderstand the way the formatting is supposed to work). > > select '[' || to_char(12,'x000') || ']'; > ?column? > -- > [x 012] > > If you're running the current version, might be worth posting a bug report. > You can work around it with something like: > > ... substr(to_char(t.tnumber,'000'),2,3) ... > > HTH > > - Richard Huxton > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] where'd the spaces come from
> Hi Bruce, > > a fix for what? > If you're meaning the leading space, then the fix is in the followup post > that I made to my original quiestion. i.e. > > psql -c "select to_char(12,'xFM000');" > to_char > - > x012 > (1 row) > > The 'FM' removes the space. So the FM is the correct way to do this, right? There is no bug? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] optimizing select ... not in (select ...)
Can it be redone using NOT EXISTS? > I have this query : > > select distinct on (Pictures.PictureID) * from Pictures where Pictures.PictureID not >in (select distinct PictureID from Keywords); > > and I find it a bit slow. Does anybody have suggestions to run this > faster ? (I have indexes on PictureID on both Pictures and Keywords) > > -- > Laurent Martelli > [EMAIL PROTECTED] http://www.bearteam.org/~laurent/ > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Temp tables being written to disk. Avoidable?
> Hi Tom, > > Thanks for your response, enlightening as always. > > > Not at present --- temp tables are not different from real tables, > > except for some naming shenanigans. So creation of a temp table will > > involve some disk hits. > > Ok, would it be a good idea to modify that for the future? Given that > temp tables:- > a) can't be seen by other connections. > b) are likely to be selected upon heavily close to creation time. > c) are likely to be short lived. > is there any reason to move them out to disk unless strictly > necessary (aside from that it may take a fair bit of > re-engineering and the core developers have other more important > and/or more interesting things to be getting on with)? If the temp table doesn't fit in memory, we will have to put it in backing store somewhere, and a disk is the logical place, right? I don't see a huge advantage of putting it in memory. We could prevent WAL writes for temp tables. That would help. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] Sql
For history, see the first chapter of my book. Also see appendix on non-standard feature. http://www.postgresql.org/docs/awbook.html > Hi, > > I'd like an information about the history of SQL. > The similarities and differences between SQL1 and SQL2 and SQL3. > > Could you help me? > > Thanks for your attention. > > Marcia Cunha > Belo Horizonte , Brasil. > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] [HACKERS] [GENERAL] getting the oid for a new tuple in a BEFORE
> > we need to control database changes within BEFORE triggers. > > There is no problem with triggers called by update, but there is > > a problem with triggers called by insert. > > > > We strongly need to know the oid of a newly inserted tuple. > > In this case, we use tg_newtuple of the TriggerData structure > > passed to thetrigger function, and its t_data -> t_oid will > > have the value '0'. > > > > Using BEFORE and AFTER triggers would make our lives much harder. > > > > Is there any way (even hack) to get the oid the newly > > inserted tuple will receive? > > Just set t_data->t_oid = newoid() - this is what backend does > in heapam.c:heap_insert(). Does that work? Doesn't that get overwritten when the actual INSERT happens? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] temporary views
> Hi > I have simple question: How to create view on a temporary table? > I need this, because pl/pgsql function returns data via temporary table. > > View created on a temporary table is useful only to the end of session. > Next time i create the same temp table i get > "Table xxx with oid xxx doesn't exist" Just name your temporary table the same name in every session. Why bother with a view. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] autoincrement
Use SERIAL instead of AUTO_INCREMENT. > How can I reproduce in PostgreSQL type structure from MySQL: > INT NOT NULL PRIMARY KEY AUTO_INCREMENT > > ? > > To be more exact -- in two words: "how can I create autoincrement > field?" > > Thanks a lot. > > -- > green > [http://www.extrasy.net] > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Sorry for blocking email
My spam filter was misconfigured and I bounced back some messages I shouldn't have. Sorry. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] VARCHAR vs TEXT
> >>>>> "BM" == Bruce Momjian <[EMAIL PROTECTED]> writes: > > BM> CHAR() is best when storing strings that are usually the > BM> same length. VARCHAR() is best when storing variable-length strings, > BM> but you want to limit how long a string can be. TEXT is for strings > BM> of unlimited length, maximum 1 gigabyte. BYTEA is for storing > BM> binary data, particularly values that include NULL bytes. > > Could you add the length limitation for TEXT to the reference manual? > I searched high and low for that limit, but never found it. Also, > what's the max VARCHAR() or CHAR() I can create? Is that also 1Gb? TEXT limit is 1GB, as shown on the 'limits' FAQ item. Is it worth mentioning here? CHAR()/VARCHAR() also 1GB limit. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] VARCHAR vs TEXT
There is a limits FAQ item, not a separate limits FAQ. Sorry for the confusion. > Perhaps 'limits' should be part of FAQ, not separate entity? > > Also a reference (or link) to 'limits' from other sections such as > mentioned below may be more appropriate than duplicating the information. > > Frank > > At 08:56 AM 10/16/01 -0400, you wrote: > >> >>>>> "BM" == Bruce Momjian <[EMAIL PROTECTED]> writes: > >> > >> BM> CHAR() is best when storing strings that are usually the > >> BM> same length. VARCHAR() is best when storing variable-length > strings, > >> BM> but you want to limit how long a string can be. TEXT is for > strings > >> BM> of unlimited length, maximum 1 gigabyte. BYTEA is for storing > >> BM> binary data, particularly values that include NULL bytes. > >> > >> Could you add the length limitation for TEXT to the reference manual? > >> I searched high and low for that limit, but never found it. Also, > >> what's the max VARCHAR() or CHAR() I can create? Is that also 1Gb? > > > >TEXT limit is 1GB, as shown on the 'limits' FAQ item. Is it worth > >mentioning here? CHAR()/VARCHAR() also 1GB limit. > > > >-- > > Bruce Momjian| http://candle.pha.pa.us > > [EMAIL PROTECTED] | (610) 853-3000 > > + If your life is a hard drive, | 830 Blythe Avenue > > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 > > > >---(end of broadcast)--- > >TIP 4: Don't 'kill -9' the postmaster > > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] temporary views
> Bruce Momjian wrote: > > > > > Hi > > > I have simple question: How to create view on a temporary table? > > > I need this, because pl/pgsql function returns data via temporary table. > > > > > > View created on a temporary table is useful only to the end of session. > > > Next time i create the same temp table i get > > > "Table xxx with oid xxx doesn't exist" > > > > Just name your temporary table the same name in every session. Why > > bother with a view. > > Creating a view makes my life easier. My temporary table has fields > like id1,id2,id3,id4 and view translates it using inner joins to > name1,name2,name3,name4. This temp table has always the same > name and I don't want to do the translation inside pl/pgsql function. OK, basically there is no way to create views reliably on temp tables: creattest=> create temp table x(y int); CREATE test=> create view z on x as select * from x; ERROR: parser: parse error at or near "on" test=> create view z as select * from x; CREATE test=> select * from z; y --- (0 rows) Of course this works, but exiting the session and restarting it gets you: test=> create temp table x(y int); CREATE test=> select * from z; ERROR: Relation "x" with OID 16562 no longer exists Internally, the problem is that the temp table is referenced by oid, not table name. If you create a temp in a later session, it doesn't have the same oid as the one in the session where you created the view. What actually should happen is that the view should go away at the end of the session. However, unlike indexes, we can have several tables involved in a view so it is hard to know exactly how to handle this. Seems like a TODO item, at least. What we could do is to create views as TEMP if they use temp tables and drop the view as soon as the session ends . You of course would have to recreate the view each time but because it is a _temp_ view, it could be done reliably by multiple backends at the same time. Added to TODO: * Allow views on temporary tables to behave as temporary views -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] temporary views
> Peter Eisentraut <[EMAIL PROTECTED]> writes: > > Bruce Momjian writes: > >> * Allow views on temporary tables to behave as temporary views > > > I don't think this is a good idea. Especially since our temp tables are > > allowed to shadow persistent tables, it would not be obvious whether the > > view you're creating with any given statement will become persistent or > > temporary. > > > I think an explicit CREATE TEMPORARY VIEW command would be fair and safe, > > but until that is done we should probably concentrate on the SQL standard > > behaviour. > > I agree with Peter on this --- CREATE TEMP VIEW seems like a fine idea, > but allowing views to be implicitly made temporary sounds like a good > way to shoot yourself in the foot. ISTM a plain CREATE VIEW should > always create a permanent object, and therefore should error out if it > refers to any temp tables. We can handle the temp views thing two ways, either allow views to map to temp tables by name, or allow temp views to map to temp tables that exist at creation time and drop the views on session exit. The original poster clearly wanted the first behavior, but I agree with Peter that the second has fewer surprises for the user and is more standard. I have updated the TODO item to: * Allow temporary views > However, there are some interesting implications here for the recurring > issue of how plpgsql functions ought to interact with temp tables. > We've generally thought of the current behavior as a simple shortcoming > of plpgsql's query caching logic, and no doubt it is --- but what > *should* the behavior be? Can a long-lived function validly refer to > short-lived tables? If so, what should the semantics be, exactly? It would be interesting of plpgsql could try for an table match by oid first, and if that fails, try a match by table name and match only if a temp table is hit. So basically the only table-name matching that would happen would be hits on temp tables. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] temporary views
> Bruce Momjian wrote: > > > > We can handle the temp views thing two ways, either allow views to map > > to temp tables by name, or allow temp views to map to temp tables that > > exist at creation time and drop the views on session exit. The original > > poster clearly wanted the first behavior, but I agree with Peter that > > the second has fewer surprises for the user and is more standard. > I think, that referring tables by names would be enough. I found another > problem connected to this. There is something like this in documetation: > "Existing permanent tables with the same name are not visible > (in this session) while the temporary table exists." Why not to overlap > permanent table? Currently it doesn't work: > > create table x (a integer); > create view y as select * from x; > select * from y; > OK > create temp table x as select * from x; > select * from y; > ERROR: Relation "x" with OID 364752 no longer exists Yes, we could add code that tried the temp table first, and if it didn't match the oid, fall back to the permanent table. Of course, it would break the temp table overlap rules. Of course, there is the question of whether it is worth doing this. If you create the view after the temp table is created it would properly map to the temp table. If you have created a temp table that masks the real table, maybe you want the view to fail. Temp tables masking real tables is already pretty powerful and mapping some fallback rules on top of this seems a little too powerful and perhaps a little too confusing. > > It would be interesting of plpgsql could try for an table match by oid > > first, and if that fails, try a match by table name and match only if a > > temp table is hit. So basically the only table-name matching that would > > happen would be hits on temp tables. > But why only plpgsql? Would it be difficult to add it to SQL > implementation > of PostgreSQL? Yes, it would be done there too. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] EXISTS Keyword
> Hello! > > I vaguely remember that someone said somewhere that > EXISTS runs faster than IN: > > SELECT * FROM table1 where field1 EXISTS (SELECT > field4 FROM table2) Move field1 into the subquery and join it to table2. See the FAQ for an example. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Server crash caused by CHECK on child
I can confirm this now works fine in current sources. No crash. > -- Hi Kevin, and everyone! > -- > -- I don't think that I only found a minor bug compared to > -- the other you wrote in your last letter: the backend crash > -- is caused by the same CHECK constraint in the child table. > -- > -- However, for you without time to analyzing Kevin's huge > -- scheme, here is the very simplified, crash-causing script. > -- > > > drop table child; > drop table ancestor; > > create table ancestor ( > node_id int4, > a int4 > ); > > create table child ( > b int4 NOT NULL DEFAULT 0 , > c int4 not null default 3, > CHECK ( child.b = 0 OR child.b = 1 ) > ) inherits (ancestor); > > insert into ancestor values (3,4); > insert into child (node_id, a, b) values (5,6,1); > > update ancestor set a=8 where node_id=5; > > - > -- > -- I am hunting it, but I have to learn all what this query-executing > -- about, so probably it takes uncomparable longer for me than for > -- a developer. > -- > -- Regards, > -- Baldvin > -- > > > > ---(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 > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] VARCHAR vs TEXT
> On Wed, 10 Oct 2001, Aasmund Midttun Godal wrote: > > > I am sure this question has been answered in some form or another > > before, but I can't really find anything on exactly this issue. > > > > Are there any differences between varchar and text other than > > > > 1. varchar has limited size > > 2. varchar is SQL 92 text is not? > > > > Especially regarding performance. > > > > Or am I correct to assume that if you need a place to store some text, > > and you are not sure how much (like an email address or a name) you > > are best off using text? > > Pretty much yes. text and varchar are pretty equivalent other than > the fact that varchar specifies a maximum size. I have added the following paragraph to the FAQ: CHAR() is best when storing strings that are usually the same length. VARCHAR() is best when storing variable-length strings, but you want to limit how long a string can be. TEXT is for strings of unlimited length, maximum 1 gigabyte. BYTEA is for storing binary data, particularly values that include NULL bytes. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Which one is faster?
Jan Wieck wrote: > Bruce Momjian wrote: > > > > Wei Weng wrote: > > > Between Using Limit P, S or using a cursor to start from FETCH ABSOLUTE > > > S and FETCH NEXT for P times, which one is faster? > > > > LIMIT is faster because it doesn't need to generate all the result, in > > some cases. > > Neither does using a cursor need to generate all the results first. It's > a portal, which is an executor state on hold, and FETCH is implemented > as ExecutorRun() with the number of wanted result rows as limitation. > Since LIMIT generates internally one more result row than really wanted, > I don't think LIMIT would be much faster, but it's for sure less > portable. Oh, I thought the portal had to be materialized before returning a row. Thanks. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] variance aggregate function incorrect? Reference Materials
Joseph Syjuco wrote: > hi > i needed the variance function ... i dont know if i introduced the wrong > parameters or maybe this variance is not the variance that im looking > for but it doesnt provide the right results > > variance=(nEx^2 - (Ex)^2)/(n(n-1)) > my sql statement > select variance(answer) from tbl_answer (where answer is of type > integer) > > because of this i need to create my own variance aggregate (unless > someone enlightens me on whats happening with the variance aggregate) so > i would like to know if theres anyone who knows a good create aggregate > tutorial (with samples) ... all i got from google so far is the > reference from ninthwonder.com Uh, I tried this: test=> select variance(relnatts) from pg_class; variance --- 24.4259169884 (1 row) Not sure if it is the right number or not. Have you tried taking the square root and see if that number is correct. The docs say: The variance is the square of the standard deviation. The supported data types and result types are the same as for standard deviation. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Localization
GRIMOIS Eric wrote: > Hi all > > Is there a simple way to localize in foreign language error messages without > modifying and compiling the sources again ? > It should be useful for final users who don't read Shakespeare in the > original version ;) Uh, we have error messages localization in 7.2. Not sure how to enable it. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Selecting data from a table created in another database...
Ligia Pimentel wrote: > I don't know if this can be done... > > In MSSQL Server I can access a table created in another database (on the > same server, of course) by using the following syntaxis... > > select * from databasename..tablename where condition; > > Can I do this in postgres? > > I'm using version 7.2 on a redhat server... Sorry, you can't do cross-db queries with PostgreSQL. Take a look at /contrib/dblink as an option. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] Possible Bug regarding temp tables (sql or psql?)
Mark Frazer wrote: > Any idea why the table can't be seen with \d in psql then? It is a known problem with temp tables. They aren't visible with \d because they are invisible system tables that are removed on exit. I think 7.3 will fix this. > > Christopher Kings-Lynne <[EMAIL PROTECTED]> [02/07/04 00:21]: > > > > No - they go away at the end of a _connection_. However, there is now a > > patch floating around on -hackers that would add an ' ON COMMIT DROP;' > > option to CREATE TEMP TABLE. > > -- > In the event of an emergency, my ass can be used as a flotation > device. - Bender > > > > ---(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 > > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Possible Bug regarding temp tables (sql or psql?)
It works, but only in CVS, not in 7.2.X. Marking something as done in TODO only means it is done and will be in the _next_ release. Sorry. --- Mark Frazer wrote: > > > Bruce Momjian <[EMAIL PROTECTED]> [02/07/04 22:10]: > > > > TODO has: > > > > * Allow psql \d to show temporary table structure > > > > Looks like it works fine now with schemas: > > > > I will mark the TODO as done. > > It doesn't work with select into though: > > config=> select 5 into temp v_tmp ; > SELECT > config=> \d v_tmp > Did not find any relation named "v_tmp". > config=> select 4 into temp v_tmp ; > ERROR: Relation 'v_tmp' already exists > config=> select version() ; >version > - > PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96 > > > -- > I heard one time you single-handedly defeated a hoard of rampaging somethings > in the something something system. - Fry > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] How do i return a dataset from a stored procedure
Roberto Mello wrote: > On Fri, Jul 05, 2002 at 01:32:33AM -0700, teknokrat wrote: > > I can't figure out how to return a group of rows from a function > > written in plpgsql (or plsql for that matter). The only way i have > > seen it done is with a setof table return value in sql. But since the > > query was a single select anyway i don't see the point of this. Is > > there a way of creating a temporary table in a procedure and using it > > as the output? > > Search the list archives (through groups.google.com - it's > comp.databases.postgresql) or see the developer docs for PL/pgSQL. You > can achive that effect by returning a cursor in PG 7.2 (the section on > returning cursors is ommitted from the current docs in > www.postgresql.org/idocs, so you need to look at the developer docs - see > www.us.postgresql.org). 7.3 will have better docs on using it but you can see them now in the developers docs: http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html See the bottom of that page for examples. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL]
jack wrote: > 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? Actually, we recomment using EXECUTE for any temp table access from pl/pgsql stored procedures. We even have an FAQ item now: 4.26) Why can't I reliably create/drop temporary tables in PL/PgSQL functions? PL/PgSQL caches function contents, and an unfortunate side effect is that if a PL/PgSQL function accesses a temporary table, and that table is later dropped and recreated, and the function called again, the function will fail because the cached function contents still point to the old temporary table. The solution is to use EXECUTE for temporary table access in PL/PgSQL. This will cause the query to be reparsed every time. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
