Re: [GENERAL] A few questions about ltree

= 'a.b'::ltree; DELETE 1 SQL select * from ltree_test; path --- a a.b.c (2 rows) Is there some obvious/easy way to prevent this? Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK

Re: [GENERAL] A few questions about ltree

of thought in ltree. Maybe it'll be possible with a future version of PostgreSQL :) Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World

Re: [GENERAL] A few questions about ltree

AFAICS. Nope, there's no ON DELETE CASCADE on the FK, and RESTRICT is the default (thankfully). But the FK constraint apparently doesn't get triggered by the delete, so neither case matters much here. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0

Re: [GENERAL] Categories and Sub Categories (Nested)

of any item at any level. I highly recommend it. Ah, one of those modules I still need to investigate. It looks promising. Can it do networked structures? That'd be kind of hard with an index on a path when a node can have multiple parent nodes, I figure... -- Alban Hertroys [EMAIL PROTECTED

Re: [GENERAL] Categories and Sub Categories (Nested)

, for example by keeping a reference to the direct parent. Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast

Re: [GENERAL] select with date_part and month failure....

field, with the added benefit that you can use 1 query for any type of interval ('1 month', '15 days', '2 weeks', etc.). Your query would become something like: select * from tstdates where lasttimedone - period = now() Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0

Re: [GENERAL] Give me some suggestion

recent version. IIRC there are some possible data corruption bugs in that version. It's a couple of years old too. Updating to a recent 7.4 version shouldn't cause any problems, but be sure to have a dump available (made with the tools of the new version). Regards, -- Alban Hertroys [EMAIL

Re: [GENERAL] Debian package for freeradius_postgresql module

compliant library. The issue at hand obviously is licensing related, the software is not the problem. And the cause of the licensing problem is apparently a restriction in GPL. Fix that, problem solved. Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31

Re: [GENERAL] pg_restore 7.4.7 locks itself out

Andrew - Supernews wrote: On 2006-04-10, Alban Hertroys [EMAIL PROTECTED] wrote: Notice the INSERT there. For a restore, you'd expect it to be COPY, _unless_ you used the -d option to pg_dump (this is a common mistake to make, given that all the other utilities use -d to specify the database

Re: [GENERAL] Performance UPDATE/INSERT

change its data but need to create a new record instead and mark the original as obsolete. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World

Re: [GENERAL] trigger firing order

, is there an equivalent for postgres ?? They're fired in alphabetical order. I tend to prefix my trigger names with numbers for that reason ;) -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede

Re: [GENERAL] Asking advice on speeding up a big table

immensely (from over 300ms to less than 1ms - uncached). That was a pretty amazing performance improvement to be sure :) So it is possible. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK

[GENERAL] pg_restore 7.4.7 locks itself out

sysadmin'd prefer to stay with versions packaged by the distributor (Debian in this case). OTOH, I would like to test this database on 8.1 some time (this is our development DB after all), so this could be a good opportunity... Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T

Re: [GENERAL] pg_restore 7.4.7 locks itself out

Tom Lane wrote: Alban Hertroys [EMAIL PROTECTED] writes: postgres 15092 0.0 0.3 43692 12924 ? D14:11 0:00 postgres: postgres vh3_live [local] INSERT This process is not blocked on a lock: it's waiting for disk I/O. Thoughts that come to mind include (1) it's going fine

Re: [GENERAL] database design questions

seperately or something like that (hmm... how much are they? I sure could use a few extra). -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World

Re: Pet-peevishness ([GENERAL] When does a check constraint execute?)

these kind of things. Try to write what you really want, instead of waving hands in the general direction and make the planner decide what you mean ;) Well, this is getting awfully pet-peevish, of course... -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0

Re: [GENERAL] Updating database structure

between statements, for example when adding a new NOT NULL column to a table. Not sure what to do in that case. Automating that would require some server side 'intelligence'... Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I

Re: [GENERAL] Enforcing serial uniqueness?

shooting some arrows, I may miss the target. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast

Re: [GENERAL] Enforcing serial uniqueness?

to the sequence. It's just a matter of wanting to do things the 'proper' way. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end

Re: [GENERAL] view creation question

Richard Huxton wrote: Larry White wrote: SELECT * FROM Tasks UNION ALL SELECT * FROM Issues; In case you care about where a record originated from: SELECT *, 'Tasks' AS source FROM Tasks UNION ALL SELECT *, 'Issues' AS source FROM Issues; Regards, -- Alban Hertroys [EMAIL PROTECTED

Re: [GENERAL] upgrade PostgreSQL 8.x on production FreeBSD

- start db -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 5: don't forget

Re: [GENERAL] How do I use the backend APIs

happens... Would it only get to halfway the values 1..10 and then go backwards again? -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World

Re: [GENERAL] How do I use the backend APIs

as well, but I've never used those. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast

Re: [GENERAL] What is the syntax for setting a default date in PostgreSQL?

Frank Church wrote: This issue always stumps me. I need to set the default date for a column in postgres. A value for today, today + x days, now (timestamp), now + x amount of days. CURRENT_DATE, CURRENT_DATE + x, now(), now() + 'x days'::interval are a few options. -- Alban Hertroys [EMAIL

Re: [GENERAL] r trim of characters other than space

surabhi.ahuja wrote: patName := trim($1); select trim(trailing `^` from patName) INTO patName; trim(patName); I think this line should contain an assignment. -- Alban Hertroys ---(end of broadcast)--- TIP 9: In versions

Re: [GENERAL] What's faster?

) show_pages; show_only_15_rows($result); -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede //Showing your Vision to the World// ---(end of broadcast

Re: [GENERAL] Are rules transaction safe?

to work. They do (as stated before) The Right Thing. You don't need to worry about them. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede //Showing your Vision to the World

Re: [GENERAL] Indexes

; an index on those columns may help. Mind you, this is more about optimization, not so much about database design. It depends on your queries whether you're going to have any benefit from this. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I

Re: [GENERAL] filtering after join

generated by the join operation. How can I do that? You should be able to use something like (rewrote your join as well): select foo(some_column) from A left join b on (A.a=B.b) where A.a 2; If that's not what you mean to do, could you be a bit clearer as to what you're trying to achieve? -- Alban

Re: [GENERAL] NOT HAVING clause?

that this should work too (not tried yet): SELECT some_column FROM some_table GROUP BY some_column HAVING MIN(sort_order) 1; As our sort_orders start from 1. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl

[GENERAL] NOT HAVING clause?

the same thing. What is the general opinion on this from the developers? Did I just have one of those wild and ridiculous ideas? :P Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK

Re: [GENERAL] NOT HAVING clause?

Michael Glaesemann wrote: On Jan 24, 2006, at 20:00 , Alban Hertroys wrote: Though this does give the right results, I would have liked to be able to use NOT HAVING. Or is there a way using HAVING that would give the same results? I'm quite sure HAVING sort_order 1 doesn't mean the same

Re: [GENERAL] NOT HAVING clause?

Richard Huxton wrote: Alban Hertroys wrote: You're mixing up WHERE and HAVING. The WHERE clause applies to the individual rows before GROUP BY. The HAVING applies to the output of the GROUP BY stage. Ah, of course, now it makes sense. Combined with Csaba's reply my original problem has

Re: [GENERAL] sql (Stored procedure) design question

power you seem to be so concerned about. It seems unlikely you would gain any measurable performance this way, if at all. I can't say I fully understand what you're trying to do, your example and explanations were somewhat confusing. Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions

Re: [GENERAL] Connection specific information - Temporary table used

savepoints. I suppose the login information is also available by other means, in that case you might want to create a view around the appropriate functionality instead of your temporary table trick. Just a few ideas... -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F

Re: [GENERAL]

that the database needs to cast the int u_id value to text for every record encountered. For 'small' data sets this shouldn't be a problem. I suppose this is a simplified example, or you wouldn't have a reason to use an SP; you'd just SELECT * FROM my_tbl WHERE u_id IN (0,1,2,3) -- Alban Hertroys

Re: [GENERAL] insert serial numbers

, CONSTRAINT iu_text UNIQUE (text) ) CREATE TABLE two ( two_id BIGSERIAL, text text NOT NULL, one_id bigint, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede //Showing

Re: [GENERAL] need help

(check the archives, I learned that by following this list). If it's indeed a locked record, the process causing the lock is listed. Either kill it or call it's owner back from his/her coffee break ;) I doubt it's anything serious. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T

Re: [GENERAL] shorter way to get new value of serial?

=upper($1) into result; IF not FOUND THEN insert into rechner (name) values (upper($1)); END IF; ... -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede //Showing

Re: [GENERAL] Is it databases in general, SQL or Postgresql?

evaluate the function with the content of your column and compare the result to your string. ERROR: syntax error at or near where at character 41 QUERY: insert into pi (fluid_id) values ( $1 ) where pr (contain) = 'ip' CONTEXT: PL/pgSQL function base line 2 at SQL statement -- Alban Hertroys

Re: [GENERAL] function DECODE and triggers

' THEN 'M' ... END AS EST You could write a stored procedure that does what you want. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede //Showing your Vision to the World

Re: [GENERAL] NULL != text ?

to: if (OLD.value IS NULL OR NEW.value IS NULL OR OLD.value NEW.value) The last part of the expression is only evaluated if both OLD.value and NEW.value aren't NULL. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl

3-state logic (was: Re: [GENERAL] NULL != text ?)

... This 3-state logic can have some interesting results... -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede //Showing your Vision to the World// ---(end of broadcast

Re: [GENERAL] more than one row returned by a subquery used as an

industries set companies = count(companies.id) from companies where industry_id = industries.id; -- Alban Hertroys ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] to drop a 30GB database. is it slow?

the availability of your database any further. This should minimize your downtime, I think - unless people have even speedier solutions, of course. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416

Re: [GENERAL] regarding select into

probably unique, you could as well do: select ser, 1 from tab1 where id=$1; Otherwise, whatever interface you're using is likely to have a rowcount value for you. If that's not what you're looking for, please don't make us guess what you want to achieve. -- Alban Hertroys [EMAIL PROTECTED

[GENERAL] ECPG in stored procedures?

. What are the cons and pros? -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede //Showing your Vision to the World// ---(end of broadcast

Re: [GENERAL] ECPG in stored procedures?

Peter Eisentraut wrote: Am Montag, 5. September 2005 11:42 schrieb Alban Hertroys: Reading up on ECPG, I figured it would be nice to be able to use that in stored procedures. The purpose of ECPG is to provide a smooth interface for hooking in SQL calls into C programs. If you want to have

Re: [GENERAL] ECPG in stored procedures?

lib are we supposed to use for SPI? -- Alban Hertroys ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [GENERAL] Select gives the wrong results

the left string is shorter). Either compare numbers, or left pad your left string with zeroes until it's the same length as the right string. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK

[GENERAL] Referencing less-unique foreign keys

localization Any way around this? -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede //Showing your Vision to the World// ---(end of broadcast

Re: [GENERAL] Referencing less-unique foreign keys

Martijn van Oosterhout wrote: On Tue, Aug 09, 2005 at 02:31:16PM +0200, Alban Hertroys wrote: Hi all, We migrated a database from version 7.3 something to 7.4.7 a while ago, and ever since that time we can't make new foreign keys to a particular table. The problem is that the primary key

Re: [GENERAL] Referencing less-unique foreign keys

til last year or so, that's quite an ordeal... There are tables where the tabel definition scrolls out of view rather rapidly... I never liked the way this was solved by my predecessor, this seems a good opportunity to fix it. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31

Re: [GENERAL] Finding points within 50 miles

Uwe C. Schroeder wrote: in the where clause use something like (requires the earthdistance contrib to be installed): geo_distance(point([origin longitude],[origin latitude]),point([target longitude column],[target latitude column])))::int = 50 I don't suppose geo_distance really returns

Re: [GENERAL] Trigger and arguments question

]. Fine. What I'm trying to do is using TG_ARGV[0] to point to a field in NEW or OLD. Is it possible? You'll be missing OLD and NEW on INSERT and DELETE respectively, I'm afraid. You may want to split your triggers for different events. -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F

Re: [GENERAL] unique index with bool

INDEX name ON table(param1, param2) WHERE param2 = true; Regards, -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL PROTECTED] W: http://www.magproductions.nl ---(end of broadcast)--- TIP 6: Have you searched our list

Re: [GENERAL] Disabling Triggers

for triggers and cascading (or that would be useful if there isn't...). Does anyone know if it is possible to run an update statement on a table and for only that statement disable the trigger on the table? -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL

Re: [GENERAL] Performance difference between ANY and IN, also array

the better alternative. -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL PROTECTED] W: http://www.magproductions.nl ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate

Re: [GENERAL] CURRENT_TIMESTAMP vs actual time

Thomas Hallgren wrote: Why use timeofday() at all? Why not now(). It will return a timestamptz without casts. For the same reason that the OP couldn't use CURRENT_TIMESTAMP. It returns the timestamp of the start of the transaction. -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0

Re: [GENERAL] Simple UPDATE runs forever

-9' the postmaster -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL PROTECTED] W: http://www.magproductions.nl ---(end of broadcast)--- TIP 6: Have you searched our list archives? http

Re: [GENERAL] Temporary Tables

Joseph M. Day wrote: Just out of curiosity, what is the performance of this? In MSSQL the only way to do something equivalent to this was to use a cursor. Cursors are painfully slow, so they are to be used as a last resort. On what do you base that cursors are slow? -- Alban Hertroys

Re: [GENERAL] Select Last n Rows Matching an Index Condition (and

things some more, I suppose. -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL PROTECTED] W: http://www.magproductions.nl ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] help with plpgsql function called by trigger

simple SQL statements, the triggers could also be implemented as a set of query rewrite rules (see CREATE RULE). That's usually more efficient, but I don't have a lot of experience with those... Regards, -- Alban Hertroys MAG Productions ---(end of broadcast

Re: [GENERAL] Disabling triggers in a transaction

)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL PROTECTED] W: http://www.magproductions.nl ---(end of broadcast

Re: [GENERAL] PostgreSQL still for Linux only?

Joshua D. Drake wrote: Nobody should ever put a server regardless of OS on a public IP. It should always be firewalled/Nat/Port Forwarding. Except for the firewall/Nat server, of course :D -- Alban Hertroys ---(end of broadcast)--- TIP 3: if posting

Re: [GENERAL] Disabling triggers in a transaction

with legacy code from an older database, I think you'll run into trouble with the above quicker than you'd like. However, if you manage to get this into the design fase of a project it'll probably work just fine (the delete problem you mentioned aside...). -- Alban Hertroys MAG Productions T: +31(0)53

Re: [GENERAL] cursor already in use error

(as does testid=2 or 3) The second query works because you fetch only one record; You don't call the SP a second time with the cursor still open, while you do with the first query. Always close your cursors. -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL PROTECTED] W

Re: [GENERAL] basic trigger using OLD not working?

in BEFORE DELETE triggers. I use several of them (PostgreSQL 7.4). -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL PROTECTED] W: http://www.magproductions.nl ---(end of broadcast)--- TIP 2: you can get off all lists

Re: [GENERAL] Table UPDATE statement

= t2.new_col1 FROM t2 WHERE t1.row_key = t2.row_key; -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL PROTECTED] W: http://www.magproductions.nl ---(end of broadcast)--- TIP 6: Have you searched our list archives

Re: [GENERAL] Lost rows/data corruption?

and/or in synchronous mode (contrary to async). -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL PROTECTED] W: http://www.magproductions.nl ---(end of broadcast)--- TIP 9: the planner will ignore your desire

Re: [GENERAL] indexing just a part of a string

at the documentation for CREATE INDEX, there are possibilities. Out of general curiosity: I mentioned using a hashed column as a possible solution. Would that be equivalent to using a hash index? Or is searching a hash value in a btree index actually faster than in a hash index? -- Alban Hertroys MAG

Re: [GENERAL] Invalid headers and xlog flush failures

recovers a dirty file system, could it be related? -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL PROTECTED] W: http://www.magproductions.nl ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please

Re: [GENERAL] Help with strange join

that you can check for them. I suppose a HAVING clause would also work, but AFAIK you need to GROUP BY to be able to do that... All of this is untested of course, so I may have made mistakes. -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL PROTECTED] W: http

Re: [GENERAL] basic pg lock question

a constraint (before insert) that checks that there is no data matching the md5 checksum, right? -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL PROTECTED] W: http://www.magproductions.nl ---(end of broadcast

Re: [GENERAL] When is a blank not a null or ''

!= ''; -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL PROTECTED] W: http://www.magproductions.nl ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] When is a blank not a null or ''

!= ''; no difference Then you probably have email addresses that exist of white space only. You should probably put a constraint on that if undesirable. Try using a regular expression like so: SELECT first_name,work_email FROM tb_contacts WHERE work_email !~ '^[[:space:]]*$'; -- Alban Hertroys MAG Productions

Re: [GENERAL] postgres session termination

people here will have their ideas about this ;) Regards, -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL PROTECTED] W: http://www.magproductions.nl ---(end of broadcast)--- TIP 9: the planner will ignore your desire

Re: [GENERAL] Extended unit

510 units, 54 prefixes You have: 1 K You want: degC * 1 / 1 Which is incorrect, of course. Same for degrees Fahrenheit. The poor command can't do baseline offsets. -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL

Re: [GENERAL] postgres session termination

wrong, but I don't think PHP supports cursors (Maybe PHP 5?). Otherwise, that would have been a neat solution indeed. -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL PROTECTED] W: http://www.magproductions.nl ---(end of broadcast

Re: [GENERAL] Extended unit

: processor intensive) math to a database engine. Just my 0.02 Euro. -- Regards, Alban Hertroys MAG Productions P: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL PROTECTED] W: http://www.magproductions.nl ---(end of broadcast)--- TIP 1: subscribe

Re: [GENERAL] Calculating a moving average (Coding style)

of it. If I would have written this, there would have been 3 triggers w/o the check on TG_OP. Is there an important drawback to doing so? Is there any document on preferred coding style in PL/PGSQL? Yes, I'm a bit of a purist... -- Regards, Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53

[GENERAL] How to manually insert an UTF-8 character into an SQL statement?

I'm trying to insert a record that contains an ô (o circumflex) into a table using the psql client. I also tried with phppgadmin and pgadmin, but both can't do this. They insert a HTML entity and error out respectively. Not what I had in mind... Supposedly I should be able to type: INSERT INTO

Re: [GENERAL] update in triggers

Jamie Deppeler wrote: Trigger CREATE TRIGGER new_trigger AFTER INSERT OR UPDATE ON chargeratetest FOR EACH ROW EXECUTE PROCEDURE chargeratetest(); function CREATE OR REPLACE FUNCTION chargeratetest () RETURNS trigger AS' begin UPDATE chargeratetest set notes=''hello'' where new.primary =

[GENERAL] Infinite recursion detected... How do I prevent that?

I have a rule similar to this: CREATE RULE rule_branch_delete AS ON DELETE TO tree DO DELETE FROM tree WHERE ancestor_id IS NOT NULL AND OLD.child_id = ancestor_id; The data is oraganized like this: ancestor_id child_id NULL1 1

Re: [GENERAL] Infinite recursion detected... How do I prevent that?

Martijn van Oosterhout wrote: Sorry, RULEs are like macros, they essentially expand and transform your original query. This also means the expansion does not depend on the data in your database. So postgresql continaually expands the query, leading to your infinite recursion error. I just found

Re: [GENERAL] Any function for calculating inersect of intervals?

Egyd Csaba wrote: Hi, wondering if exists any functions which aim to calculate intersect of two intervals. E.g. I have four dates (D1, D2, D3, D4) and I want to know if (D1,D2) intersects (D3,D4) or not. Try INTERSECTS :P SELECT * FROM table WHERE (D1, D2) INTERSECTS (D3, D4); Related, are

Re: [GENERAL] Any function for calculating inersect of intervals?

Alban Hertroys wrote: SELECT * FROM table WHERE (D1, D2) INTERSECTS (D3, D4); Ahem... Let's call that end of the day dementia or something... Of course it's called OVERLAPS, not INTERSECTS... ---(end of broadcast)--- TIP 8: explain analyze is your

Re: [GENERAL] Insufficient system resources for PostgreSQL 7.4.x?

Ji Nmec wrote: But a problem occured. PHP scripts end due fatal errors Fatal error: Maximum execution time of 30 seconds exceeded When I had used Looks like you got yourself an endless loop there. Alban Hertroys. ---(end of broadcast)--- TIP 3

Re: [GENERAL] pl/pgsql trigger: syntax error at or near ELSEIF

on INSERT, the other on DELETE, and maybe a third on UPDATE) than writing a single and trying to figure out which of those three it was in the trigger. Alban Hertroys. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index

Re: [GENERAL] Transaction size

Tom Lane wrote: Alban Hertroys [EMAIL PROTECTED] writes: Is it possible that there is some limitation to the number of statements in a single transaction? 2^32, and if you'd exceeded it, you'd get a very specific error message saying so. Well, that's a relief. At least it means I'm not running

Re: [GENERAL] Transaction size

Tom Lane wrote: Alban Hertroys [EMAIL PROTECTED] writes: It does have a reference to a table with statusses, but those are rather static. I suppose an integrity check is comparable to doing a select with respect to locking strategies? (Meaning that it wouldn't be the cause of my problem

[GENERAL] Transaction size

Is it possible that there is some limitation to the number of statements in a single transaction? I'm trying to debug a problem where a program locks up during an insert[*]. Actually, there are 3 simultaneous transactions going on, all doing inserts of about 3000 records each. The problem

<    8   9   10   11   12   13