ognize everything in the newer server,
-Kevin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
stions
Also, it's not considered good form to post an issue to more than
one PostgreSQL list. This is probably most appropriate on the
pgsql-performance list, so if you want to follow up with more detail
to get more detailed suggestions, it would be best to abandon this
thread and start a ne
The documentation only mentions Visual Studio 2005 and Visual Studio
2008, but I see no reason why it shouldn't work. Check out the
requirements listed in the documentation:
http://www.postgresql.org/docs/9.0/interactive/install-windows-full.html
On Wed, Jul 20, 2011 at 3:55 AM, Sofer, Yuval wro
Have you tried changing the block size?
http://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table.2C_and_a_database.3F
On Wed, Jul 13, 2011 at 11:03 AM, Miguel Angel Conte wrote:
> I have the metadata in the same csv.
>
> On Wed, Jul 13, 2011 at 3:00 PM, Ke
Is there any room for improvement in the data types?
On Wed, Jul 13, 2011 at 11:03 AM, Miguel Angel Conte wrote:
> I have the metadata in the same csv.
>
> On Wed, Jul 13, 2011 at 3:00 PM, Kevin Crain wrote:
>>
>> How are you determining the data types for these columns?
nce if the
> information is all into a same row.
>
> On Wed, Jul 13, 2011 at 3:28 AM, Kevin Crain wrote:
>>
>> I still can't imagine why you'd ever need this...could you explain
>> what this does? I'm just curious now
>>
>> On Tue, Jul 12
I still can't imagine why you'd ever need this...could you explain
what this does? I'm just curious now
On Tue, Jul 12, 2011 at 10:55 PM, Kevin Crain wrote:
> This is an unfortunate situation, you shouldn't be required to do
> this, the people generating your re
You can do full-text search in postgres now using ts_vectors. I'd
recommend going that route. Doing like comparisons is not a good idea
if you don't know the first part of the string you are searching
forIt appears to be much faster from my experience to search for
ab% than it is to search fo
This is an unfortunate situation, you shouldn't be required to do
this, the people generating your requirements need to be more
informed. I would make damn sure you notify the stakeholders in this
project that the data model is screwed and needs a redesign. I agree
that you should split this tabl
> On 2011-07-06, Kevin Crain wrote:
>> That's why you need to do this inside a function. Basically just make
>> an insert function for the table and have it calculate the count and
>> do the insert in one transaction.
>
> you will still get duplicates, so include code
rote:
>>
>> On Tuesday, July 05, 2011 01:11:11 pm Kevin Crain wrote:
>> > You don't need a loop there. Assuming your order id field is of type
>> > varchar you can just build the first part of your string and then do a
>> > count to get the last part usi
My previous reply was intended for John.
On Tue, Jul 5, 2011 at 1:11 PM, Kevin Crain wrote:
> You don't need a loop there. Assuming your order id field is of type
> varchar you can just build the first part of your string and then do a
> count to get the last part using a L
You don't need a loop there. Assuming your order id field is of type
varchar you can just build the first part of your string and then do a
count to get the last part using a LIKE comparison:
select count(id_order) + 1 from sometable WHERE id_order LIKE 'O-20110704 -%';
If you do this inside a
s)))-a.ts) AS duration
from log a order by user_id, project_id, ts) AS foo group by user_id,
project_id, ts) AS day_set group by user_id, project_id, date_trunc
order by user_id, project_id, date_trunc;
-Kevin Crain
On Thu, Jun 9, 2011 at 6:43 AM, Andreas wrote:
> hi,
> I have a lo
My approach would be to add a column for LAST_TS and place a trigger
on insert that populates this new column. Then you have something you
can put in your WHERE clause to test on.
On Fri, Jun 3, 2011 at 12:52 PM, wrote:
> I have a table that, at a minimum, has ID and timestamp columns. Records
Why is (0,20:10) listed in your expected results when there is a (0,20:08)?
On Fri, Jun 3, 2011 at 12:52 PM, wrote:
> I have a table that, at a minimum, has ID and timestamp columns. Records
> are inserted into with random IDs and timestamps. Duplicate IDs are allowed.
>
> I want to select re
It looks like maybe he is trying to fetch records that either have no
previous entries or have another record with a timestamp 5 minutes
before them at the time they are inserted...
On Sat, Jun 4, 2011 at 4:45 AM, Jasen Betts wrote:
> On 2011-06-03, lists-pg...@useunix.net wrote:
>>
>> ID TS
Will you be using a full timestamp with that or are you only concerned
about hours and minutes? If you want a full timestamp do you care
about the seconds? For example, do you want to be able to do this for
'2011-06-01 23:59:04' and '2011-06-02 00:04:04'?
On Fri, Jun 3, 2011 at 12:52 PM, wrote:
ordinary update, hence the error. In order to get this
to work I had to add a trigger for each child table as well to call my
update function trigger.
--Kevin Crain
On Tue, May 31, 2011 at 6:40 AM, Kevin Crain wrote:
> Can procedural languages be used in rules? I didn't see any examples
Can procedural languages be used in rules? I didn't see any examples
in the documentation that suggested something like this could be done
using rules.
--Kevin Crain
On Mon, May 30, 2011 at 2:21 AM, Jasen Betts wrote:
> On 2011-05-27, Kevin Crain wrote:
>> I am trying to creat
I am trying to create a trigger on updates to a table that is
partitioned. The child tables are partitioned by month and include
checks on a timestamp field. I want the trigger on the updates to
call a function that replaces the update entirely. In order to do
this my trigger deletes the record
ot; -- if the statement which is taking a long
time is the DELETE, and you want to delete all rows in the table,
try TRUNCATE TABLE.
-Kevin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
and (contrib is null or contrib = ' ' or contrib like '%b%')
order by modified desc
limit 100
;
-Kevin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Karl Denninger wrote:
> Let's take the following EXPLAIN results:
We could tell a lot more from EXPLAIN ANALYZE results.
The table definitions (with index information) would help, too.
-Kevin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes
your attention to this matter.
Kevin Duffy
ld it be called?
If I put it in parentheses, how many times would it be called?
Any comments from the PostgresSQL gurus out there?
Happy Friday.
KD
From: Dan McFadyen [mailto:d...@cryptocard.com]
Sent: Friday, March 20, 2009 3:51 PM
To: Kevin
would be calc'ed by
using a cursor to step through
CAPITALIZATIONLEVEL from lowest to highest. This function would be
declared STABLE.
Which method would execute more efficiently?
Thanks for considering my issue.
Kevin Duffy
CREATE TABLE capitalization
.
Does anybody know if a where-used function exists for the wonderful
database known as Postgres?
Many Thanks
Kevin Duffy
n_bbcode := replace(abbcode[1], '/', '') ||' '|| abbcode[3];
else
rtn_bbcode := tmp_bbcode; -- simple pass though case does
not work?!?!
end if;
end if;
return rtn_bbcode ;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Kevin Duffy
Take a look at LIKE or ILIKE
kd
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Kevin Duffy
Sent: Friday, November 21, 2008 11:31 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] function - string ends with
Hello All:
Is anyone aware
Hello All:
Is anyone aware of a function in PgSQL that will check if a string ends
with a specific string?
I.e. rposition(substring in string ) returns int
Starts searching right to left within string
Thanks for your attention to this matter.
Kevin Duffy
f record 2 in IMPORT_INV is for the same clothing item as
record 27, and this a new inventory item,
but for different locations. Will this new inventory item get
added twice to my ITEM table.
Many thanks for considering this issue.
Kevin Duffy
On Wednesday 29 October 2008 18:39:42 Kevin Duffy wrote:
> Hello:
>
>
>
> I have a couple of queries that are giving me headaches.
>
> They are actually very simple, but I do not understand why
>
> I am not getting the expected results. Maybe I need new g
x_ (index_key) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_tmpindexmember_taskrun FOREIGN KEY (taskrunkey)
REFERENCES taskrun (taskrunkey) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;
ALTER TABLE tmp_index_member OWNER TO postgres;
Kevin Duffy
WR Capital Management
--
From: Scott Marlowe [mailto:[EMAIL PROTECTED]
Sent: Friday, September 05, 2008 5:35 PM
To: Kevin Duffy
Cc: pgsql-sql@postgresql.org; Frank Bax
Subject: Re: [SQL] variables with SELECT statement
On Fri, Sep 5, 2008 at 3:28 PM, Kevin Duffy <[EMAIL PROTECTED]>
wrote:
> No looks like I h
et me guess I have to upgrade.
kd
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane
Sent: Friday, September 05, 2008 5:27 PM
To: Frank Bax
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] variables with SELECT statement
Frank Bax <[EMAIL PROTECTED]&
No looks like I have 8.2
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Frank Bax
Sent: Friday, September 05, 2008 5:13 PM
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] variables with SELECT statement
Kevin Duffy wrote:
> Just testing
EMAIL PROTECTED]
Sent: Friday, September 05, 2008 4:47 PM
To: Kevin Duffy
Subject: Re: [SQL] variables with SELECT statement
2008/9/5, Kevin Duffy <[EMAIL PROTECTED]>:
> OK that is a syntax I have never seen. But correct we are getting
> close.
>
> Noticed that string_to_array does no
al Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Frank Bax
Sent: Friday, September 05, 2008 4:07 PM
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] variables with SELECT statement
Kevin Duffy wrote:
> Within my table there is a field DESCRIPTION that I would like to
tance is requested.
Thanks
Kevin Duffy
WR Capital Management
40 Signal Rd
Stamford, CT
203-504-6221
Thanks Tom!
Also, how do I check if a language is already created so I don't load
it twice?
"ERROR: language "plpgsql" already exists
SQL state: 42710"
Here is the code fixed.
/*
CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
CREATE T
I wrote this function but I'm not sure how to test it in PG Admin III
Query.
CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
CREATE TABLE handles (
handleID_pk serial PRIMARY KEY UNIQUE NOT NULL,
userID_fk integer UNIQUE NOT NULL,
handle
e) as unionTable
WHERE unionTable.score= (SELECT max(unionTable.score) FROM unionTable);
Shane Ambler wrote:
Kevin Jenkins wrote:
Hi,
I have the following table which holds the result of 1 on 1 matches:
FName1, LName1, Score1, FName2, LName2, Score2, Date
John, Doe,85 Bill, Gates
Hi,
I have the following table which holds the result of 1 on 1 matches:
FName1, LName1, Score1, FName2, LName2, Score2, Date
John, Doe,85 Bill, Gates, 20 Jan 1.
John, Archer, 90 John, Doe,120 Jan 5
Bob,Barker, 70 Calvin, Klien 8 Jan 8
John,
er you need it.
Or is it terribly more complicated than this?
Kevin
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Rodrigo De Leon
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] CREATE TABLE AS inside of a function
Rodrigo De Leon wrote:
> On 7/21/06, Kevin Nikiforuk <[EMAIL PROTECTED]> wrote:
>> So now that I've got my loops working, on to my next newbie
>> question. I've c
FROM ldevrg
WHERE rg='$lv';
END LOOP;
RETURN 1;
END;
$$ LANGUAGE plpgsql;
Thanks,
Kevin
---(end of broadcast)---
TIP 6: explain analyze is your friend
9
psql:rgio.sql:16: LINE 6: FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP
psql:rgio.sql:16:
^
Ideas?
____
From: [EMAIL PROTECTED] on behalf of Kevin Nikiforuk
Sent: Thu 7/20/2006 7:46
-0716=# FOR LV in 1..10 LOOP
xp512-0715-0716-# select * from ldevrg;
ERROR: syntax error at or near "FOR" at character 1
LINE 1: FOR LV in 1..10 LOOP
^
I'm still pretty new to postgres, but based on the documentation I'm not
picking up what I&
Took
a quick glance but were running windows on both sides and it didnt look like
that would work. Didnt look real hard though.
Kevin -Original Message-From:
Scott Marlowe <[EMAIL PROTECTED]>To: "Forums @ Existanze"
<[EMAIL PROTECTED]>Cc: pgsql-sql@p
table would work.
I'll have to look into it. Thanks! Kevin
-Original Message-From: "Aaron Bono"
<[EMAIL PROTECTED]>To: "Kevin Bednar"
<[EMAIL PROTECTED]>, pgsql-sql@postgresql.orgDate:
Mon, 10 Jul 2006 12:15:34 -0500Subject: Re: [SQL]
MS-SQL<-&g
store and the other an ecommerce system
for a web site. Any and all help is appreciated since I can't find much
of anything on syncing these 2 database systems!
Kevin BednarSystems SupportStockwell Design Group
http://www.stockwelldesigngroup.com
[EMAIL PROTECTED]
I could probably work this out for you but I have no time today. However, as a
'plan b' maybe try this...
1- create a temp table based on all tables & conditions in the query except
for the outer table (i.e. user, ascpDef, address, invention, and user)
2- do an outer join on the above temp table
On Wednesday 12 April 2006 12:49, [EMAIL PROTECTED] wrote:
> Hi all,
> I have been working on converting our Informix DB to PostgreSQL. There are
> some differences with SQL syntax.
>
> I have done many outer conversion so far, but all has either one outer or
> simple
> one. But this one I do not k
On Thursday 06 April 2006 15:37, Owen Jacobson wrote:
> Judith wrote:
> >Hi every body, somebody can show me hot to execute a
> > query from a shell
>
> echo QUERY HERE | psql databasename
>
> Or, if you want to run several queries, run psql and run your queries
> there.
>
> ---
close to Oracle
PL-SQL is Postgres? Would a Oracle PL-SQL book cover the
basics?
Remember be kind to
the newbee.
Kevin Duffy
Hi,
I'm trying to assign the row count of a query to a variable in a function
but I'm not having any luck.
Could someone tell me the syntax? I've been looking in the docs and
googling for a long time but just can't find the answer.
I've tried:
CREATE OR REPLACE FUNCTION ret1() RETURNS int4 AS '
Hi,
I have a 14 million row table with one index on two fields one is a varchar
the other is a date. The combination of the two makes the row unique.
Data
-
name date... other fields
a 1/1/01
a 1/2/01
a 1/3/01
b 1/1/01
b 1/2/01
d 1/1/01
d 1/2/01
I would like to find the "missing" rows between two sets without using a
subselect (or views).
This query finds the rows that are in t1 but not in t2. (see the script
below for table definitions.)
QUERY 1:
select * from t1 left join t2 on t1.i = t2.i where t2.i is null
The above query i
You need to define fn_foo w/o params per Doc Section 19.9.
Your intent as expressed in the trigger def (args) can then be fulfilled
through special top level vars.
On Mon, 2004-08-02 at 16:20, Jeff Boes wrote:
> Hmm, this is puzzling me:
>
> create or replace function fn_foo(text) returns trigger
uot;column_update" ();
The trigger fires on every update, but the procedure doesn't do
anything unless the particular column changes. I don't think a
trigger can be defined to fire on anything more granular than a
table operation.
Kevin
---(end of broadca
R "tg_check_record"
BEFORE INSERT OR UPDATE ON table_rates
FOR EACH ROW EXECUTE PROCEDURE "check_record" ();
Kevin
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscrib
" FROM orderlines ol " +
" WHERE ol.theorder = ? " +
" AND ol.TYPE<>'P' " +
" AND ol.TYPE<>'T' ) AS a " +
t;pg_class ic " .
"WHERE " .
"ic.relname not like 'pg%' " .
"AND ic.relname not like '%pk' " .
"AND ic.relname not like '%idx' ";
which I am not certain is complete but appears to work.
Could anyone help me with the SQL to retrieve Foreign Keys for a
particular Table?
Much appreciated.
Kevin Gordon
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
time(access_time) - time(release_time)
as duration from mastertbl limit 10
Any suggestions?
Thanks,
Kevin
--
Kevin Old <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Dan,
I use Xpga Java Postgresql client and it is awesome.
It's at http://www.kazak.ws/xpg/
Kevin
On Wed, 2002-10-30 at 10:30, Dan Hrabarchuk wrote:
> gASQL is a gnome-db client that looks like it has a lot of promise. The
> only problem is I've never been able to get the ap
Hello all;
I'm trying to speed up a query which returns the majority of a table so and
index isn't helpful.
I've got more than enough RAM to hold my table so, can anyone tell me if
there is there a way to force Postgres to cache a table in RAM?
Any help would be appreciated.
ordan'
>
> Result: 1 record
It's case-sensitive. You can do this:
SELECT *
FROM People
WHERE lower(first_name) = 'jordon'
Kevin
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
All;
Can anyone please help with the tuning of this query?
With 77000 rows in the operator_messages database the query is taking almost
15 seconds to return. Preference woul dbe under 5 seconds if possible.
System load on a dual processor P3 with 1.5GB of memory remains under .4
during the query
e you left out is this:
INSERT INTO test (c1,c2) VALUES('a','c');
INSERT INTO test (c1,c2) VALUES('c','a');
I want that to fail, but I haven't been able to get it to fail
using unique indexes. I presume ordering is significant.
er
then the new smaller size (or you're taking your life into your own
hands so to speak. :-)
HTH,
Kevin
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
val (as above) and returning that.
As I do the 2 statement approach above, I haven't done a function, but
it doesn't look like it would be that hard to do.
HTH,
Kevin
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
t; or similar.
>
In this vain, is there someplace in the docs that has a type conversion
table (matrix) that shows what datatype can be cast into what other
datatype (both implicitly and explicitly)? I haven't seen one and it
would be helpful for us newbies.
Thanks!
Kevin
---
22 seconds'::interval;
> interval
> --
> 00:07:02
> (1 row)
That works on inserts into a "time" field too, cool tip. THANKS!
Kevin
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, pleas
Peter Eisentraut wrote:
> Kevin Brannen writes:
>
>
>>EXCEPT that now fails in 7.2.1 (I just upgraded this afternoon). It
>>forces me to use "b'00'" instead of "b'0'::bit(6)".
>
>
> Which is a problem why?
Because
o comply with the SQL standard. To implement
zero-padded bit strings, a combination of the concatenation operator and
the substring function can be used.
---
Obviously the source of my problem. However, whoever wrote that note
didn't say how to do it (examples are *SO* useful), and I ca
uld create the sequence myself, but the engine does
such a good job. :-)
Thanks,
Kevin
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
urces ...) Anyway,
> there's no need for LIMIT 1 inside an EXISTS, because the planner
> assumes that automatically.
Thank you muchly, I did some profiling and SELECT EXISTS is indeed
exactly what I wanted.
-Kevin Way
--
Kevin Way <[EMAIL PROTECTED]>
http://www.overtone.o
f how
they work, I don't see how to make the function return before parsing
all the results anyway, am I wrong here?
Thanks for any help, or 2x4s,
Kevin Way
--
Kevin Way <[EMAIL PROTECTED]>
http://www.overtone.org/
---(end of broadcast)---
ECK constraints
from my child tables, and I'm hoping some genius will solve the problem
by the time I'm looking to deploy.
-Kevin Way
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send &
s query-executing
> -- about, so probably it takes uncomparable longer for me than for
> -- a developer.
That's my problem as well, though your example is vastly easier to
trace than mine.
-Kevin Way
---(end of broadcast)---
TIP 2: you
at has me
looking at the costs involved with migrating to Oracle.
-Kevin
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
ix. (If none of these charities
are acceptable, surely one can be found later that is acceptable to both
parties).
Again, I greatly appreciate any help, and I apologize that my test case is
still fairly sizeable, despite being about 10% the size of the original
code.
-Kevin Way
msg06097/pgp0
_node_vote_count(NEW.target_id, NEW.nays, 0););
-- users
INSERT INTO users (name, pass_hash, realname, email) VALUES ('mosch',
'dafe001b7733b0f3236aa95e00f8ed88', 'Kevin', '[EMAIL PROTECTED]');
INSERT INTO users (name, pass_hash, realname, email) VALUES (
Thank you. Later checking showed that these requirements were listed in
the first sentence of the relevant page. Everything works like a champ
now. I've made a small donation to the EFF and to the Red Cross as a
minor thanks for your prompt help.
Kevin Way
---(e
s for the life of me.
Kevin Way
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
eration just require differing syntax, because the referenced field
is inherited from another table, or is this not possible?
Kevin Way
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregi
Here's one approach:
create view vw_maxrain as select max(rain) as rain, date_part('year',day) as
year from meteo group by year;
select day, meteo.rain from meteo, vw_maxrain where
meteo.rain=vw_maxrain.rain;
"Salvador Mainé" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL
cess Driver] You tried to execute a query that
does not include the specified expression 'LecturerName' as part of an
aggregate function.
I am trying to put the results of this query into a recordset and I am using
an accessdatabase
Thanks in advance,
Kevin.
Are there any good techniques that estimate the time it will take to execute
an SQL statement, specifically an INSERT, SELECT, UPDATE, or DELETE? What
factors are important to consider when estimating the execution time of
these types of SQL statements?
Thank you,
Kevin
Does anyone have any performance numbers regarding SQL statements,
specifically SELECT, UPDATE, DELETE, and INSERT? For instance, on average
how long does a typical SELECT (UPDATE, DELETE, INSERT) statement take to
execute?
Thank you,
Kevin
As titled, is autocommit of Postgresql is default to be "ON", how to turn it
off then?
Thnaks,
Kevin
--
-
Kevin LAM, System Analyst
Crown Development Ltd.
A Shun Tak Group Company
Tel: (852) 2283-2132
Fax:(852) 2283-2727
-
92 matches
Mail list logo
