Re: [SQL] Query optimisation
From: "Michel Vrand" <[EMAIL PROTECTED]> > $conditions may be > > 1/ ...AND groupes.nom = '$something' AND > or > 2/ ...AND groupes.nom ~* '$something' AND > > In the first case, the query is reasonnably fast (0.30 s for 4 items on > 15000) > In the second case, the query becomes very slow (more than 31 s for the same > result) > > to give example, in the first case $something = "Beatles" >in the second case $something = "beatl" > > How to optimise speed ? I tried to type EXPLAIN but I do not understand the > result : > You are right - the index *does* make that much difference. The problem is that ~* matches *anywhere* in the string so it can't use the index - it has to read each entry. If you want to match the start of the entry, I know of one way to make the index work. Use groupes.nom>='beatl' AND groupes.nom<='beatlz' And that should work - although 'z' is a poor character to use - pick the highest valid character in your character set. You might also want to look in the archives for the thread on pgsql-general with a subject of 'Simple Question: Case sensitivity' - Richard Huxton
Re: [SQL] Weird problem with script...
- Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, January 03, 2001 1:02 AM Subject: Re: [SQL] Weird problem with script... > [EMAIL PROTECTED] writes: > > I'm building a script to create the tables in my database. Everything > > works fine except one thing with this part of my script: > > > create table tbl_resume_free_text_type ( > > type_id int, > >type text > > ); > > > -- insert into tbl_resume_free_text_type (type_id, type) values (1, 'Hobbies'); > > -- insert into tbl_resume_free_text_type (type_id, type) values (2, 'Special Talents'); > > > If I uncomment the two insert statements I get an error message > > stating that tbl_resume_free_text_type doesn't exist. > > Odd. The three statements work just fine for me when executed by hand > in 7.0.3. Anyone else able to reproduce a problem? > > regards, tom lane Works fine on 7.0.0 putting them in a text file and doing psql < filename Jamu - how are you executing this? - Richard
[SQL] Non-procedural field merging?
I have two tables, foo and foo2: richardh=> select * from foo; a | b ---+- 1 | xxx 1 | yyy richardh=> select * from foo2; c | d ---+--- 1 | And I would like to set d to 'xxxyyy' (i.e. merge entries from b). Of course the following doesn't work because the 'd' seen is the one from before the query starts. richardh=> update foo2 set d = d || foo.b from foo where foo.a=foo2.c; UPDATE 1 richardh=> select * from foo2; c | d ---+- 1 | yyy Now - I can always solve the problem procedurally, merging the values in my application but I was wondering if any of the smarter people on the list have an SQL way of doing it (something with sub-queries?) PS - I realise I might get 'xxxyyy' or 'yyyxxx' without forcing an order but I don't actually care in this case. TIA - Richard Huxton
Re: [SQL] Non-procedural field merging?
From: "Tom Lane" <[EMAIL PROTECTED]> [snipped my Q about merging text fields from one table into another] > You could do it with a user-defined aggregate function (initial > value '' and transition function ||). I am not sure that aggregates > work in an intelligent way in UPDATE --- ie, I am not sure it would > work to do > > update foo2 set d = catenate(foo.b) from foo where foo.a=foo2.c; Actually, (to my surprise) it did work. I used: richardh=> create aggregate catenate(sfunc1=textcat, basetype=text, stype1=text, initcond1=''); CREATE richardh=> select a,catenate(b) from foo group by a; a | catenate ---+-- 1 | xxxyyy (1 row) Then tried the update - worked with no problem, noting that: richardh=> update foo2 set d = catenate(foo.b) from foo where foo.a=foo2.c; UPDATE 1 richardh=> select * from foo2; c | d ---+ 1 | yyyxxx (1 row) The order is reversed between the select and the update! Important lesson in the relationship between SQL and set theory noted (my college lecturers would be proud of me ;-) > I seem to recall some discussion concluding that that didn't have > very well-defined semantics. I can see how you'd have problems if you were expecting the aggregate to return the same value on each run (a vacuum presumably could reorder the values). In my case, this isn't important. I must admit it didn't occur to me you could create your own aggregates without resorting to C. Shame it's not a standard SQL feature. Thanks Tom - don't know how you find the time to give so much help in the lists. - Richard Huxton
Re: [SQL] Querying date interval
- Original Message - From: "Renato De Giovanni" <[EMAIL PROTECTED]> > Hi, > > Is there any SQL workaround to get the right results from the select > statement bellow? Or am I doing something wrong?? > > select * from testdate where field1 between '2000-10-01' and > '2000-11-30' ; > >field1 > > 2000-09-30 < why is it here?? > 2000-10-20 > 2000-11-25 > (3 rows) > Someone else mentioned DATESTYLE - the other thing to check is that you haven't got a local summertime adjustment on 30th Sep or 1st Oct - that caused issues in some previous versions of postgres IIRC (have a rummage in the archives) - Richard Huxton
Re: [SQL] primary key and indexing
Sorry for lack of proper quoting, you might find it useful to From: "Sharmad Naik" <[EMAIL PROTECTED]> I m sorry If anyone has already asked this Q earlier I wanted to know Q1. Whether I can have another key on which the table is indexed even though i have a primary key..Then how would the data be accessed according to primary key or the indexed key of my choice or i can index as per my choice ( primary / index key)? You can create as many indices as you like. Type "\h create index" in psql for details, but basically: CREATE INDEX indexname ON table (columnlist) PostgreSQL will use whichever index it thinks is the most useful. Remember to vacuum analyze to update table statistics. Q2. If possible can anybody tell me how to get the script given in the e-book of Postresql be interpreted in Perl i.e perl gives an error that Pg is not connected to Dbase Don't know the script you're talking about. Check the settings in the connect command. Try "perldoc Pg" for information on how the Pg module works. - Richard Huxton
Re: [SQL] select returns no line
- Original Message - From: "Attila Kevei" <[EMAIL PROTECTED]> > goodwill=>\d users > Table= users > +--+--+- --+ > | Field | Type| Length| > +--+--+- --+ > | user_id | int4 not null default nextval ( | 4 | > | user_login | varchar() not null | 15 | > | user_passwd | varchar() not null | 15 | > | user_exp | timestamp| 4 | > +--+--+- --+ > Indices: users_pkey > >users_user_login_key Have you tried dropping the index? Could be mangled or a locale problem... - Richard Huxton
Re: [SQL] monster query, how to make it smaller
- Original Message -
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, January 23, 2001 2:42 PM
Subject: [SQL] monster query, how to make it smaller
> Hello everybody
>
> I need some help on a monster query. Please see the attached file for the
> query itself. The only difference is Z_durch_soll and the offset, which is
> currently 0.25. The query will run in a loop where I increment this offset
> until I find enough records.
I'm not entirely clear on what you are trying to do, but perhaps something
like
... AND Z_durch_soll in ('286.35', '286.30', '286.25')
instead of all the UNIONs? This is the same as
... AND (Z_durch_sol1='286.35' OR Z_durch_sol1='286.30' ...)
HTH
- Richard Huxton
Re: [SQL] script for unidirectional database update
From: "Markus Wagner" <[EMAIL PROTECTED]> > I need to periodically transfer the content of one db into another. The > target db should be deleted before and there should be one ascii file > containing the data, because there's a firewall between the two db's and > file transfer ist the most simple thing to do. Does anyone have a script > to automate this? > Look into pg_dumpall - Richard Huxton
Re: [SQL] Archival of Live database to Historical database
From: "Stef Telford" <[EMAIL PROTECTED]> > Hello everyone, > I have hit on a limit in my knowledge and i am looking for > some guidance. Currently I have two seperate databases, one for > live data, the other for historical data. The only difference really > being that the historical data has a Serial in it so that the tables > can keep more than one 'version history'. > > What i would like to do, is after my insert transaction to the > live database, i would like the information also transferred to the > historical one. Now. I can do this via perl (and i have been doing it > this way) and using two database handles. This is rather clumsy and > I know there must be a 'better' or more 'elegant' solution. Not really (AFAIK) - this crops up fairly regularly but there's no way to do a cross-database query. You could use rules/triggers to set a "dirty" flag for each record that needs copying - but it sounds like you're already doing that. If you wanted things to be more "real-time" you could look at LISTEN/NOTIFY - Richard Huxton
Re: [SQL] fetching the id of a new row
Albert REINER wrote: > > I do not know of a way to insert and select in one statement without > the use of a function (what's the problem with those, by the way?), > but as far as I can tell nextval() will return the next value for any > backend, so if you have more than one backend inserting at the same > time you might end up inserting with the same id twice. Instead you Actually nextval() works fine across backends. It always increments the sequence, so repeated calls waste numbers. > should insert once, without specifying the id (so that the default > value, which must be set to nextval()) will be used; to obtain the id, > if indeed you need it, you can than select currval(), which is > guaranteed to work on a per-backend basis. Yep - it's either get nextval and insert or insert and check currval. - Richard Huxton
Re: [SQL]how to select * from database1 table,database2 table
From: "guard" <[EMAIL PROTECTED]> if join database1 database2 how to make I use VB or DELPHI You can't join between databases at present. I don't think this is in the pipeline for 7.2 even. The only solution I know of at present is to connect to two databases in the application and do the join "by hand" there. - Richard Huxton
Re: [SQL] constraint/restrict
From: "Olaf Marc Zanger" <[EMAIL PROTECTED]> > hi there, > > with two tables i want to make some constraint-restrictions > > to make sure that now country-row is deleted if there is still a country_id > in address table. > > e.g. > > address: 1, 2, ... > country: 2, ... > > now country wouldn't be allowed to be deleted. > > how to do that? You want a foreign-key (only in version 7) - check the reference manual for CREATE TABLE - and look for the keyword REFERENCES Basically, it's like: create table foo (fooid serial unique, footxt text); create table bar (barid serial, barfoo int4 references foo (fooid), bartxt text); Then after a few inserts... delete from foo where fooid=1; ERROR: referential integrity violation - key in foo still referenced from bar - Richard Huxton
Re: [SQL] problem with dates
From: "postgresql" <[EMAIL PROTECTED]> > This is a rather generic question about "date" and "time". I seem to > be beating my head on the wall. I was trying to use a set up a table > with a 'date' and 'time' field. I wanted to keep the two separate. > > Can someone explain if there is a difference between a time field > and a timestamp. I don't mean the visual date and time as a single > element. I mean in concept. I have been pouring through the docs > and it appears from my reading that a 'date' field is supposed to act > like the date portion of a timestamp. But it doesn't and I just don't > understand why. What isn't working correctly? I seem to be able to compare dates with timestamps and combine a date + time into a timestamp. > if there is some document somewhere that explains this just point > me to it. Look at the page "datetime-types.htm" in the docs (might only be in the docs for 7.1). - Richard Huxton
Re: [SQL] Estimation of SQL statements
Kevin Quinlan wrote: > > Are there any good techniques that estimate the time it will take to execute > an SQL statement, specifically an INSERT, SELECT, UPDATE, or DELETE? What > factors are important to consider when estimating the execution time of > these types of SQL statements? > Thank you, > Kevin Complexity and size of statement, complexity and size of tables involved, complexity and size of indices involved, amount of data (if any) returned, RDBM design and implementation consideration, OS, hardware, hardware loading, client design... Really, you need to know what you are trying to acheive. The best way to measure speed is to build a system and populate it with dummy data. Then, analyse your results. - Richard Huxton
Re: [SQL] Function to return recordset
From: "Qiqi Dong" <[EMAIL PROTECTED]>
> Hi,
>
> I need help! How can I get either sql or plpgsql function to return
> recordset?
>
> Thanks,
> Qiqi
If you have a table foo you can define your function with "returns foo" but
you cannot use this like:
select foo_function('x');
At present it is only useful for use by other functions (I believe). I think
Tom said this is on the developer todo list though, so sometime after 7.1 it
should be possible.
- Richard Huxton
Re: [SQL] underscore problem
From: "postgresql" <[EMAIL PROTECTED]> > Is there anywhere in the docs that says we should not use an > underscore in a table name? > > select acode from FC_Client_Info where jobno = '1234'; > fails with a warning that fc_client_info does not exist > notice the lower case. If I quote the table > > select acode from "FC_Client_Info" where jobno = '1234'; > the select works fine It's the case not the underscore. PG lower-cases table/field names both on creation and on queries. You can create/access mixed-case tables by quoting them, as you've discovered. This usually crops up converting from a different DB to Postgresql. - Richard Huxton
Re: [SQL] SELECT DISTINCT problems
From: "SCAHILL KEVIN" <[EMAIL PROTECTED]>
> I would like to pull each distinct value of LecturerName with any one
> corresponding ProjectCode value, it does not matter what the ProjectCode
> value is, but all attemps at this have failed so far.
>
> I have tried this but it does not work:
>
> Set rsLecturers = Server.CreateObject("ADODB.Recordset")
> sqlLect = "Select LecturerName, MIN(ProjectCode) from tblSuggestions WHERE
> LecturerName IN ( SELECT DISTINCT LecturerName FROM tblSuggestions)"
> rsLecturers.Open sqlLect, Conn, 3, 3
>
> I get this error when I try to run this:
> [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query
that
> does not include the specified expression 'LecturerName' as part of an
> aggregate function.
The "min()" function means this is what access refers to as a "totals
query". Try something like:
SELECT LecturerName,min(ProjectCode) FROM tblSuggestions GROUP BY
LecturerName
Basically, anything that is not min() or max()ed should be mentioned in the
GROUP BY.
You might need to quote "LecturerName" (like that) etc since they are
mixed-case. On the other hand the ODBC might deal with all that for you.
- Richard Huxton
Re: [SQL] Insert into VIEW ???
Jacek Zagorski wrote: > > Is it possible to INSERT into xyz > where xyz is a view ? > What is the proper syntax ? > > Thanks Much > Jacek Zagorski You'll need to set up the rules for updating - PG can't figure out what you want automatically. There's a page on this in the programmer's guide. - Richard Huxton ---(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] Temp Tables & Connection Pooling
David Olbersen wrote: > > On Fri, 2 Mar 2001, Gerald Gutierrez wrote: > > ->Recently I wanted to implement Dijkstra's algorithm as a stored procedure, > ->and finding that PL/PGSQL cannot return record sets, I thought about using > ->a temporary table for the results. If tempoary tables are session-specific, > ->however, then wouldn't connection pooling make it unusable since the table > ->might "disappear" from one query to the next? What are alternative > ->approaches to implementing Dijkstra's algorithm inside the database? > > > Wouldn't a VIEW do what you want? > > > -- Dave Presumably Gerald's after speed here - IIRC Dijkstra's is shortest path finder, so probably not cheap. I was thinking about the temp table problem the other day, and the best I could come up with involved creating a higher-level connection (application-level session basically). You'd create a table mytempNNN (where NNN is a unique number to identify your user's session) and add a line to a tracking table (NNN,now()) Every time you use mytempNNN update the tracking table's time and run a separate reaper process to kill anything not used for 15 minutes (or whatever). You should be able to automate this to a degree with triggers etc. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: Passing table names to PL/PGSQL for SELECT/UPDATE/INSERT
Gerald Gutierrez wrote: > > I'd like to generalize my function. As per Richard Huxton's suggestion to > create tables named after session ID (thanks Richard)s, I'd like to pass in > some table names so that the algorithm can read from and write into tables > that I specify as parameters to the function. Sometihng like: Known limitation - sorry, should have made things clear. > select dijkstra(inputtablename, outputtablename); > > I've tried typing the parameters as TEXT, and then just inserting $1 in the > select (e.g. SELECT * from $1 ...) This doesn't work, nor did a number of > other ways I tried. Basically, I can think of 3 solutions. Bear in mind it's late here, I've had a few drinks with dinner. 1. Use the EXECUTE statement in 7.1 to run the query 2. Use the EXECUTE statement to construct a custom function, one for each session with the table hard-coded (you could also do this from the application I'd guess) 3. Try pl/Tcl - I _think_ that lets you construct a dynamic query, but I don't know TCL so can't say (it's supposed to be easy enough but I've never got round to it). HTH - Richard Huxton ---(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] Problems with RULE
From: "Jens Hartwig" <[EMAIL PROTECTED]> > Hello Richard, > > this was a very precise analysis - thanks for the effort you made! Precisely wrong in this case. My mistakes have some of the finest tolerances in the world. 8-) > Nevertheless the Tom's explanation of the behaviour in case of views was > sufficient for me. But still I don't understand the behaviour in my case ... Yep - thanks Tom (another item for my notebook). I _think_ I understand Tom's explanation of your case - does this make sense? You have CREATE RULE r1 ON t_xyz WHERE old.deleted=false ... So - if you issue "DELETE FROM t_xyz" you'd want two things to happen: 1. where deleted is false set it to true 2. where deleted was true delete the record So - PG rewrites the query into two parts: DELETE FROM t_xyz WHERE old.deleted=false DELETE FROM t_xyz WHERE NOT(old.deleted=false) Unfortunately, the changes from the first part are visible to the second part so you end up marking everything for deletion then deleting it. Of course in your case you were selecting id=1 so it wasn't so obvious. I think that's what's happening here. Unfortunately, setting DEBUG_PRINT_xxx doesn't seem to show any detail, do I can't show a trace. Of course, with a trigger you can have an IF..THEN..ELSE to make sure you control the order of execution. - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] List Concatination
Josh Berkus wrote: > I have an interesting problem. For purpose of presentation to users, > I'd like to concatinate a list of VARCHAR values from a subtable. To > simplify my actual situation: > > What I'd like to be able to do is present a list of clients and their > comma-seperated contacts in paragraph form, hence: > > Client Contacts > McMurphy Assoc. Jim Henson, Pat O'Gannet, Kelley Moore > > Ross Construction Sara Vaugn, Bill Murray, Peter Frump, > Siskel Ebert > Well, basically you can use a standard join, order it and eliminate duplicate client names in the application. That's the "proper" way. But - if you don't care about the order of contacts you can define an aggregate function: create aggregate catenate(sfunc1=textcat, basetype=text, stype1=text, initcond1=''); Then group by client and catenate(firstname || ' ' || lastname) You'll want to read the CREATE AGGREGATE page in the reference manual, replace textcat with your own routine that adds a comma and you'll need a finalisation routine to strip the final trailing comma. Note that this is probably not a good idea - the ordering of the contacts will not be well-defined. When I asked about this Tom Lane was quite surprised that it worked, so no guarantees about long-term suitability. - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Poor document!!
Noodle wrote: > > I cannot believe that PostgreSQL has a so poor document, I cannot found any > useful information. I afraid I have to select MySQL. > > Does anybody know if PostgreSQL support Unicode and Full-Text Index? I > couldn't found any information about these from http://www.postgresql.org! Try typing "Unicode" into the search page and press the "Search" button. If you find this too difficult then another system might well be more suitable for you. The administrator's manual has a whole section on multibyte support. See the contrib/ directory for details on full-text indexing (fti). - Richard Huxton ---(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] my pgsql error?
From: "Christopher Sawtell" <[EMAIL PROTECTED]> > Please could a kind soul help me with this. > I can't find _any_ - however cryptic - docs about plpgsql. You need to look in the User's guide Chapter 9 (in the pre-release docs anyway) > create function nice_date(date) returns varchar as ' > declare > t alias for $1; > d text; > m text; > y text; > begin > day := rtrim(to_char(\'t\'::timestamp, \'Day\')); > month := rtrim(to_char(\'t\'::timestamp, \'DD Month\')); > year := rtrim(to_char(\'t\'::timestamp, \'\' )); > nd := d || m || y; > end; > return nd; > end;' language 'plpgsql'; Two "end;" lines - remove the first to fix the error you are getting. Also - you define d,m,y and use day,month,year You don't define nd The to_char lines you are using will try and convert the literal string 't' to a timestamp. You will want some spaces in the nd value. So, your script will become: create function nice_date(date) returns varchar as ' declare t alias for $1; d text; m text; y text; nd text; begin d := rtrim(to_char(t::timestamp, \'Day\')); m := rtrim(to_char(t::timestamp, \'DD Month\')); y := rtrim(to_char(t::timestamp, \'\' )); nd := d || \' \' || m || \' \' || y; return nd; end;' language 'plpgsql'; Note to readers: this is not a general service, I'm in a good mood ;-) For a moment I thought you could do to_char(now(),'Day DD Month ' but you're quite right you need to rtrim() the various pieces. - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] copy a record from one table to another (archive)
- Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, March 13, 2001 12:45 PM Subject: [SQL] copy a record from one table to another (archive) > Hello there > > Is it possible to move a record from one table to another to do a archive > with a simple command like move ? > begin; insert into archive_foo (select * from foo where foo_id=1); delete from foo where foo_id=1; commit; Is probably the closest you could get. Alternatively, you could wrap the above up in a function and just go: select do_archive_foo(1); - Richard Huxton ---(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] my pgsql error?
Christopher Sawtell <[EMAIL PROTECTED]> said: > On Wed, 14 Mar 2001 01:38, Richard Huxton wrote: > > From: "Christopher Sawtell" <[EMAIL PROTECTED]> > > > > > Please could a kind soul help me with this. > > [ ... ] > > > Note to readers: this is not a general service, I'm in a good mood ;-) > In that case, thank you very much indeed, and may the blessings of > the Deities be on you. > > > For a moment I thought you could do to_char(now(),'Day DD Month ' > > but you're quite right you need to rtrim() the various pieces. > > Personally I think the fact that you have to do all that rtrim() stuff is > very close to being a bug. What do you think? > It certainly comes under the headings of "unexpected" and "making more work for me" but I seem to remember it's supposed to be compatible with Oracle so it might be something Oracle does. - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL]
From: "Alexaki Sofia" <[EMAIL PROTECTED]> > I execute the following query > select * from test1 where fromuri like 'http://ww%'; > > I get the following message > NOTICE: PortalHeapMemoryFree: 0x16563a8 not in alloc set! > ERROR: AllocSetFree: cannot find block containing chunk It's complaining that memory it tries to free hasn't been allocated. Something has got mangled here. I'd take a backup of the table drop the index and recreate it, see if that helps. Also - what version of Postgres is this? One of the developers might recognise a known bug if it isn't current. - Richard Huxton > This is a bit strange taking into account that the total size > of database buffers I have defined is 80Mb > > Thanks in advance for your help > Sofia Alexaki > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] PL/pgSQL "compilation error"
From: "Tim Perdue" <[EMAIL PROTECTED]> > Hello all - I apologize for the newbie-esque question, but the debug output > from postgres when you have a bug in your PL/pgSQL procedure is none to > detailed. > > I've created the following procedure and am getting an error when I try to > update the table. The error is something like "parse error near ; on line 50". > Line 50 is the last line. You're writing 50 line functions and you're calling yourself a newbie? Hate to think what that makes me. > There's probably something glaring wrong in here that I'm not seeing, but any > help would be appreciated. Nothing huge leaps out at me except for the \ and the occasional lower-case 'new' - don't know if 'new' is the same as 'NEW' - not sure I've tried it. I have used statements on multiple lines without the \ though, so it might be worth snipping them and seeing what happens. > I don't know if the \ at the end of the line is a problem, but those were > added late in the game and didn't change the error message ;-) [snip] > UPDATE artifact_counts_agg SET open_count=open_count-1 \ >WHERE group_artifact_id=new.group_artifact_id; Failing that, email me the definition of artifacts_count_agg and artifact and I'll play with it here. - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] need to join successive log entries into one
George Young wrote: > > On Wed, 14 Mar 2001, you wrote: > > On 3/14/01, 5:24:12 PM, George Young <[EMAIL PROTECTED]> wrote regarding [SQL] > > I need to join successive log entries into one: > > > I have a table like: > > > > > run | seq | start| done > > > 1415|261| 2001-01-29 12:36:55| 2001-02-07 13:02:38 > > > 1415|263| 2001-02-14 07:40:04| 2001-02-15 16:05:04 > > > 1415|264| 2001-02-16 16:05:05| 2001-03-08 16:34:03 > > > 1415|265| 2001-03-08 16:34:04| > > > > Try: > > > > select run,min(start),max(done) from mytable group by run; > > Alas, this combines *all* entries for a given run, not just those that > are imediately adjacent (in time, or by 'seq' number)... I thought it was complicated, then I thought it was easy. Looks like I was right first time. I was thinking that some huge self-join might do it, but I can't see how to go beyond a run of two adjacent entries. The only thing I can think of is to add a "batch" column and build a trigger to set it as data is inserted. I'm assuming the entries are put in one at a time and in order. That way you just need to look at the last entry to determine if the new one is in the same batch. Any use? - Richard Huxton > -- > George Young, Rm. L-204[EMAIL PROTECTED] > MIT Lincoln Laboratory > 244 Wood St. > Lexington, Massachusetts 02420-9108(781) 981-2756 ---(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: Help with UPDATE syntax
From: "Michael Davis" <[EMAIL PROTECTED]>
> Try eliminating the statement " from user_group_map map". It does not
belong in the update. Here is the fully rewritten statement:
>
> update
> user_group_map
> set
> user_id = 4
> where
> user_id = 9 and
> not exists (
> select * from
>user_group_map ug2
> where
> user_id = 4 and
> ug2.group_id = map.group_id and
^^^
> ug2.role = map.role);
^^^
I take it these are actually "user_group_map"?
- Richard Huxton
---(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 question
postgresql wrote: > > I have a table that I want to add a serial type column. Is there a way > to add it or do I have to create a new table and insert into it. I have > experimented with: > > insert into newdb (name) select name from olddb order by jobno; > > however, pg does not allow the 'order by' during an insert/select Doesn't make sense on an insert - you want an alter table followed by an update. An insert will add new rows, not just add values to a column. > I am on version 7.0.3 > > Maybe I don't need to do this. What I am trying to accomplish is to > have PG create job numbers. Today, I only have 2 workstations that > push jobs into the server and PG tracks the job number. However, I > have been informed that in the next 6-8 months the number of job > creation workstations will grow to 8 - 10. A serial type is basically nothing more than a sequence with a column that uses the sequence as a default value. There's more on this in the docs (a couple of lines in the notes I wrote off techdocs.postgresql.org) but the simplest way to see how it works is to define a table foo with a serial in it and do a \d or pg_dump it to see how it works. > I would like to migrate to a job number created when the insert is > done. Once you see how it works, pg_dump the database, edit the file and re-import the data. Nice clean solution and easy to cope with if something goes wrong. - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] creating "job numbers"
From: "postgresql" <[EMAIL PROTECTED]>
> In my current setup I have only one workstation that is actually
> inputting new jobs. So, I took the expedient way to create the job
> number. Ask PG to count the rows, add a magic number and insert
> this data. This all happens in one connection. What are the odds of
> two people hitting the db at the same time? In the current set up nil.
> There is only one entry computer. I want to change the system to use
> a job number generated by PG. I created a test table and I am
> playing with inserting and the sequence function works great.
> However, I am at a loss of how to pick up this next (last) job. I have
> read the docs and I still am confused. I can not first ask with the
> number will be, and asking for the previous oid after the fact can
> also lead to the same problem. so that leaves me with, 1 ask for
> that last oid from this workstation ip, or 2 since a job is inserted with
> data, I could do a select of this data after the insert (not very
elegant).
I wouldn't use oid's for this - create a jobnum field and use a sequence.
Sequences are smarter than you think, use:
select currval('mysequence') to get the current value and
select nextval('mysequence') to get the next value *for this backend*
So - each client will be guaranteed a unique number. Note that if you "use
up" a number and e.g. an insert fails there will be gaps in your numbering.
Also check out the SERIAL data-type which can provide automatic numbering
for the fields.
I'm sure there are examples in Bruce's book (there's a link on
www.postgresql.org)
- Richard Huxton
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL] how to build this string ?
From: <[EMAIL PROTECTED]> > Hello there Hello again Juerg - I take it you got that monster query working after? > Is it possible (and I think it is) to do the following : > > I have a table with diameters and types. I need to build a comma separated > string. > > typ diam > 01800 > 01840 > 01870 > 011120 > > select diam from zylinder where typ='01' > > should produce the string "800,840,870,1120" You'll want to build yourself a custom aggregate function. Check the mail archives for someone else who did this recently (in the last month or so, Tom Lane was involved in the discussion too). I forget the fella's name, but he should have almost exactly what you want. You can then do something like: select typ, commify(diam) from zylinder group by typ; - Richard Huxton ---(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] Serials.
Grant wrote: > > Please see below for my table schema. I have two questions. > > (1) Why is a sequence limited to 2147483647, it seems very small? That's 2 billion(ish) - the largest signed 32 bit integer. > (2) If I reset the sequence, then try another insert. It will not insert > anything until it cycles through all sequences and finds an unused one. It > will give the following error each time it tries to insert a row with a > used sequence: > > PostgreSQL query failed: ERROR: Cannot insert a duplicate key into unique > index releases_pkey > > How can I possibly get around this issue so that I can be sure to always > have a free id without getting the error above? Well - you've told PG to only allow unique id values and then reset the sequence that generates its values. It's doing pretty much what you'd expect, I'd say. How fast are you inserting these bookings? According to my calculations that's a sustained rate of 68 inserts/sec over a whole year. If you just want unique records, put the primary key over id,added and let the id_seq cycle. If you want more than 2 billion unique id values you'll need to combine the sequence with another value - see my postgresql notes at techdocs.postgresql.org and perhaps look into the plpgsql cookbook (www.brasileiro.net from memory) - Richard Huxton ---(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] Help
From: "Mohamed ebrahim" <[EMAIL PROTECTED]> > Hi, > > I am a user postgresql. I want to update a table > automatically when we reach monthend. i.e i want to > update some table on 31 of every month automatically > without any user attention. I want to know how to do > this. If anyone knows how to do this please mail me. i > will be ever thankfull to him I'm presuming that you are on some kind of unix-like system. If so, check the "cron" system (man cron, man crontab) - use this to run a script at a set time each month - the script can then update your database. This can be as simple as placing a script into /etc/cron.monthly/ on some systems (e.g. Linux Redhat) but in any case is not too complicated. PS - it is usually easier to do this early on the first day of each month (every month has a day 1, not all have a day 31). - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Still don't know how to build this string ?
From: <[EMAIL PROTECTED]> > I have a table with diameters and types. I need to build a comma separated > string. > > typ diam > 01800 [snip] > > select diam from zylinder where typ='01' > > should produce the string "800,840,870,1120" Try the following as a starting point: CREATE FUNCTION comma_join(text,text) RETURNS text AS ' BEGIN IF $1>\'\' AND $2>\'\' THEN RETURN $1 || \',\' || $2; ELSE RETURN $1 || $2; END IF; END; ' LANGUAGE 'plpgsql'; CREATE AGGREGATE joinall ( sfunc = comma_join, basetype = text, stype = text, initcond = '' ); \d dia Table "dia" Attribute | Type | Modifier ---+-+-- typ | integer | diam | integer | SELECT typ,joinall(diam::text) FROM dia GROUP BY typ; typ | joinall -+-------- 1 | 800,840,870,1120 2 | 760,800,900,1200,1234,1352 (2 rows) Note the explicit cast of diam into text. - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Functions and Triggers
From: "Norbert Schollum" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, March 26, 2001 10:24 AM Subject: [SQL] Functions and Triggers > Hello there! > > here is what i want to realize: > > a trigger, that puts a now() in the last_updated field, on any update of > that table. > > i read the manual but i wasnt able to make a working function. > what is the return value here? is there any or is it void? "opaque" - a special value for triggers. > has somebody a example for me that is similary to my problem? Yep - see the postgres notes from techdocs.postgresql.org - I've got an example there of exactly this (it's in the "automating" chapter - sorry, forget the precise URL) Also have a look at the Cookbook (linked to from same place) which might well have more examples. - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] 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]
Re: [SQL] RE: pl/pgsql and returning rows
From: "Bruce Momjian" <[EMAIL PROTECTED]> > 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 The functions called get_details though, so I assumed it's supposed to be shorthand for a join. - Richard > > 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] > ---(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] Escaping \
From: "Martijn van Dijk" <[EMAIL PROTECTED]> > I've a problem with escaping a \ in a string. > > When I enter the query: > > SELECT '''\\\''; I get the right result: '\' > > But when I try this in a Function: > > CREATE FUNCTION sp_tmp() RETURNS varchar(10) > AS ' > SELECT ''\\\' AS RESULT' > LANGUAGE 'sql'; > > I get the following Parse-erros: > > psql:tmp1:4: ERROR: Unterminated quoted string. I don't understand why, > when you leave the SELECT-statement out you get '''\\\'' and that is not > unterminated. Had something similar myself the other day. The reason is that you are already one deep in Postgres' string-parser, so you need something like: SELECT '''' AS RESULT HTH - Richard Huxton ---(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] date_part bug?
"Salvador Mainé" wrote:
>
> Hello:
>
> I'm using postgres 7.0.2. When I use date_part('day', date) sometimes I
> get wrong values. Ie:
> and date_part('day', '1999-3-28')=27
>
> and date_part('day', '2000-3-26')=25
>
> Is it a bug? Is there any SQL equivalent function?
I remember some issues with summertime settings and timezones (I think
the person how originally found the bug was in New Zealand and PG got
confused by a +13 timezone) - don't know if this might affect you or if
it was fixed before 7.0.2
You might want to try the to_char function and see if that displays the
same problem.
- Richard Huxton
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Function x returns opaque in error typeidTypeRelid
Sondaar Roelof wrote: > > Hello, > > I am a bit at a loss here. > If I create a function which returns something it works fine. > If it returns opaque I get the following: ERROR: typeidTypeRelid: Invalid > type - oid = 0 > What am I doing wrong ? > > dhcp=# create function lalala() returns opaque as ' > dhcp=# > dhcp=# select lalala(); > ERROR: typeidTypeRelid: Invalid type - oid = 0 I believe you can only use opaque from triggers - if you call a function with select, it not unreasonably expects to have data returned. In the absence of anything relevent I tend to return either 1 or 'succeeded' or similar. - Richard Huxton ---(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] Trigger Function and Html Output
Stef Telford wrote: > > Hello, > i find i must turn once again to the list for help, in what > is probably another silly request. That's what the list is for, and it's only silly if you already know the answer :-) > The long and short of it is this, I would like the view to > return a html table. I understand that I can do this via a function > and a trigger on select, massage the data, and then construct > each line to simply be returned to the perl program. The only > problem comes with, as far as i am aware, a trigger returns and > is processed on a 'per line' basis for a select, so how would i > get the view's column titles output for the table header ? For this sort of stuff, I like to make as much use of Perl's DBI and HTML templating modules as I can. For the templating, it's trivial to write your own stuff for this sort of problem, but have a look at the various modules on CPAN - there'll be something there for you. I've got to admit, I try to avoid using SELECT * if I can - maybe it's just me, but I've always felt if the database changes the code needs to break. I'd rather get errors than unexpected results. IMHO of course. > I ideally want to make as much of the perl construction > of the table from the database, this includes the head of the table, > all and each row, and then of course closing the table 'off'. I know > this sounds like a strange way to do things, but i think this is the > best way to go. Definitely look at some of the general-purpose templating modules. They'll all handle tables. - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Trigger Function and Html Output
From: "Stef Telford" <[EMAIL PROTECTED]> > Richard Huxton wrote: > > Each select works on a view, rather than hardcode the view into the > perl CGI, i would rather have the table header/column titles returned > as the first item as text/html (i know about the func procedure to get the > table_attributes) and then all the formatting thereafter done by the database > for each row. > > maybe i am naive in thinking this way, but surely the a database function > formatting the returned string must be quicker then perl. (speaking > generically of course, i conceed that there are times when the reverse > is true) Not sure you'd notice much of a speed difference - in most cases other overheads will be the deciding factor. Of course, YMMV. > thank you for the input, and if i was jst starting out i would agree with > you. I cant really explain it any better than i have previously, but > hopefully you will see that i want to use the database to do this. > > hopefully that isnt that strange a request ;) Strange or not, it's your database and you're the only one in a position to make this sort of decision. I did think that it was the psql front-end that did the HTML stuff. But - there seems to be html stuff in fe-print.c (src/interfaces/libpq) and the Perl module Pg has html output options in $result->print(...) which might be flexible enough for your needs. I must admit I've never used it, so I don't know if you can add attributes to table elements, use styles etc. HTH - Richard Huxton ---(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] passing parameters between forms
From: "Sharmad Naik" <[EMAIL PROTECTED]>
> Hi,
> I have created a database with following structure
>
> CREATE TABLE member(
> mem_id INT,
> name TEXT,
> age INT);
>
> the first page is as follows :
>
>
>Member ID :
>
> The second page that is search 2.php is as follows :
>
>
> echo("");
> echo("$mem_id");
Create a hidden input element that will be posted on to the third form:
echo('');
> echo("$name");
> echo("$age");
> echo("");
> // require("search3.php");
> // series($mem_id,$name,$age);
> echo("");
> In my search3.php I want to insert (which i have not given) or atleast
echo the parameter like mem_id, age and name.My Q
> +is how do i pass the parameters between search2.php and search3.php.If
there is another way out pls tell.
> I tried using functions to get the work done.but couldn't get my way out.
This is more of a cgi question really - I'd suggest a quick visit to
www.faqs.org and have a look at the CGI faq.
- Richard Huxton
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Error:TypeCreate: type links already defined
From: "Najm Hashmi" <[EMAIL PROTECTED]> > Hi all, I have posted my question since last Thurday and noone has answered it > yet. My problems is that I droped a table using pgaccess from pg_class. Now > If I try to create that table I get the following error: TypeCreate: type > links already defined. > Could some one help me out here. > Regards Need more information Najm - obivously PG is trying to create a type "links" and failing - do you have anything called "links" in your table definition or the rest of your database? Perhaps look in the pg_types table: select oid,* from pg_types where typname like 'lin%'; Don't go deleting it without knowing what it is though. If your table is called "links" I'd guess it's fine to delete it, but I'd take a backup first anyway. - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Appropriate indices to create for these queries
From: "Gerald Gutierrez" <[EMAIL PROTECTED]> > > I've been looking into indices and which ones to create and I'm getting > myself a little confused. The "PostgreSQL Introduction and Concepts" book > didn't help very much. I wonder if a kind soul can give me some tips. > > SELECT * FROM T1 WHERE a=1 and b='hello'; > > Is the appropriate index for this query: > > CREATE INDEX ndx ON T1 (a, b) ? Maybe - you seem to have got to the core of the matter below... > When I was testing, it seemed that even if I created the index with only > "a", EXPLAIN told me that it would just do an index scan, seemingly > indicating that it didn't matter whether I had an "a" index, or an "a, b" > index. For the above query, any of : index on "a" , "b", "a,b" will probably be used (if you have enough data to justify it). > How about for WHERE a=1 or b='hello' and other more complex forms? Is there > documentation that describes a variety of different queries and what kind of > indices are best? In this case, an index on "a,b" isn't much use since the b='hello' values are presumably scattered amongst all the various 'a' values. In practice, unless you do a lot of a=1 and b="hello" queries you're probably better off with separate indexes on a and b, or possibly even just on one of them. I tend to apply indexes to fields that take part in a join then add them one at a time to other fields as it becomes clear which takes part in important queries. Don't forget that it takes a certain amount of effort to maintain an index. You've already found the EXPLAIN command - this is your best guide to where an index can be useful. - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] passing null parameter to plpgsq functions
From: "Picard, Cyril" <[EMAIL PROTECTED]> > Hello > I wrote a plpgsql function with few parameters ; when I call the function, > some of the parameters can be null. > In this case, all the parameters are considered as null in the function's > body ! is it a feature ? how can I work around this ? It's a feature in versions before 7.1 (or possibly 7.0.x) you can still get that behaviour in 7.1 by asking for strict null handling on a specific function. The only remedy AFAIK is to upgrade. - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] passing null parameter to plpgsq functions
From: "Picard, Cyril" <[EMAIL PROTECTED]> > Thank you ! I planned to use the 7.1 to get the outer join capability. > > Is the 7.1 stable ? Pretty much - it's reached release candidate stage and I've been using it for development for a while now. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Memory and performance
[EMAIL PROTECTED] wrote: > > Hi all, > > I have noted that Postgresql don't make a good memory handle. I have > made the tables/procedure (in attached file) and run it as "select bench(10, > 5000)". This will give a 5 records inserts (5 x 1). (well, I run it > on a P200+64MB of RAM, under Linux, and Postgres 7.0.2. In a more powerfull > machine, you can try other values). That's 50,000 inserts in one transaction - have you tried 50 transactions of 1000 inserts? > I get as result, the following times: > 5 | group 5 | 00:02:08 > > Note that, with memory increse, the system becomes slow, even if the > system has free memory to alocate (yes, 64MB is enough to this test). I > didn't see the source code (yet), but I think that the data estructure used > to keep the changed records is a kind of chained list; and to insert a new > item, you have to walk to the end of this list. Can it be otimized? I don't fancy your chances before 7.1 ;-) > The system that I'm developing, I have about 25000 (persons) x 8 (exams) > x 15 (answers per exam) = 300 records to process and it is VERY SLOW. If you need to import large quantities of data, look at the copy command, that tends to be faster. - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Index on View ?
From: "Keith Gray" <[EMAIL PROTECTED]> > Is it possible (feasible) to create an index on a view. > > We have a large table and a defined sub-set (view) > from this table, would it be possible to keep an index > of the sub-set. I don't think so - the view is basically just a select rule that rewrites queries based on it. Indexes on underlying tables should be used though. Difficult to suggest what indices you might need without knowing the view/tables/queries involved. - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Index on View ?
From: "Keith Gray" <[EMAIL PROTECTED]> > Richard Huxton wrote: > > > > Indexes on underlying tables should be used though. Difficult to suggest > > what indices you might need without knowing the view/tables/queries > > involved. > > As an example I may have an "Invoice" table with several thousand invoices. > I could design a query/view "Aged" to get all unpaid invoices > greater than 15 days old. > > I would often look for Invoices per Client and should have an index on > Invoice(ClientID). > > e.g. CREATE INDEX Invoice_ClientID ON Invoice(ClientID); OK - makes sense. > > Is there any advantage in having an index on ClientID for the Aged query? > > e.g. CREATE INDEX Aged_ClientID ON Aged(ClientID); > > Would this index be continually maintained by the RDBMS or only on lookup? You can't do this at all I'm afraid. You can only index actual data. In this case you already have an index on clientID so you're covered. For your 15-day query, if it looks something like: SELECT * FROM invoices WHERE status='UNPAID' AND inv_date < CURRENT_DATE-'15 days' you might want indexes on inv_date and status. This doesn't mean that they will definitely be used though - it depends on how many records you have and how many the query returns. It *is* possible to define an index on a function, so you could in theory write a quite_old(inv_date) function and index that, but I doubt it makes much sense in your case. - Richard Huxton ---(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] Subqueries in select clause
Sara Cohen <[EMAIL PROTECTED]> said: > Hi, > > I am attempting to use subqueries in the select clause of a query > and am encountering difficulties. > > The Problem: > > > I would like to use a subquery that returns one column, but more than one > tuple. The result of this subquery is then used in an aggregate function. > Using Oracle, I could get by this problem with: > > select max(d) from ((select count(b) as d from c group by a)); > > However, my version of postgres doesn't support subqueries in the from > clause. In 7.1 at least you can do it if you alias the sub-query: select max(d) from (select count(b) as d from c group by a) as calias; Sorry, I don't know if 7.0.2 handles this (but you probably want to upgrade anyway - I seem to remember some problems with 7.0.2) HTH - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] groups in postgres
Martín Marqués <[EMAIL PROTECTED]> said: > I building an application that will load data on a database by lots of > people. So I thought about putting them in a group and giving priviledges to > the group. > > The problem is the the information on the main table is related with fields > of other tables, and depending on how it's related, I want some users to be > able to modify the data and I don't want those users to insert new data if > the relation is right for them. > > Am I clear up to know? > > Now, how can I do this? Is it posible manipulating groups? Or do I have to > play with triggers. Sounds like you want different groups for different users, and a view for each group. You can then define rules for each view defining the access that is available. Is that what you're after? - Richard Huxton ---(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: Cursors in plpgsql
From: "Hans-Jürgen Schönig" <[EMAIL PROTECTED]> > Currently no real cursors are supported - you can build a workaround using > a loop - this works in most cases. > I have found a doc (a very good one) that describes porting from Oracle to > Postgres - accidentally I have lost the link but I remeber that I have You can get to it from http://techdocs.postgresql.org/ - one of Roberto Mello's contributions IIRC - Richard Huxton ---(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] INT8 sequences
Radius Administrator wrote:
>
> Is it true that PostgreSQL sequences are always INT4? If so, how
Yep
> would the following be modified to work as obviously intended?
>
> CREATE SEQUENCE tbl_id INCREMENT 1 START 1;
> CREATE TABLE tbl
> (
> id INT8 DEFAULT NEXTVAL('tbl_id') PRIMARY KEY
> );
Check the mailing list archives for lengthy discussions on int8
sequences. Once you've found it's mostly people complaining without
volunteering to do anything go over to http://techdocs.postgresql.org/
and look at Roberto Mello's plpgsql cookbook and the int8 page in my
postgresql notes - I summarised anything useful I saw about it. The
first example I give is probably fine for just generating unique id's.
- Richard Huxton
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Is function atomic?
From: "Wei Weng" <[EMAIL PROTECTED]> > If it is not, is it possible to acquire a lock on a row ? how about a > lock on a table? All functions take place within a transaction, but since PG doesn't support nested transactions yet you can't roll back the effects of a nested function. - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] view and performance
From: "Wei Weng" <[EMAIL PROTECTED]> > Is there any gain on performance if you choose to create a view over a > very complex query instead of running the query itself directly? I *believe* views are pre-parsed so you gain that time. Can't believe you'd notice much difference either way unless the query was very fast to execute. - Richard Huxton ---(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] Is function atomic?
From: "John Hasler" <[EMAIL PROTECTED]> > Richard Huxton writes: > > All functions take place within a transaction, but since PG doesn't > > support nested transactions yet you can't roll back the effects of a > > nested function. > > Do you have any idea when it will? Check the "todo" list in the developers' area on the website - that'll show what's planned for 7.2 Might be more hands available now though, with the RedHat announcement. - Richard Huxton ---(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] Returning multiple Rows from PL/pgSQL-Function
From: "Alvar Freude" <[EMAIL PROTECTED]> > Hi, > > I want to create a function (PL/pgSQL), which return multiple rows. But it > fails -- when Creating the function, I get a notice: > How should I do this? Can't at the moment. > or, in more detail the exact function: > > >CREATE FUNCTION get_emotions (timestamp) RETURNS SETOF records AS > ' > DECLARE > start ALIAS FOR $1; > end_id int4; > BEGIN > SELECT emotion_id FROM emotions > WHERE date <= start > LIMIT 1 > INTO end_id; Not entirely clear what your function is for, but the above select looks a bit odd. Do you not want to "order by" here so you can get the "most recent" emotion_id or whatever? > RETURN SELECT emotion_id, emotion1, [...] > FROM emotions > WHERE emotion_id BETWEEN end_id-3000 AND end_id > ORDER BY date_epoch + full_rating*(3600*12) > LIMIT 300; > END; > ' > LANGUAGE 'plpgsql'; I'd rewrite this as just a select, or a view if you want to keep things clean in the application, possibly with that first select encapsulated in a function (sorry, I'm not entirely clear what your code is doing). so: CREATE VIEW get_emotions_view AS SELECT emotion_id, emotion1, ... ORDER BY date_epoch + full_rating*3600*12 LIMIT 300; and then issue a query like: SELECT * FROM get_emotions view WHERE emotion_id BETWEEN last_em_id()-3000 AND last_em_id(); If you set the "is_cachable" flag on the last_em_id() function it should only be calculated once. HTH - Richard Huxton ---(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] Returning multiple Rows from PL/pgSQL-Function
From: "Alvar Freude" <[EMAIL PROTECTED]> > For now i do the hole stuff on client side with two selects: > First selecting the end_id, then (2. Statement) sort the stuff within > end_id and end_id-3000 and return the 300 most "best". > > > my $end_id = $self->db_h->selectrow_array( > "SELECT emotion_id > FROM emotions >WHERE date <= ? >ORDER BY date DESC >LIMIT 1", >undef, >$self->date_from_sliderpos($params[0])); > > my $st_h = $self->db_h->prepare( > " > SELECT emotion_id, emotion1, ..., full_rating, date > FROM emotions > WHERE emotion_id BETWEEN ? AND ? > ORDER BY date_epoch + full_rating*(3600*12) > LIMIT 300 > "); > > $st_h->execute($end_id-3000, $end_id) or die "execute kaputt"; So - basically you want something like: SELECT * from emotions WHERE emotion_date <= [cutoff time] ORDER BY calculated_score(date_epoch,full_rating) LIMIT 300 Where you'd have an index on "calculated_score". Well - you can either have a "score" field and use triggers to keep it up to date or build an index on the "calculated_score()" function. Depends on your pattern of usage which is going to be better for you. You can create a functional index as easily as a normal one: CREATE FUNCTION calculated_score(integer, integer) RETURNS integer AS ' BEGIN RETURN $1 + ($2 + 3600 + 12) END; ' LANGUAGE 'plpgsql'; CREATE INDEX emot_calc_idx ON emotions ( calculated_score(date_epoch, full_rating) ); If you've never used triggers before, there is a section in the docs and also some examples at techdocs.postgresql.org Is that the sort of thing you were after? - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Problems with PG_DUMP and restore
From: "Josh Berkus" <[EMAIL PROTECTED]> > Folks, > > In an effort to do some general cleanup in my database functions, I > dumped the schema (pgdump -s) and the data (pgdump -a) to seperate text > files. > > I editied and restored the schema fine. However, when I attemped to > restore the data (via \i filename), it failed selectively; some tables > were restored but many were not. No errors were logged. Try having a look at the order the tables get inserted esp. with regard to any foreign keys etc. - I'm not sure pgdump is that clever about such things. - Richard Huxton ---(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] Problems with PG_DUMP and restore
From: "Josh Berkus" <[EMAIL PROTECTED]> > Richard, > > Try having a look at the order the tables get inserted esp. with > > regard to > > any foreign keys etc. - I'm not sure pgdump is that clever about such > > things. > > Thanks. I did try that; however: > 1. Even trying re-loading the tables twice did not work, as it should > have with missing foriegn keys. > 2. I did not see any Unmatched Reference errors in the log, as I > typically do with missing keys. > > Further, I *was* able to re-load the database from a unitary pg_dump > file ... one that includes both DDL and data. It's just when I split > the pg_dump into two files -- one for DDL, one for data -- that it > fails. > > BTW, I'm using 7.1RC2 Was there not some fix to pgdump in 7.1.1? Yep - pg_dump fixes (Philip) pg_dump can dump 7.0 databases (Philip) Might be worth a quick upgrade & see what happens. Failing that, it isn't something to do with permissions and pgdump connecting as various users? - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] SQL - histogram
From: "Txugo" <[EMAIL PROTECTED]> > I have a table where one record represent a person, including his height. > I'd like to know how many person have more than 150 cm, more than 160 cm > and so on. > How can I do that using SQL? > > Example: > people > 150 - 1000 >> 160 - 850 >> 170 - 500 >> 180 - 200 >> 190 - 30 > thanks in advance richardh=> select * from people; id | height + 1 |150 2 |155 3 |160 4 |165 (4 rows) richardh=> select * from heights; cm - 150 160 (2 rows) richardh=> select cm,count(id) from people, heights where height>=cm group by cm; cm | count -+--- 150 | 4 160 | 2 (2 rows) HTH - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Group by date_part
From: "Roberto Mello" <[EMAIL PROTECTED]>
> On Tue, Jul 10, 2001 at 08:04:55PM +0100, Graham Vickrage wrote:
> >
> > The statement I have only selects the count if there is at least 1 order
for
> > a particular day, which make sense.
> >
> > I however need a count of 0 for days that don't have any. Can anyone
help?
> >
> > SQL:
> >
> > SELECT date_part('day', date), count(*)
> > FROM client_order WHERE (date >= '01/05/01' AND date < '01/06/01') AND
> > status = 'Processing'
> > GROUP BY date_part('day', date);
>
> Didn't look very hard, but can't you just add a:
>
> CASE WHEN count(*) > 0 THEN count(*) ELSE 0 END AS count
Don't think so - this one always catches me out, and I have to figure out
how to solve it each time. The problem is that if there aren't any records
with (eg) date = 2001-05-16 then there is simply nothing to return.
I've only ever come up with two solutions:
1. Create a temporary table with all the dates required and a total field
initialized to zero. Update the totals as desired then just read from the
table.
2. Create a temporary table with the dates and join against it. Then you can
use a CASE construct as above to get a zero.
Which one I select depends on usage patterns. If the dates don't change much
(e.g. weekending dates) then I'll use #2, otherwise #1.
I'd be very interested in any clever way of doing this without a temporary
table (or equivalent - if functions could return sets of values you could
use that, but it's basically the same thing).
- Richard Huxton
---(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] "Display of specified number of records."
From: "rajesh" <[EMAIL PROTECTED]> > Hi, > I have got following simple SQL. > Select TestID from test where testname = ' ' order by testdate. > > Suppose for argument sake there are 100 records and testID's are 1 > to 100. > Is it possible to modify this SQL so that it will display records > from 10 to 50 and not any other records. Robby Slaughter has given one solution in another reply. If that's not quite what you're after, and you want the 10th to 50th results from the above query you can do: ... order by testdate limit 40 offset 10; This calculates the results and then throws away the first nine and anything after the 50th. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Date Validation?
From: "Josh Berkus" <[EMAIL PROTECTED]> > Folks, > > Pardon me for asking this again, but I received *no* responses last > week. > > Question: Is there any function or method which will allow me, in SQL > or PL/pgSQL, to validate dates without raising an error for invalid > dates? > > Expansion: I have a number of PL/pgSQL functions which must accept > dates as parameters from the PHP4 interface. There is, however, the > possibility that an invalid date will be passed, such as "2001-02-31" > due to the nature of HTML forms date selector. Out of curiosity Josh, why aren't you validating in PHP? - only takes a couple of lines there. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] ANNOUNCE: Updated PostgreSQL Notes
Finally got my act together and updated my PostgreSQL Notes. You can access them from http://techdocs.postgresql.org (another plug for Justin ;-) Changes include: - downloadable zipped version - short example of accessing PostgreSQL from PHP - short example of accessing PostgreSQL from Perl (Pg & DBI) - typos corrected etc. If you do download it, please take a minute to send some feedback, coz I can't tell which pages are useful without it. - Richard Huxton ---(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] Hey! ORDER BY in VIEWS?
Josh Berkus wrote: > > Pater, Robbie, Bruce, > > Makes sense. I take it that this is a deviation from the ANSI 92 > standard, then? > > What happens if I put an ORDER BY in a view, then call an ORDER BY in a > query, e.g.: > Does the second ORDER BY override or suppliment the view ORDER BY, or is > it ignored? It overrides. People seem to be forgetting ORDER BY ... LIMIT has selective qualities as well as ordering ones. The example someone gave me was when you use LIMIT ... OFFSET to fetch results a page at a time. If you want the last page of your results you need to do something like: SELECT * FROM messages ORDER BY msg_timestamp DESC LIMIT 20; 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. - Richard Huxton ---(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] ERROR: UNIQUE constraint matching given keys for referenced table "sequences" not found
From: "Dado Feigenblatt" <[EMAIL PROTECTED]> > I'm trying to create some tables with foreign keys. > When I try to create a foreign key ... > > foreign key(seq_code) references sequences(seq_code) on update > CASCADE on delete CASCADE, > > I get this message: > > ERROR: UNIQUE constraint matching given keys for referenced table > "sequences" not found > > The problem is that the referenced field and table exist. > Any hint? Do you have a unique index on sequences.seq_code? - Richard Huxton ---(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
From: "Gary Stainburn" <[EMAIL PROTECTED]>
> Hi all, I've just written my first pl/pgsql function (code included below
for
> you to pull apart).
Looks fine to me. Try it with "SELECT INTO" etc rather than "select into"
and see if you prefer it - I find it makes the variables/fields stand out
better.
> It takes an int4 mid (e.g. 15) and then using a select pulls out the team
> number (e.g. 'NE/012' and a unit number (e.g. 2) and returns the full unit
> number NE/012-02.
>
> I now want to write the reverse function, where I can enter 'NE/012-02'
and
> get back the mid 15. The bit I'm stuck on is now I split the team part
from
> the member part so that I can build the select statement.
Use the substr() function. Since you are careful to turn member-numbers into
2-digits you shouldn't need anything more complex.
richardh=> select substr('abcdefghijkl',2,3);
substr
bcd
(1 row)
So, something like
teamnum := substr(idstring,1,6);
membnum := substr(idstring,7,2);
This can get you your team/member which you can query to get your "mid". If
you aren't enforcing uniqueness on (tnumber,mnumber) now might be a good
time to do so.
If the teamnum isn't always a fixed length search for the '-' with strpos()
richardh=> select strpos('abcdefg','e');
strpos
5
HTH
- Richard Huxton
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] pl/pgsql - code review + question
From: "Gary Stainburn" <[EMAIL PROTECTED]> > My problem now is that when I include the code to handle the record not being > there, from the pgsql chapter (section 23.2.3.3) I get the following errors Hey - stop that! If everyone starts reading the docs and quoting chapter refs all we'll be left with are *difficult* questions ;-) > based of the function below. Can anyone explain why the concat of the string > is failing. If I simply "raise exception ''member not found''" all works fine. Yep - this one keeps getting me too. > raise exception ''Member '' || unitno || '' not found''; RAISE EXCEPTION ''Member % not found'', unitno; Don't know why the parser for RAISE doesn't like string concat. Possibly because it maps to the elog() error-reporting function underneath. - Richard Huxton ---(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
From: "Tom Lane" <[EMAIL PROTECTED]> > "Richard Huxton" <[EMAIL PROTECTED]> writes: > > Don't know why the parser for RAISE doesn't like string concat. > > Laziness ;-). Someone should fix plpgsql so that RAISE does take > expressions, not just literals and simple variables. It probably > wouldn't be a big change, but I've no time to look at it myself; > any volunteers out there? False Laziness perhaps (spot my Perl background) OK - stick me down for having a look at it. Had a quick peek and I think it's within my abilities. Give me a couple of weeks, because I haven't looked at a YACC file since my university days (gram.y is YACC isn't it?) I'll read up the rules for submitting patches and get something workable back by August 3rd. 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? - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] pl/pgsql - code review + question
From: "Richard Huxton" <[EMAIL PROTECTED]> > False Laziness perhaps (spot my Perl background) > > OK - stick me down for having a look at it. Had a quick peek and I think > it's within my abilities. Give me a couple of weeks, because I haven't > looked at a YACC file since my university days (gram.y is YACC isn't it?) > > I'll read up the rules for submitting patches and get something workable > back by August 3rd. > > 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? What am I talking about - OFFSET is going to be parsed by the SQL parser not the plpgsql parser. Ignore me I'm blathering - Richard Huxton ---(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
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
Re: [SQL] Re: Records exactly the same.
"Fons Rave" <[EMAIL PROTECTED]> wrote in message 9jbrpj$r67$[EMAIL PROTECTED]">news:9jbrpj$r67$[EMAIL PROTECTED]... > > Well, there isn't an easy answer for you ... because you've designed > > your database wrong. Records should *never* be the same. That is, ni > > fact, one of the cardinal rules of Relational Database Design. > > Well, I started with "I'm a beginner". But I'm sure there's no reason NOT to > accept two records that are exactly the same. In the example I gave, it is clear > that the information I want to store can contain two records that are exactly > the same; doing the same thing, on the same day, for the same amount of time. In > this case it is the technical structure that doesn't want it like that. So I > have to change it to make it work. OK - so you enter two records, one hour in the morning and one hour in the afternoon. You then realise you *didn't* work in the afternoon and you want to delete one of the records. You can't because you can't build a query that specifies the one without the other. You can't delete only one, you can't update only one and you can't select only one. This is because the only way to pick out a piece of data in SQL is by it's values - there is no "file position" or "record number" (well there's OID but you're best off leaving that). If you want to store two separate pieces of information, make sure they differ in some way (timestamp, serial-number, whatever). If you can't think of anything useful to distinguish between records add a SERIAL column called "id". If you don't *want* two records, but just want the total hours worked that day, then restrict your system to one record per person (or job or whatever it was) per day. You can either do this in your application, or (better but harder) do it with triggers in PostgreSQL itself. If you can't quite see where the problem is at the present time, just add an "id" or "seq" field of type SERIAL and get on with your project. The reason people on the list are shouting is because we got burnt at some time with exactly this thing and we're trying to stop that happening to you. Oh - check the glossary at techdocs.postgresql.org and look at "Normal Forms" (numbered 1st NF, 2nd NF etc). Check out a book on RDBMS theory too if you get a chance. HTH - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] How to get the server version??
From: "Roberto João Lopes Garcia" <[EMAIL PROTECTED]> > Is there any way, possible an SQL or pgsql command, to get the server version? select version(); - Richard Huxton ---(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] Get name of columns in a table
María Elena Hernández wrote: > To get a list of columns, run "psql -E" and do a standard "\df foo" where foo is your table name. This will show you exactly how PG does it. I've found this very useful with all the backslash commands. HTH - Richard Huxton ---(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] Converting epoch to timestamp?
From: "Roberto Mello" <[EMAIL PROTECTED]>
Hi Roberto - long time no see.
> I searched the docs for function to convert epoch to timestamps but
> couldn't find any. Are there any?
richardh=> select now();
now
2001-08-01 09:25:58+01
(1 row)
richardh=> select extract('epoch' from now());
date_part
---
996654359
(1 row)
richardh=> select '1970-01-01'::date + '996654342 seconds'::interval;
?column?
2001-08-01 08:25:42+01
(1 row)
That's the only way I've ever known to do it. Note the one-hour offset
because I'm currently in BST rather than GMT timezone (ignore the few
seconds discrepancy - that's me querying then cutting and pasting).
Be interested to find out if there's a neater way. Can't believe there's not
some EPOCH_BASE constant that could be used.
- Richard Huxton
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] where'd the spaces come from
From: "Bruce Momjian" <[EMAIL PROTECTED]> > > 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? Well - it's certainly *unexpected* behaviour isn't it? It is documented though (Karel Zak's given plenty of examples too): "FM suppresses leading zeroes or trailing blanks that would otherwise be added to make the output of a pattern be fixed-width" Some of the examples show the difference too: to_char(12,'9990999.9') => ' 0012.0' to_char(12,'FM9990999.9') => '0012' I think the issue is you look at to_char() and make assumptions if you're not familiar with it. I *seem* to remember someone saying Oracle worked this way. - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Tagging rows into collections?
On Wednesday 19 Jun 2002 10:19 pm, Tom Lane wrote: > Steve Wampler <[EMAIL PROTECTED]> writes: > > An event has: timestamp,event_name,list_of_attributes > >The list_of_attributes are simple (string) name,value pairs. > > > > However, although selection performance isn't a priority, the > > ability to reconstruct the events from the database is needed > > and the above simple table doesn't provide enough information > > to do so. (The resolution on the timestamp field isn't > > enough to distinquish separate events that have the same name.) > > What PG version are you using? In 7.2 the default timestamp resolution > is microseconds, rather than seconds. That might be enough to fix your > problem. Still doesn't *guarantee* uniqueness though, just makes it less likely. > If not, your two-table approach sounds reasonable. You could stick > with one table and use arrays for the name/value columns, but that > will make searches harder. How about using a sequence to generate unique numbers for you? Looks like a SERIAL type won't be much use, but a sequence can used without tying it to a field. One thing to be careful of - if you have multiple clients inserting then the numbers won't necessarily be in order. That is, client 1 might insert 10,11,12,13 and client 2 20,21,22,23 but in time-order they might be 10,11,20,12,22,23,13. This is because each client will get a batch of numbers to use (for efficiency reasons). Be aware that I'm not 100% certain on that last sentence. - Richard Huxton ---(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] CHECK clause doesn't work with CASE clause
On Thursday 27 Jun 2002 10:52 am, Jörg Holetschek wrote:
> Hi folks,
>
> I have a problem with a CHECK clause that doesn't seem to work properly.
> WHEN ((focus <> NULL) AND (epilepsy_class = 'f')) THEN
> WHEN ((focus <> NULL) AND (epilepsy_class IN ('g',
> It shouldn't be possible to insert a value into focus when epilepsy_class
> has one of the values 'g' or 'n'. But it is. Can anyone help?
Should that not be "IS NOT NULL"?
- Richard Huxton
---(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] newbie question
On Sunday 07 Jul 2002 10:59 pm, Mirco D'Angelo wrote: > Hi > > I am going to learn MySql 'cause I have to, but would it be better, or > let's say, more interesting, to learn postgressql? Is it newer, more > common, etc.? Mysql is more common on web-hosting platforms, but you can find PostgreSQL if you look. PostgreSQL's ancestors go back further than MySQL - both get updated regularly, so "newer" doesn't mean much. Mysql is easier to setup initially, but both systems require knowledge to tune properly. Both have a lot of online documentation - I'm not sure that one is better than the other. The mailing lists for PostgreSQL are very helpful and contain some very experienced people. PostgreSQL offers some more advanced features. Once you are used to these features it is irritating to find them missing in MySql. For some applications they are pretty much mandatory. PostgreSQL also tends to fit standards more than MySQL. Where I use MySQL behind a website I tend to use PostgreSQL to manage the data here during development. I'd recommend installing both if you can. Try to make sure anything you write works on both and check the documentation for where both diverge from standards. - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Error with DISTINCT and AS keywords
On Wednesday 10 Jul 2002 1:25 pm, Achilleus Mantzios wrote: > On Wed, 10 Jul 2002, Andreas Schlegel wrote: > > select DISTINCT tnr, titel, 'TEST' AS testcol from tTitel; > > ERROR: Unable to identify an ordering operator '<' for type 'unknown' > > Use an explicit ordering operator or modify the query > > Do it as > > select DISTINCT tnr, titel, 'TEST'::text AS testcol from tTitel; Does anyone know what type a quoted literal has? The error message reports "unknown" - is that because it may be a date? It does strike me as a little unexpected that a quoted string doesn't default to text. - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How do I concatenate row-wise instead of column-wise?
On Tuesday 16 Jul 2002 9:09 am, Marcus Claesson wrote: > I have a table like this: > SELECT * FROM old_tab; > id|descr > --- > 3|ddd > 3|ddd > 3|eee > 3|fff > SELECT * FROM new_tab; > id|descr > -- > 1|aaa;bbb > 2|ccc;bbb > 3|ddd;eee;fff > 4|bbb You'll want to write your own aggregate function - something like max() which will work over a range of values. This is easier than you might think. The only thing to be careful of is that order isn't guaranteed, so by default you could have "ddd;eee;fff" or "fff;ddd;eee" etc. Go to techdocs.postgresql.org and check the "Postgresql Cookbook" courtesy of Roberto Mello and also the "Postgresql Notes" by me. Also check the online manual and the mailing archives (try searching on "aggregate" and "catenate" or "concat"). HTH - Richard Huxton ---(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] Indexing UNIONs
On Tuesday 16 Jul 2002 11:42 pm, Josh Berkus wrote: > OK, I guess I'll have to get into detail: > [detail on cases and trial-groups] > > Thus, I need to relate (in views and queries) each Event to the Union of > Cases and Trial Groups. I just can't figure out how to do so without the > database discarding the indexes on Cases in the process and things slowing > to a crawl. Well, if there was some commonality between cases and trial groups you'd have noticed it. How about two event tables, one for each type of schedulable event and unioning those? Of course, that's just shuffling the complexity around since you'll need a view with the relevant rewrites and possibly some way of detecting scheduling conflicts? - Richard Huxton ---(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] Newbie: Creative use of LIMIT??
On Wednesday 17 Jul 2002 3:20 am, Shmuel A. Kahn wrote: > Assuming I have the following two tables of people and their nicknames, > and that I want to create a list containing UPTO 2 (or any value > greater than 1) nicknames for EACH member of a specific family (Fam > column), how would I do this? > > but am totally clueless on how to impose a limit on the number of rows > to get for each individual family MEMBER. Something along these lines should be what you're after: SELECT * FROM nicknames; id | person_id | nick +---+-- 1 | 1 | Darth 2 | 1 | Lord Vader 3 | 1 | Mr Black-hat 4 | 2 | Luke 5 | 2 | Metal-hand 6 | 2 | Bad-hair boy (6 rows) SELECT * FROM nicknames n1 WHERE n1.id IN ( SELECT n2.id FROM nicknames n2 WHERE n2.person_id=n1.person_id ORDER BY id LIMIT 2 ); id | person_id |nick +---+ 1 | 1 | Darth 2 | 1 | Lord Vader 4 | 2 | Luke 5 | 2 | Metal-hand (4 rows) Note that this is running a separate subquery for each person_id so if you have a large table performance might not be brilliant. The usual advice is to try to rewrite the IN as an EXISTS instead, but I'm not clear on how you'd do that in this case. Actually, looking at it, it might run a separate subquery for each row. Ideally, there'd be some way of having a "PERGROUP LIMIT" imposed, but I'm afraid I don't know of one. - Richard Huxton ---(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] convert a bigint into a timestamp
On Thursday 25 Jul 2002 1:48 am, marc sturm wrote: > Hello, > > Does anyone know how to convert a bigint into a date > or timestamp in a SQL query. > Thanks a lot. The problem is that there is no cast from bigint=>interval directly AFAICT, so go via text. richardh=> select extract(epoch from now()); date_part -- 1027593096.67471 (1 row) richardh=> select '1970-01-01 00:00:00 GMT'::timestamp + ((1027593096::bigint)::text)::interval; ?column? 2002-07-25 11:31:36+01 In your case you'd replace (1027593096::bigint) with the name of your column. HTH - Richard Huxton ---(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] Return Primary Key from Procedure
On Wednesday 24 Jul 2002 3:12 pm, Peter Atkins wrote:
> All,
>
> I have two tables t_proj, t_task see below:
>
> CREATE TABLE t_proj (
> proj_id INT NOT NULL AUTO_INCREMENT,
> PRIMARY KEY (proj_id),
> task_id integer(12),
> user_id integer(6),
> title varchar(35),
> description varchar(80)
> );
>
> CREATE TABLE t_task (
> task_id INT NOT NULL AUTO_INCREMENT,
> PRIMARY KEY (task_id),
> title varchar(35),
> description varchar(80)
> );
These are MySQL, not standard SQL - the AUTO_INCREMENT won't work for you in
PostgreSQL. Likewise things like integer(12) - see online manual for details
on types and SERIAL columns.
> When I insert into t_task I need to return the task_id (PK) for that insert
> to be used for the insert into the t_proj table.
>
> I tried using RESULT_OID but I have no idea how to obtain the true PK using
> this opague id. Below is the procedure I tried to use.
You can get an AUTO_INCREMENT type feature by decaring task_id as SERIAL type,
but I'd recommend an explicit SEQUENCE.
richardh=> CREATE SEQUENCE foo_test_seq;
CREATE
richardh=> CREATE TABLE foo2 (task_id int4 NOT NULL DEFAULT
nextval('foo_test_seq'), a int4);
CREATE
richardh=> INSERT INTO foo2 (a) VALUES (1);
INSERT 7023473 1
richardh=> INSERT INTO foo2 (a) VALUES (2);
INSERT 7023474 1
richardh=> INSERT INTO foo2 (a) VALUES (2);
INSERT 7023475 1
richardh=> SELECT * FROM foo2;
task_id | a
-+---
1 | 1
2 | 2
3 | 2
(3 rows)
richardh=> SELECT currval('foo_test_seq');
currval
-
3
(1 row)
The sequence guarantees you an accurate report for this process. So - if you
have two processes each inserting, they'll only see their own "currval". Also
read up on nextval and sequences.
Sequences / serial type are more flexible than MySQL's AUTO_INCREMENT. You can
have multiple serials in a table, and share a sequence between several tables
if you want.
- Richard Huxton
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] concurrent connections is worse than serialization?
On Tuesday 13 Aug 2002 9:39 pm, Wei Weng wrote: > I have a testing program that uses 30 concurrent connections > (max_connections = 32 in my postgresql.conf) and each does 100 > insertions to a simple table with index. > > It took me approximately 2 minutes to finish all of them. > > But under the same environment(after "delete From test_table, and vacuum > analyze"), I then queue up all those 30 connections one after another > one (serialize) and it took only 30 seconds to finish. > > Why is it that the performance of concurrent connections is worse than > serializing them into one? What was the limiting factor during the test? Was the CPU maxed, memory, disk I/O? I take it the insert really *is* simple - no dependencies etc. > I was testing them using our own (proprietary) scripting engine and the > extension library that supports postgresql serializes the queries by > simply locking when a query manipulates a PGconn object and unlocking > when it is done. (And similiarly, it creates a PGconn object on the > stack for each concurrent queries.) I assume you've ruled the application end of things out. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Explicite typecasting of functions
On Wednesday 14 Aug 2002 10:30 am, Andreas Tille wrote:
> Hello,
>
> I want to insert new data into a table with increasing data set ids.
> The table has two separate "regions" of data: Those with Ids below
> 100 and other. If I want to create a new Id in the "lower region"
> I tried the following (simplified example):
>
>
> CREATE TABLE Items (
> Idint DEFAULT NextItem()
> CREATE FUNCTION NextItem() RETURNS INT4
> AS 'select max(Id)+1 from Items where Id < 100;'
> LANGUAGE 'sql';
>ERROR: Relation "items" does not exist
>
> Any hint to solve this kind of chicken-egg-problem?
Your solution is not safe anyway - you could end up with two processes trying
to insert the next value.
Can I suggest two sequences: item_low_seq and item_high_seq? Set the initial
value of each to 1,000,000 and 99,000,000 (or whatever) and then use
whichever sequence is appropriate.
In the example above you'd want something like:
id int not null default nextval('item_low_seq')
- Richard Huxton
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Few Queries
On Wednesday 14 Aug 2002 1:29 pm, Sugandha Shah wrote:
> Hi ,
>
> No luck . Even with Select Into . Please if any body has faced similar
> problem and knows a solution.
You don't need select into here - the sample below deletes everything older
than one day. You should be able to adapt it to your needs. Your example had
a + rather than a - which would delete things in the future, that might have
been what you intended, or it might not.
The RAISE NOTICE line prints a useful debug value. I like to scatter these
around while I'm testing.
CREATE FUNCTION del_old_history() RETURNS int4 AS '
DECLARE
var_history_age_limit int4;
set_timetimestamp;
BEGIN
var_history_age_limit:=1;
set_time := current_date() - (var_history_age_limit::text || '' days
'')::interval;
RAISE NOTICE ''set time = %'',set_time;
delete from history where complete_time <= set_time;
return var_history_age_limit;
END;'
LANGUAGE 'plpgsql';
-- Richard Huxton
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] concurrent connections is worse than serialization?
On Wednesday 14 Aug 2002 3:20 pm, Wei Weng wrote: > On Wed, 2002-08-14 at 05:18, Richard Huxton wrote: > > On Tuesday 13 Aug 2002 9:39 pm, Wei Weng wrote: [30 connections is much slower than 1 connection 30 times] > > What was the limiting factor during the test? Was the CPU maxed, memory, > > disk I/O? > > No, none of the above was maxed. CPU usage that I paid attention to was > at most a 48%. Something must be the limiting factor. One of - CPU - Memory - Disk I/O - Database (configuration, or design) - Application If it's not CPU, is the system going into swap or are you seeing a lot of disk activity? > > I assume you've ruled the application end of things out. > > What does this mean? I mean if you don't actually run the queries, then 30 separate processes is fine? If you can provide us with an EXPLAIN of the query and the relevant schema definitions, we can rule out database design. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] concurrent connections is worse than serialization?
On Wednesday 14 Aug 2002 4:29 pm, Wei Weng wrote:
> > [30 connections is much slower than 1 connection 30 times]
>
> Yeah, but the problem is, say I have 20 users using select on the
> database at the same time, and each select takes 10 seconds to finish. I
> really can't queue them up (or the last user will reall have to wait for
> a long time), can I?
No, and you shouldn't have to - Postgresql can handle hundreds of concurrent
connections if needs be.
> > If it's not CPU, is the system going into swap or are you seeing a lot of
> > disk activity?
>
> I did hear a lot of disk noise when I ran the test. How do I tell if the
> "system is going into swap"?
You'd expect disk activity to a degree, it's a database after all. To see how
much and what is happening try one of : vmstat, iostat, top. You should have
at least one of these on whatever system you are using.
> Is there any system settings I can/should change to make this a little
> faster?
There are a number of settings in postgresql.conf - see the online docs (I
think there's a tuning document on techdocs.postgresql.org). Bear in mind
that the default settings for postgresql are very small, so you might want to
look at the sort memory/shared memory settings. Increase values a bit at a
time and see where you get a good balance. See the list archives for lots of
discussion of this.
> |IDVARCHAR(40) | --> primary key
> |Name VARCHAR(100)|
> And I did an INSERT INTO foo ('some-unique-guid-here', 'Test Name');
Try using the script multicli_ins.sh below - it creates 10 perl clients each
of which inserts 100 rows in separate transactions. This is about as
inefficient as you can get.
Have a play with different numbers of connections and see if there's a
pattern.
=> select max(ts) - min(ts) from foo;
?column?
-
00:00:06.448863
So - I can insert 1000 using 10 clients in under 6.5 seconds. That's on an
untuned system on a development server (400MHz, 256MB, IDE disk).
=== table definition ===
CREATE TABLE "foo" (
"id" character varying(40),
"name" character varying(100),
"ts" timestamp with time zone DEFAULT now()
);
CREATE UNIQUE INDEX foo_key ON foo (id);
=== Perl script: pg_ins_test.pl ===
#!/usr/bin/perl -w
my $id = shift;
open CMD,"| psql -q";
my $key = "${id}AAA";
for (my $i=0; $i<100; $i++) {
print CMD "INSERT INTO foo (id,name) VALUES ('$key','Test name');\n";
$key++;
}
close CMD;
print "Done $id\n";
=== Shellscript: multicli_ins.sh ===
#!/bin/sh
psql -c 'DELETE FROM foo'
for i in A B C D E F G H I J
do
./pg_ins_test.pl $i 2>/dev/null &
done
=== End shellscript ===
- Richard Huxton
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Explicite typecasting of functions
On Thursday 15 Aug 2002 8:13 am, Andreas Tille wrote:
> On Wed, 14 Aug 2002, Tom Lane wrote:
> > CREATE TABLE without mentioning the default
> > CREATE FUNCTION
> > ALTER TABLE ... SET DEFAULT
> >
> > Note however that pg_dump is not bright enough to deduce that you did
> > this. It will dump the table definition first, with the DEFAULT clause,
> > and so you'll have to do manual surgery on the dump file if you ever
> > need to reload.
>
> Hmmm, the original problem I wanted to solve using this function is that
> pg_dump is not bright enough to store sequences.
Eh? If you run a pg_dump on your database you should get something like:
CREATE SEQUENCE "bar_a_seq" start 1 increment 1 maxvalue 9223372036854775807
minvalue 1 cache 1;
CREATE TABLE "bar" (
"a" integer DEFAULT nextval('"bar_a_seq"'::text) NOT NULL,
"b" integer
);
... data then follows...
-- Name: bar_a_seq Type: SEQUENCE SET Owner: richardh
SELECT setval ('"bar_a_seq"', 3, true);
It's that last bit that sets the value of the sequence. Now AFAIK pg_dump's
been like that pretty much forever.
Note - if you only pg_dump the table, you won't get the sequence, you need to
dump the whole DB and grep away the bits you don't want.
- Richard Huxton
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Character translation?
On Thursday 05 Sep 2002 11:15 pm, Linn Kubler wrote: > Hi, > > I have a view like this: > create view myview as > select recid, title, firstname || chr(32) || lastname as expert, rank > from mytable; > > When I use this view via odbc on a Windows 2000 system using Visual FoxPro > the expert field shows up as a memo field. This is analogous to the text > field in PSQL. > What I'd like to do is have the expert column come through as a varchar > type so that it shows up as a text field in VFP. Any suggestions? > I was looking at the functions and didn't see anything that would do the > trick for me but I could have just missed something. I also saw a > reference to the cast() function but couldn't find any usage info on it in > the online docs. I think you're on the right track, try something like: select (firstname || ' ' || lastname)::varchar as expert ... If it works, could you let the list know in case anyone else needs this in future. If not, there is an ODBC list too (see postgresql.org website for details) - Richard Huxton ---(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] Slow Multi-joins performance [DEVELOPERS attn please]
On Friday 06 Sep 2002 11:59 am, [EMAIL PROTECTED] wrote: > Has the performance for queries with lots of joins (more than 5) been > improved in v7.2 ? > > I'm using 7.0.3 on Redhat 7.1 on a K2-6 500 CPU, and it is about 3 times > slower than MySQL, or Access on windoze platform :-( > > I tried different command-line optimisations, and got the best results (on > other data) with "-fm -fh -fs", but still not to the expected results of a > fraction of a second to return the data. > Changing the sort buffer options, etc, had little effect. > > To prove the point (albeit a trivial example), here is some test tables, > that take over 3 seconds to retrieve one row of data from tables containing > only one row of data each. (Tom - sorry to cc: you on this, but I'm not sure if I'm talking rubbish here) Interesting - I get something similar here. If I rewrite the view with explicit joins as below: SELECT t.id, a.name AS ta, b.name AS tb ... FROM t JOIN a ON t.ta=a.id JOIN b ON t.tb=b.id ... it returns instantly. Running an EXPLAIN ANALYSE, both have similar query plans with twelve nested joins and 13 seq scans (as you'd expect for tables with 1 row each). The only apparent difference is the order of the seq scans. The best bit is the Total runtime: 4.32 msec (original) Total runtime: 5.32 msec (explicit JOINs) Which says to me that your form is fine. Testing says otherwise, so there must be some element of the query that is not being accounted for in EXPLAIN ANALYSE. Your log shows the genetic algorithm (geqo_main line) kicking in because it sees a complex query and it could be that this is the problem - PostgreSQL takes a look at the 13-way join and thinks it's going to be very expensive. If you had a genuinely complex query, the time to analyse options would be a benefit, but here I'm guessing it's not. Perhaps try it with increasing amounts of data and more restrictions and see if performance stays constant. - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Dublicates pairs in a table.
On Tuesday 17 Sep 2002 7:36 am, Ries van Twisk wrote: > Richard, > > do you suggest using a stored procedure to handle this? I do expect that > the table will be large (for me large is a around 1-2 records, the > table as more columns but I only need the restriction on c1 & c2) but I > dont expect lots of inserts and deletes. Data entry will be done using a > regular user but of course a update needs to be done within a acceptable > time so the user does not get annoyed. I'd go with the UNIQUE INDEX. You're unlikely to be able to write code any faster than the built-in indexing code. It's also simpler and easier for someone else to see what's going on. The only time the index will slow you down is on an insert/delete when the index needs to be updated as well as the table. However, without the index you'll need to scan the table to check for duplicates. For thousands of records that can only be slower. To put your mind at ease, I'd put together some test data and try it - nothing like seeing some real evidence. HTH - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Returning a reference to a cursor from a function
On Thursday 12 Sep 2002 7:12 pm, david williams wrote: > To anyone who can help me, > > I am new at Postgresql and am having some problems. > I went a stage further attempt to put this query into a function as such > CREATE FUNCTION getallusers() RETURN integer AS' > DECLARE > Liahona CURSOR FOR Select * from users; > BEGIN > > FETCH FORWARD ALL IN Liahona; > > CLOSE Liahona; > END; > 'language 'plpgsql'; Number of issues here - it's RETURNS on the first line, you haven't OPENed the cursor and I don't think you can use that form of FETCH in the function. Look at the Programmers Guide, ch 23.7 "Cursors" for details on how to do it. To get you going, here's one that returns a count of how many records were fetched from an indicated table. -- BEGIN function -- DROP FUNCTION foo_count(text); CREATE FUNCTION foo_count(text) RETURNS integer AS ' DECLARE my_name ALIAS FOR $1; csr1 refcursor; dummy RECORD; n int4; BEGIN n:=0; RAISE NOTICE ''counting table: %'',my_name; OPEN csr1 FOR EXECUTE ''SELECT * FROM '' || my_name; FETCH csr1 INTO dummy; WHILE (FOUND) LOOP n:=n+1; FETCH csr1 INTO dummy; END LOOP; CLOSE csr1; RETURN n; END; 'language 'plpgsql'; -- END function -- Put this in a text-file and use \i filename from psql to import the definition - makes it easier to debug. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
