Excellent - thanks, Josh!
--
Andrew J Perrin - http://www.unc.edu/~aperrin
Assistant Professor of Sociology, U of North Carolina, Chapel Hill
[EMAIL PROTECTED] * andrew_perrin (at) unc.edu
On Wed, 25 Aug 2004, Josh Berkus wrote:
I have a table of people ("reviewers"), a table of review assignments
("assign"), and a table of review acceptances ("accept"). I would like to
be able to write a query to return the latest (e.g., max(assign_date))
assignment for each reviewer, plus the acc_id field from "accept". I
think I should
I'm developing the second stage of a database that will eventually be used
to model networks of references between documents. I already have a table
of core documents, and the next step is to track what documents each of
these core documents refers to. (Is this confusing enough already?)
The rela
*Disclaimer:* I do not have any formal training in database
theory. However, I have done quite a lot of work with databases in
practice, and have some understanding of "missing values" in statistics.
I would tend to think of the "no applicable value" case of a NULL as being
the result of poor or l
One strategy is to use some sort of middleware that takes care of this. On
a project I did a few years ago, I used a perl module that read the record
from Postgres and made it into a perl object. The object contained a
variable, "changed", that reflected whether anything had actually changed
in the
Read the error text:
> beckerbalab2=> SELECT ffix_ability.name, ffix_ability.cost
^
> beckerbalab2-> FROM ffix_can_learn NATURAL JOIN ffix_ability
> beckerbalab2-> WHERE ffix_can_learn.character_name = 'Zidane'
> beckerbalab2-> EXCEPT --this is the differe
No, I don't think it's supposed to be case-sensitive. In any case, whether
it's supposed to be or not, it certainly isn't in practice.
Solutions include:
SELECT *
FROM People
WHERE lower(first_name)='jordan';
and:
SELECT *
FROM People
WHERE first_name ~* 'Jordan';
ap
o maybe you could just write a sql one and
> it would just work. Of course I've never used one and don't know anything
> about it really so I could be wrong :)
>
> -philip
>
> On Wed, 25 Sep 2002, Andrew Perrin wrote:
>
> > Does anyone know of a routin
Does anyone know of a routine for formatting SQL statements in a
structured way? Standalone or for emacs would be fine. I'm thinking of
something that could take a long SQL text statement and format it, e.g.:
select foo from bar where baz and bop and not boo;
becomes
SELECT foo
FROM
On Thu, 12 Sep 2002, Stephan Szabo wrote:
> On Thu, 12 Sep 2002, Andrew Perrin wrote:
>
> > Greetings-
> >
> > I have a table of participants to be contacted for a study. Some are in
> > the "exposure" group, others in the "control" group
On 12 Sep 2002, Roland Roberts wrote:
> >>>>> "Andrew" == Andrew Perrin <[EMAIL PROTECTED]> writes:
> ...
> Can you do this via a subselect:
>
> SELECT * FROM
> ( SELECT ... FROM participants
> WHERE typenr=1 AND
> U
Assistant Professor of Sociology, U of North Carolina, Chapel Hill
[EMAIL PROTECTED] * andrew_perrin (at) unc.edu
On Thu, 12 Sep 2002, Tom Lane wrote:
> Andrew Perrin <[EMAIL PROTECTED]> writes:
> > SELECT ... FROM participants
> > WHERE typenr=1 AND
> > UNION
>
Greetings-
I have a table of participants to be contacted for a study. Some are in
the "exposure" group, others in the "control" group. This is designated by
a column, typenr, that contains 1 for exposure, 2 for control.
The complication is this: I need to select 200 total. The 200 number
should
Interesting - my experience is that Access, at least, generally treats
NULL's correctly:
(This was done under Access 2000):
create table foo (name text(20))
insert into foo values ("bar");
insert into foo values ("bar");
insert into foo values ("bar");
insert into foo values ("bar");
insert into
Try:
- The ILIKE operator, for example,
SELECT * FROM account WHERE username ILIKE "test";
- upper() or lower(), for example,
SELECT * FROM accont WHERE lower(username) = "test";
-
Andrew J. Perrin - Assistant Professor of Sociology
Except that he wants max(timestamp) by id; perhaps a GROUP BY would
help, something like (untested):
select max(timestamp) from log group by id;
Tom Lane wrote:
>
> "Marc Sherman" <[EMAIL PROTECTED]> writes:
> > I'd like to select the newest (max(timestamp)) row for each id,
> > before a given
These do suggest (although not require) that the *user* postgres will be
running bash when logged in. To check for sure, do:
finger postgres
which will give you the current shell among other things.
However, this doesn't speak to what I think you're asking, which is
command history and completi
I ditto what Bruce said - trying to get a true sequence without gaps is a
losing battle. Why don't you, instead, use a serial column as the real
sequence, and then a trigger that simply inserts max(foo) + 1 in a
different column? Then when you need to know the column, do something
like:
SELECT nu
, Tom Lane wrote:
> Andrew Perrin <[EMAIL PROTECTED]> writes:
> > fgdata=# \d sx_l_m_r_a
> > ERROR: cache lookup of attribute 197 in relation 47074 failed
> > fgdata=# select * from pg_views;
> > ERROR: cache lookup of attribute 317 in relation 48494 failed
>
&g
Greetings-
I'm in a bit of a pickle. I rebuilt a big query on top of which lots of
little queries rest, so as to use some new columns in the query. Now, I
get error messages when trying to access any view that SELECTs from the
rebuilt query:
fgdata=# \d sx_l_m_r_a
ERROR: cache lookup of attrib
ax
you can get what you're looking for by simply JOINing Records and
Data. Then, when you want to "change" a record - say, for example, Andrew
Perrin moves from Berkeley to Chapel Hill, thereby changing phones from
510-xxx- to 919-xxx- - you actually *add* a new record, with a
hi
Check out nextval() and currval(). They do exactly what you need. They're
also specific to the current backend, so you can guarantee that the same
value won't be passed to two different frontend sessions.
nextval('sequencename') -> the number that will be assigned next in
Entirely untested, but how about replacing currval() in your first try
with nextval()? My theory is that the compilation of the function is
happening before the INSERT happens; therefore the sequence hasn't been
incremented yet; therefore there's no currval() for this backend
instance. If you use
Well, you should probably get yourself a good SQL book :) but here's a try
(untested). How about something like:
SELECT DISTINCT title_no, paidto_date
FROMtable1, table2
WHERE table1.title_no = table2.title_no
AND table1.effect_date <> table2.paidto_date;
Again
trom wrote:
>
> On Sun, Mar 11, 2001 at 10:38:10PM +0100, Peter Eisentraut wrote:
> > Andrew Perrin writes:
> >
> > > I'm trying to write what should be a simple function that returns the
> > > minimim of two integers. The complication is that when one of th
Greetings-
I'm trying to write what should be a simple function that returns the
minimim of two integers. The complication is that when one of the two
integers is NULL, it should return the other; and when both are NULL, it
should return NULL. Here's what I've written:
CREATE FUNCTION min(int4,
Folks-
I inadvertantly compiled pg 7.0.3 without gnu readline installed, so I now
don't have access to lots of the nice command-line utilities it would have
conveyed. Is there any way to add in the functionality now, or do I need
to recompile? If I need to recompile, how can I do so without wipin
psql
\i filename.txt
-Andy Perrin
"Jeff S." wrote:
>
> I want to build my tables by placing all the sql
> statements in a file. What is the correct way to use
> this file with psql?
>
> Example: My text file has this in it:
>
> CREATE TABLE table1 (
>table1_id serial,
>field1 char(5
',' || arg2 || ',' || arg3 || ',' || arg4 || ',' ||
> arg5)
>
> i.e.:
>
> create function minimum(text) returns integer
>
> and then do the parsing internally ('specially if you're using perl).
> Pretty bad, but it's an opti
Greetings.
I find myself in need of a minimum() function. This is different from the
min() function, which operates across records; what I need is to be able
to say:
UPDATE foo SET newcol = minimum(field1, field2, field3, field4);
>From what I can tell there is no such beast built in, but I wou
Or how about just:
SELECT count(*) FROM tablename WHERE a > b;
--
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology
Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin
[EMAIL PROTECTE
How 'bout these:
fgdata=# select * from fruit order by dt desc limit 1;
number | fruit | dt
+-+
20 | Oranges | 2000-06-07 00:00:00-05
(1 row)
fgdata=# select * from fruit where fruit='Apples' order by dt desc limit
1;
number |
I hope that title line is reasonably accurate. Here's what I'm trying to
do, and would love it anyone can provide guidance.
I have a table of utterances in a focus group setting; each record
contains an identifier for the speaker and group, as well as the length of
the utterance (in words) and th
33 matches
Mail list logo
