Alvaro and Tom, thanks so much. I was getting worried that I was going
to have to ask my customers to dump and restore periodically, ugh. I
think I need to learn a bit more about postgresql internals to help me
with my project. Not thinking about selecting for oids is kind of
embarrassing.
This is with 8.1.8, but I don't see any mention of any bug fixes that
cover this.
I've run into this sort of obscure problem. I'm using libpq with a
front end database api where I need to track column names and how
they're returned in libpq queries. What's happening is that I start out
in a query. It seems like they're always returned this way. For
instance:
select * from projects join employees on projects.manager = employees.id;
projects.id would always appear in the return list before employees.id?
Omar Eljumaily wrote:
This is with 8.1.8, but I don't see any mention
I think you can coax the date_trunc function to give you a proper start
day. I think it's more than adding an integer to your date, though.
You also have to do some mod work after the function returns, I think.
I agree that the point isn't that you can't do it with some effort,
however.
Ted, my reason for asking the question that I believe precipitated this
thread was that I wanted a single sql statement that aggregated time
data by week. Yes, I could do the aggregation subsequently in my own
client side code, but it's easier and less error prone to have it done
by the
But you're always returning Monday, right? Your grouping will be
correct, but to get the actual truncation date, you have to subtract back.
select (date_trunc('week', '2007-03-07'::date + 5)::date-5);
select (date_trunc('week', '2007-03-06'::date + 5)::date-5);
select (date_trunc('week',
I want to tabulate time data on a weekly basis, but my data is entered
on a daily basis.
create table time_data
{
employee varchar(10),
_date date,
job varchar(10),
amount
}
So I want to tabulate with a single sql command. Is that possible?
If I had a separate week end table
Edward 1/2 100
etc
I'd also like to return zero or null values when the data doesn't
exist. Wouldn't I need an iterator to do that?
Thanks,
Omar
Tom Lane wrote:
Omar Eljumaily [EMAIL PROTECTED] writes:
I want to tabulate time data on a weekly basis, but my data is entered
Thanks Alvaro. That's good to know. Actually I was spacing on the need
for this. The date_trunc function with group by actually works for me.
select sum(amount), date_trunc('week', period_end) as dt from time_data
group by dt;
Alvaro Herrera wrote:
Omar Eljumaily wrote:
Thanks Tom
select count(*), address where address ~* 'magil' or address ~*
'whitewater' etc group by address
would that work?
Rhys Stewart wrote:
Hi all,
i have a table with an address column. I wanted to count the number of
rows with a given regex match. so i ended up with the following very
Since this thread has already degraded, I'll offer my two cents. The
biggest screw ups in US history have been instigated by groups of
privileged White men. I know my name may sound otherwise, but I'm a
White American male, so I'm not pointing the finger at another group.
Let's see, Enron,
Thank God the DOI is inefficient. If they were good at what they do,
which is generally malicious, we'd all be in trouble.
Your story reminded me of a dear friend who works for the department of
the interior here in the US who routinely was dressed down for writing
functional, reliable
What happens if you do an outer join instead of an inner join?
Charlie Clark wrote:
Hi,
I'm getting unexpected results on a query which involves joining two
tables on two common variables (firstname and lastname).
This is the basic query:
SELECT table1.lastname, table1.firstname
FROM
I don't believe there is, or can be, any asynchronous multi-master
replication system for any database that will work with all possible
general purpose constructs.
I believe it's possible in theory if you have system wide transaction
locking, i.e. synchronous. However, if you have to have
I think a foreign key restraint is basically a trigger that throws an
exception (RAISE statement) when the restraint is violated.
Something trigger function like:
If table1
if not in table1
raise
else if table2
if not in table2
raise
Sorry if this isn't exactly postgresql specific. I periodically run
into this problem, and I'm running into it now. I'm wondering if
there's something about group by that I don't understand. As an
example what I'd want to do is return the id value for the check to
each payee that has the
OK, I see what's going on. I can have more than one max(amount) with
the same amount and payee. Thanks so much. Like I said, it's sort of
dogged me off and on many times.
Thanks.
Bill Moran wrote:
Omar Eljumaily [EMAIL PROTECTED] wrote:
Sorry if this isn't exactly postgresql specific
17 matches
Mail list logo