Torsten,
Usually, the “insert ... (select ...)” has the select portion enclosed in
parenthesis. Don't know if solution is that simple but did not see it in your
examples. It may be worth a try.
Stuart
- Original message -
> Hello,
>
> i have a problem with a trigger
Glenn Maynard wrote:
(JMdict?
yup. ;-)
I was playing with importing that into a DB a while back,
but the attributes in that XML are such a pain--and then my email died
while I was trying to get those changed, and I never picked it up
again.)
On Mon, Apr 13, 2009 at 1:20 PM, Stuart McGraw
Scott Marlowe wrote:
2009/4/7 Stuart McGraw :
Hello all,
I have a table with a primary key column
that contains sequential numbers.
Sometimes I need to shift them all up or down
by a fixed amount. For example, if I have
four rows with primary keys, 2, 3, 4, 5, I
might want to shift them down
Jasen Betts wrote:
On 2009-04-08, Stuart McGraw wrote:
Hello all,
I have a table with a primary key column
that contains sequential numbers.
Sometimes I need to shift them all up or down
by a fixed amount. For example, if I have
four rows with primary keys, 2, 3, 4, 5, I
might want to
Hello all,
I have a table with a primary key column
that contains sequential numbers.
Sometimes I need to shift them all up or down
by a fixed amount. For example, if I have
four rows with primary keys, 2, 3, 4, 5, I
might want to shift them down by 1 by doing:
UPDATE mytable SET id=id-1
(
vacuums of a whole lot of tables by hand...
Thanks
Stuart
PS. Running on NetBSD 3
---(end of broadcast)---
TIP 6: explain analyze is your friend
---(end of broadcast)---
TIP 4: Have you searche
32801 | 52750
Is there anything I have missed as far as setting this up is concerned,
anything I could try? I would really rather use autovacuum than manage
the vacuums of a whole lot of tables by hand...
Thanks
Stuart
PS. Running on NetBSD 3
---(end of bro
es that help you?
I tried to strip the example down to its bare essentials but in this
case I would be partitioning by lineitem_key and would obviously index
and add a CONSTRAINT on that as well. I don't think it would help
though, the query needs to merge from all tables.
Thanks f
t;>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
If I partition the table by creating a top level table L, and inheri
ghly the same time?
Out of interest, since we are grouping by transaction_key which is
unique, surely the other Transaction fields in the group by could be
ignored by the planner?
Thanks
Stuart
(running postgresql 8.2.5 on NetBSD 3)
>> Slow query
EXPLAIN
SELECT t.transaction_key,t.cash
least microsecond precision.
Well, you can't get better than microsecond precision with timestamps
in Postgres. And the only way you can rely on that level of precision
is to compile with --enable-integer-datetimes.
Michael Glaesemann
I thought that might be the case, thanks for the hel
like to have
at least microsecond precision.
Thanks
Stuart
Table definition:
db=> \d+ Transactions;
Table "test.transactions"
Column | Type |
Modifiers
"Andrew Sullivan" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]
> On Tue, Jul 17, 2007 at 09:40:21AM -0600, Stuart wrote:
> > is not really structually important -- it is a value that
> > exists soley for the UI.
>
> Hmm. Maybe you sh
"Andrew Sullivan" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]
> On Tue, Jul 17, 2007 at 07:34:26AM -0600, Stuart wrote:
> > I am not looking for gapless sequences. The reason I
> > want to do this is the "typ" column is actually an indic
"chester c young" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]
> >
> > CREATE TABLE items (
> > id INT,
> > typ INT...
> > PRIMAY KEY (seq,typ));
> >
>
> >id typ
> > +-
> > 1 'a'
> > 2 'a'
> > 3 'a'
> > 1 'b'
> > 4 'a'
"Ragnar" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]
> On sun, 2007-07-15 at 09:28 -0600, Stuart McGraw wrote:
> > Advice requested :-) I have a table like:
> >
> > CREATE TABLE items (
> > id INT,
> > typ INT...
>
Advice requested :-) I have a table like:
CREATE TABLE items (
id INT,
typ INT...
PRIMAY KEY (seq,typ));
I would like 'id' to be like a SERIAL except that I
want independent sequences for each value of 'typ'.
So if 'items' is:
id typ
+-
1 'a'
2 'a'
Phillip Smith wrote:
> May I suggest you post an EXPLAIN ANALYZE to the group for the query you're
> having problems with...?
I will do that but it has happened to me enough that it seems to be
a general pattern, not something specific to one of my queries,
so I thought some communal knowledge ma
I have several times now run into what seems
like similar performance problems with some
of my postgresql queries.
I have a view that runs reasonably quicky.
I use this view in a subselect in another
query and that query too runs reasonably
quicky.
The view returns some unwanted duplicate
row
Does postgresql have a function that will give me
the numeric unicode code point for a character in
a unicode (aka utf8) database text string? That is,
something like ascii() but that works for unicode
characters?
---(end of broadcast)---
TIP 2:
each of the selects and then have to merge,
sort and limit the results. This seemed to work although it gets clumsy
if there are a whole lot of extra criteria.
Thanks again for the help,
Stuart
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
rt of issuing 3 queries and joining them that I can
do this? I had hoped to be able to compare (a,b,c)>(10,100,1000) but of
course that evaluates to (a>10) and (b>100) and (c>1000). It feels like
there should be a simple solution to this... please help :)
Thanks
Stuart
-
On 2006/10/11 Stuart wrote:
> [..]
Apologies for following up my own post, but after
struggling with that query for over a day, I figured
out the answer within thirty minutes of posting.
(Sigh)
My slow query was:
> SELECT p.id AS pid, a.id AS aid, sub.bid AS bid
> FROM p
>
I am having a problem with the performance of a
view that will be a critical part of a database system
I am working on, and would appreciate some advice.
Apologies for the length of this posting!
I have a parent table P and to child tables A and B,:
CREATE TABLE p (
id INT NOT NULL PRIMARY
2') | "-" | date_trunc('year', DATE
'2005-02-12');
Stuart
-Original Message-
>From: "Penchalaiah P." <[EMAIL PROTECTED]>
>Sent: Jul 11, 2006 7:34 AM
>To: pgsql-sql@postgresql.org
>Subject: [SQL] above the date function
Alex,
Postgres's "select into" does not work like oracle.
The "select into" for postgresql creates another
table. Try this;
select aliasvalue || trim(arrayval[i]) into
newtablename from currenttablename where
trim(searchfield) like '%' ||
trim(sea
Marek,
Not sure but, try switching the lines
db_data.mda_mod_con _CON,
db_data.set_mda_fue _FUE
with
db_data.set_mda_fue _FUE,
db_data.mda_mod_con _CON
so there query is:
SELECT
_CON.con_id,
_MOD.mod_ty,
_VER.version,
_YEA.year,
_CON.dri_id,
_CON.man_cod,
_ENG.eng_p
be able to update such a field
automatically anytime a record was updated. Any help would be appreciated.
Thanks,
Stuart
---(end of broadcast)---
TIP 8: explain analyze is your friend
up using immutable
funtions to aid the query, or just a function to do it. However I think
this does what you asked in a query. I've put a script at the end.
hth,
- Stuart
-- s is the stuff to group by
-- dt is the datetime thing
create table Q (
s int4,
dt timestamptz);
truncate Q;
IN
he current maximum+1
hth,
- Stuart
P.S. Sorry about the format change the disclaimer adder forces
> -Original Message-
> From: Scott Cain [mailto:[EMAIL PROTECTED]]
> Sent: 15 July 2003 14:00
> To: [EMAIL PROTECTED]
> Subject: [SQL] Cannot insert dup id in pk
>
>
> THI
Title: RE: [SQL] sort for ranking
Could you do something like the following:
SELECT sum_user,(SELECT count(sum_user)+1 FROM tbl_sums AS t WHERE t.sum_user>tbl_sums.sum_user) AS ranking FROM tbl_sums ORDER BY ranking
hth,
- Stuart
P.S. Sorry about format change, the disclaimer adder forces
I'm using 7.3.2 which extends the inheritance of some checks and triggers to
inherited tables, but still falls short of foreign key referencees looking
into child tables. Are there plans to address these defficencies and when
might we see this take place.
Thanks
S
into t2 now();
> t3 := t2 - t1;
> RAISE NOTICE '' from % to % = %'',t2,t1,t3;
> return t3;
>
> end;
> ' language 'plpgsql';
>
use timeofday(); as the others have the same value throughout the transaction
- Stuart
;"+username+"', '"+password+"')";
%>
to actually concatonate a string including the username & password variables, however I've not really used Java much so you might want to ask on the [EMAIL PROTECTED] list.
hth,
- Stuart
[EMAIL PROTECTED] w
bove, the timer1 and
timer2 TIMESTAMPs are always identical.
Any help/pointers/suggestions appreciate... well of course a working
solution would be the best ;)
Cheers,
Stuart.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
--
2002-11-26 13:47:34.88358+00
(1 row)
testdb2=# select timestamp 'now';
timestamptz
---
2002-11-26 13:47:47.701731+00
(1 row)
The first SELECT returns a 'timestamp', but the next two return a
'timestamptz'
use pgsqlism how about:
select file_md5 from image_instance WHERE
md5 = '546b94e94851a56ee721f3b755f58462' AND
image_width between 0 and 160 AND
image_length between 0 and 160 AND
ORDER BY image_width::int8*image_length::int8 LIMIT 1
This should get the smallest overall image size within your bounds.
It might be faster to do ORDER BY image_width,image_length LIMIT 1
but this wouldn't necessarily give the smallest if the aspect ratio changed
hth,
- Stuart
or things I may do wrong?
>
> TIA,
> Michiel
>
>
Trigger functions can only return type OPAQUE which isn't seen by the client program.
To get the value of the serial field for the last insert do:
SELECT currval('TableName_SerialFieldName_seq');
This will get the last value from the sequence used by this connection (although it will error if no values have been requested).
hth,
- Stuart
Josh has submitted an appropriate report to pgsql-bugs... so perhaps it will
happen!
Stuart.
> -Original Message-
> From: Jean-Luc Lachance [mailto:jllachan@;nsd.ca]
> Sent: 07 November 2002 16:29
> To: Rison, Stuart
> Cc: ''[EMAIL PROTECTED]' ';
dn't work either!
> I'll tinker later today; there has to be a way to do it.
I'd definitely appreciate further suggestions, but thanks all the same for
you help. I have a feeling that you might have to write a PL function to
perform the operation... but I haven't really thought about it!
Stuart.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
accomodate rowtypes for which the fields
are not known "a priori".
Any clever workarounds?
Cheers,
Stuart.
PS. I started using PGSQL five years ago... then I had to leave it alone
for a while... I can't believe how much it's grown and developed. AMAZING,
congra
t regards
> --
> Javier
>
ORDER BY year,month,day should do it.
hth,
- Stuart
DELETE FROM partitur WHERE EXISTS (SELECT * FROM partitur AS ss_partitur
WHERE ss_partitur.userid=partitur.userid AND ss_partitur.ts>partitur.ts);
Seems like it should seems like it should delete all old values (however I
have not tested it)
- Stuart
> -Original Message-
> Fro
Have a look at lo_import and lo_export. Can handle chunks at a time
Also bytea type. You have to handle complete items with this. Although it'd
need some formating. I believe zeros go to \000, ' to \' and \ to \\ as a
bare minimum, but am not at all sure on that.
- Stuart
> ---
be able to manage the data with tools on other
platforms. I guess I will have to give potential alternatives more
consideration.
Thanks again,
Stuart
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an
On Monday 15 October 2001 16:12 pm, Tom Lane wrote:
> Stuart Grimshaw <[EMAIL PROTECTED]> writes:
> > SELECT a.category, b.headline, b.added, c.friendlyname
> > FROM caturljoin as a
> > INNER JOIN stories as b ON (a.url = b.source)
> > INNER JO
I have 3 tables that I am trying to join together:
--
Table "caturljoin"
Attribute | Type |
---+-+
category | integer |
url | integer |
Index: caturljoin_url
caturljoin_cat
Table "stories"
Attribute |
subselects in check
constraints. It would be nice to allow reference checking through the
inherited tables so destination.area_id would be valid for any country,
state, or city area_id entered.
create table destination (
.
area_id char(10) references areas ...,
.
.);
Thanks,
Stuart
d the same for
group reader and included any caps reader will need to be quoted and in the
same case.
- Stuart
> -Original Message-
> From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> Sent: Monday, September 03, 2001 5:35 PM
> To: [EMAIL PROTECTED]
> Subject: GRAN
n int4 with a
default value of nextval('').
- Stuart
> -Original Message-
> From: Jaydip [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, August 23, 2001 8:51 AM
> To: [EMAIL PROTECTED]
> Subject: Help On Postgresql
>
> Dear Friends
>
> I am Jaydip Dewanj
T ((name=description) IS NULL) AND
(name=description) AS both FROM t WHERE name='bleh' OR description='bleh'
GROUP BY NOT ((name=description) IS NULL AND (name=description);
I think this should be more efficient than UNIONs, but am not an expert on
the query planner or exe
'plpgsql';
You'll also need to have the plpgsql language created for your database
(eg: createlang plpgsql testdb)
then just execute the above script then select cnt_rows(); in psql.
-Stuart
ORIGINAL MESSAGE:
hi all,
consider the pg server with databases
bhuvan
uday
guru
the need i
s this table structure (I don't know if he's giving it
> out, but he said it wasn't very difficult).
We've done a similar thing for Java. It was ridiculously easy to create a
TreeModel wrapped around this data. Almost too easy; it made me feel dirty.
Stuart Statman
Directo
y b is a subcategory of subcategory c, where I'm not sure your
model will preserve or guarantee that. (Does that sentence deserve a prize?)
In general, if you know that you will need to periodically alter a table to
add columns, you should come up with a different model that doesn't r
ion, I guess.
The problem with this method is if you need to insert a category, or move a
category. You'll need to re-id a bunch of categories, and bubble those
changes out to every table that refers to this table.
Stuart Statman
Director of Software Development
Slam Media, Inc.
BEGIN:VCARD
Could it be that the first query is using max which will only return the max
for b.lot and b.price..
so maybe.
select sum(max(b.lot))as quantity, max(b.price) as price from bid b, person
p where b.auction_id = 84 and p.id = b.person_id ;
Just a thought.
-Original Message-
From: [EMAIL
I'm not sure this is the right list for this but maybe someone could point
me in the right direction.
We are getting some DB servers to test performance. I was wondering if
anyone knows where we can get a test script of sorts that would allow us to
check performance on each of the servers.
TIA
You should be able to query the system table that holds that column names.
Not sure which it is tho.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf
Of G. Anthony Reina
Sent: Monday, September 11, 2000 4:38 PM
To: [EMAIL PROTECTED]
Subject: [SQL] How can I s
-Original Message-
From: Stuart Foster <[EMAIL PROTECTED]>
To: PG-SQL <[EMAIL PROTECTED]>
Date: Wednesday, 30 August 2000 2:25
Subject: [SQL] Viewing a function
Helllo Stuart,
Good question.
I have been fiddly with a function editor using zeos controls and I have
lo
How can a view a function after it's created ?
I've created a SQL function that I need to review and possibly change. What
is the best way to go about this.
TIA
Please ignore.
Thanks
61 matches
Mail list logo
