Or the
other way round: anything that is equal or greater than the first
of the current month:
select ...
from foobar
where obstime >= date_trunc('month', current_date);
I knew it had to be something si
Greetings,
I want to be able to select all data going back to the beginning of
the current month. The following portion of an SQL does NOT work,
but more or less describes what I want...
... WHERE obstime >= NOW() - INTERVAL (SELECT EXTRACT (DAY FROM
NOW()
I'd do somethings like:
select * from (
select id, sum(col1), sum(col2) from tablename group by yada
) as a [full, left, right, outer] join (
select id, sum(col3), sum(col4) from tablename group by bada
) as b
on (a.id=b.id);
and choose the join type as appropria
I have a query:
SELECT id, SUM(col1), SUM(col2) FROM mytable WHERE condition1 = true
GROUP BY id;
This gives me 3 columns, but what I want is 5 columns where the next
two columns -- SUM(col3), SUM(col4) -- have a slightly different
WHERE clause, i.e., WHERE condi
SQL gurus,
I have a table with 4 columns: lid(varchar), value(float),
obstime(datetime), event_id(integer)
I want to find the MAX(value) and the time and date that it occurred
(obstime) in each group of rows where the lid and event_id are the
same. What I have works correctly in identifyin
I am using psql's \copy command to add records to a database from a
file. The file has over 100,000 lines. Occasionally, there is a
duplicate, and the import ceases and an internal rollback is performed.
In other words, no data is imported even if the first error occurs near
the end of the f
Thanks, Joe and Tom. You cleared the webs out of my brain. I used
HAVING before, but not lately and I got rusty.
Mark
Tom Lane wrote:
Mark Fenbers writes:
I want to do:
SELECT id, count(*) FROM mytable WHERE count(*) > 2 GROUP BY id;
But this doesn
I want to do:
SELECT id, count(*) FROM mytable WHERE count(*) > 2 GROUP BY id;
But this doesn't work because Pg won't allow aggregate functions in a
where clause. So I modified it to:
SELECT id, count(*) AS cnt FROM mytable WHERE cnt > 2 GROUP BY id;
But Pg still complains (that column cnt
Try putting your subqueries into temporary tables, first, inside a
BEGIN ... COMMIT block. But your subqueries would produce the
negative, i.e., everything except where sitescategory.idsites =
ps.idsites. Then reference these temp tables in your query with inner
or outer joins as appropriate.
I have created a new 8.3 version DB and populated it. A specific user
of this database (george) has been setup with a password, so that every
time I use psql or some other utility, I need to supply this password.
So I want to drop the password authentication. I tried rerunning
createuser (an
A seldom-used database of mine was not recently vacuumed and I've run
into the error: FATAL: database is not accepting commands to avoid
wraparound data loss in database "stop"HINT: Stop the postmaster and
use a standalone backend to vacuum database "stop". In fact, I get this
error while
I am an ex-Informix convert. Informix used the term "schema" to refer
to the SQL-format definition of how a table or view was created. E.g.,
CREATE TABLE john ( char(8) lid, ...); Some views we have are quite
complex (and not created by me) and I want to create a similar one in
Pg. If I cou
I have working PostgreSQL databases on 3 of my ~30 Linux boxes. I want
my software to be able to determine which of my 30 boxes have functional
databases on them. Since Pg is part of the baseline distro, merely
checking for the existence of an executable doesn't solve my problem.
I tried loo
ist?
Mark
Mark Fenbers wrote:
I want to get Pg (v7.4.7) to output a date field in a different format
than -mm-dd through the use of an environmental variable (because
I have no access the SQL). Is this possible? I know about the
DATESTYLE variable, but that seems to work only with
I want to get Pg (v7.4.7) to output a date field in a different format
than -mm-dd through the use of an environmental variable (because I
have no access the SQL). Is this possible? I know about the DATESTYLE
variable, but that seems to work only within a query transaction, and
has no eff
Wow! I didn't know you could have a (select ...) as a replacement for
a 'from' table/query. Your SQL worked as-is, except I had to add a
'limit 1' to the first subquery.
Thanks! I would have never figured that out on my own!
Mark
chester c young wrote:
select l.lid,l.fs,max(h.obstime) from location as l
inner join height as h on h.lid = l.lid
where l.fs > 0.0
group by l.lid,l.fs;
The above query works as expected in that is fetches the lid, fs and
time of the latest observation in the height table (for the
corresponding lid), but I also want
I'm having trouble with installing pgadmin. If this is not the
apporpriate group for seeking help with this, please excuse me and tell
the correct one. I have not found anything else more appropriate.
'make' for pgadmin fails, I think it is because I don't have wxWidgets.
wxWidgets fails.
You might find the "DISTINCT ON" syntax does just what you want --- see
the "weather report" example on the SELECT reference page. It's not
standard SQL though.
This works! Thanks!
What would have to be done if I needed a standard SQL solution?
Mark
---(end of bro
I currently have a working SQL that SELECTs all records whose
'river_stage' column exceeds the 'flood_stage' column. (Very simple --
no applause needed.) Typically, if I get one record, I get a
consecutive series of them since rivers rise and fall in a continuous
fashion, and usually respond
I want to SELECT INTO mytable WHERE (criteria are met), except that I
want to APPEND into an existing table the rows that are selected,
instead of creating a new table (which SELECT INTO will do). How can
this be done?
(Is this what the "FOR UPDATE OF tablename" clause is for?)
Mark
---
I am looking for a way to reformat the information that is generated from
\d mytable
into SQL syntax, such that the table can be recreated with 'psql -f
mytable.sql' complete with index and constraint definitions. I can do
awk and sed commands to do this if I need to, but first wanted to che
A colleage of mine in another office has RedHat Enterprise 3 installed.
We do not have this yet, but will in the fall. According to him, the
DBD::Pg module that has been a part of the Red Hat baseline from Redhat
7.2 (or earlier) through RH Fedora Core has been removed from RH
Enterprise 3 ba
Yes, your varlena links are what I was looking for as a source of help...
Thanks!
Mark
Michael Fuhr wrote:
On Thu, May 19, 2005 at 03:17:07PM -0400, Mark Fenbers wrote:
I need to create an aggregate function to do some math not currently
provided by the available tools. Can someone point to
I need to create an aggregate function to do some math not currently
provided by the available tools. Can someone point to an example
aggregate function syntax that I can use as a template for my own
function. I'm still a little green on some aspects of PostgreSQL and am
drawing a blank on ho
Is there a way to make a query more efficient by executing a sub-select
only once?
In a query such as:
SELECT a, (select b from c where d = e limit 1), npoints( (select b
from c where d = e limit 1) )
FROM f
WHERE isValid( (select b from c where d = e limit 1) );
I do the same sub-se
TABLE cascade
Jhon Carrillo
Ingeniero en Computación
Caracas - Venezuela
- Original Message -
From:
Mark
Fenbers
To:
pgsql-sql@postgresql.org
Sent:
Friday, May 13, 2005 2:38 PM
Subject:
[SQL] Replacing a table with constraints
I have a
I have a table called Counties which partially contains a lot bad
data. By" bad data", I mean some records are missing; some exist and
shouldn't; and some records have fields with erroneous information.
However, the majority of the data in the table is accurate. I have
built/loaded a new tab
I want to update a column in myTable. The value this column is set to
depends on a nested select statement which sometimes returns 0 rows
instead of 1. This is a problem since the column I'm trying to update
is set to refuse nulls. Here's a sample:
update myTable set myColumn = (Select altC
29 matches
Mail list logo
