Re: [SQL] AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better)

on a bunch of other tables. What is it doing? Figure out/get rid of that and you're problem will go away. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-sql mailing list

Re: [SQL] Advice returning data as an array

/arrays.html#ARRAYS-IO What would help us help you past that is if you show what you have already tried so we know where you need correction/help. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- S

Re: [SQL] Taking the cache out of the equation?

eries would normally only be called once for the same target data.What tricks are there to flush, ignore, circumvent the caching boost? (Especially in the production environment.) Why on earth would you want your queries to always go to disk? Erik Jones, Database Administrator Engine

Re: [SQL] left join where not null vs. inner join

re asking about. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- 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] pg_stat_activity return all connections

c, am I right? Yes. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- 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] How to count from a second table in an aggregate query?

tact_id = cp.contact_id and log_type in ('web', 'detail') order by src_contact_id; src_contact_id | log_type | count +--+--- 1 | web | 4 1 | detail | 4 1 | web | 4 1 | web

Re: [GENERAL] [SQL] bash & postgres

On Mar 23, 2009, at 7:05 AM, Tom Lane wrote: Erik Jones writes: On Mar 22, 2009, at 9:03 PM, Greenhorn wrote: How do I use \c (or any other psql commands beginning with a "\") in a bash script? For multi-line input to a psql call in a bash (or any decent shell) script, I&#x

Re: [SQL] bash & postgres

) in a bash script? For multi-line input to a psql call in a bash (or any decent shell) script, I'd use a here document: #!/bin/bash #!/bin/bash two="2" psql -d pagila <(P.S. Your quotes around $two in your original are not needed, in fact they're straight up

Re: [SQL] [GENERAL] pg_restore error - Any Idea?

-Fc will contain a table of contents of all of the database objects in the dump file. Something in that is causing an error for pg_restore. Does the version of pg_restore match up with the version of pg_dump that you used to make the dump? Erik Jones, Database Administrator Engine Yard

Re: [SQL] Way to eliminate pg_dump activity from pg_stat_all ?

e rest of the activity. It sounds like the proper wording for a feature request here would be something like "Disable stats collection on a per-session basis". Erik Jones wrote: I doubt it. From the server's perspective, pg_dump is just a client executing queries. If the db i

Re: [SQL] Derived columns / denormalization

he groups table with the total update values for each groups entry with updates. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make chang

Re: [SQL] some howto/theory book/tutorial on practical problem solving in SQL

esign strategies for selected problems". O'Reilly's SQL Hacks is a good one that fits the bill you describe. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-sql mailing list

Re: [SQL] Object create date

served being placed in a COMMENT for the object. That would have the added bonus of being able to search in one place (pg_description) across all objects of all types for a given creation/modification date. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliabilit

Re: [SQL] Way to eliminate pg_dump activity from pg_stat_all ?

g_statio_all_tables where schemaname='public' order by 1 desc; But I think I'm getting clutter from the nightly backups. Is there a way to keep pg_dump activity out of the statistics? I can think of several reasons to want such activity excluded, not just this one. Erik Jones, Da

[SQL] Array from INSERT .. RETURNING in plpgsql?

EATE FUNCTION Time: 3.319 ms pagila=# select testfun(); ERROR: array value must start with "{" or dimension information CONTEXT: PL/pgSQL function "testfun" line 6 at execute statement Is there any way to do what I'm trying without explicity looping over the results of

Re: [SQL] First day of month, last day of month

I'm used to AFAIR, As Far As I Rekall... :) Or AFAICS, As Far As I Can See Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com -- Sent via pg

Re: [SQL] First day of month, last day of month

On Apr 24, 2008, at 9:52 AM, Terry Lee Tucker wrote: On Thursday 24 April 2008 10:47, Bart Degryse wrote: > Well, that's what it does afaikt. afaikt -> as far as I can tell. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps or

Re: [SQL] First day of month, last day of month

extract(month from now()) -> 4 date_trunc('month', now()) -> 2008-04-01 00:00:00-05 I typically find date_trunc much more useful but I may just think that because I've been writing partitioning code a lot lately. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.29

Re: [SQL] Problem with FOREIGN KEY

sers table that doesn't satisfy the foreign key constraint, i.e. there is a Users row with SecurityRoleId=0 and no row in SecurityRole with ID=0. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate &a

Re: [SQL] Testing for null record in plpgsql

bt swapping in integer or dates will be difficult) and a test suite written I'll probably throw it up on github since people often ask how to do this kind of thing. On Apr 11, 2008, at 2:40 AM, Craig Ringer wrote: Erik Jones wrote: Now, let's say I want to call this from another

[SQL] Testing for null record in plpgsql

e(); IF row(res.*) IS DISTINCT FROM row(null_rec.*) THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; $$ LANGUAGE plpgsql; Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations e

Re: [SQL] advocacy: case studies

hared-nothing parallel cluster environment with FPGA acceleration." ? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql Erik Jones DBA | Emma

Re: [SQL] Finding all References to a Primary Key

that reference a particular person in the people table? pg_catalog.pg_constraint has that info. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.

SOLVED - Re: [SQL] Dynamic sql and variable record types

On Mar 20, 2008, at 5:28 PM, Erik Jones wrote: Hi, I've been working on a generic date partitioning system and I think I've reached something that can't be done, but I thought I'd post a question to the masses in the hope that I'm missing something. The basic

[SQL] Dynamic sql and variable record types

the INSERT statement. But, I can't see how to use a record in query passed to EXECUTE. Am I right in thinking (now) that this can't be done? Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate &am

Re: [SQL] Select into

can write that as: update t1 set (col2, col3) = (t1copy.col2, t1copy.col3) from t1 as t1copy where t1.col =1 and t1copy.col1=3; Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visi

Re: [SQL] Create on insert a unique random number

quences and for sent messages generate a random id using md5(now()::text). In a sense, then, we have "public" and "private" keys. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate &a

Re: [SQL] trigger for TRUNCATE?

TABLE rather than DELETE> Not that DDL statement triggers wouldn't be just as useful for replication. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in styl

Re: [SQL] Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?

grow quite a bit more than 60 or 70... Say 200. Then you could have it so that the random chopper function only gets kicked off every 100th or so time. I like that idea. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everyw

Re: [SQL] How to keep at-most N rows per group? periodic DELETEs or constraints or..?

#x27;t running user history report queries constantly that's probably what I'd do. Also, if you're sure you won't need anything but the last 50 records per user, I'd definitely agree with cleaning out data that's not needed. Erik Jones DBA | Emma® [EMAIL PROTEC

Re: [SQL] How to keep at-most N rows per group? periodic DELETEs or constraints or..?

WHERE user_id=NEW.user_id ORDER BY timestamp DESC OFFSET 50); Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style

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

test(col1, col2, col3); returning test - 5 8 12 Is giving the max of the three columns for each row. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at h

Re: [SQL] reading WAL files in python

one has already suggested, if you want to learn more about Postgres and Python, look at Skytools. I'm not just saying to use it, read the code and, if you like, offer help with patches. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma

Re: [SQL] temp table existence

t. I don't know, that feels cleaner to me than TRUNCATEing a table that might not be available to the session. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in s

Re: [SQL] Advice for generalizing trigger functions

On Dec 27, 2007, at 12:03 PM, Richard Broersma Jr wrote: --- On Thu, 12/27/07, Erik Jones <[EMAIL PROTECTED]> wrote: TG_TABLE_NAME will have the name of the table the trigger was fired on. With that and using EXECUTE for your INSERT statements, you'll probably be set. True the

Re: [SQL] Advice for generalizing trigger functions

the name of the table the trigger was fired on. With that and using EXECUTE for your INSERT statements, you'll probably be set. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & m

Re: [SQL] System catalog future changes

er input on the above SQL - should I be doing this in another way? Thanks for any thoughts or advice, If all you're looking for is regular tables, the I'd use the pg_tables view. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fa

Re: [SQL] Describe Table

llecting info on table, but seems there must be an easier way. I desire to create a standard type SQL dump syntax. If you start psql with the -E flag, it will display all sql generated by internal commands such as those generated by \d commands. Erik Jones Software Developer | Emma® [EMAIL

Re: [SQL] Trigger definition . . . puzzled

g from a pgAdmin 1.8 setup. The function you've shown won't do anything because BEFORE row triggers that return NULL don't do anything (for that row). If you want the operation to continue without any modification then just return NEW. Erik Jones Software Developer | Emm

Re: [SQL] Function result using execute

THEN curr_amount := 0; END IF; should do. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com --

Re: [SQL] Foreign Key for multi PK or design question

ou're going to have to give a more concrete example of what it is you're trying to do, i.e what those questions are, table structures, etc. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere commu

[SQL] Rule rewrite to possible union?

application code changes wherever possible. Is there any way I can make this happen? Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us on

Re: [SQL] execute system command from storage procedure

On Dec 6, 2007, at 10:32 AM, Sabin Coanda wrote: Hi there, Is it possible to execute a system command from a function ? (e.g. bash ) If you use one of the untrusted procedural languages (e.g. plperlu, plpythonu, ...) you can. Erik Jones Software Developer | Emma® [EMAIL PROTECTED

Re: [SQL] NULLIF problem

parison--and the cast--are done.) I think you may need to handle this is you middleware, or handle the IF THEN explicitly in a function. Maybe CASE would work: CASE WHEN mytime = '' THEN NULL ELSE CAST(mytime AS TIMESTAMP) END Why not just: UPDATE table SET mytime=NULL

Re: [SQL] design of tables for sparse data

Name, Maths, English, Sports, History) (42, Frank Miller, yes, yes, yes, no ) (43, Suzy Smith, yes, no, no, yes) You should look into the crosstab contrib package. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations ev

Re: [SQL] JOINing based on whether an IP address is contained within a CIDR range?

eering 7, '/index.html', 132828282, Engineering I'm wondering what the best way of doing this is (considering that http_log could have >10 rows) Is it possible to somehow JOIN using the <<= and >>= network operators? Or would I have to iterate the network_na

Re: [SQL] get only rows for latest version of contents

her little trick that can come in handy for this: SELECT DISTINCT ON (Idnode) Idnode, version_no, c1, c2, c3 FROM business ORDER BY Idnode, version_no DESC; Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere

Re: [PERFORM] [SQL] two queryes in a single tablescan

Note Markus's point that both queries must be initiated by concurrent connections. Since Postgres doesn't have any kind of shared transaction mechanism across connections then this is inherent. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 6

Re: [SQL] Accessing field of OLD in trigger

mple Right, "dynamic variables" aren't available in plpgsql. Check out any of the other pl languages available if you can. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere com

Re: [SQL] pg_dump question

store, i.e postgres takes care of those for you. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com -

Re: [SQL] Finding broken regex'es

On Oct 2, 2007, at 10:48 PM, Tom Lane wrote: Erik Jones <[EMAIL PROTECTED]> writes: IIRC, if they're PERL compatible which it would seem from the php function you're using, no. Postgres supports POSIX regexes but not (right now anyway) PERL regexes. Actually what we suppor

Re: [SQL] Finding broken regex'es

. Is there any way to do this directly within the db ? IIRC, if they're PERL compatible which it would seem from the php function you're using, no. Postgres supports POSIX regexes but not (right now anyway) PERL regexes. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800

Re: [SQL] Many databases

On Sep 23, 2007, at 11:56 PM, Erik Jones wrote: On Sep 21, 2007, at 6:16 PM, Stefan Arentz wrote: Well I'm just toying with an idea. The problem I'm facing is that I would like clients to only see the tuples that they have created and own. I guess I'll just skip direct sql a

Re: [SQL] Many databases

hat manages the data. Not a big deal but it complicates things :-) You could do the same thing with views on those tables. One problem with multiple databases is keeping global meta data for all of your customers together in a simple way. Erik Jones Software Developer | Emma® [EMAIL PROTECTE

Re: [SQL] postgresql HEAD build failure

lines. (Of course, repository- wide versioning another common reason.) Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http:/

Re: [SQL] Database normalization

be really strict about things. If you want to make querying the table simple for either case create Clients and Services views on the table. This also gives you the ability to add other entity types where you may to track whatever kind of updates these are. Erik Jones Software

Re: [SQL] Block size with pg_dump?

-bytes, will the rest of each block get skipped? I.e., do I have to use dd on the way back too? And if so, what should the blocksize be? Postgres (by default) uses 8K blocks. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps

Re: [SQL] Increment a sequence by more than one

le, as long as you supply the the id it won't generate a new id and you'll maintain your row-row relationships. If you do require that the block not have gaps, check out the article on how to do this here: http://www.varlena.com/ varlena/GeneralBits/130.php Erik Jones Software Devel

Re: [SQL] Foreign Key inter databases

gn key from customer db to main db. Not directly as pg constraints, no. But, what you can do is create a trigger that simulates the same effect. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate &am

Re: [SQL] How do I connect postgres table structures and view structures to an existing svn repository?

ly why things changed. Another good idea is to include in these delta (or migration) scripts the necessary sql to rollback the change. Then it's not too hard to write a tool that you can give db connection params and a version # to sync to. Erik Jones Software Developer | Em

Re: [SQL] Using escape strings in an insert statement.

th standard_conforming_strings turned on, it would just need to be: INSERT INTO test (test_text) values ('abc\123'); Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL proje

Re: [SQL] [GENERAL] yet another simple SQL question

e the following: firstname --- John Mark Jennifer Thanks again for all of your help today. Everything you guys have been sending has produced successful results. Try something along the lines of: SELECT substring(firstname from '^(\w*)\W') from table_name; Erik

Re: [SQL] new idea

On Apr 9, 2007, at 9:14 AM, Andrew Sullivan wrote: On Mon, Apr 09, 2007 at 09:11:57AM -0500, Erik Jones wrote: I don't really even see the need for inheritance here. This is what most ORMs do at the application level already. Wel, sure, but the poster seemed to think that having a w

Re: [SQL] new idea

this idea future ? What are you think ? What does this do that inheritance doesn't already do? I don't think I see anything. I don't really even see the need for inheritance here. This is what most ORMs do at the application level already. erik jones <[EMAIL PROTECTED]&g

Re: [SQL] Droping indexes

ation? http://www.postgresql.org/docs/8.2/interactive/sql-createindex.html -- erik jones <[EMAIL PROTECTED]> software development emma(r) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] join/group/count query.

ead those count statements wrong and the crosstab contrib is what you're looking for. -- erik jones <[EMAIL PROTECTED]> software development emma(r) ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] join/group/count query.

ds to what you are actually trying to do, giving us your table definitions and what you are trying to achieve would help a lot more than just telling us the problem you are having. The column names in your query are in no way descriptive and tell us nothing about your actual table structure. --

Re: [SQL] Finding gaps in scheduled events

is null; With your test data, it shows all the times except for 8:30, 9:30 and 9:45. Nice! And, he can run that query again, flipping the 15 to 30, to get the list of available 30 minute gaps. That's a heck-of-a lot simpler than the stuff I discussed earlier. -- erik jones <[EMA

Re: [SQL] Finding gaps in scheduled events

rent row's a.finish and the next's a.start to get the gap (with a special case to handle the last scheduled event). -- erik jones <[EMAIL PROTECTED]> software development emma(r) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] COALESCE and GROUP BY and AGGREGATES

Volkan YAZICI wrote: On Nov 13 10:49, Erik Jones wrote: Ok, here's a sample table for the question I have: CREATE TABLE sales_table ( sale_type varchar default 'setup' not null, sale_amount numeric not null sale_date timestamp without timezone default now()); So, let&#x

[SQL] COALESCE and GROUP BY and AGGREGATES

e; but, no dice. Any ideas? I know I can break this out into separate queries for each type and the COALESCE will work, but in my real-world situation I have a lot more than three types and that'd be ugly. Thanks, -- erik jones <[EMAIL PROTECTED]> software development emma(r)

Re: [SQL] Wildcard LIKE and Sub-select

looking into full-text? I have roughly 10 million keywords and 1 million badwords. Thanks, Travis Hmm... Maybe (this is untested): DELETE FROM keywords USING badwords WHERE keyword ILIKE ANY (SELECT '%' || badword || '%' FR

Re: [SQL] i have table

Aaron Bono wrote: On 10/4/06, *Erik Jones* <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>> wrote: There is one non-SQL related reason that I like to be able to order columns, at least the way they are displayed whenever the table is described: human comprehension. Fo

Re: [SQL] i have table

er. - use CREATE TABLE ... AS SELECT to select the data into a new table, drop the old table, rename the new one to the old one. In both cases, you've to recreate all missing indices, foreing key constraints etc. HTH, Markus -- erik jones <[EMAIL PROTECTED]> software devel

Re: [SQL] error with mor than 1 sub-select

ives? http://archives.postgresql.org -- erik jones <[EMAIL PROTECTED]> software development emma(r) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] UPDATE with WHERE clause on joined table

M clause is where you put relations other than the one you are updating. Try this: UPDATE customer SET language='Spanish' FROM address ad, country co WHERE customer.eoid=ad.eoid_customer AND ad.eoid_country=co.eoid AND co.country_name='SPAIN' AND customer.email LIKE '%.es'

Re: [SQL] Disk is full, what's cool to get rid of?

d, etc...), there really isn't much of a point as this database is for development only and is only going to be around for about another month when we build a whole new pristine development db and environment from the ground up (I can't wait!), but these are all good things to know. --

Re: [SQL] Disk is full, what's cool to get rid of?

2006, Erik Jones wrote: Awesome. Do I need to reset that to any magic # after the vacuum? I'm not all that up on filesystem maintenance/tweaking... Scott Marlowe wrote: I can't tell you the number of times that little trick has saved my life. On Thu, 2006-07-27 at 11:32, Jeff F

Re: [SQL] Disk is full, what's cool to get rid of?

bably just "tune2fs -m 0 " to give yourself enough space to get out of the jam before you go deleting things. Then you might want to vacuum full afterwards. On Thu, 27 Jul 2006, Erik Jones wrote: Hello, quick question. I've run into an issue with the disk that my develop

[SQL] Disk is full, what's cool to get rid of?

tself? Any suggestions would be greatly appreciated... -- erik jones <[EMAIL PROTECTED]> software development emma(r) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail

Re: [SQL] CREATE TABLE AS inside of a function

ing concatenation to build the string if you're using variables from the function in the query. Pl/pgSQL doesn't have any variable substitution inside of strings (like in double quoted string in PHP) which is why you need to use the concatenation bit. --

Re: [SQL] Table Join (Maybe?)

times I've spent banging my head against the proverbial wall (you do have a proverbial wall don't you?) trying to get these kinds of queries to work with joins, sub-queries, case statements, etc... only to come back to using union on simple, to-the-point queries. -- erik jones &l

Re: [SQL] SELECT substring with regex

BASE_NAME That will match everything up to the first mm or cm. Note that you don't have to worry about the second set of brackets returning anything as the regexp version of substring only returns what is matched by the first parenthesised subexpression. -- e

Re: [SQL] Problem with array subscripts in plpgsql trigger function

Aaron Bono wrote: On 7/5/06, *Erik Jones* <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>> wrote: Aaron Bono wrote: > On 7/5/06, *Erik Jones* <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> <mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>&

Re: [SQL] Problem with array subscripts in plpgsql trigger function

Aaron Bono wrote: On 7/5/06, *Erik Jones* <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>> wrote: Ok, I have a trigger set up on the following (stripped down) table: CREATE TABLE members ( member_id bigint, member_status_id smallint, member_is_delete

[SQL] Problem with array subscripts in plpgsql trigger function

ional assignments I use the values in status_deltas to update another table holding status totals here*/ END; $um$ LANGUAGE plpgsql; on the two lines that access set array values I'm getting the following error: ERROR: invalid array subscripts What gives? -- erik jones <[EMAIL PROTECT

Re: [SQL] Alternative to Select in table check constraint

active for each employee. If you changed the contstraint to: CHECK ( 2 > .) then you'd be able to unset the active status and then set a new one to active. -- erik jones <[EMAIL PROTECTED]> software development emma(r) ---(en

Re: [SQL] avg(interval)

just over nine days - > > Uh ... how do you arrive at that conclusion? I haven't done the math, > but by eyeball an average of four-something days doesn't look out of > line for those values. It seems he's calculating (7 + 9 + 23 + 4 + 3) / 5 ...