select ... order by "FROM" is not null, "FROM";
If you have large amount of rows (with or without nulls) it is faster if
use a partial index.
create index ... on ...("FROM");
create index ... on ...("FROM") where "FROM" is null;
JLL
[EMAIL PROTECTED] wrote:
Use the coalesce() function. (coalesce
Andrew,
If assing is not a many to many relation,
why did you not fold accept_id into assign?
Any way, here is the query you need:
select assign.reviewer_id, ss.max_assign_date,
accept.assign_id, accept.accept_id
from (
select reviewer_id, max( assign_date) as max_assign_date
from assign grou
If your intent is to insert a new record with position incremented by 1,
you should use a trigger. Look at the autoincrement thread from few days
ago.
Markus Bertheau wrote:
Ð ÐÑÑ, 17.08.2004, Ð 16:12, Bruno Wolff III ÐÐÑÐÑ:
SELECT MAX(position) FROM (SELECT position FROM classes WHERE name =
'
This query does not make sense to me.
Why would you create an updatable subquery just to get the highest value?
Maybe you are trying to achieve something other than what the query
suggest. You wou care to put in words what you want to do?
JLL
Markus Bertheau wrote:
Hi,
why is the following query
Markus Bertheau wrote:
Ð ÐÑÐ, 25.07.2004, Ð 15:18, Keith Gallant ÐÐÑÐÑ:
Hello
I am wondering if it is possible to use a SINGLE LIKE statement for a
selection from a list.
For example: If I want to return all results that a phrase starts with a
number, can I make a call similar to the following:
SE
If you do not have foreign key restrinctions, create a temp table from
the select as:
CREATE TEMP TABLE tmp AS SELECT DISTINCT ON (location) location, time,
report FROM weatherReports ORDER BY location, time DESC;
TRUNCATE weatherReports; INSERT INTO weatherReports SELECT * FROM tmp;
HTH
Achill
One way to do it would be to:
Not put the percentile in the sales table;
Create an percentile table with a foreign key to the sales table primary
key and percentile int4:
CREATE TABLE percentiles(
fkey PRIMARY KEY REFERENCES sales( ),
percentile INT4 );
Create a sequence for that ancillary table
reas wrote:
Jean-Luc Lachance schrieb:
Do you really need MANY-TO-MANY between customers and projects?
I can see customers owning many projects, but do you really have
projects belonging to many customers?
In this case yes.
projects (
1, 'x-fair 2003';
2, 'y-fair 2003';
3,
Do you really need MANY-TO-MANY between customers and projects?
I can see customers owning many projects, but do you really have
projects belonging to many customers?
If not, fold cust_proj into projects.
Otherwise, UNIQUE (cp_id, stall_no) on stalls should be enough.
Andreas wrote:
Hi folks,
Is
Have noticed it is not the same function...
Theodore Petrosky wrote:
Great I got the double quotes in the trigger... like
this:
CREATE FUNCTION notify_jobinfo() RETURNS "trigger"
AS '
BEGIN
EXECUTE ''NOTIFY
"''||TG_RELNAME||''_''||NEW.jobnumber||''"'';
RETURN NEW;
END
' LANGUAGE plpgsql;
and it
Try:
SELECT d.divisions_name, s.pd_geo, COUNT(s.pd_geo)
FROM ser s, ser_divisions d
WHERE s.ser_divisions = '3131'
AND s.ser_divisions = d.divisions_id
GROUP BY d.divisions_name, s.pd_geo;
Martin Kuria wrote:
Thanks Huxton,
Sorry for not explaining fully here is what I would like to achieve:
You are obviously not using C locale.
If you can't change it for some reason, you can use:
select * from accounts order by int4( trim( acno, '#'));
JLL
"George A.J" wrote:
>
> hi all,
> i am using postgres 7.3.2 .i am converitng a mssql database to
> postgres.
> now i am facing a strange proble
select distinct on( goodid) * from table order by goodid, storehistoryid
desc, totalnum, operationdate;
> aicean wrote:
>
> How can I produce the following desired result?
>
> goodidtotalnum operationdate storehistoryid
>132 35.000 09-28-2003 66
>
Wouldn't:
insert into r
select count(*)
from users
where date( lastlogin) > current_date - MaxDays * interval '' 1 day''
group by date( lastlogin);
be more efficient?
Tom Lane wrote:
>
> Dan Langille <[EMAIL PROTECTED]> writes:
> > WHERE lastlogin between current_date - interval \
Hey! here is a (stupid maybe) idea. Why not disallow 'NaN' for a float?
JLL
Stephan Szabo wrote:
>
> On Tue, 22 Jul 2003, Bruce Momjian wrote:
>
> > Well, my 2 cents is that though we consider NULL when ordering via ORDER
> > BY, we ignore it in MAX because it really isn't a value, and NaN
If a compare with NaN is always false, how about rewriting it as:
result = ((arg1 < arg2) ? arg2 : arg1).
Or better yet, swap arg1 and arg2 when calling float8smaller.
Use flaost8smaller( current_min, value).
JLL
Tom Lane wrote:
>
> "Michael S. Tibbetts" <[EMAIL PROTECTED]> writes:
> > I'd expe
Erik,
If you intent is to get a running total of a and b ordered by seq, you
should try this (assuming the table name is t):
update t set c = ( select sum(a) + sum(b) from t t1 where t1.seq <=
t.seq);
You should have an index on seq.
If the table is very large, it is going to be painfully slow.
Andreas,
try
select sum_user,nextval('tipp_eval_seq')-1 as ranking from (
select user_sum from tbl_sums order by user_sum desc) as ss;
JLL
Andreas Schmitz wrote:
>
> Hello *,
>
> I have a little problem that confuses me. We are gathering values from a table
> as a sum to insert them into
Have a look at translate(). It behaves like the unix command 'tr'.
Randall Lucas wrote:
>
> Hi Mallah,
>
> I had this problem once, and put together this bunch of regexes. It's
> by no means optimal, but should solve 90% and would easily be adapted
> into a plperl function.
>
> Begin perl:
>
KISS
why not use PHP to concatenate the authors while pub_id is the same???
If you insist on having each author in its own column,
put them at the end and concatenate with .
jll
Chadwick Rolfs wrote:
>
> So, I have the same problem, but I need all authors for each publication
> to show up in
I beg to differ.
A NULL field means not set.
Having to use work around because the database does not index null is
one thing, but making it a general rule is not.
Having NULL indexed would also speed up things when "is null" is part af
the query.
Until then...
JLL
Greg Stark wrote:
>
> One
Why not try the obvious first?
order by gradedtime is null, gradedtime desc;
"Ross J. Reedstrom" wrote:
>
> On Sun, Feb 09, 2003 at 05:29:29PM -0500, A.M. wrote:
> > I have a simple query that sorts by descending date but the NULL dates
> > show up first. Is there a way I can sort so they come
In DOS and Windows, text lines end with .
In Unix, text lines end with only.
hex decoct
=CTRL-M or 0x0D or 13 or 015
=CTRL-J or 0x0A or 10 or 012
Chad Thompson wrote:
>
> >
> > Unix EOL is LF not CR.
> >
> >
>
> Is this the only difference between a dos and unix text fi
You can acheive the same result with:
tr -d '"\015' < file_name.txt | psql {etc...}
Unix EOL is LF not CR.
Guy Fraser wrote:
>
> Hi
>
> You will need two text utilities {dos2unix and sed} to do this in the simplest
> way. They are fairly standard text utilities and are probably already on you
If you want character translation like the tr command under unix,
use TRANSLATE.
Andy Morrow wrote:
>
> Hi
>
> im trying to execute an update command on a postgresql DB table using
> pgAdmin II
>
> im using the following statement
>
> UPDATE commandlist SET command = REPLACE (command,'A','
I think you meant:
SELECT date,
sum( case when point = 1 then flow else 0 end) as flow1,
sum( case when point = 2 then flow else 0 end) as flow2,
sum( case when point = 3 then flow else 0 end) as flow3,
sum( case when point = 4 then flow else 0 end) as flow4,
sum( case when point = 5 then flow els
Use the AS keyword to introduce a column alias.
Select thisverlongtablename.thefirstfield as title, ... from
JLL
Josh Berkus wrote:
>
> Chris,
>
> > In my capacity as a vet student, I'm trying to create a database of
> antibiotics. The way that I have set it up so far is to have one main tab
Josh,
Thanks for the info.
I need to change an insert into an update when the key already exists.
I have been using a rules to test it on a small set (table) and it
works.
"Rules can't use indexes" just scared me. I will have to test on a
larger set.
Also, I had the impression that if a trigger
Thanks for the info.
Do you mean that if an update affects more than one row I should use
triggers because the rules will be executed only once?
JLL
Richard Huxton wrote:
>
> On Friday 06 Dec 2002 4:03 pm, Jean-Luc Lachance wrote:
> > Hi all!
> >
> > Is there a guide
Hi all!
Is there a guideline on the use of rules compared to triggers when both
can be use to achieve the same result?
JLL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Eric try:
select num, p1,p2 ... from contacts
inner join groups using (contacts.num=groups.contactNum)
where groups.groupNum=a
and contact.p3=b
and not exists (
select 1 from groups g2
where g2.contactNum = groups.contactNum and
g2.groupNum != a);
or
select num, p1,p2 .
If you do not mind non standard, how about:
SELECT DISTINCT ON(id_father) * FROM children ORDER BY id_father,
child_age;
Dennis Björklund wrote:
>
> On 29 Nov 2002, Sergio Oshiro wrote:
>
> > How can I get the rows of the children name and its "father" such that
> > they have the min child_age
Watch out! 36.85 weeks could have 37 sundays...
Take into account the day of the week of the first and last day.
Also, process the first and last day separately and work with whole day,
if you want to exclude part of the day.
Dan Langille wrote:
>
> On 22 Nov 2002, praveen vejandla wrote:
>
ng system catalogs to be useful in the past.
>
> This http://www.postgresql.org/idocs/index.php?catalogs.html will
> get you started.
>
> Thanks,
>
> Paul Ogden
> Claresco Corporation
>
> > -Original Message-
> > From: [EMAIL PROTECTED]
> > [
While we are on the subject,
is there any ERD of the system's table somewhere?
JLL
Josh Berkus wrote:
>
> Pedro,
>
> > I´m looking for the name of the table that contains all databases in my
> system. I already see this in the postgre manual, but i´m forgot where
>
> pg_database
>
> --
In a PLPGPSQL script, once you know count(*) try
execute ''select * from table limit '' || int4( theCount / 4);
If you want numbering, create a sequence and add nextval() to the query.
JLL
Jeff Boes wrote:
>
> Here's a puzzler:
>
> Given a query that returns rows ranked by some criteria, h
I would personnaly like this feature (assigning a composite from another
similar composite) to be added to PLPGSQL. Another nice feature would be
to able to insert a composite into a table without have to name all
atributes.
Just my $.02
"Rison, Stuart" wrote:
>
> >> 2) I am looking for an eleg
Thank goodness for nested select!
select data1 from test where data2 = ( select distinct data2 from test
where data1 = 'pooh') and data = 3;
JLL
Richard Huxton wrote:
>
> On Thursday 31 Oct 2002 6:21 pm, Wei Weng wrote:
> > data | data1 | data2
> > --+---+---
> > 1 | foo | ba
Of course, I meant
SELECT COALESCE( (SELECT true FROM ... WHERE ... AND boolcol LIMIT 1),
FALSE);
Jean-Luc Lachance wrote:
>
> Why not simply:
>
> SELECT COALESCE( (SELECT true FROM ... WHERE boolcol LIMIT 1), FALSE);
>
> JLL
>
> Josh Berkus wrote:
> &
Why not simply:
SELECT COALESCE( (SELECT true FROM ... WHERE boolcol LIMIT 1), FALSE);
JLL
Josh Berkus wrote:
>
> Tom,
>
> > Perhaps
> > SELECT true = ANY (SELECT boolcol FROM ...);
> > or
> > SELECT true IN (SELECT boolcol FROM ...);
> >
> > Which is not to say that MAX(bool) migh
Tom,
You can add
sum( case when then 1 else 0 end)
for each field that you need.
JLL
Tom Haddon wrote:
>
> Hi Folks,
>
> I'm hoping to put together a query that generates a report on a table with
> a large number of boolean fields. This report has to be able to adapt to
> the number of fie
I think you meant:
select profile.name
from profile,attribute
where ( profile.id = attribute.containerId)
and ( profile.state =' 1020811' or ( attribute.name = 'marketsegment'
and attribute.value = '1020704');
> select profile.name from profile,attribute where
> ((profile.state='1020811') or (
Try
select to_char( '1969-10-22'::date, '-MM-DD');
wishy wishy wrote:
>
> hi folks,
> we have a PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC 2.96
> installation on pogo linux 7.2
> we are facing a data problem when we do the following
> select to_char(to_date('1969-10-22','-M
Are you looking for SERIAL data type?
Josh Berkus wrote:
>
> Jim,
>
> > Do any existing drivers / database version combinations support the
> > isAutoIncrement method?
>
> What programming language are you referring to? VB? Delphi?
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San F
Try a rule or a triger that checks for
NOT EXISTS ( select 1 from eyp_listing where group_id = New.group_id and
userid != New.userid)
"Rajesh Kumar Mallah." wrote:
>
> Hi ,
>
> can anyone tell me how can i enforce below in a table.
> I want that no more that one distinct userid exists for a
OK, forget system_clock() or clock() timeofday() will do.
Jean-Luc Lachance wrote:
>
> How can you make a difference between now('statement'), and
> now('immediate').
> To me they are the same thing. Why not simply now() for transaction, and
> now('CLOCK
How can you make a difference between now('statement'), and
now('immediate').
To me they are the same thing. Why not simply now() for transaction, and
now('CLOCK') or better yet system_clock() or clock() for curent time.
JLL
Josh Berkus wrote:
>
> Tom,
>
> > I'd be happier with the whole thing
Oh, sorry I missed that.
Still if C1 and C2 are interchangable, a rule could force C1 <= C2 and
swap them if necessary.
Richard Huxton wrote:
>
> On Friday 27 Sep 2002 5:17 pm, Jean-Luc Lachance wrote:
> > What's wrong with
> > CREATE UNIQUE INDE
What's wrong with
CREATE UNIQUE INDEX foo_both_uniq ON foo(a,b);
???
Richard Huxton wrote:
>
> On Wednesday 25 Sep 2002 2:10 am, Kevin Houle wrote:
> > I have the same issue with a table that currently holds well
> > over 600,000 rows. The case you left out is this:
> >
> > INSERT INTO test
How about:
select now() - date_part( 'DOW', now()) as starts_on,
now() -date_part( 'DOW', now()) + 6 as ends_on;
"John Sebastian N. Mayordomo" wrote:
>
> How do I get the start and end date of the present week?
> Is this possible?
>
> For example this week
> Start = Sept. 22
> End = Sept
That is great! Thanks for the info.
Tom Lane wrote:
>
> Jean-Luc Lachance <[EMAIL PROTECTED]> writes:
> > How about making available the MVCC last version number just like oid is
> > available. This would simplify a lot of table design. You know, having
> > to a
Hi all developpers,
This is just a idea.
How about making available the MVCC last version number just like oid is
available. This would simplify a lot of table design. You know, having
to add a field "updated::timestamp" to detect when a record was updated
while viewing it (a la pgaccess).
Th
I think you meant min(date)...
Josh Berkus wrote:
>
> Marco,
>
> > Is there a way to obtain this records by performing one
> > single query and not by making for each city something like
> > "SELECT city,date FROM table WHERE city='London' AND date>'2002-07-19
> > 15:39:15+00' ORDER BY date AS
How about:
select city, min(date) from thetable where date > '2002-07-19
15:39:15+00' group by city;
JLL
Marco Muratori wrote:
>
> Hi
> suppose I have the following situation:
>
> citydate
> -+---
> London | 2002-08-08 07:05:16+00
> London | 2002
As in an order by clause... If it existed.
Josh Berkus wrote:
>
> JLL,
>
> > I want to update a field with a 'NEXTVAL', but I want the record updated
> > in a specific order.
> > Any simple way of doing this other than having to create a temp table?
>
> Please be more speciifc. What do you me
Well It's Friday and I am still geting vacation messages from
Bob
Tom Lane wrote:
>
> Jean-Luc Lachance <[EMAIL PROTECTED]> writes:
> > Can someone *please* temporarely remove
> >"Bob Powell" <[EMAIL PROTECTED]>
> > fr
Hi all,
I want to update a field with a 'NEXTVAL', but I want the record updated
in a specific order.
Any simple way of doing this other than having to create a temp table?
JLL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
h
What is wrong with:
select field_group, sum( case when f1 = 'D' then cnt else 0 end) as
D_COUNT,
sum( case when f1 = 'R' then cnt else 0 end) as R_COUNT,
sum( case when f1 = 'X' then cnt else 0 end) as X_COUNT
from (select field_group, f1, count (*) as cnt from tab group by
field_group, f1) a
PostgreSQL does not know how to sort 'TEST'.
You must help it be telling it what tpe it is.
Add ::text after 'TEST' as in 'TEST'::text.
Maybe PostgreSQL should default to text for unknown types...
JLL
Andreas Schlegel wrote:
>
> Hi,
>
> I need some help to let this sql statement run with Po
That is because your query is generating a cartesian product.
Try:
SELECT (
SELECT SUM(totalprice)
FROM invoices
WHERE custnumber = '1'
) - (
SELECT SUM(paymentamount)
FROM payments
WHERE custnumber = '1'
)
Roy Souther wrote:
>
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
here is the algorithm:
date := now - day_of_the_week
interval := interval + day_of_the_week
date := date + int( interval/5)x7 + ( interval mod 5)
Josh Berkus wrote:
>
> Folks,
>
> Hey, I need to write a date calculation function that calculates the date
> after a number of *workdays* from a s
61 matches
Mail list logo
