IIRC the standard syntax is based on db2's horrendous merge on command,
which was only added to the standard a couple months back.
IIRC the main downside to the select/update method is it introduces a
race condition that can only be solved by locking the table; not an
issue for most my$ql apps bu
On Fri, 18 Jun 2004, Jeff Rogers wrote:
> create table articles (
> topic varchar(50),
> created date,
> data text
> );
>
> create index articles_topic_idx on articles(topic);
> create index articles_created_idx on articles(created);
>
> If I want to get the 5 most recent articles in a topic
On 18/06/04, Harald Fuchs ([EMAIL PROTECTED]) wrote:
> In article <[EMAIL PROTECTED]>,
> Rory Campbell-Lange <[EMAIL PROTECTED]> writes:
>
> > I should have mentioned that we need the messages sent very soon after
> > they have landed in the 'inbox'; otherwise cron would definitely be the
> > way
It might be worthwhile to experiment with 2 new indexes:
Create UNIQUE index articles_created_topic_idx on articles(created,
topic);
Create UNIQUE index articles_topic_created_idx on articles(topic,
created);
Probably, one of the two should become your primary key.
That will give the optimizer s
I have a query that it seems is destined to be slow one way or another. I
have a table of around 30k articles, categorized by topic and ordered by date:
create table articles (
topic varchar(50),
created date,
data text
);
create index articles_topic_idx on articles(topic);
create index
[EMAIL PROTECTED] ("Scott Marlowe") wrote in message news:<[EMAIL PROTECTED]>...
> On Thu, 2004-06-17 at 23:03, Deepa K wrote:
> > Hi,
> > I am using Postgresql 7.1.3. PgSQL server gets shut down once the hard
> > disk space becomes full. Is thers any particular reason to shut down the
> > server
Title: RE: [GENERAL] INSERT ON DUPLICATE KEY UPDATE
I agree. You could always do a SELECT and if the record was found then UPDATE otherwise INSERT. A little more effort than MYSQL but again I don't believe the way MYSQL is allowing you to do it is standard.
Duane
-Original Message
Tom Allison wrote:
Doug McNaught wrote:
Jonathan Barnhart <[EMAIL PROTECTED]> writes:
I know that postgres runs under Cygwin. It was announced that 7.4 would
have a windows native version or some such. I have found nothing but
Cygwin versions however.
Native Windows support is slated for 7.5 (wh
"Florian G. Pflug" <[EMAIL PROTECTED]> writes:
> ... I upgraded to 7.4.2, and fixed the system-tables
> according to the 7.4.2 release-note. But this didn't really help - the
> "analyze table" issued after fixing the system-tables exited with an
> error about an invalid page header in one of our ta
On Fri, 2004-06-18 at 13:17, Robert Fitzpatrick wrote:
> ohc=# CREATE OR REPLACE FUNCTION "public"."clear_common_groups" ()
> RETURNS trigger AS'
> ohc'# BEGIN
> ohc'# IF NEW.common_area = ''t'' THEN
> ohc'# UPDATE tblhudunits SET common_area = ''f'' WHERE
> hud_building_id = NEW.hud_building
Eduardo Pérez Ureta wrote:
On 2004-06-18 17:19:40 UTC, Duane Lee - EGOVX wrote:
I would suspect you would need to write a trigger to do this.
It seems the mysql way of doing this is easier and safer.
And non-standard AFAIK.
Why is that not implemented in postgresql?
Is it better done with a trigge
On 2004-06-18 17:19:40 UTC, Duane Lee - EGOVX wrote:
> I would suspect you would need to write a trigger to do this.
It seems the mysql way of doing this is easier and safer.
Why is that not implemented in postgresql?
Is it better done with a trigger or with any other way?
Eduardo
> -Origina
On 7.4.2 I have a trigger that I want to update any existing boolean
values to false if a new one in that group is declare true by inserting
a new record or updating an existing record:
ohc=# CREATE OR REPLACE FUNCTION "public"."clear_common_groups" ()
RETURNS trigger AS'
ohc'# BEGIN
ohc'# IF NE
Title: RE: [GENERAL] INSERT ON DUPLICATE KEY UPDATE
I would suspect you would need to write a trigger to do this.
-Original Message-
From: Eduardo Pérez Ureta [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 18, 2004 9:38 AM
To: [EMAIL PROTECTED]
Subject: [GENERAL] INSERT ON DUPLICATE K
"Florian G. Pflug" <[EMAIL PROTECTED]> writes:
> I faintly remember that I once stumbled upon a way to declare variadic
> functions (functions that take a variable number of arguments) in plpgsql -
> but I just searched the docs and can't find any reference to variadic
> functions.
Nope. You can
Harald Fuchs wrote:
In article <[EMAIL PROTECTED]>,
Rory Campbell-Lange <[EMAIL PROTECTED]> writes:
I should have mentioned that we need the messages sent very soon after
they have landed in the 'inbox'; otherwise cron would definitely be the
way to go -- including an @reboot line.
This rules out
Carl Anderson <[EMAIL PROTECTED]> writes:
> Would you be willing to try to load a large table (from a SQL file).
> It reliably demonstrates the behavior causing an "invalid page header"
> during completion (for me)
I would very much like to see a test case that reliably produces
"invalid page head
Title: RE: [GENERAL] virtual fields on VIEW?
If I understand you correctly I believe this will work for you.
create view as select
t1.id, t1.date, t1.field1, t1.field2,
t2.fieldA, t2.fieldB,
-- state, stuff
case
when t
How may I do a INSERT ON DUPLICATE KEY UPDATE like in mysql:
http://dev.mysql.com/doc/mysql/en/INSERT.html
?
Eduardo
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Ron Snyder <[EMAIL PROTECTED]> writes:
> That's all just background for my real question-- is there anything in the
> standards (or elsewhere) that says you can't put binary(**) data into a char
> field? When I changed the field to a bytea, processing time was
> significantly reduced.
bytea is th
On Fri, Jun 18, 2004 at 11:42:29 -0400,
Tom Lane <[EMAIL PROTECTED]> wrote:
>
> The SQL spec doesn't allow unconstrained lengths for these types
> so it gives no guidance about what to display in the information_schema
> views. Any opinions?
It might make some sense to use the maximum length s
[EMAIL PROTECTED] writes:
> This is part of the Columns View, if you add a numeric field to your table
> and don't provide any Length or Precision then :
> numeric_precision is returned as 65535
> numeric_scale is returned as 65531
Yeah, that's what you'd get for a numeric field with no length
co
Hi
One of our production systems was running 7.4.1 for a few months, when
suddenly some queries that used a specifiy table (a cache table) started
crashing the backend.
A colleague of mine "fixed" the problem by simply dumping and rebuilding
the affected table (That was possible since it was onl
On Fri, Jun 18, 2004 at 16:13:38 +0300,
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> hi,
>
> I want to make the following thing :
> select-based updatable VIEW, which have two more virtual-fields.
> One of them is concatenation of others and the second is calculated on the fly.
> Can I do th
Hi
I faintly remember that I once stumbled upon a way to declare variadic
functions (functions that take a variable number of arguments) in plpgsql -
but I just searched the docs and can't find any reference to variadic
functions.
So - please enlighten me - are there variadic functions in plpgsql
> SELECT ...
>CASE
> WHEN date < CURRENT_DATE THEN 'green'::text
> WHEN date > CURRENT_DATE THEN 'red'::text
> ELSE 'blue'::text
>END
>AS state,
>(t1.field2 || t2.fieldA) AS stuff
> FROM ...
]- aha thanx..
> >>> BOTH state and stuff will be only available for SELEC
NOTE THAT if field2 or fieldA might contain NULL values u should use
coalesce if u don't want to have a NULL value if one of the fields is NULL:
If field2 and fieldA are strings you will have something like that
(coalesce(t1.field2,'') ||coalesce(t2.fieldA,'')) AS stuff
> [EMAIL PROTECTED]
[EMAIL PROTECTED] wrote:
hi,
I want to make the following thing : select-based updatable VIEW,
which have two more virtual-fields. One of them is concatenation of
others and the second is calculated on the fly. Can I do this and if
yes how? can u give some example?
Here is the test bed :
table1) i
28 matches
Mail list logo
