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

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] TSearch2 other languages

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] Possible rounding error of large float values?

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] Complicated "group by" question

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] from PG_DUMP to CVS

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] Aggregate query for multiple records

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] Aggregate query for multiple records

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] from PG_DUMP to CVS

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

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] Aggregate query for multiple records

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] colored PL with emacs

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] Isnumeric function?

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] How to rename a constraint/trigger??

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?

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] aggregate function stddev

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] Datetime conversion in WHERE clause

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] Isnumeric function?

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] Isnumeric function?

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] Test for file exists?

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] raise is not working

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] Porting problem from Informix to Postgres...

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] bibliographic schema

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] Making dirty reads possible?

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] Cast NULL into Timestamp?

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] replacing mysql enum

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] [NOVICE] Indexing Strategy

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] [NOVICE] FUNCTION, TRIGGER and best practices

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] working with multidimensional arrays in plpgsql

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] SQL error: function round(double precision, integer) does not exist

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] crosstab

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] Postgresql FK to MS SQL triggers

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] can a function return a virtual table?

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] Error saving image to PostgresSQL 8.x database

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] Some help please

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] converting varchar to integer

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] dates and selection

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] uuid type (moved from HACKERS)

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] Indexing an array?

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] Using descriptor areas to insert

* 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] PGSQL encryption functions

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

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

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] Is there any way to stop triggers from cycling?

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

Re: [SQL] Programatically Backup Database Using Visual Basic

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] Schema management tool

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

Re: [SQL] window function to sort times series data?

he above query, I get: SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque)$re$); regexp_matches {bar,beque} (1 row) Josh -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Compare the resulta of a count sql into bash

er -- psql simply exits with something like 0 or 1 depending on whether the command(s) succeeded. Of course, you could cook up some script to parse the text returned by psql to figure out the rowcounts, but that's a much greater pain than just implementing whatever you're trying to

Re: [SQL] Generic design: char vs varchar primary keys

treatment of trailing spaces), and perhaps the ease of expanding the length constraint in the future. Josh -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] insert or update within transaction

a more general > statement that does an insert if the key doesn't exist or an update if it > allready is there? Sounds like you're looking for the MERGE statement, which doesn't exist in PostgreSQL yet. You might want to check out: http://www.postgresql.org/docs/current/static

Re: [SQL] Partitionning + Trigger and Execute not working as expected

On Tue, Nov 8, 2011 at 11:04 AM, Sylvain Mougenot wrote: > EXECUTE 'INSERT INTO '|| currentTableName || ' values (NEW.*)'; The quotes in the above line are wrong; you want it like: EXECUTE 'INSERT INTO '|| currentTableName || ' values ' || (NEW.*);

Re: [SQL] Partitionning + Trigger and Execute not working as expected

rt_trg BEFORE INSERT ON foo FOR EACH ROW EXECUTE PROCEDURE job_insert_trigger(); INSERT INTO foo (a, b) VALUES (1, 2); Josh -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Partitionning + Trigger and Execute not working as expected

ounds like you're looking for the USING clause of EXECUTE. Try this: CREATE OR REPLACE FUNCTION job_insert_trigger() RETURNS TRIGGER AS $BODY$ DECLARE currentTableName character varying := 'job_' || '2011_11'; BEGIN EXECUTE 'INSERT INTO '|| currentTableName

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

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] How to query by column names

t;SELECT * FROM $tablename" | while etc Worst case, you'll end up with a messy $leftside and $rightside variable set. To answer the original question, the field must be hard coded either as a list or that perhaps over-used(?) asterisk. If you really need to pull and use t

Re: [SQL] INSERT INTO

L statement: > INSERT INTO TABLE2 (TE_INDI) VALUES ('SANT'ANGELO LODIGIANO'); INSERT INTO TABLE2 (TE_INDI) VALUES SELECT TE_INDI FROM TABLE1 WHERE (...) No quoting or server->client->server worries to deal with at all... Best of luck, - Josh Williams ---

Re: [SQL] Alternative to INTERSECT

performance problems is probably less like that the INTERSECT is the problem with all those LIKE's in there? Is t.value indexed? Josh ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] Foreign Key inter databases

s and do foreign keys that way, but I've never used either, so that might not work/make sense/be possible, etc. -Josh ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] pg & Delphi

how to adjust Delphi to use Postgres. > > You should be able to connect via ODBC. Googling "PostgreSQL Delphi" > seems to give some useful links. > Delphi should let you use dlls written in other languages (it did when I used Delphi last, which has been years). So I'd

Re: [SQL] pg & Delphi

i'd like to know all ways to connect Postgres and Delphi)? If I remember correctly, when I was doing MySQL and Delphi (again, it's been years), we didn't use components to talk to the database -- instead, we simply called functions out of libmysql.dll. Were I doing the same

Re: [SQL] Does postgresql8.2 supports multithreading?

PostgreSQL. Consult the documentation for whatever connection library you're interested in using (libpq, JDBC, npgsql, etc.) to find out what thread-related issues you'll need to consider using that library. -Josh ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Accessing field of OLD in trigger

be accessed? > > I'd like to do something like: > > for recvar in 'select OLD.'||quote_ident(TG_ARGV[0])... I THINK you are out of luck here. I hear it's possible to do but in one of the other PL languages say pl/tcl, though I can't seem to find

Re: [SQL] Get the max(value1, value2, value3) from a table

ust, you'll probably have to create your own function to do that. Which should be fairly easy to do if you're working with a static number of columns/data types/etc... - Josh ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

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

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: [SQL] Subselects returning array and ANY...

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] Table to Excel

d query results to a file: \o myresults.html Then run a single query: SELECT col1, col2 FROM foo WHERE bar = '1' ORDER BY baz; \q Now you can open your .html file directly into Excel since it's just a big html table. Josh -- Sent via pgsql-sql mailing list (pgsql-sql

<    4   5   6   7   8   9