[SQL] "Subclassing" in SQL

[Andrew Perrin]

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 then several boolean variables, each
representing whether a substantive concept is present in the utterance or
not.  The trouble is that some of these concept variables (called
'codes') really are subsets of one another. For example, one code (called
`cd_interest') is a particular instance of another code (called
'cd_pragmatic').  My question is whether there is any way to represent
this relationship in SQL, without changing the underlying data. That is, I
don't want to simply do:

UPDATE statements SET cd_pragmatic = 't' WHERE cd_interest;

because it's theoretically possible for me to change this conceptual
relationship in the future.  What I think I'm looking for is some sort of
a join that will cause postgres to consider cd_pragmatic as True whenever
cd_interest is true.

Any thoughts?

--
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology  
Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin
[EMAIL PROTECTED] - [EMAIL PROTECTED]

Re: [SQL] Help retrieving lastest record

[Andrew Perrin]

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 | fruit  |   dt   
++
 15 | Apples | 1999-07-20 00:00:00-05
(1 row)

Cheers,
Andy Perrin

--
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology  
Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin
[EMAIL PROTECTED] - [EMAIL PROTECTED]
On Thu, 15 Feb 2001, Steve Meynell wrote:

> Ok what I am trying to do is select out of the database the latest
> record meeting a certain criteria.
> 
> Example:
> 
> Number |Fruit | Date
> 15Apples  July 20, 1999
> 20OrangesJune 7, 2000
> 13 PearsJan 31, 2000
> 17 Apples April 10, 1999
> Now what I need to do is select the oranges out because the date is the
> latest one, something like:
> 
> select * from basket where max(date);
> This would yield me:
> 20OrangesJune 7, 2000
> 
> I know this doesn't work but I need something like it.
> or something like
> 
> select * from basket where max(date) and fruit='Apples';
> This would yield me:
> 15Apples  July 20, 1999
> 
> Thank you in advance,
> 
> 
> --
> Steve Meynell
> Candata Systems
> 
> 
> 

Re: [SQL] sum(bool)?

[Andrew Perrin]

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 PROTECTED] - [EMAIL PROTECTED]
On Fri, 23 Feb 2001, Daniel Wickstrom wrote:

> > "Olaf" == Olaf Zanger <[EMAIL PROTECTED]> writes:
> 
> Olaf> hi there i'd like to add up the "true" values of a
> Olaf> comparison like
> 
> Olaf> sum(a>b)
> 
> Olaf> it just doesn't work like this
> 
> Olaf> any workaround
> 
> Try using a case statement:
> 
> select sum(case when  a > b then 1 else 0 end) 
> 
> 
> -Dan
> 

[SQL] create function w/indeterminate number of args?

[Andrew Perrin]

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 would be happy
to be proved wrong.

Anyway... I can write such a function easily enough in perl, probably
something like:

  my $min=$_[0];
  $min > $_ and $min = $_ foreach @_;
  return $min;

but I can't determine how to allow varying numbers of arguments to be
passed to a function. That is, sometimes I need minimum(arg1, arg2) but
sometimes it's minimum(arg1, arg2, arg3, arg4, arg5), etc.  

Thanks-
Andy Perrin

--
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology  
Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin
[EMAIL PROTECTED] - [EMAIL PROTECTED]

Re: [SQL] create function w/indeterminate number of args?

[Andrew Perrin]

In case anyone else was interested in this issue: I hadn't fully
understood the power of the fact that min(int4,int4) was a different
function from min(int4,int4,int4).  It's not exactly an implementation
of an indeterminate number of arguments, but I used the feature to make
min() work for any number of arguments up to 6.  The method is obviously
extensible further, but 6 is all I need for the moment. It's still ugly,
but maybe slightly less so than Ansley's kind solution.  Here's the SQL
code:

CREATE FUNCTION min(int4, int4)
RETURNS int4
AS 'BEGIN
IF $1 > $2
THEN
RETURN $2;
ELSE
RETURN $1;
END IF;
END;'
LANGUAGE 'plpgsql';

CREATE FUNCTION min(int4,int4,int4)
RETURNS int4
AS 'BEGIN
RETURN min($1, min($2, $3));
END;'
LANGUAGE 'plpgsql';

CREATE FUNCTION min(int4,int4,int4,int4)
RETURNS int4
AS 'BEGIN
RETURN min(min($1,$2),min($3,$4));
END;'
LANGUAGE 'plpgsql';

CREATE FUNCTION min(int4,int4,int4,int4,int4)
RETURNS int4
AS 'BEGIN
RETURN min($1,min($2,$3),min($4,$5));
END;'
LANGUAGE 'plpgsql';

CREATE FUNCTION min(int4,int4,int4,int4,int4,int4)
RETURNS int4
AS 'BEGIN
RETURN min(min($1,$2),min($3,$4),min($5,$6));
END;'
LANGUAGE 'plpgsql';



> Michael Ansley wrote:
> 
> Really ugly, but you could cast to string and concatenate with commas:
> 
> minimum(arg1 || ',' || 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 option ;-)
> 
> -Original Message-
> From: Andrew Perrin [mailto:[EMAIL PROTECTED]]
> Sent: 26 February 2001 05:05
> To: [EMAIL PROTECTED]
> Subject: [SQL] create function w/indeterminate number of args?
> 
> 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 would be
> happy
> to be proved wrong.
> 
> Anyway... I can write such a function easily enough in perl, probably
> something like:
> 
>   my $min=$_[0];
>   $min > $_ and $min = $_ foreach @_;
>   return $min;
> 
> but I can't determine how to allow varying numbers of arguments to be
> passed to a function. That is, sometimes I need minimum(arg1, arg2)
> but
> sometimes it's minimum(arg1, arg2, arg3, arg4, arg5), etc.
> 
> Thanks-
> Andy Perrin
> 
> --
> 
> Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology
> Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin
> [EMAIL PROTECTED] - [EMAIL PROTECTED]
> 
> **
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom they
> are addressed. If you have received this email in error please notify
> Nick West - Global Infrastructure Manager.
> 
> This footnote also confirms that this email message has been swept by
> MIMEsweeper for the presence of computer viruses.
> 
> www.mimesweeper.com
> **

-- 
--
Andrew J. Perrin - Programmer/Analyst, Desktop Support
Children's Primary Care Research Group, UNC - Chapel Hill
(919)966-9394 * [EMAIL PROTECTED]

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] How do I use text script containing SQL?

[Andrew Perrin]

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),
>PRIMARY KEY (table1_id)
> );
> 
> I want to be able to use the file to create my table.
> I've tried psql -d databasename -e < filename.txt
> but that doesn't work.
> 
> __
> Do You Yahoo!?
> Get email at your own domain with Yahoo! Mail.
> http://personal.mail.yahoo.com/
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] recompiling to use gnu readline?

[Andrew Perrin]

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 wiping out
existing data?

Thanks.

--
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology  
Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin
[EMAIL PROTECTED] - [EMAIL PROTECTED]


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] PL/PgSQL and NULL

[Andrew Perrin]

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, int4)
RETURNS int4
AS 'BEGIN
IF $1 ISNULL
THEN
RETURN $2;
ELSE 
 IF $2 ISNULL
 THEN
RETURN $1;
 ELSE 
  IF $1 > $2
  THEN
RETURN $2;
  ELSE
RETURN $1;
  END IF;
 END IF;
END IF;
END;'
LANGUAGE 'plpgsql';

and here's what I get:

fgdata=#  select min(10, NULL);
 min 
-

(1 row)

so it looks like, for whatever reason, it's returning NULL when it should
be returning 10. Can anyone offer advice?

Thanks.

--
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology  
Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin
[EMAIL PROTECTED] - [EMAIL PROTECTED]


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] PL/PgSQL and NULL

[Andrew Perrin]

Thanks - I'll work on it that way. I know the general-case min() should
probably return NULL if any element is null, but I'm in need of what I
described for a specific case in which the result should be "the minimum
non-null entry", which of course is NULL if all entries are null.

--
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology  
Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin
[EMAIL PROTECTED] - [EMAIL PROTECTED]

On Sun, 11 Mar 2001, Ross J. Reedstrom 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 the two
> > > integers is NULL, it should return the other; and when both are NULL, it
> > > should return NULL.
> > 
> > Functions involving NULLs don't work well before version 7.1.
> > 
> 
> True but a little terse, aren't we Peter? Functions all return null if
> any of their parameters are null, prior to v 7.1, as Peter pointed out.
> In 7.1, they only behave this way if marked 'strict'.
> 
> Arguably, that's the _right_ behavior for the case your describing:
> in tri-valued logic, NULL means UNKNOWN: it could be any value. So
> min(x,NULL) is UNKNOWN for any value of x, since the NULL could be larger
> or smaller.  If you want to do it anyway, you'll have to code your logic
> directly in the SQL query. You'll find the COALESCE function useful:
> it returns the first non-NULL argument. Combined with CASE, you should
> be able to do return the minimum, non-null entry.
> 
> Exact code left as an excercise for the reader. ;-)
> 
> Ross
> 


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] SQL Dummy Needs Help

[Andrew Perrin]

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, untested - try it and see.

--
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology  
(Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill)
[EMAIL PROTECTED] - http://www.unc.edu/~aperrin

On Fri, 9 Mar 2001, Alder wrote:

> I'm pretty new to SQL and can't figure out how to write what should be a
> simple query of two tables.  Could someone here possibly help me out?
> 
> Table 1 has two fields I'm interested in: TITLE_NO and EFFECT_DATE
> Table 2 also has two fields of interest:TITLE_NO and PAIDTO_DATE
> 
> TITLE_NO is unique in Table 1, meaning each TITLE will have a unique
> EFFECT_DATE.  Table 2 represents an accounting history of each TITLE, so for
> each TITLE_NO there may be one or more PAIDTO_DATE values.  In both Tables
> the _DATE fields are stored as 9-character strings in the fomat MMDD.
> In all cases, the MM and DD values in Table 2 should be identical with those
> in Table 1.
> 
> My intention is to produce a list that will contain the TITLE_NO of each
> TITLE where the MMDD value for EFFECT_DATE differ from any of the
> PAIDTO_DATE values for that TITLE_NO.  The list must contain ONLY the
> PAIDTO_DATE values that differ, and the corresponding TITLE_NO.
> 
> Sorry I can't explain this a little more technically, but if anyone can
> advise me, that would be fabulous.
> 
> Thanks,
> Terry
> 
> 
> 
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Rule/currval() issue

[Andrew Perrin]

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 nextval(), you'll get the next increment, which
should be appropriate.

As I think about it, this could have record-locking implications in a
heavy use environment, since the possibility exists of another INSERT
between the nextval() and the INSERT in this situation - I don't know if
that's actually an issue, or if there would be a way around it.

Andy Perrin

Tim Perdue wrote:
> 
> This is related to the plpgsql project I was working on this morning. I'm
> trying to create a rule, so that when a row is inserted into a certain table,
> we also create a row over in a "counter table". The problem lies in getting
> the primary key value (from the sequence) so it can be inserted in that
> related table.
> 
> I tried a couple different approaches. Neither works.
> 
> artifact_group_list is a table where the primary key, group_artifact_id is
> SERIAL type. When I insert a row, I want to get that new value in my rule.
> 
> --
> --  Define a rule so when you create a new ArtifactType
> --  You automatically create a related row over in the counters table
> --
> CREATE RULE artifactgroup_insert_agg AS
> ON INSERT TO artifact_group_list
> DO INSERT INTO
> artifact_counts_agg (group_artifact_id,count,open_count)
> VALUES (currval('artifact_grou_group_artifac_seq'),0,0);
> 
> I get this:
> 
> ERROR: artifact_grou_group_artifac_seq.currval is not yet defined in this session
> 
> If I write the rule this way:
> 
> CREATE RULE artifactgroup_insert_agg AS
> ON INSERT TO artifact_group_list
> DO INSERT INTO
> artifact_counts_agg (group_artifact_id,count,open_count)
> VALUES (new.group_artifact_id,0,0);
> 
> ...it doesn't fail with an error, but the sequence increments twice.
> 
> Tim
> 
> --
> Founder - PHPBuilder.com / Geocrawler.com
> Lead Developer - SourceForge
> VA Linux Systems
> 
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html

-- 
--
Andrew J. Perrin - Programmer/Analyst, Desktop Support
Children's Primary Care Research Group, UNC - Chapel Hill
(919)966-9394 * [EMAIL PROTECTED]

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] creating "job numbers"

[Andrew Perrin]

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
   the current backend; and
currval('sequencename') -> the number that was last assigned in the
   current backend (undefined if there's been
   no INSERT in this session)

Hope this helps.

--
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology  
(Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill)
[EMAIL PROTECTED] - http://www.unc.edu/~aperrin

On Thu, 22 Mar 2001, postgresql wrote:

> I have been working with PG for about 2 months now. I am creating a  
> job tracking system for my company. I have written a front end on the 
> workstations (all macintoshes) that seems to be working quite well. 
> However, I have a problem with a concept.
> 
> In my current setup I have only one workstation  that is actually 
> inputting new jobs. So, I took the expedient way to create the  job 
> number. Ask PG to count the rows, add a magic number and insert 
> this data. This all happens in one connection. What are the odds of 
> two people hitting the db at the same time? In the current set up nil. 
> There is only one entry computer. I want to change the system to use 
> a job number generated by PG. I created a test  table and I  am 
> playing with inserting and the sequence function works great. 
> However, I am at a loss of how to pick up this next (last) job. I have 
> read the docs and I still am confused. I can not first ask with the 
> number will be, and asking for the previous oid after  the fact can 
> also lead to  the same problem. so that leaves me  with, 1 ask for 
> that last oid from this  workstation ip, or 2 since a job is inserted with 
> data, I could do a select of this data after the insert (not very elegant).
> 
> How are you professionals handling this problem? I like the ability to 
> insert and have the system give me the number. As I grow into more 
> workstations inputting the jobs I won't have to worry about chasing 
> the next  highest number.
> 
> Thanks,
> Ted P.
> 
> 
> 
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
> 


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] trigger output to a file

[Andrew Perrin]

I haven't given this a lot of thought, so take it with a grain of
salt. But my feeling is that publishing such a detailed log isn't the most
effective way to do this sort of thing. How about, instead, changing the
structure of your database to keep "old" information?  Consider, for
example, a simple phone book. You might have the following fields:

id  firstname   lastname   phone   fax

just to keep things simple.  How about, instead, having two tables:
1.) Records, which ONLY has the id column; and
2.) Data, which has:

id   rev   firstname   lastname   phone   fax

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
higher rev, to Data.  So, before:

id: 0
rev: 1
firstname: Andrew
lastname: Perrin
phone: 510-xxx-
fax:

And after:

id: 0
rev: 1
firstname: Andrew
lastname: Perrin
phone: 510-xxx-
fax:

id: 0
rev: 2
firstname: Andrew
lastname: Perrin
phone: 919-xxx-
fax:


SELECTing the highest rev will give you current data; selecting everything
for id 0 sorted by rev will give you the change history.

Just a thought.

Andy Perrin

--
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology  
(Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill)
[EMAIL PROTECTED] - http://www.unc.edu/~aperrin

On Fri, 23 Mar 2001, Jan Wieck wrote:

> pgsql-sql wrote:
> > Hello Everyone,
> >
> > Here's my simple question.
> >
> > I just want to know/get the recent changes made to a table.
> > Deeper? I wanted the Postgresql server to *publish* every
> > changes made to a table (similar to replication, incremental transfer,
> > etc.).
> > What is the best way to go about it?
> >
> > My idea is to create a trigger for every insert/update that will
> > select the new/updated record and output it to a file. Or better
> > yet, I would like to output the actual sql query to file.
> > Is it possible to output the result of an sql query from a trigger
> > to a file? How?
> 
> Putting the SQL query to a file would be my approach too.
> 
> The  trigger approach lacks the capability to discard changes
> already logged in case of a  transaction  rollback.  Thus,  I
> wouldn't buy it.
> 
> For the query string logging, alot more is required. Not only
> the queries themself are  needed,  you'd  need  to  serialize
> snapshot creation, log sequence allocations and the like. And
> the program rolling forward this kind  of  log  into  another
> database  needs  control mechanisms to tell the database that
> it's in this recovery mode and has to ask for those values in
> case it needs them.
> 
> You might guess it already, I've been thinking about it for a
> year or so now. And I'm still not past  the  point  to  start
> implementing it.
> 
> >
> > I would appreciate any answer. Thank you very much.
> > Sherwin
> >
> >
> > ---(end of broadcast)---
> > TIP 6: Have you searched our list archives?
> >
> > http://www.postgresql.org/search.mpl
> >
> 
> 
> --
> 
> #==#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.  #
> #== [EMAIL PROTECTED] #
> 
> 
> 
> _
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
> 
> 
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] all views in database broken at once

[Andrew Perrin]

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 attribute 197 in relation 47074 failed
fgdata=# select * from pg_views;
ERROR:  cache lookup of attribute 317 in relation 48494 failed

A SELECT from the rebuilt query itself works fine, so I know it's not
actually a data problem. Is there anything I can do to rebuild these
views? I don't think I have the original SQL sitting around to drop and
recreate them.

Thanks for any advice.

--
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology  
(Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill)
[EMAIL PROTECTED] - http://www.unc.edu/~aperrin


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] all views in database broken at once

[Andrew Perrin]

Thanks - I appreciate the quick reply. As it turns out, I was able to find
the original SQL I used to generate (most of) the queries, so I'm
okay. But I'm intrigued: what is it that causes this? Is it *my*
recreating the view on which the other views depend, or is it some
internal glitch?

Thanks again.

--
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology  
(Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill)
[EMAIL PROTECTED] - http://www.unc.edu/~aperrin

On Sat, 24 Mar 2001, 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
> 
> > A SELECT from the rebuilt query itself works fine, so I know it's not
> > actually a data problem. Is there anything I can do to rebuild these
> > views? I don't think I have the original SQL sitting around to drop and
> > recreate them.
> 
> You're in deep trouble :-(.
> 
> It's at least theoretically possible to fix this by hand, but it'll be
> tedious.  You'll need to dump out the "compiled" form of the view rule
> for each broken view, manually correct the OID for each referenced view,
> and UPDATE pg_rewrite with the corrected rule string.
> 
> A quick example:
> 
> regression=# create view vv1 as select * from int8_tbl;
> CREATE
> regression=# select ev_action from pg_rewrite where rulename = '_RETvv1';
> 
> ({ QUERY :command 1  :utility <> :resultRelation 0 :into <> :isPortal false 
>:isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable ({ RTE 
>:relname vv1 :relid 147764  :subquery <> :alias { ATTR :relname *OLD* :attrs <>} 
>:eref { ATTR :relname *OLD* :attrs ( "q1"   "q2" )} :inh false :inFromCl false 
>:checkForRead false :checkForWrite false :checkAsUser 0} { RTE :relname vv1 :relid 
>147764  :subquery <> :alias { ATTR :relname *NEW* :attrs <>} :eref { ATTR :relname 
>*NEW* :attrs ( "q1"   "q2" )} :inh false :inFromCl false :checkForRead false 
>:checkForWrite false :checkAsUser 0} { RTE :relname int8_tbl :relid 18887  :subquery 
><> :alias <> :eref { ATTR :relname int8_tbl :attrs ( "q1"   "q2" )} :inh true 
>:inFromCl true :checkForRead true :checkForWrite false :checkAsUser 256}) :jointree { 
>FROMEXPR :fromlist ({ RANGETBLREF 3 }) :quals <>} :rowMarks () :targetList ({ 
>TARGETENTRY :resdom { RESDOM :resno 1 :restype 20 :restypmod -1 :resname q1 :reskey 0 
>:reskeyop 0 :res!
so!
> rtgroupref 0 :resjunk false } :expr { VAR :varno 3 :varattno 1 :vartype 20 
>:vartypmod -1  :varlevelsup 0 :varnoold 3 :varoattno 1}} { TARGETENTRY :resdom { 
>RESDOM :resno 2 :restype 20 :restypmod -1 :resname q2 :reskey 0 :reskeyop 0 
>:ressortgroupref 0 :resjunk false } :expr { VAR :varno 3 :varattno 2 :vartype 20 
>:vartypmod -1  :varlevelsup 0 :varnoold 3 :varoattno 2}}) :groupClause <> :havingQual 
><> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <> 
>:resultRelations ()})
> 
> What you need to fix are the :relid fields of the RTE entries for the
> referenced tables.  The :relname field of the RTE gives the real name
> of the table it references, and you look in pg_class for the associated
> OID.  For example,
> 
> regression=# select oid from pg_class where relname = 'int8_tbl';
>   oid
> ---
>  18887
> (1 row)
> 
> shows that the above view's reference to int8_tbl isn't broken.
> 
> Of course you'll need to be superuser to do the UPDATE on pg_rewrite,
> and you will probably find that you need to quit and restart the backend
> before it will use the changed view definition.
> 
> Good luck!
> 
>   regards, tom lane
> 
> PS: Yes, I know we gotta fix this...
> 
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
> 
> http://www.postgresql.org/search.mpl
> 


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] serial type; race conditions

[Andrew Perrin]

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 number_i_care_about FROM table WHERE serial_number =
currval('serial_number_seq');

ap

--
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology  
(Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill)
[EMAIL PROTECTED] - http://www.unc.edu/~aperrin

On Thu, 29 Mar 2001, Bruce Momjian wrote:

> > How does currval work if you are not inside a transaction. I have 
> > been experimenting with inserting into a table that has a sequence. 
> > If the insert fails (not using a transaction) because of bad client input 
> > then the next insert gets the proper next number in the sequence.
> 
> If you are in a transaction, and the INSERT succeeds but the transaction
> rolls back, the sequence does not get reused.  Each backend has a local
> variable that holds the most recent sequence assigned.  That is how
> currval works.
> 
> > 
> > given sequence 1,2,3,4,5 exists
> > insert into table date 1/111/01 (obviously wrong) insert fails...
> > try again with good data, insert succeeds and gets number 6 in the 
> > sequence.
> > 
> > i'm getting what I want. A sequence number that does not increment 
> > on a failed insert. However, how do I get the assigned sequence 
> > number with currval when I am not using a transaction? What 
> > happens when multiple users are inserting at the same time? 
> > 
> > I am trying to create a sequence with out any "missing" numbers. If 
> > there is a failure to insert, and a sequence number is "taken". I want 
> > the empty row.
> > 
> > Thanks,  it is getting clearer
> 
> You really can't use sequences with no gaps.  Sequence numbers are not
> _held_ until commit because it would block other backends trying to get
> sequence numbers.
> 
> -- 
>   Bruce Momjian|  http://candle.pha.pa.us
>   [EMAIL PROTECTED]   |  (610) 853-3000
>   +  If your life is a hard drive, |  830 Blythe Avenue
>   +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026
> 
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
> 


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] use of arrow keys to traverse history

[Andrew Perrin]

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 completion within psql (the program), not postgres
(the user).  If that's what you want, you have to compile psql with gnu
readline support, which means you also have to have the gnu readline
libraries available on your machine.

Andy Perrin

"Peter J. Schoenster" wrote:
> 
> Hi,
> 
> Not sure where this question belongs ... I thought postgresql was
> running under the bash shell where I can use up and down arrow
> keys to traverse my command history. I can do this in mysql but
> oddly not in oracle or postgresql.
> 
> /home/postgres
> 
> -rw-r--r--   1 postgres postgres 1422 Feb 16 15:50 .Xdefaults
> -rw---   1 postgres postgres  458 Feb 17 16:59 .bash_history
> -rw-r--r--   1 postgres postgres   24 Feb 16 15:50 .bash_logout
> -rw-r--r--   1 postgres postgres  230 Feb 16 15:50 .bash_profile
> -rw-r--r--   1 postgres postgres  313 Feb 17 16:36 .bashrc
> 
> Which in my ignorance leads me to believe that postgres will run in
> the bash shell and so I expect the use of arrow keys or command
> history.
> 
> Clues appreciated.
> 
> Peter
> 
> ---
> "Reality is that which, when you stop believing in it, doesn't go
> away".
> -- Philip K. Dick
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

-- 
--
Andrew J. Perrin - Programmer/Analyst, Desktop Support
Children's Primary Care Research Group, UNC - Chapel Hill
(919)966-9394 * [EMAIL PROTECTED]

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Select most recent record?

[Andrew Perrin]

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 cutoff date; is this possible?
> 
> select * from log order by timestamp desc limit 1;
> 
> regards, tom lane
> 
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

-- 
--
Andrew J. Perrin - Programmer/Analyst, Desktop Support
Children's Primary Care Research Group, UNC - Chapel Hill
(919)966-9394 * [EMAIL PROTECTED]

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Case Insensitive Queries

[ANDREW PERRIN]

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
University of North Carolina, Chapel Hill
269 Hamilton Hall CB#3210, Chapel Hill, NC 27599-3210 USA
   [EMAIL PROTECTED] - http://www.unc.edu/~aperrin

On 29 May 2001, Mark wrote:

> Is it possible to execute a query using a where clause that allows case
> insensitive comparison between a field and text.
> 
> For example:
> 
> select * from account where username = 'test'
> 
> where username could be 'Test', which would be a match.  As is, this
> compare is case sensitive.
> 
> grep'd the source, but stricmp is only used for keywords and not actual
> column data.
> 
> Any help would be greatly appreciated.
> 
> Thanks,
> 
> Mark
> 
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
> 


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Standards

[ANDREW PERRIN]

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 foo values (NULL);
insert into foo values (NULL);
insert into foo values (NULL);
insert into foo values (NULL);


select count(*) from foo where name=NULL;
returns 0

select count(*) from foo where name is null;
returns 4

select count(*) from foo where name <> "bar";
returns 0

Cheers,
Andy

-
   Andrew J. Perrin - Assistant Professor of Sociology
University of North Carolina, Chapel Hill
269 Hamilton Hall CB#3210, Chapel Hill, NC 27599-3210 USA
   [EMAIL PROTECTED] - http://www.unc.edu/~aperrin

On Thu, 7 Jun 2001, Mark Stosberg wrote:

> Stephan Szabo wrote:
> > 
> > On Wed, 6 Jun 2001, Tom Lane wrote:
> > 
> > > Stephan Szabo <[EMAIL PROTECTED]> writes:
> > > > Yes, column = NULL should *never* return true according to the spec (it
> > > > should always return NULL in fact as stated).  The reason for breaking
> > > > with the spec is AFAIK to work with broken microsoft clients that seem to
> > > > think that =NULL is a meaningful test and generate queries using that.
> 
> > I'd rather have the default be the spec correct behavior
> > and let people configure their server to follow the misinterpretation.
> 
> I like that idea as well. Someone like me who didn't know that this
> feature was in there for M$ could have assumed it _was_ standard
> behavior, and started using it as a habit. Then when I started porting
> my code to another database, I'd have an extra surprise in for me. :) 
> 
> Rather than being an option targeted at just this piece of grammer,
> perhaps it could a piece of a potentially larger option of "stricter
> standards compliance." I realize there are a number of useful extensions
> to the SQL standard in Postgres (which I like and use.), but it seems
> like there would be uses for minimizing non-standard behavior, as well. 
> 
> Thank you all for your contributions to Postgres-- I use it everyday. :) 
> 
>   -mark
> 
> http://mark.stosberg.com/
> 
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly
> 


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

[SQL] LIMIT within UNION?

[Andrew Perrin]

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 include *all* those eligible in the exposure group, plus enough
from the control group to bring the total number up to 200. (Yes, there is
a valid reason for this.) Furthermore, I need to sort the output of the
two groups *together* by zip code.

What I've done is to write a script that counts the number of eligible
exposure candidates:

SELECT count(*) FROM participants WHERE 
 AND typenr=1

Then subtract that number (currently 28) from 200 to get 172 control
participants. Then the problem starts. 

SELECT ... FROM participants
WHERE typenr=1 AND 
UNION
SELECT ... FROM participants
WHERE typenr=2 LIMIT 172
ORDER BY zip;

returns ERROR:  parser: parse error at or near "ORDER"

I've tried a variety of parentheses to no avail.

Can someone shed some light?

Thanks!

--
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



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] LIMIT within UNION?

[Andrew Perrin]

Thanks! That did it.

The inner parens are necessary - without them the ORDER BY seems to be
parsed as part of the second subquery and is therefore a syntax error.

Best,
Andy

--
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 Thu, 12 Sep 2002, Tom Lane wrote:

> Andrew Perrin <[EMAIL PROTECTED]> writes:
> > SELECT ... FROM participants
> > WHERE typenr=1 AND 
> > UNION
> > SELECT ... FROM participants
> > WHERE typenr=2 LIMIT 172
> > ORDER BY zip;
> 
> I think you need
> 
> SELECT * FROM
> (
> SELECT ... FROM participants
> WHERE typenr=1 AND 
> UNION
> (SELECT ... FROM participants
> WHERE typenr=2 LIMIT 172)
> ) ss
> ORDER BY zip;
> 
> Not sure if the inner set of parens is essential, but it might be.
> The outer SELECT superstructure is definitely necessary to give a
> place to hang the ORDER BY on.
> 
>   regards, tom lane
> 


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] LIMIT within UNION?

[Andrew Perrin]

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 
>   UNION
>   SELECT ... FROM participants
>   WHERE typenr=2 LIMIT 172 )
> ORDER BY zip;
> 

Unfortunately in this case the LIMIT is applied to the fully-UNIONed set,
limiting the total number of cases to 172 instead of just those from the
second subquery. Tom Lane's example worked, though.

Thanks,
Andy


--
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





---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] LIMIT within UNION?

[Andrew Perrin]

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. 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 include *all* those eligible in the exposure group, plus enough
> > from the control group to bring the total number up to 200. (Yes, there is
> > a valid reason for this.) Furthermore, I need to sort the output of the
> > two groups *together* by zip code.
> 
> Do you get more than 200 if there are more eligible people 

Yes - in the (rather rare) case that there are 200 or more eligible
exposure subjects, the result set should be the total number of eligible
exposure subjects.

> and does the
> ... ever include the same person in both sides of the union?

No; each person is only in one of the two sides.

> 
> If not in the second case, union all would probably save the database
> some extra work since it won't have to try to weed out duplicates.

I'll try that.

> 
> If not in the first case, then wouldn't a limit 200 on the after union
> result set work rather than a separate count and subtraction?
> 

Interesting - this would count on the UNION including all cases in the
first query before those in the second query. Are UNIONed records
presented in any predictable order?



--
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





---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] SQL formatter?

[Andrew Perrin]

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   bar
WHERE  baz
   AND bop
   AND NOT boo
;

Thanks,
Andy

--
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



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] SQL formatter?

[Andrew Perrin]

Unfortunately it is Windows based. The emacs mode for SQL is pretty
primitive too. Oh well - maybe I'll write one someday.

Thanks,
Andy

--
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 Sep 2002, Philip Hallstrom wrote:

> Looks to be windows based, but...
> 
> 
>http://www.techno-kitten.com/PBL_Peeper/Online_Manual/SQL_Formatter/sql_formatter.html
> 
> first hit when searching on google for "sql formatter".  there were a lot
> of other options...
> 
> You might look at how some of those C code indenter's work.  Seems like
> some of them support multiple languages which means they maybe have some
> sort of "language definition" so 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 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   bar
> > WHERE  baz
> >AND bop
> >AND NOT boo
> > ;
> >
> > Thanks,
> > Andy
> >
> > --
> > 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
> >
> >
> >
> > ---(end of broadcast)---
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
> >
> 
> 
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Case Sensitive "WHERE" Clauses?

[Andrew Perrin]

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

--
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 Thu, 26 Sep 2002, Jordan Reiter wrote:

> Are string comparisons in postgresql case sensitive?
> 
> I keep on having this response:
> 
> SELECT *
> FROM People
> WHERE first_name='jordan'
> 
> Result: 0 records
> 
> SELECT *
> FROM People
> WHERE first_name='Jordan'
> 
> Result: 1 record
> 
> I though that string matching in SQL was case-insensitive. Isn't this correct? If 
>not, what workarounds have been used successfully before? Obviously, formatting the 
>search string for the query is not a solution...
> -- 
> 
> Jordan Reiter  mailto:[EMAIL PROTECTED]
> Breezing.com   http://breezing.com
> 1106 West Main St  phone:434.295.2050
> Charlottesville, VA 22903  fax:603.843.6931
> 
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly
> 


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] error...what to do?

[Andrew Perrin]

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 difference operator hope it works
> beckerbalab2-> SELECT ffix_ability.name, ffix_ability.cost
> beckerbalab2-> FROM ffix_can_learn NATURAL JOIN  ffix_ability
> beckerbalab2-> WHERE ffix_can_learn.character_name = 'Steiner';
> ERROR:  No such attribute or function 'name'
  

Your ffix_ability table contains the columns
"ability_name",'ability_description","type", and "cost".  There's no
column called "name".

Try again with the line above as SELECT ffix_ability.ability_name

ap

--
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 Sat, 12 Oct 2002, George wrote:

> The I am trying to do a set difference query. The query question is as
> follows: 3.Find the names and costs of all abilities that Zidane can
> learn,
> 
>  but that Steiner cannot. Can anyone help with this ..please.
> 
>  
> 
> The tables to use are as follows: 
> 
> beckerbalab2=> select * from ffix_ability;
> 
> ability_name |  ability_description   |
> type| cost
> 
> --++
> +--
> 
> Flee | Escape from battle with high probability.  |
> Active |0
> 
> Cure | Restores HP of single/multiple.|
> Active |6
> 
> Power Break  | Reduces the enemy's attack power.  |
> Active |8
> 
> Thunder Slash| Causes Thunder damage to the enemy.|
> Active |   24
> 
> Auto-Haste   | Automatically casts Haste in battle.   |
> Passive|9
> 
> Counter  | Counterattacks when physically attacked.   |
> Passive|8
> 
> MP+20%   | Increases MP by 20%|
> Passive|8
> 
> Thievery | Deals physical damage to the target|
> Active |8
> 
> Fire | Causes Fire damage to single/multiple targets. |
> Active |6
> 
> Flare| Causes Non-Elemental damage.   |
> Active |   40
> 
> Leviathan| Causes water damage to all enemies.|
> Active |   42
> 
>  
> 
> beckerbalab2=> select * from ffix_can_wear;
> 
>  character_name |equipment_name
> 
> +--
> 
>  Dagger | Rod 
> 
>  Dagger | Aquamarine  
> 
>  Zidane | Aquamarine  
> 
>  Vivi   | Aquamarine  
> 
>  Steiner| Diamond Sword   
> 
>  Steiner| Ragnarok
> 
>  Dagger | Running Shoes   
> 
>  Zidane | Running Shoes   
> 
>  Vivi   | Running Shoes   
> 
>  Steiner| Running Shoes   
> 
>  Dagger | Ritual Hat  
> 
>  Zidane | Ritual Hat  
> 
>  Vivi   | Ritual Hat  
> 
>  Dagger | Angel Earrings  
> 
>  Zidane | Dagger  
> 
>  Zidane | The Tower   
> 
>  Dagger | Leather Hat 
> 
>  Zidane | Leather Hat 
> 
>  Vivi   | Leather Hat 
> 
>  Vivi   | Black Robe  
> 
>  Steiner| Aquamarine  
> 
> (21 rows)
> 
>  
> 
> 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 difference operator hope it works 
> 
> beckerbalab2-> SELECT ffix_ability.name, ffix_ability.cost
> 
> beckerbalab2-> FROM ffix_can_learn NATURAL JOIN  ffix_ability  
> 
> beckerbalab2-> WHERE ffix_can_learn.character_name = 'Steiner';
> 
> ERROR:  No such attribute or function 'name'
> 
> beckerbalab2=>
> 
>  
> 
> 


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] unnecessary updates

[Andrew Perrin]

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 object. Finally, there was an object method put() that took care of
updating the database. put() checked the changed property and simply
silently finished unless changed was true.

ap

--
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, 30 Oct 2002, chester c young wrote:

> When doing database work over the web, especially when many records are
> on one page, *many* updates get posted to pg that do not change the
> record.  Eg, the page may contain 50 records, the user changes 1, and
> submits.
> 
> I assume that a no-change update takes the same resources as a "real"
> update, ie, a new block is allocated to write the record, the record
> written, indicies are rerouted to the new block, and the old block
> needs to be vacuumed later.  Is this true?
> 
> In SQL, the only way I know to prevent this thrashing is to write the
> update with an elaborate where clause, eg, "update ... where pk=1 and
> (c1!='v1' or c2!='v2' or ... )".  This adds cost both to the app server
> and to pg - is the cost justified?
> 
> Finally, is there anyway to flag pg to ignore no-change updates?  This
> seems to me to me the most efficient way of handling the needless work.
> 
> thanks
> chester
> 
> __
> Do you Yahoo!?
> HotJobs - Search new jobs daily now
> http://hotjobs.yahoo.com/
> 
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] RFC: A brief guide to nulls

[Andrew Perrin]

*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 lazy planning, and therefore not really true NULL
values. (This is not to say I don't *do* it, but that it's not
theoretically appropriate to interpret a NULL as being "no applicable
value.")  To use your example 1, I would think a properly-planned and
structured database should account for the possibility of a sexless
customer by means of relationality:

customers:
custid
name

cust_sexes:
custid
sex

which would get rid of the NULL for the sex of ACME Widgets Ltd.  By
contrast, it wouldn't get rid of the NULL for Jackie Smith, who would
appropriately be represented by an entry in customers (, 'Jackie
Smith') and one in cust_sexes (, NULL).

(Otherwise the introduction is excellent.)

Any comments?

Andy

--
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, 15 Jan 2003 [EMAIL PROTECTED] wrote:

> There have been a few posts recently where people have had problems with
> nulls. Anyone got comments on the below before I submit it to techdocs?
> 
> TIA
> 
> - Richard Huxton
> 
> A Brief Guide to NULLs
> ==
> 
> What is a null?
> ===
> A null is *not* an empty string.
> A null is *not* a value.
> A null is *not* a "special" value.
> A null is the absence of a value.
> 
> 
> What do nulls mean?
> ===
> Well, they *should* mean one of two things:
> 1. There is no applicable value
> 2. There is a value but it is unknown
> 
> Example 1: Imagine you have a customer table with name and sex fields.
> If you get a new customer "ACME Widgets Ltd", the sex field is meaningless
> since your customer is a company (case 1).
> If you get a new customer "Jackie Smith" they might be male or female, but
> you might not know (case 2).
> 
> Example 2: You have an address table with (street,city,county,postalcode)
> fields.
> You might insert an address ("10 Downing Street","London",Null,"WC1 1AA")
> since you don't have a valid county.
> You might also insert an address ("1 Any Street","Maidstone","Kent",Null)
> where there *must be* a valid postalcode, but you don't know what it is.
> 
> It might be useful to be able to distinguish between these two cases - not
> applicable and unknown, but there is only one option "Null" available to
> us, so we can't.
> 
> 
> How do nulls work?
> ==
> There is one very important rule when dealing with nulls. The result of
> any operation or comparison, when applied to a null is null. The only
> exception is testing if a value is null.
> 
> Example: with the customer table above you could run the following queries:
>   SELECT * FROM customer WHERE sex='M';
>   SELECT * FROM customer WHERE sex<>'M';
> Now you might think this returns all customers, but it will miss those
> where sex is null. You've asked for all rows where the value of sex is 'M'
> and all those with values not equal to 'M' but not rows with *no value at
> all*
> 
> It might help to think of a database as a set of statements you *know* to
> be true. A null indicates that you *cannot say anything at all* about that
> field. You can't say what it is, you can't say what it isn't, you can only
> say there is some information missing.
> 
> So, to see all the customers with unknown or inapplicable sex you would need:
>   SELECT * FROM customer WHERE sex IS NULL;
> 
> There are actually three possible results for a test in SQL - True (the
> test passed), False (the test failed) and Null (you tested against a
> null). A result of null usually gets treated as False, so testing against
> nulls always fails.
> 
> If you try to perform an operation on nulls, again the result is always
> null. So the results of all of the following are null:
>   SELECT 'abc' || null;
>   SELECT 1 + null;
>   SELECT sqrt(null::numeric);
> The first case can be especially confusing. Concatenating a null string to
> a string value will return null, not the original value.
> 
> 
> Uniqueness and nulls
> 
> If you define a unique index on a column it prevents you inserting two
> values that are the same. It does not prevent you inserting as many nulls
> as you like. How could it, you don't have a value so it can't be the same
> as any other.
> 
> Example: We create a table "ta" with a unique constraint on column "b"
>   CREATE TABLE ta (
> a int4,
>   b varchar(3),
>   PRIMARY KEY (a)
>   );
>   CREATE UNIQUE INDEX ta_b_idx ON ta (b);
>   INSERT INTO ta VALUES (1,'aaa');  -- succeeds
>   INSERT INTO ta VALUES (2,'bbb');  -- succeeds
>   INSERT INTO ta

[SQL] "Best practice" advice

[Andrew Perrin]

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 relationship is one-to-many, and I can handle that fine. The issue
is: some of the references in the core documents will be to other core
documents. Other references will be to documents that are not in the core
documents table.  I need to track whether the document referred to is
in the core table or not. The question is how best to capture this.  Ideas
I have are:

1.) A single referrals table that can track both kinds:
referring_id  --> serial number of the referring core document
referred_title
referred_author
referred_date
referred_page
referred_id   --> serial number of the referred document if it's in
  the core table; otherwise NULL


2.) Two referrals tables:
referring_id  referring_id
referred_titlereferred_id
referred_author
referred_date
referred_page


3.) A "peripheral documents" table and a referrals table:
periph_id referring_id
title referred_table
authorreferred_id
date
page

Comments?

Thanks.

--
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



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Complicated "group by" question

[Andrew Perrin]

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 be able to do this with a GROUP BY clause, but am having no
luck.

Table structure:

reviewers   assign  accept
-
reviewer_id assign_id   accept_id
reviewer_id assign_id
... assign_date
... ...


Thanks for any guidance.

Andy

--
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


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Complicated "group by" question

[Andrew Perrin]

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:

> Andrew,
>
> > 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 be able to do this with a GROUP BY clause, but am having no
> > luck.
>
> Some vagueness: you didn't say whether you wanted to see two assignments if
> they have the same, latest date.   Nor did you specify whether you wanted to
> see assignments that had not been accepted (the below assumes yes to both)
>
> Hmmm ... one way, SQL-standard:
>
> SELECT reviewer.name, assign_date, acc_id
> FROM reviewers JOIN assign ON reviewer.id = assign.reviewer_id
>   LEFT OUTER JOIN accept ON assign.id = accept.assign_id
> WHERE assign_date IN (SELECT max(ass2.assign_date) FROM assign ass2
>   WHERE ass2.reviewer_id = reviewers.id)
>
> or for a bit faster execution on PG you cann replace that WHERE clause with:
>
> WHERE assign_date IN (SELECT ass2.assign_date FROM assign ass2
>   WHERE ass2.reviewer_id = reviewers.id ORDER BY ass2.assign_date DESC LIMIT 1)
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings