Re: [SQL] Subselects returning array and ANY...

2008-02-27 Thread Josh Berkus
rowset of arrays, not a single array. This means that ANY() doesn't know exactly what to do with it. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Subselects returning array and ANY...

2008-02-27 Thread Josh Berkus
res=# select 'x' = ANY ( '{x,y,z}'::TEXT[] ); ?column? -- t -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)

2006-10-09 Thread Josh Berkus
will break if this is > just changed. Many. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Schema management tool

2006-06-27 Thread Josh Berkus
Kyle, Nice to hear from you guys again! Action Target was one of the pioneers of production open source in manufacturing; nice to know that you're still doing well with it. --Josh ---(end of broadcast)--- TIP 9: In versions below 8.0, the plan

Re: [SQL] Programatically Backup Database Using Visual Basic

2006-04-19 Thread Josh Berkus
Paul, > Hi is there a way wherein I can interface the pg_dump of PostgreSQL Server > in Visual basic. > > I mean for example I want to backup Databases "DB1", "BD12", "DB3" using > pg_dump You'd have to run them as shell commands in VB. Not sure

Re: [SQL] Is there any way to stop triggers from cycling?

2006-03-08 Thread Josh Berkus
on the table 2) put a rule on the view to re-order, which re-orders the *table* so there's no cascade 3) use the triggers to do the other tree-maintenance stuff, only for their own rows/children (cascading triggers work *very* well for tree maintenance). -- --Josh Josh Berkus

[SQL] Is there any way to stop triggers from cycling?

2006-03-08 Thread Josh Berkus
2 So I'm trying to come up with a way to ensure that each row is visited only once, but it doesn't seem to be possible. Ideas? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] PGSQL encryption functions

2005-11-01 Thread Josh Berkus
tions? You check out pgcrypto in /contrib in the PostgreSQL source? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] Using descriptor areas to insert

2005-09-22 Thread Josh Berkus
* an Informix person. Could you give an example? Also, if you still have contact, Dave Cramer or Elein should be able to answer this question ... -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: don

Re: [SQL] Indexing an array?

2005-09-08 Thread Josh Berkus
Silke, > I have a problem with arrays in Postgres. I want to create a really > large array, lets say 3 billion characters long. Change your application design. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)-

Re: [SQL] uuid type (moved from HACKERS)

2005-09-07 Thread Josh Berkus
Mark, Nathan, I'm moving this over to the PGSQL-SQL list, away from -hackers, as it's no longer a -hackers type discussion. Hope you don't mind! > On Wed, Sep 07, 2005 at 11:31:16AM -0700, Josh Berkus wrote: > > I'm also a little baffled to come up with any real

Re: [SQL] dates and selection

2005-08-18 Thread Josh Berkus
Joel, > Now I found I had to do something like this just to have a timestamp > (problem is I do not want the format of the time stamp, my clients want to > see the month as a string) Um, what's wrong with: to_char(some_timestamp, 'Mon DD HH:MI:SS') ? -- Jo

Re: [SQL] converting varchar to integer

2005-08-16 Thread Josh Berkus
Tomas, >I've written two on my own (see the functions below), >but maybe there's something faster? Nope. 'cept I'd combine those two functions into a single function that returns NULL if the value isn't an integer. -- --Josh Josh Berkus Aglio D

Re: [SQL] Some help please

2005-07-01 Thread Josh Berkus
mple. Examples are in the /contrib directory where the connectby source is: /contrib/tablefunc/README.tablefunc -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, plea

Re: [SQL] Error saving image to PostgresSQL 8.x database

2005-06-29 Thread Josh Berkus
ace Why are you reading the 7.1 docs and using 8.0? How about reading the 8.0 docs? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] can a function return a virtual table?

2005-04-23 Thread Josh Berkus
d O'reilly can't help (or > i'm to dumb *g*). http://techdocs.postgresql.org/guides/SetReturningFunctions Beware, though, that query plan estimation for SRFs is less accurate than for regular subqueries, so you could end up with unnecessarily slow query execution. Test! -

Re: [SQL] Postgresql FK to MS SQL triggers

2005-03-05 Thread Josh Berkus
he other way? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] crosstab

2005-03-01 Thread Josh Berkus
ever I would appreciate your feeback > before writing this procedure? A proc won't run any faster ... much slower, in fact.Unless you mean that you want to use it to correct the actual table structure, which is what you should be doing? -- --Josh Josh Berkus Aglio Database Sol

Re: [SQL] SQL error: function round(double precision, integer) does not exist

2005-02-28 Thread Josh Berkus
of precision which I do not want. NUMERIC and FLOAT are different data types. Do: round({value}::NUMERIC, {places}) -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] working with multidimensional arrays in plpgsql

2005-01-26 Thread Josh Berkus
Sibtay, > As you might have observed here, the actual problem is > how to do assignment to multidimensional array locations using the > subscript operater. Maybe post your results, too? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of

Re: [SQL] [NOVICE] FUNCTION, TRIGGER and best practices

2004-12-16 Thread Josh Berkus
in two different tables, yes? So that'll require an explicit transaction wrapping the two inserts. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] [NOVICE] Indexing Strategy

2004-12-14 Thread Josh Berkus
queries are *always* structured like the above (time + symbol) I'd suggest CLUSTERing on the index. Also, I hope that you didn't really name a column "time". -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] replacing mysql enum

2004-12-11 Thread Josh Berkus
27;a', 'b', 'c' ); Then you declare the table as: table ( field abc_col, ); I find that DOMAINs give vastly enhanced managability compared to table constraints. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--

Re: [SQL] Cast NULL into Timestamp?

2004-12-10 Thread Josh Berkus
mixing up the column order? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Making dirty reads possible?

2004-12-06 Thread Josh Berkus
s. FWIW, Gavin Sherry is working on CREATE PROCEDURE for 8.1, which may include some ability to have multiple-transaction procedures. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] bibliographic schema

2004-10-18 Thread Josh Berkus
nal keys and perl scripts. I'm afraid that bibliographic references is a rather esoteric need in the OSS community.I personally haven't seen such a thing. You may have to create your own. -- --Josh Josh Berkus Aglio Database Solutions San Francisco -

Re: [SQL] Porting problem from Informix to Postgres...

2004-09-22 Thread Josh Berkus
27;re going to take the trouble of porting an application, you should port it to something current -- Debian Stable or not. Particularly since, in a year, you can expect that the PostgreSQL community will probably stop doing security/stability patches for 7.2. -- --Josh Josh Berkus Aglio Data

Re: [SQL] raise is not working

2004-09-21 Thread Josh Berkus
ession on psql (assuming you're using psql; if you're using a GUI tool, that could be the problem). I've a feeling that your function is erroring out *before* it gets to the raise. -- Josh Berkus Aglio Database Solutions San Francisco

Re: [SQL] Test for file exists?

2004-09-20 Thread Josh Berkus
m in order to prevent database users from being able to compromise it. It's a security thing. You could easily write a function in an "untrusted" language, like PL/PerlU or PL/PythonU, which would test for file existence and return a true/false value. --

Re: [SQL] Isnumeric function?

2004-09-09 Thread Josh Berkus
at prevents us from using it right now, and fixing the bug is complicated. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Isnumeric function?

2004-09-09 Thread Josh Berkus
tial index on the appropriate numeric values: CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~ '^[0-9]{1,9}$'; However, this approach may be more/less effective that the segregation approach you've already taken. -- Josh Berkus Aglio Data

Re: [SQL] Datetime conversion in WHERE clause

2004-09-09 Thread Josh Berkus
with Date::Manip, but use what you like. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] aggregate function stddev

2004-09-09 Thread Josh Berkus
I also noticed that oracle has stddev_sample and stddev_population. > This is just a wish list. Were you aware that in PostgreSQL you can write your own aggregates? It's relatively easy to do. -- Josh Berkus Aglio Database Solutions San Francisco -

Re: [SQL] Isnumeric function?

2004-09-08 Thread Josh Berkus
ld be written: ~ '^([0-9]+)|([0-9]*\\.[0-9]+)$' ... though that still seems inelegant to me. Is there a regex expert in the house? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have

Re: [SQL] How to rename a constraint/trigger??

2004-09-08 Thread Josh Berkus
can rename all > the ""constrains in this database? You can't rename constraints, you have to drop and re-create them under a new name (do this in a transaction for data safety). However, this requires knowing what all those constraints do. I'd suggest doing a text pg_

Re: [SQL] Isnumeric function?

2004-09-08 Thread Josh Berkus
Can't see any easy way, though ... -- 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] colored PL with emacs

2004-08-31 Thread Josh Berkus
Martin, > > I have SQL highlighting, but what I want are colors for the PL/pgSQL > > key words. It would make PL programming much easier. KDE's Kate has PostgreSQL highlighting. Unfortunately, the config is XML so it's not transferrable to Emacs ... -- --Josh Josh

Re: [SQL] Aggregate query for multiple records

2004-08-28 Thread Josh Berkus
ince the date column does have the -year and MM-month > parts. Well, you'd want to convert the column to a timestamp, and then you could compute months. Or you could break it in seperate integer "year" and "month" columns and do the same thing. -- Josh Berkus Aglio

Re: [SQL] Aggregate query for multiple records

2004-08-27 Thread Josh Berkus
urally in a programmming language) 3) Then running your aggregate becomes very easy/fast: SELECT wid, sum(oil) as tot_oil, sum(hours) as tot_hours FROM prd_data WHERE months_prod < 7 GROUP BY wid ORDER BY wid; -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] from PG_DUMP to CVS

2004-08-27 Thread Josh Berkus
ES/sometype.sql some-dbname/some-schema/OPERATORS/OPsomeoperator.sql In this last, all dependant objects of, for example, a table (rules, triggers, indexes, etc. ) would be rolled up into one file. It's this last version that I personally favor. -- Josh Berkus Aglio Database Solutions San F

Re: [SQL] Aggregate query for multiple records

2004-08-27 Thread Josh Berkus
nstances" of that same table. Hmmm ... let me make the distinction more clear: SELECT wid, (SELECT SUM(oil) FROM prd_data prd2 WHERE prd2.wid = prd1.wid ORDER BY "date" LIMIT 6) as tot_oil, (SELECT SUM(hours) FROM prd3 WHERE prd3.wid = prd1.wid ORDER BY &

Re: [SQL] Aggregate query for multiple records

2004-08-26 Thread Josh Berkus
ords and these aggregate queries > take hours. It might. Not for the summaries themselves (which are fastest as aggregates), but to build a table that has only 6 records per WID. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)---

Re: [SQL] from PG_DUMP to CVS

2004-08-26 Thread Josh Berkus
Riccardo, > Looks promising, but still what I need is a proper CVS output, as I > need to review the changes made to the specific database structure. If it's Perl, I'd be interested in contributing. I've long needed something like this myself. -- Josh Berkus Aglio D

Re: [SQL] Complicated "group by" question

2004-08-25 Thread Josh Berkus
E assign_date IN (SELECT max(ass2.assign_date) FROM assign ass2 WHERE ass2.reviewer_id = reviewers.id) or for a bit faster execution on PG you cann replace that WHERE clause with: WHERE assign_date IN (SELECT ass2.assign_date FROM assign ass2 WHERE ass2.reviewer_id = reviewers.id ORDER BY

Re: [SQL] Possible rounding error of large float values?

2004-08-24 Thread Josh Berkus
still possible to find the row using the > original value Hmmm ... is 15 digits the limit of NUMERIC? It may be. > Is this expected behavior? Yes. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: th

Re: [SQL] TSearch2 other languages

2004-08-23 Thread Josh Berkus
nity will really appreciate it if you can do so; it's time we expanded the number of TSearch languages and efforts like yours are how it happens. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get

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

2004-08-19 Thread Josh Berkus
Jan, > Because the value in b.y is redundant. b.x->a.x->a.y is exactly the same > value and he even wants to ensure this with the constraint. And in the absence of that constraint, what ensures that b.y = a.y, exactly? -- Josh Berkus Aglio Database Solutions S

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

2004-08-18 Thread Josh Berkus
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

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

2004-08-18 Thread Josh Berkus
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] SQL Challenge: Arbitrary Cross-tab

2004-08-18 Thread Josh Berkus
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

2004-08-18 Thread Josh Berkus
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

2004-08-18 Thread Josh Berkus
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] SQL Challenge: Arbitrary Cross-tab

2004-08-17 Thread Josh Berkus
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

[SQL] SQL Challenge: Arbitrary Cross-tab

2004-08-17 Thread Josh Berkus
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] multi column foreign key for implicitly unique columns

2004-08-17 Thread Josh Berkus
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

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

2004-08-13 Thread Josh Berkus
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] Wierded error in recursive function; debugging ideas?

2004-08-11 Thread Josh Berkus
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] Wierded error in recursive function; debugging ideas?

2004-08-11 Thread Josh Berkus
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

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

2004-08-10 Thread Josh Berkus
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] Suggestions on storing re-occurring calendar events

2004-08-09 Thread Josh Berkus
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

Re: [SQL] surrogate keys and replication.

2004-08-09 Thread Josh Berkus
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] surrogate key or not?

2004-08-08 Thread Josh Berkus
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 key or not?

2004-08-06 Thread Josh Berkus
'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?

2004-07-23 Thread Josh Berkus
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?

2004-07-23 Thread Josh Berkus
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?

2004-07-22 Thread Josh Berkus
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] ? on announcement of 7.5

2004-07-21 Thread Josh Berkus
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?

2004-07-21 Thread Josh Berkus
Sad, First of all, please excuse me if I've misunderstood you below because of translation issues. You'll find I'm rather strident, but it's because the reasons you're presenting, or seem to be, are excuses for bad database design I hear every day on the job, and end up having

Re: [SQL] surrogate key or not?

2004-07-21 Thread Josh Berkus
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?

2004-07-21 Thread Josh Berkus
unt the number of "bad databases" I've encountered which contained tables with a surrogate key, and NO REAL KEY of any kind. This makes data normalization impossible, and cleanup of the database becomes a labor-intensive process requiring hand-examination o

Re: [SQL] Question about a CIDR based query

2004-06-29 Thread Josh Berkus
--- > > 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] Question about a CIDR based query

2004-06-29 Thread Josh Berkus
-- > 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

2004-06-25 Thread Josh Berkus
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] feature request ?

2004-06-23 Thread Josh Berkus
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 which column(s) are the right foreign key

2004-06-20 Thread Josh Berkus
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] use of a composite type in CREATE TABLE?

2004-06-20 Thread Josh Berkus
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] ERROR: duplicate key violates unique constraint

2004-06-04 Thread Josh Berkus
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 arrays or not?

2004-04-29 Thread Josh Berkus
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] Equivalant of SQL Server's Nchar and NVARCHAR

2004-04-29 Thread Josh Berkus
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?

2004-04-29 Thread Josh Berkus
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] SQL challenge--top 10 for each key value?

2004-04-10 Thread Josh Berkus
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] setting a non-standard date format for the duration of a session

2004-04-10 Thread Josh Berkus
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] hi sir urgent..required a Query

2004-04-10 Thread Josh Berkus
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] Utility of recursive queries?

2004-04-09 Thread Josh Berkus
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] SQL challenge--top 10 for each key value?

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

Re: [SQL] distinct values without seq scan

2004-04-07 Thread Josh Berkus
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] Function To Log Changes

2004-04-05 Thread Josh Berkus
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] [pgsql-advocacy] SQL Spec Compliance Questions

2004-04-02 Thread Josh Berkus
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] [pgsql-advocacy] SQL Spec Compliance Questions

2004-04-02 Thread Josh Berkus
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] Array_append does not work with Array variables in PL/pgSQL?

2004-04-01 Thread Josh Berkus
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] Array_append does not work with Array variables in PL/pgSQL?

2004-04-01 Thread Josh Berkus
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

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

2004-04-01 Thread Josh Berkus
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] left join on a view takes significantly more time.

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

Re: [SQL] SQL Spec Compliance Questions

2004-03-31 Thread Josh Berkus
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] SQL Spec Compliance Questions

2004-03-30 Thread Josh Berkus
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

[SQL] SQL Spec Compliance Questions

2004-03-30 Thread Josh Berkus
?? 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] Slow sub-selects, max and count(*)

2004-03-29 Thread Josh Berkus
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 --

Re: [SQL] perfomance question

2004-03-18 Thread Josh Berkus
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] Compiling pl/pgsql functions

2004-02-22 Thread Josh Berkus
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] User defined types -- Social Security number...

2004-02-22 Thread Josh Berkus
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

  1   2   3   4   5   6   7   8   9   >