Re: [SQL] postgres index on ILIKE

hing module for PostgreSQL. hmmm ... the Tsearch home page appears to be down. Check out the readme in your postgreSQL source code: PG_SOURCE/contrib/tsearch/readme -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)

Re: [SQL] Problems to be solved as soon as possible

SQL-standard just for compatibility with 2 commercial databases, and so support for DATETIME was phased out over the next two versions. You can fix your scripts by doing a search-and-replace on DATETIME and replacing it with TIMESTAMP, which provides the same functiona

Re: [SQL] Link Oracle tables in Postgre

right now and just barely didn't make it into the 7.4 source. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister Yo

Re: [SQL] Bug in psql - Postgresql 7.3.1?

p4 script, but has been duplicated on the psql command > line. BTW, there are known bugs in 7.3.1; you should upgrade to 7.3.4. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Bug in psql - Postgresql 7.3.1?

but in fact they were. Time to look up your order of operations! -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] create new field

egers in it ? Actually, you just need to use the to_number function as an intermediary: UPDATE tab SET new_col = CAST(to_number("OLD_COL", '') AS INT); -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)-

Re: [SQL] privileges

Sad, > can anyone give me a link to a Reference manual > which describes all privileges on any DB object and it's meaning :-) > thnx. http://www.postgresql.org/docs/7.3/static/sql-grant.html http://www.postgresql.org/docs/7.3/static/user-manag.html -- Josh Berkus Aglio Database

Re: [SQL] UPDATE one table with values from another

thing on IRC . -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Selecting rows as if they were columns?

are 3 standard ways to solve it, depending on the exact circumstances of your case. It's probably best if you buy Joe Celko's "SQL for Smarties, 2nd Ed.", which has a 50-page chapter on the topic and explores the methods in detail. -- Josh Berkus Aglio Database Solut

Re: [SQL] Calc

There have been several discussions on running totals on this list over the last couple of weeks. See the archives for possible solutions. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and

Re: [SQL] Can't convert numeric to_char() in catenate function

'', '' || to_char($2,'D99') You need to escape your single quotes by double-quoting them, eg.: to_char($2,''D99'') -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)---

Re: [SQL] Which is faster SQL or PL/PGSQL

and, as T-SQL and PL/pgSQL have substantially different syntax for control structures and cursors. See the porting articles on techdocs.postgresql.org > which is faster . i am using postgres 7.3.2 You should upgrade to the 7.3.4 if possible as it has some bug fixes missing in 7.3.2. -

Re: [SQL] Scripting only the functions

Kumar, > Using pg_dump I could manage to take a script for all the DB objects. But > wanted to take the script (DDL) for all the scripts in my database. While I > searched I dont find any options in the pg_dump except for script tables > only. > > Is there a way? Currently, no

Re: [SQL] Which is faster SQL or PL/PGSQL

ile we're on the topic, anyone know any good ways to speed up EXECUTE statements in PL/pgSQL functions? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send

Re: [SQL] Which is faster SQL or PL/PGSQL

resql.org/pgsql-patches/2003-07/msg00239.php Not sure how useful it would be in my case; I'm using EXECUTE because I'm building a dynamic query based on user input, so the query plans would need to vary radically from run to run. -- -Josh Berkus Aglio Database

Re: [SQL] Which is faster SQL or PL/PGSQL

Tom, > AFAICS, the whole point of EXECUTE in plpgsql is that it doesn't take > any shortcuts, and so the answer to Josh's question can only be "don't > use EXECUTE"... Yeah, that's what I thought, I was just hoping for some low-hanging fruit. -- -Jos

Re: [SQL] converting an oracle procedure to postgres

urn "TRUE" at the end, and then your client code can interpret any non-true result (Error message, null) as an error. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] assorted Postgres SQL/ORDBMS questions

a particular way, use to_char() when you query them. Dates are stored as dates, not as strings. BTW, MS SQL Server's implementation of DATETIME sucks rocks and violates the SQL standard besides. So don't go comparing them on me. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: Fw: [SQL] Max input parameter for a function

penalty on Postgres! 5. Re-compile Postgres, starting with "make clean" 6. Run initdb 7. Restore your database cluster from the pg_dumpall file -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)

Re: [SQL] plpgsql related question: intervals and variables

gSQL. I think you want now() instead. I'm afraid that you're going to need a tutorial on SQL datatypes, casting, and similar issues ... I wish I had one to recommend to you. Just keep in mind that SQL scripting languages (like PL/pgSQL) are not Perl! -- -Josh Berkus Aglio Database S

Re: [SQL] Expressional Indexes

xpressional Indexes". What's wrong with it? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] Expressional Indexes

index" or "computed index" > but dunno if that really conveys anything. Well, "Expression Indexes" is the most accurate. Or "Expression-Based Indexes." -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadca

Re: [SQL] Query planner: current_* vs. explicit date

Scott, Chris, > I'd guess that the planner doesn't know what current_date::timestamp is > ahead of time, so it chooses a seq scan. Yes, this is a known problem. There was a single-query workaround, but I can't remember it right now. -- -Josh Berkus Aglio Database Sol

Re: [SQL] [NOVICE] Functional index problems. (Was: Many joins: monthly summaries S-L--O--W)

CREATE INDEX syntax, > correct? (Besides referring to a column.) Well argued. The problem is Peter's point, which you weren't cc'd on: >At least it's better than "functional index", because I had always >wondered where the dysfunctional index

Re: [SQL] extend INSERT by 'INSERT INTO table FETCH ... FROM cursor' syntax

ALL is not currently implemented. You would need to use a loop, and insert one row at a time by value. 2) You can't insert the rows you've just deleted from the base tables. In your example, the TOTAL cursor would be empty. I think that what you really

Re: [SQL] Help on update that subselects other records in table, uses joins

gods only know what you'll end up with. I suggest Joe Conway's "SQL for Smarties" or "SQL Queries for Mere Mortals" from another author. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- T

Re: [SQL] Help on update that subselects other records in table, uses joins

Bryan, > I could be mistaken, but I'm pretty sure Josh means Joe Celko's "SQL for > Smarties" as I've seen him mention it before, and not Joe Conway. Ooops! yes, Joe Conway is a major PostgreSQL contributor and author of the tablefunc /contrib library; Joe C

Re: [SQL] Problems with NEW.* in triggers

Jamie, > Any thoughts on what I'm doing wrong?? Yes. If you want to modify the new data, you need to use a BEFORE trigger. AFTER triggers can't modify NEW, just read it. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end

Re: [SQL] [HACKERS] Schema boggle...

is list who've written thousands of lines of PL/pgSQL ... -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] avoid circular references

> what happens when I create new tables. But what can I do in > my trigger to have PostgreSQL understand there's an integrity > violation ? A trigger. Just use a BEFORE trigger and raise an exception if a self-parent is found. -- Josh Berkus Aglio Database Solutions San Francisco -

Re: [SQL] plpgsql question

; ERROR: Wrong record type supplied in RETURN NEXT Um, that's a regular error. How is it a Seg Fault? >From the error, the frist thing I'd suggest you do is to check carefully into each of the column types and order in your query and the return type. I'll bet that one c

Re: [SQL] plpgsql question

urprised if you got an error for using an INT4 in place of an INT8. > and for varchar(30) I can just put varchar in my type definition. That I don't think will be a problem; varchar limits are indifferently supported anyway. -- Josh Berkus Aglio Database Solu

Re: [SQL] Quota query with decent performance?

e from person p2 order by p2.age DESC LIMIT 1 OFFSET 2) as prank WHERE person.age >= prank.age This should give you all of the rows whose ages are in the top 3 ranks much faster. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)

Re: [SQL] Looks are important

George, > SELECT RPAD(no,30,' ') || tableb.kind FROM tablea > WHERE tablea.kind = tableb.kind Try SELECT RPAD(no, (35 - LENGTH(tableb.kind)), ' ') -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--

Re: [SQL] Calendar Scripts - Quite a complex one

ur legal calendaring app. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] postgreSQl 7.4.1 and Hibernate

Beya, > just wanted to know whether anyone on this group uses Hibernate in > conjunction with postgreSQL 7.4.1? Not me personally but there are some companies who do. Also Alzabo. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of bro

Re: [SQL] [PERFORM] Set-Returning Functions WAS: On the performance of views

s very hackneyed, as I'm sure you realize. Overall, I'd say that the programming team you've been inflicted with don't like relational databases, or at least have no understanding of them. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of br

Re: [SQL] java.lang.StringIndexOutOfBoundsException: String index

lso, this should be on the PGSQL-JDBC mailing list, not this one. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] [PERFORM] Set-Returning Functions WAS: On the performance of views

smarter than me, and I find that comforting ;) Flattery will get you everywhere. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Update from same table

ble, you need to use the full name. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] Slow sub-selects, max and count(*)

while inserting and re-apply them afterward. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Slow sub-selects, max and count(*)

t_tick = ticker and dte = hist_date); This is not the same query as #1. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Tip: a function for creating a remote view using dblink

the right place for this kind of thing) We're working on something, but nothing's up yet. In the meantime, use the Techdocs Wiki to post it so that we don't lose track: http://techdocs.postgresql.org/guides -- -Josh Berkus Aglio Database Solutions San Francisco

Re: [SQL] Distributed Transactions

ommitting a transaction across multiple servers/databases, it's under development and may be released with version 7.5. Or later. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all li

Re: [SQL] Compiling pl/pgsql functions

ost full DBA powers without being the superuser, and deny them direct access to the pg_proc table. This would be a real PITA, though. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading thro

Re: [SQL] DISTINCT ON troubles

ry table is not necessary. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Row counts/data changes. Any catalog table that has this info?

accurate; however, if you VACUUM regularly it will be within 5-10%. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] User defined types -- Social Security number...

ng an > input / output "mask"... After you've created your DOMAIN, based on the TEXT type, you can overload the input and output functions to format correctly. Beware, though: input & output functions pretty much have to be written in C. -- Josh Berkus Aglio Database Soluti

Re: [SQL] Compiling pl/pgsql functions

d question: can I, as a database user, query the source code for functions I don't have permissions on?This seems like an easy adjustment to the system tables, if so. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)-

Re: [SQL] perfomance question

Sad, > what are perfomance difference bitween > a) update t1 set f1 = 'x', f2 = 'y'; > b) update t1 set f1 = 'x', f2 = f2; > c) update t1 set f1 = 'x'; > ? Not a lot. Why don't you try it? -- -Josh Berkus Aglio Database S

Re: [SQL] Slow sub-selects, max and count(*)

Greg, > Please don't confuse the issue by throwing Max() and Count() into the same > basket. When on earth is that post of mine from? Seems like it's several months, if not a couple of years, old. -- -Josh Berkus Aglio Database Solutions San Francisco --

[SQL] SQL Spec Compliance Questions

?? 10) Also not sure 11) In development, expected within the next two versions. Currently we have non-SQL-standard recursion by several methods. 12) No 13) Not sure. 14) Yes 15) I think so. Feedback, please! -- -Josh Berkus Aglio Database Solutions San Francisco

Re: [SQL] SQL Spec Compliance Questions

very bureaucratic, and I doubt the person who asked me has any control over the questionnaire. -- -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data m

Re: [SQL] SQL Spec Compliance Questions

ome feedback? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] left join on a view takes significantly more time.

e the planner at some stage. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[SQL] Array_append does not work with Array variables in PL/pgSQL?

NOTICE: derived value It seems like I cannot assign new elements to arrays inside a PL/pgsql function. What gives here? PostgreSQL 7.4.1 on Linux. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] Array_append does not work with Array variables in PL/pgSQL?

ng something fundamental. BTW, did you get my e-mail to Hackers about ARRAY[] IS NULL? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Array_append does not work with Array variables in PL/pgSQL?

Joe, > I saw it, but I've been too swamped to really read it. I'll try to carve > out some time this afternoon. No urgency on my part. More something to fix for 7.5 -- -Josh Berkus Aglio Database Solutions San Francisco ---(

Re: [SQL] [pgsql-advocacy] SQL Spec Compliance Questions

t reading that paragraph makes me think that the type is somehow supposed to contain metadata or summary data for the table itself. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through

Re: [SQL] [pgsql-advocacy] SQL Spec Compliance Questions

the SQL99 Committee smoking crack, or what?What the heck is that *for*? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] Function To Log Changes

t worry about logging the individual columns, or 2) Use PL/tcl, PL/Pyton, or C where you can select columnns by ordinal position or other dynamic factor. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if p

Re: [SQL] distinct values without seq scan

a data normalization issue (these values should really be in a reference list with an FK), to take advantage of the 7.4 optimization, try: SELECT advertiser FROM logrecords GROUP BY advertiser ORDER BY advertiser; -- Josh Berkus Aglio Database Solutions San Francisco -

Re: [SQL] SQL challenge--top 10 for each key value?

standards goodbye. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] Utility of recursive queries?

James, > Would recursive queries be the trick to doing things like unwinding a > linked-list to either the head or tail, with: Yes. Also check out contrib/ltree and contrib/tablefunc in your handy-dandy PostgreSQL source code. -- Josh Berkus Aglio Database Solutions San Fra

Re: [SQL] hi sir urgent..required a Query

alue > Please send a SQL query for this... Please re-post this with your table design in the body of your e-mail, and NO doc attachment. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/readi

Re: [SQL] setting a non-standard date format for the duration of a session

NLS_DATE_FORMAT='f'" Oracle statement. Sort of. See the Docs on "Runtime Configuration" on the GUC variable "Datestyle". -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- T

Re: [SQL] SQL challenge--top 10 for each key value?

y have. So, yes, SQL92 needed development and expansion. But we didn't need SQL99. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] Use arrays or not?

gt; > Arrays are non-standard SQL, and I hear that PHP-support for postgres & > arrays is rudimentary. So that might be an argument to avoid using them, > and go for option 2. From the standpoint of performance (or wisdom), can > you help me decide what I should choose? Or is there maybe an even better > way to structure my data? > > Thanks for any contribution! > > Roelant. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Equivalant of SQL Server's Nchar and NVARCHAR

This includes: TEXT (recommended) VARCHAR CHAR -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] Use arrays or not?

tart having a variable number of XML records? Normalized designs are almost always easier to deal with from a perspective of long-term maintainence. The arrays, as far as I can tell, gain you nothing in ethier performance or convenience. -- -Josh Berkus Aglio Database Solutions San Fr

Re: [SQL] ERROR: duplicate key violates unique constraint

7;,'$HOME_NET','any','->','dos.rules >',3,current_timestamp,0); INSERT 29393 1 > > And > cews=> insert into sensor_signature values (-1,268); > INSERT 29394 1 This isn't the same id you tested with the function. Mind running

Re: [SQL] use of a composite type in CREATE TABLE?

Hannes, > does anyone know how it is posible to set a composite type as the data > type of a column when creating a new table? This is not yet supported. Hopefully it will be supported in the upcoming version 7.5. -- Josh Berkus Aglio Database Solutions San Fra

Re: [SQL] question about which column(s) are the right foreign key

iderations make that impossible. However, if fixing this issue is not an option, I'd just use the object-version id as my FK. Unless, of course, you think you might fix the problem later. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of

Re: [SQL] feature request ?

s well as being different from every other programming language in existance ... -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Question about a CIDR based query

ost likely Postgres thinks that the >>= query is returning 60% of your table, which makes indexes useless. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ?

Re: [SQL] Question about a CIDR based query

-- > Seq Scan on tmp (cost=0.00..606.60 rows=14544 width=33) >Filter: (route >>= '62.1.1.0/24'::cidr) -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Question about a CIDR based query

--- > > Seq Scan on tmp (cost=0.00..606.60 rows=14544 width=33) > >Filter: (route >>= '62.1.1.0/24'::cidr) Oh, and also a SELECT VERSION(); would be nice. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] surrogate key or not?

unt the number of "bad (Bdatabases" I've encountered which contained tables with a surrogate key, and (BNO REAL KEY of any kind. This makes data normalization impossible, and (Bcleanup of the database becomes a labor-intensive process requiring (Bhand-examination o

Re: [SQL] surrogate key or not?

not? > Are these the right questions? Also you'll want to consider the speed of CASCADE operations whenever a type_name changes. If these changes occur extremely infrequently, then you can ignore this as well. -- -Josh Berkus Aglio Database Solutions San Francisco --

Re: [SQL] surrogate key or not?

Sad, (B (BFirst of all, please excuse me if I've misunderstood you below because of (Btranslation issues. You'll find I'm rather strident, but it's because the (Breasons you're presenting, or seem to be, are excuses for bad database design (BI hear every day on the job, and end up having

Re: [SQL] ? on announcement of 7.5

Sad, > can anyone comment the announcement of 7.5 > about "nested transactions" ? > doesn't the nesting hurt the matter of transaction ? 7.5 hasn't been announced. It's not even in beta. -- -Josh Berkus Aglio Database Solutions San Francisco --

Re: [SQL] surrogate key or not?

would only give one diagnosis. Otherwise, you have more than database problems. And it prevents you from having to rely on a flaky long text key. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have

Re: [SQL] surrogate key or not?

4) Your spec may be incorrect and surrogate keys make it easier to make design changes in production. Once again, though, this is an *implementation* issue and not a *logic* issue, as I asserted ... -- -Josh Berkus Aglio Database Solutions San Francisco -

Re: [SQL] surrogate key or not?

Lot's of it isn't in the state yet where we want it but we are > getting there - or so I think. When I have time, sure! But, this afternoon I am off to OSCON so I won't have a chance for 2 weeks at least. Drop me a personal e-mail in August so I don't forget. --

Re: [SQL] surrogate key or not?

'm criticizing is the tendency of a lot of beginning DBAs -- and even some books on database design -- to say: "If you've created an integer key, you're done." Had I my way, I would automatically issue a WARNING on any time you create a table in PG without a key. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] surrogate key or not?

Kenneth, > but why would anyone want to change the value of an autogenerated serial row? But if you're using a real key, it may need to change. The only reason *not* do do it that way is performance issues with CASCADE. -- -Josh Berkus Aglio Database Solutions San F

Re: [SQL] surrogate keys and replication.

e simplicity. But you want the other pieces of information clearly in the GUID key; otherwise you need to do a lot of calculation and querying to figure out, when Server 11 wants to update Row 283432 of Table "status", whether it can be done locally or needs to be "exchanged&quo

Re: [SQL] Suggestions on storing re-occurring calendar events

e parent event and the repeats to an integer, and any date where the modulo is 0 and is less than 70 is a re-occurance. Overall, though, I've found approach [a] to be easier and more convenient. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of b

[SQL] Wierded error in recursive function; debugging ideas?

t does use arrays. I did try tinkering with some of the functions internals without apparent effect. I also checked for in_array and it's not a visible built-in function. Is this maybe a PostgreSQL bug? Version is 7.4.1 -- -Josh Berkus "A developer of Very Little Brain" Agli

Re: [SQL] Wierded error in recursive function; debugging ideas?

events'', child_rec.event_id, NULL) <> ''OK'' THEN RETURN ''LOCKED: One or more of the child events of the current event are locked by '' || ''another us

Re: [SQL] Wierded error in recursive function; debugging ideas?

Joe, > Are you sure this message isn't coming from some PHP middleware, e.g. > peardb or something. See: > http://us2.php.net/manual/en/function.in-array.php Hm ... possible. Will check with my PHP guy. Would explain why I've not been able to track down the error. -

Re: [SQL] [PERFORM] Performance Problem With Postgresql!

e may not help you if they feel you are being rude. Richard H has posted the solution to your problem. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] multi column foreign key for implicitly unique columns

nobody's really interested enough. However, you have an easy way out: ALTER TABLE objects ADD CONSTRAINT obj_unq_2 UNIQUE (name, class_name); This will add the unique constraint that Postgres wants without changing your data at all. -- Josh Berkus Aglio Database Sol

[SQL] SQL Challenge: Arbitrary Cross-tab

a row-by-row procedural loop? (to reiterate: I'm not allowed to use a custom aggregate or other PostgreSQL "advanced feature") -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off al

Re: [SQL] SQL Challenge: Arbitrary Cross-tab

we only run this bill once a month. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] SQL Challenge: Arbitrary Cross-tab

I tried Stephan's idea, it works, but it's so slow that we're going to to the procedural loop. Thanks, all! -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive F

Re: [SQL] multi column foreign key for implicitly unique columns

int, but there is no way in standard SQL to create an FK for it.This is one of the places I point to whenever we have the "SQL is imperfectly relational" discussion. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)-

Re: [SQL] SQL Challenge: Arbitrary Cross-tab

213447 | 047 | | | | | | | Darn I wish this didn't have to be portable .... -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-noma

Re: [SQL] multi column foreign key for implicitly unique columns

selected for the FK class_name, field_name relates to the same class_name in objects. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command

Re: [SQL] multi column foreign key for implicitly unique columns

of completely redundant data. :-( I'll wait for ASSERTIONS, I think. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

<    3   4   5   6   7   8   9   >