Unique indexes can be partial, i.e. defined with a where clause (that must
be included in a query so that PostgreSQL knows to use that index) whereas
unique constraints cannot.
JORGE MALDONADO wrote
> I have search for information about the difference between "unique index"
> and "unique constrain
t I cannot find any function that will replace all instances of a
string AND can base it on a regular expression pattern. Is there a
better way to do this in 9.1?
You were on the right track with regexp_replace but you need to add a
global flag:
regexp_replace(column_name,'\W','','g')
See examples under
http://www.postgresql.org/docs/9.1/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP
Cheers,
Steve
On 01/07/2013 11:44 AM, Emi Lu wrote:
Is there a function to split a string to different rows?...
Have you looked at regexp_split_to_table?
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref
u get to a newer version (anything past 8.3? 8.4) you can use the
new upgrade tools moving forward to minimize downtime during the upgrade
process but you will *always* need to test and evaluate before deploying.
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make
eries(1,15) as allnumbers where
allnumbers not in (select anumber from fooo);
They all give you the same result. The "right" choice will depend on the
size of your table, how it is indexed, how fully it is populated and
even on your version of PostgreSQL. (Apologies for the funky fi
4
6
8
10
12
14
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
On 03/27/2012 07:48 AM, Rehan Saleem wrote:
well i am quite sure its PostgreSQL forum and it is obvious, i am
asking this to concatenate in plpgsql.
*From:* Steve Crawford
*To:* pgsql-sql@postgresql.org
*Sent:* Monday
e1 where userid=' + 5
exec(sqi)
where 5 is the userid from table1
thanks
Cheers,
Steve
statements that need a longer timeout you can change just for those
statements.
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
g
intervals across DST boundaries, have changed over time. IIRC most of
those changes were pre-8.3 but haven't looked recently.
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
you are thinking about a *time* with time zone (a type that
exists due to SQL requirements but which is a somewhat nonsensical type,
the use of which is not recommended):
http://www.postgresql.org/docs/9.1/static/datatype-datetime.html#DATATYPE-TIMEZONES
Cheers,
Steve
--
Sent via pgsql-sql mailin
ression match and regexp_replace to ensure
that the pattern is anchored at the end of the field and includes the
"@" sign in the expression to avoid accidentally matching something like
...@theholyghost.org.
You can always do a select of the emp_email alongside the replacement
express
much other goodness.
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
raint?
Try updating the values in both tables within a transaction with
constraints set to deferred:
http://www.postgresql.org/docs/current/static/sql-set-constraints.html
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
with extra work to properly
handle the first 10-days of each year.
Alternately, you could have a separate table that just tracks the
creation dates of the temporary tables and be free from any requirement
to have dates be part of the table names.
Cheers,
Steve
--
Sent via pgsql-sql mailing li
spect that your first step should be
to check pg_config to see if the server from which you are attempting to
recover data was compiled with --enable-integer-datetimes.
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
get "invalid reference to
FROM-clause entry for table apps".
So my questions are: 1) How do we cause the paymentcalc function to be
executed only once? and 2) How do we call a table returning function
with inputs from a table?
Thank you very much!
Steve
--
Sent via pgs
ndexes though an index on ts will
probably suffice for most cases.
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
http://www.postgresql.org/docs/9.0/static/rowtypes.html on composite
data types and scroll to section 8.15.3.
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
~~* '%text%';
count
---
98
(1 row)
Shouldn't it be 99? That is out of 100 records there is one that has
"text" in column "col" so the !~~* should return 99 rows. ??
-wes
select count(*) from table where col is null;
(null is neither equal nor not-equal to
string ''.
There might be a more elegant way.
Is there ?
regards
Look at regexp_replace()
http://www.postgresql.org/docs/9.0/static/functions-string.html
http://www.postgresql.org/docs/9.0/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP
Cheers,
Steve
--
Sent vi
On 05/14/2011 07:36 PM, Jasen Betts wrote:
use the "NOT IN" operator with a subquery to retch the disallowed
values
Hmmm, "retch" as a synonym for "output"? I've seen more than one case
where that is an appropriate description. :)
Cheers,
Steve
--
Sen
nes but
it would be well worth your time to carefully read
http://www.postgresql.org/docs/8.4/interactive/datatype-datetime.html
(IIRC, you are using 8.4) a couple times.
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
RT_TO_UTC( '2011-03-22 14:17:00+01'::timestamptz
,'CET'),'-mm-dd hh24:MI:SS') AS winter, to_char(CONVERT_TO_UTC(
'2011-04-22 14:17:00+02'::timestamptz ,'CET'),'-mm-dd hh24:MI:SS')
AS summer;
If you can use the correct time zone name, ever
27;
else 'night'
end as shiftname
This can be used for grouping as well as display.
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Original-Nachricht
> Datum: Sun, 12 Sep 2010 01:52:04 +0400
> Von: Dmitriy Igrishin
> An: Steve
> CC: pgsql-sql@postgresql.org
> Betreff: Re: [SQL] Question about PQexecParams
> Hey Steve,
>
> 2010/9/11 Steve
>
> > Hello list,
> >
&
Original-Nachricht
> Datum: Sat, 11 Sep 2010 11:08:00 -0400
> Von: Lew
> An: pgsql-sql@postgresql.org
> Betreff: Re: [SQL] Question regarding indices
> On 09/11/2010 08:29 AM, Steve wrote:
> > I have a small question about the order of values in a query
Original-Nachricht
> Datum: Sat, 11 Sep 2010 11:04:16 -0400
> Von: Tom Lane
> An: "Steve"
> CC: pgsql-sql@postgresql.org
> Betreff: Re: [SQL] Question regarding indices
> "Steve" writes:
> > I have a small question about the o
Original-Nachricht
> Datum: Sat, 11 Sep 2010 10:05:18 -0400
> Von: Michael Gould
> An: Steve
> Betreff: Re: [SQL] Question regarding indices
> Steve,
>
Hello Michael,
> If I remember correctly the sort only works on the final result set and so
>
Can any one help me with this?
// Steve
--
GMX DSL SOMMER-SPECIAL: Surf & Phone Flat 16.000 für nur 19,99 Euro/mtl.!*
http://portal.gmx.net/de/go/dsl
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
the
SQL query with ordered data influence the speed of the query?
// Steve
--
GRATIS: Spider-Man 1-3 sowie 300 weitere Videos!
Jetzt freischalten! http://portal.gmx.net/de/go/maxdome
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http
something wrong there.
I saw very bad clock performance on one Linux box I had (dual-single core
AMD cpus, no VMs), even with NTP, until I changed the clocksource kernel
parameter to hpet. Unfortunately (or fortunately) I no longer have that box.
--
Steve Wampler -- swamp...@noao.edu
The gods that
I'm working on a web app for a quality control checklist. I already
have a table set up, but I have a hunch that our model is sub-optimal
and I could get some better performance.I'm hoping someone on this
list can help me think clearly about how to express this efficiently
in SQL.
Each checklist h
is that the multi-table scenario
will be better suited to flagging aggregates for suppression.
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
integer part of the number, which math function can do this for me?
For example, I have 3.900 and I need only the 3 (the integer part), which math
function to be used?
floor(3.900)
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription
nt1::text || '-' || int2::text ||
'-1')::date and
date_trunc('month', date_to) >= (int1::text || '-' || int2::text ||
'-1')::date
...
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
is
moved from the "current" to the "historical" table and the new one added
to the "current" table. The latest status report will only need a simple
join on the "current" table with a max size of 100,000 rather than a
more complex query over a 100,000,0
...
canon=# select count(maf) from gallo.sds_seq_reg_shw
canon-# where maf ISNULL;
count
---
0
(1 row)
I believe count will only count not-null anyway so this will always
return zero. Try count(*) instead of count(maf). Here's an example:
st...@[local]=> select * from
Date: Wed, 15 Apr 2009 21:23:04 -0700
From: Steve Midgley
To: Erik Jones
Subject: Re: How to count from a second table in an aggregate query?
Message-ID: <49e6b2a8.5040...@misuse.org>
Erik Jones wrote:
>
> On Apr 15, 2009, at 1:15 PM, Steve Midgley wrote:
>
>>
Erik Jones wrote:
On Apr 15, 2009, at 1:15 PM, Steve Midgley wrote:
I want to generate an analysis report that counts the values in two
separate tables. I've been able to accomplish what I want with two
separate queries that I then merge together in Excel. Essentially
what I need
both queries side-by-side along with the FK id's
in a single result set
Thanks for any assistance on this!
Steve
/*SQL STARTS*/
drop table if exists contact_log;
drop table if exists contact_property;
create table contact_log(id serial NOT null, src_contact_id integer,
log_type character
3,4 for the
ordering, but then you have to mess with two records in order to swap
the positions of (say) item 2 and 3. Of course you can do this pretty
easily inside a transaction, and you don't have to worry about the mess
of moving PK's.
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
reason.
How about this:
select round.*, stage.name from round
left join stage on stage.id = round.stage_id
ORDER BY round.score DESC;
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
sks
these days, but for a read-only database, that seems like an option
(10gb of ram disk for your read-only data and 6 gb of ram for OS and
Pg).
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
rebuilding them manually later?
Thanks for any insight on that (and I hope my question helps the OP as
well - if this seems off topic let me know),
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
ot;table_name" and then looking at all
the "column_name" fields, building your column
list, excepting the column_names you wish to exclude..
Best,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
y'),
('Rob Roy', '(2,0)', 'today'),
('Rob Roy', '(2,0)', 'today');
-- SOLUTION QUERY HERE --
select count(user_tracker.id), locations.name from user_tracker
right outer join locations on user_tracker.location <@ locations.area
group by locations.name;
-- END SQL SCRIPT --
OUTPUT:
3;"Manhattan, NY"
0;"Talahassee, FL"
6;"Frankfurt, GE"
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
ors for this along
with a GiST index - it's not perfect for long distances - it assumes
the earth is flat, but it works great for small distances and is very
fast).
I hope this helps. Feel free to contact me on-list or off, if you want
to discuss more.
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
such a utility I would like it to be
reviewed/evaluated?
Thanks a lot guys.
--
"If you would take, you must first give, this is the beginning of
intelligence" -- Lao Tze, Tao Te Ching
Steve L. Nyemba
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make chang
nce would be anything comparable either (I'd guess that using
the "regex" operators (like "~*" would be the way to go).
I thought I'd mention this other approach in case it was of interest
and you haven't run across it before.
Sincerely,
Steve
--
Sent
d solutions, questions and ideas back to this list as
you go. I've forwarded this thread already to several people who work
with related issues, and they're very interested in some solutions as
well.
So stay in touch as you work on this, please.
Sincerely,
Steve
--
Sent via pgsql-sql
works even on the command line, which is helpful. You can type this
directly into the CMD prompt now:
dir "c:/temp"
All new programs I write on Windows (in Ruby) use forward slashes for
paths, and it works just fine. Not sure about VB or C#, but I'd guess
you can make it work. Might be simpler than all the escaping work..
Best,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
;
CREATE TABLE test
(
filename character varying(255) NOT NULL,
data bytea
);
insert into test (filename, data)
values (E'c:\\tmp\\tst.tif', '1234');
select replace(filename, E'\\', E''), data from test
Does this do it?
Steve
--
Sent via pg
t;Working with binary data and bytea
datatype" and try the example code there. Just feed it your images as
input and all should be good.
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
eople_id = p.people_id)
JOIN test_attributes b
ON ((b."people_id" = p."people_id")
WHERE
(a."attribute" = @firstAttr))
AND (b."attribute" = @secondAttr));
Also, any suggestions about how to figure out this on my own without
bugging the list in the future would be gre
ue, use the coalesce
function:
select coalesce(my_column, 'a null value');
will return the string 'a null value' whenever my_column is null.
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
having to look up the max value on each one (which
would also require that you shut off access to the table and for a much
longer time).
Hope that helps,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
At 11:28 AM 10/23/2008, Joe wrote:
Steve Midgley wrote:
# (invoiceid, txid)
(A, 1)
(A, 3)
(B, 1)
(B, 2)
(C, 5)
(D, 6)
(D, 7)
(E, 8)
(F, 8)
For journalling, I need to group/cluster this together. Is there a
SQL
query that can generate this output:
# (journal: invoiceids, txids)
[A,B
n you want
two columns of output, each column being a pg array?
I may not be the best person to answer the actual SQL question, but I
thought I'd clarify your requirements so the list members can have the
best chance of answering.
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@p
morphic join" but I could be wrong
about
> that. I'd guess you could construct a rule or trigger to validate
the
> foreign key data on insert/update but that's out of my skill area.
Hi Steve,
So in your solution the f_table column is just text which needs to be
validated by
uld construct a rule or trigger to validate the
foreign key data on insert/update but that's out of my skill area.
Hope that helps a little,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
At 09:50 PM 9/29/2008, Richard Broersma wrote:
On Mon, Sep 29, 2008 at 7:48 PM, Steve Midgley <[EMAIL PROTECTED]>
wrote:
> In my specific case it turns out I only had duplicates, but there
could have
> been n-plicates, so your code is still correct for my use-case
(though I
At 05:38 PM 9/26/2008, Oliveiros Cristina wrote:
In-Reply-To: <[EMAIL PROTECTED]>
References: <[EMAIL PROTECTED]>
<[EMAIL PROTECTED]>
<[EMAIL PROTECTED]>
Howdy, Steve.
SELECT id
FROM dummy a
NATURAL JOIN (
SELECT fkey_id,name
FROM dummy
GROUP BY fkey_id,
aps a natural join like in Oliveiros' sql would do the
job?
Thanks for any advice on either of these solutions. I'm going to learn
a lot here if someone can pound it into my head.
Thanks,
Steve
It seems to be returning any records that have sequential id's
regardless
At 11:
uld be a bonus!
It seems like there's a clever way to do this without cursors but I
can't figure it out!
Thanks for any help!
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
great explanation as to the value of natural
keys! You haven't won me over, but you did teach me something - which I
appreciate.
Best,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
er heard someone say they've been bitten by using
an arbitrary surrogate key system, but I myself have been bitten and
have heard lots of stories of problems when using natural keys.
I hope this helps some,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Sorry for the fairly long post.
I'm having a big problem trying to update one table from another in
PostgreSQL 8.3.1.
I have a lookup table called termgroup:
# select * from termgroup;
termgroupname | mindays | maxdays
---+-+-
1-30 days | 1 | 30
31-59 d
t against that in WHERE clause? I could be
misunderstanding the whole thing though..
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
metimes in
middleware and it works pretty well. There's probably a pure-sql
solution in Pg as well but this method should work across any SQL
platform, which seems like one of your requirements.
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
At 03:51 PM 7/31/2008, Tom Lane wrote:
Steve Midgley <[EMAIL PROTECTED]> writes:
> At 07:29 AM 7/16/2008, Tom Lane wrote:
>> I think what is happening is that ORDER BY knows that and gets rid
of
>> the duplicate entries while DISTINCT ON fails to do so.
> Of course rem
u could aggregate against your datetime stamp by N hours or
days as well. If this idea is of interest you can write back to the
list or off-list to me for more info.
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
At 07:29 AM 7/16/2008, Tom Lane wrote:
Steve Midgley <[EMAIL PROTECTED]> writes:
Interesting. You realize of course that sorting by the same
expression
twice is completely redundant? I haven't dug through the code yet but
Thanks Tom. Yeah, I was a little embarrassed to throw this
ate_max" CASE is causing the
problem.
Thanks for any advice or suggestions on how to get this to run
correctly. Is this a bug?
Basically I'm doing this as an optimization - I can get much better
performance running the DISTINCT ON in some circumstances than using
DISTINCT, but the edge case above is breaking my tests and preventing
me from implementing the idea. The code is generated by an application
layer which is not really paying attention to whether or not the two
CASE statements apply to the same field or not (sometimes they do
sometimes they don't)..
Thanks!
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
is really important in general for the list.
There's a lot of good information out there and I think it's not so
great if this list were to limit itself only to public domain and open
copyright documentation for consideration.
Just two more cents from the peanut gallery on a Sat
lication layer developers with a consistent set of
interfaces to obtain data that are not tied to the data tables
themselves. And allowing them to insert/update/manage tables via
structured interfaces as well.
Am I missing something?
Best,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@po
of
the database was created. If you restored from a dump or otherwise
recreated the database for any reason (version upgrade, machine
migration, disaster recovery, etc.), the timestamps would represent the
time of the restore, not the time of the creation of the original database.
Cheers,
Steve
orm against ANSI-92 (or any other std) if you do not regularly
test against a set of platforms, your solution will converge on
supporting only the platforms you do regular test against.
I hope that helps,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To ma
Steve Crawford wrote:
Allan Kamau wrote:
Hi Steve,
Am having difficulties (there is a chance I could be the only one)
trying to see how the results you've listed under "I would want to
get:" section can be generated from the information you have provided
in your implicit pr
Allan Kamau wrote:
Hi Steve,
Am having difficulties (there is a chance I could be the only one)
trying to see how the results you've listed under "I would want to
get:" section can be generated from the information you have provided
in your implicit problem statement.
Remember
might be a day or two. I am looking for a query that will list any
device having no variation in the recent events.
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
op search / coding schemes that say
things like "find all records with chicken and skin, where the
following words do not appear between the words chicken and skin: beef,
pork, cow, pig, etc.."
Just some thoughts for you there.
Best,
Steve
--
Sent via pgsql-sql m
) for a given ndb_no:
select nutrient_no,nutrient_value from nutrient_data where ndb_no =
13473 order by nutrient_value limit 5;
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
from nutrient_data where nutrient_no=203;
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
you there. I'm sure others have different
perspectives which are equally or more valid!
Best,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
l_accents|short_accents...etc
I keep the country names with and without accents to make searching
easier across keyboards/locales.
I hope this helps too -- I think Craig has given you the lion's share
of good advice for sure - and I definitely follow the practices more or
less as he laid them
into yourtable SELECT * from xxx;
DROP TABLE xxx;
If you are so close to out-of-space on your disk that you don't have the
room those 90,000 records will require, you may have to dump/restore
using another machine.
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.or
r ADO driver and see if it gives you the same problems..
Just some more grist for the solution mill, hopefully!
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
gs in ODBC that
caused ODBC to do as absolutely little as possible. Just receive raw
sql, pipe it to my server and hand it back to me in a memory data
structure. All the data structure parsing was done by my middleware
connection wrapper to ODBC and none was done by ODBC itself. Once I got
his caused very poor performance on
large tables. EXPLAIN will not reveal this. You might want to set the
server to log all transactions and see what the app is really doing at
the server level.
Cheers,
Steve
f you index ip's using a
custom data type and search/group for specific octets, you'll get much
better performance than just searching via a regular b-tree string
index..
http://www.postgresql.org/docs/8.3/static/datatype-net-types.html
Steve
--
Sent via pgsql-sql mailing list (pg
es and rollbacks will
cause holes.
If you just need consecutive row-numbering on output (not in the table)
and if the row numbering doesn't need to match the same record each
time, you can create a temporary sequence and select
nextval('tempsequence'), from yourtable.
Cheers
ommand here");
I think you want something more like this psuedo code:
system.set_environment("PGPASSWORD")="pass1234";
system.exec("psql my command here");
I hope this helps,
Steve
clears up what I was recommending! I didn't
anticipate it would stir up this much analysis and I hope the OP finds
your input and mine useful in coming up with a final answer to his
issue. Thanks for taking the time to consider the issue and I'll look
forward to any additi
At 12:36 PM 3/18/2008, D'Arcy J.M. Cain wrote:
On Tue, 18 Mar 2008 12:23:35 -0700
Steve Midgley <[EMAIL PROTECTED]> wrote:
> 1) Create a second field (as someone recommend on this list) that
is an
> MD5 of your primary key. Use that as your "accessor" index from the
ly people here). This will
supplement your sparse index by detecting people who are scanning your
sparse index space and generating lots of "misses."
Hope that helps,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Aarni Ruuhimäki wrote:
Thanks Steve,
I'm not sure if I quite grasped this. It gives a bit funny results:
SELECT sum ((date_smaller(res_end_day, '2007-12-31'::date) -
date_larger(res_start_day, '2006-12-31'::date)) * group_size) AS
days_in_period,
c.country_name AS
p_size
)
Basically remove the "+1" so we don't include both start and end dates
but move the start base back one day so anyone starting prior to Feb 1
gets the extra day added.
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
+ 1) * group_size
) as person_days
group by country_id;
Add where-clauses to either for efficiency.
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
speaking there is
one sequence per primary key, by default on each table. So if you reset
that key, then your table will start issuing keys at that new number.
Another way to be more safe is to "+5" your sequence, so that even if a
few inserts slip in, you're still ahead of the g
1 - 100 of 191 matches
Mail list logo
