[SQL] CREATE RULE ignored, what did I do wrong
OK, below is the dump of the table definition. Several other tables reference this and
have ON DELETE CASCADE. In this table there is a rule for ON DELETE. The WHERE clause
(NOT old.is_deleted) should always be the case, as the field is FALSE for all existing
entries (checked).
The cascading deletes are all performed when I delete from this table. The rule is
not. The record is NOT retained with is_deleted now TRUE. I turned on log_statement,
and saw only the queries corresponding to the cascading delete, not my DO INSTEAD
queries.
Does the cascade happen first?? If so, how do I get in ahead of it?
Thanks.
***
smoothed_rank_episode_id | integer| not null default
nextval('base_rank_episode_base_rank_episode_id_seq'::text)
base_rank_episode_id | integer| not null
smoothing_id | integer| not null default 0
smoothing_parameters | double precision[] | not null default '{}'::double
precision[]
is_deleted | boolean| default false
Indexes:
"smoothed_rank_episode_pkey" primary key, btree (smoothed_rank_episode_id)
"smoothed_rank_episode_ak1" unique, btree (base_rank_episode_id, smoothing_id,
smoothing_parameters)
Foreign-key constraints:
"$1" FOREIGN KEY (smoothing_id) REFERENCES smoothing_algorithm(smoothing_id) ON
UPDATE CASCADE ON DELETE CASCADE
Rules:
del_smoothed_rank_episode AS ON DELETE TO smoothed_rank_episode WHERE (NOT
old.is_deleted) DO INSTEAD (DELETE FROM historical_rank WHERE
(historical_rank.smoothed_rank_episode_id = old.smoothed_rank_episode_id); DELETE FROM
signal WHERE (signal.signal_episode_id IN (SELECT signal_episode.signal_episode_id
FROM signal_episode WHERE (signal_episode.smoothed_rank_episode_id =
old.smoothed_rank_episode_id))); UPDATE smoothed_rank_episode SET is_deleted = true
WHERE (smoothed_rank_episode.smoothed_rank_episode_id = old.smoothed_rank_episode_id);
)
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] CREATE RULE ignored, what did I do wrong
I was trying to implement a pseudo-delete, where the (millions of) records in several child tables were actually deleted, but a flag was set in the summary table instead of deleting it, as an archiving mechanism. (If the flag was already set, the WHERE clause in the RULE should be false, and the delete happen as usual?!) The FK relation below has the summary table as the child, and isn't critical. It's the tables for which this is the parent that are the issue. Do you have an idea how to implement this best? Tom Lane <[EMAIL PROTECTED]> wrote .. > [EMAIL PROTECTED] writes: > > Foreign-key constraints: > > "$1" FOREIGN KEY (smoothing_id) REFERENCES smoothing_algorithm(smoothing_id) > ON UPDATE CASCADE ON DELETE CASCADE > > Rules: > > del_smoothed_rank_episode AS ON DELETE TO smoothed_rank_episode > > WHERE (NOT old.is_deleted) DO INSTEAD ... > > The DELETE commands generated by the foreign key ON DELETE CASCADE will > get rewritten by your ON DELETE rule. You probably do not want to do > this; or at least not make it an INSTEAD rule. > > There has been some debate in the past about whether rules should be > able to break foreign-key constraints, but I tend to class it as a > "you should know what you're doing" feature. Preventing this kind > of error would inevitably result in a serious reduction of the power > of the rule feature. > > 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])
[SQL] MOVE in SQL vs PLPGSQL
I can't find a good way to skip over a large number of records in PLPGSQL (I want to fast-forward and I don't need the I/O of reading and throwing away hundreds of records.) In SQL, I could just use MOVE. That doesn't appear to be supported in PLPGSQL?! Help? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] filtering after join
I want to use a UDF to filter tuples t that are generated after a join. More specifially, I have a UDF foo(record), which computes a value for a given tuple. I can do the filtering before the join. e.g.: select * from A, B where foo(A)<2 and A.a=B.b; But I want to apply foo() to the tuples generated by the join operation. How can I do that? Thanks! -- andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] filtering after join
Sorry for the confusion. This is what i meant. Thanks, Michael. select * from (select * from A, B where A.a = B.b) as s where foo(s) < 2; On 1/25/06, Peter Eisentraut <[EMAIL PROTECTED]> wrote: > andrew wrote: > > I want to use a UDF to filter tuples t that are generated after a > > join. More specifially, I have a UDF foo(record), which computes a > > value for a given tuple. I can do the filtering before the join. > > e.g.: > > > > select * from A, B where foo(A)<2 and A.a=B.b; > > What makes you think that the filtering happens before the join here? > And why would it matter? The results should be all the same. > > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ > -- andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] filtering after join
But the function foo() would produce different values for the two queries, so the result will be different. A simple example is foo() computes the sum of all the integer fields of the input record. On 1/26/06, Peter Eisentraut <[EMAIL PROTECTED]> wrote: > andrew wrote: > > Sorry for the confusion. This is what i meant. Thanks, Michael. > > > > select * > > from (select * from A, B where A.a = B.b) as s > > where foo(s) < 2; > > > > On 1/25/06, Peter Eisentraut <[EMAIL PROTECTED]> wrote: > > > andrew wrote: > > > > I want to use a UDF to filter tuples t that are generated after a > > > > join. More specifially, I have a UDF foo(record), which computes > > > > a value for a given tuple. I can do the filtering before the > > > > join. e.g.: > > > > > > > > select * from A, B where foo(A)<2 and A.a=B.b; > > Note that these two queries will produce the same internal execution > plan, so if you wanted to make some difference you will not. > > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ > -- andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] filtering after join
How will the query planner do for a nesting query? Treat the subqueries as multiple queries and then link them together? where can I find the information (codes or documents)? Thanks. On 1/26/06, Peter Eisentraut <[EMAIL PROTECTED]> wrote: > andrew wrote: > > But the function foo() would produce different values for the two > > queries, so the result will be different. > > A simple example is foo() computes the sum of all the integer fields > > of the input record. > > OK, I see now where you're getting at. You want to combine the record > type of A and B into one. Then the proposed solution is right. > > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ > -- andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] filtering after join
I can see the final plan by using the EXPLAIN command. But I want to know the procedure of the planner in handling nesting queries. Can you direct me to the corresponding part of the code and/or the documents? Thanks. On 1/26/06, Peter Eisentraut <[EMAIL PROTECTED]> wrote: > andrew wrote: > > How will the query planner do for a nesting query? Treat the > > subqueries as multiple queries and then link them together? > > where can I find the information (codes or documents)? > > Look at the execution plan using the EXPLAIN command. > > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ > -- andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] filtering after join
I got errors in this query. I have a function complete(record) which takes a generic record type data. But it seems cannot be applied to a sub-select result: backend> explain select * from (select * from Person,Auction where Person.id=Auction.seller) as s where complete(s) QUERY: explain select * from (select * from Person,Auction where Person.id=Auction.seller) as s where complete(s) ERROR: Cannot pass result of sub-select or join s to a function On 1/27/06, andrew <[EMAIL PROTECTED]> wrote: > I can see the final plan by using the EXPLAIN command. But I want to > know the procedure of the planner in handling nesting queries. Can you > direct me to the corresponding part of the code and/or the documents? > Thanks. > > On 1/26/06, Peter Eisentraut <[EMAIL PROTECTED]> wrote: > > andrew wrote: > > > How will the query planner do for a nesting query? Treat the > > > subqueries as multiple queries and then link them together? > > > where can I find the information (codes or documents)? > > > > Look at the execution plan using the EXPLAIN command. > > > > -- > > Peter Eisentraut > > http://developer.postgresql.org/~petere/ > > > > > -- > andrew > -- andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] filtering after join
Sorry. I am working on a research prototype built on 7.3 which contains a cosiderable amount of efforts. The original developers of the prototype have no interest in upgrading it. It is also impossible for me to upgrade it by myself. Could you give me any tips to sovle the current problem? Thanks! On 1/27/06, Tom Lane <[EMAIL PROTECTED]> wrote: > andrew <[EMAIL PROTECTED]> writes: > > I got errors in this query. I have a function complete(record) which > > takes a generic record type data. But it seems cannot be applied to a > > sub-select result: > > As I seem to recall having mentioned several times already, PG 7.3 is > really, really weak in this area. (If you ever got it to work, which > I doubt you will, you'd probably then start to notice how badly it > leaks memory for whole-row operations...) > > You should either forget this idea or invest the effort to move up > to PG 8.1. > > regards, tom lane > -- andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] filtering after join
I have traced the code. It exits when the argument is the result of a join or a subselect in function ParseFuncOrColumn(). The reason mentioned in the comments is lack of named tuple type. How can force it to create such a tuple type? is there a way? thanks a million times! On 1/27/06, Tom Lane <[EMAIL PROTECTED]> wrote: > andrew <[EMAIL PROTECTED]> writes: > > I got errors in this query. I have a function complete(record) which > > takes a generic record type data. But it seems cannot be applied to a > > sub-select result: > > As I seem to recall having mentioned several times already, PG 7.3 is > really, really weak in this area. (If you ever got it to work, which > I doubt you will, you'd probably then start to notice how badly it > leaks memory for whole-row operations...) > > You should either forget this idea or invest the effort to move up > to PG 8.1. > > regards, tom lane > -- andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] m4 macros plus PostgreSQL anyone?
Has anyone used the macro processor m4 to generate PostgreSQL (or any other
sql) code automatically from a data dictionary? We have made a good start
on this, but would like to avoid reinventing any wheels, or wasting time
down blind alleys.
To illustrate the benefits of this approach, consider the following
example:
UserTable(
User,
{
{fId, text,},
{fName, text,},
{pRole, integer,},
{pGroup, integer,},
{fOk, integer,1}},
{
{fId}})
This is automatically expanded via our macro library into the maintenance
script listed at the bottom of this message. Please excuse the obvious
mistakes. We are still debugging and have only been using PostgreSQL a
couple of months and m4 for a couple of days. Any advice or recount of
your own experiences would be appreciated.
About Us: we are an IT startup based in Tasmania. It is our mission
to reengineer corporate database systems for the web using strictly open
source technology and state of the art methodologies. Naturally the bulk
of what we develop will eventually be released as open source as well,
once we get our business model sorted out. (My partner and I bet our houses
and our careers on PostgreSQL and this little venture. Any advice
on that would be welcome too. )
Cheers,
Andrew Smith
---
/* rebuild user table tUsers */
ALTER TABLE tUsers RENAME TO tOldUsers;
DROP SEQUENCE nUser;
CREATE SEQUENCE nUser;
CREATE TABLE tUsers
(
kUser INTEGER NOT NULL DEFAULT NEXTVAL('nUser'),
fId TEXT NOT NULL,
fName TEXT NOT NULL,
pRole INTEGER NOT NULL,
pGroup INTEGER NOT NULL,
fOk INTEGER NOT NULL DEFAULT 1
);
GRANT ALL ON tUsers TO PUBLIC;
BEGIN;
INSERT INTO tUsers (kUser,fId,fName,pRole,pGroup,fOk)
SELECT kUser,fId,fName,pRole,pGroup,fOk FROM
tOldUsers;
SELECT SETVAL('nUser',(SELECT MAX(kUser) FROM tUsers)) FROM tUsers
LIMIT 1;
COMMIT;
DROP TABLE tOldUsers;
CREATE INDEX tUsers0 ON tUsers(kUser);
CREATE INDEX tUsers1 ON tUsers(fId);
/* rebuild user history table hUsers */
ALTER TABLE hUsers RENAME TO hOldUsers;
DROP SEQUENCE gUser;
CREATE SEQUENCE gUser;
CREATE TABLE hUsers
(
jUser INTEGER NOT NULL DEFAULT NEXTVAL('gUser'),
hUser INTEGER NOT NULL,
hAction CHAR NOT NULL,
hWhen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
kUser INTEGER NOT NULL,
fId TEXT NOT NULL,
fName TEXT NOT NULL,
pRole INTEGER NOT NULL,
pGroup INTEGER NOT NULL,
fOk INTEGER NOT NULL DEFAULT 1
);
GRANT ALL ON hUsers TO PUBLIC;
BEGIN;
INSERT INTO hUsers (jUser,hUser,hAction,hWhen,kUser,fId,fName,pRole,pGroup,fOk)
SELECT jUser,hUser,hAction,hWhen,kUser,fId,fName,pRole,pGroup,fOk
FROM hOldUsers;
SELECT SETVAL('gUser',(SELECT MAX(jUser) FROM hUsers)) FROM hUsers
LIMIT 1;
COMMIT;
DROP TABLE hOldUsers;
CREATE INDEX hUsers0 ON hUsers(jUser);
CREATE INDEX hUsers1 ON hUsers(kUser);
SELECT SETVAL('nUser',(SELECT MAX(kUser) FROM hUsers)) FROM hUsers
LIMIT 1;
/* create rules for history table maintenance */
VACUUM ANALYZE pg_proc; DROP RULE rUsersU0;
VACUUM ANALYZE pg_proc; DROP RULE rUsersD0;
VACUUM ANALYZE pg_proc; DROP RULE rUsersI0;
VACUUM ANALYZE pg_proc; DROP VIEW vUsers;
VACUUM ANALYZE pg_proc;
CREATE VIEW vUsers AS SELECT a.fId AS hUser,b.kUser,b.fId,b.fName,b.pRole,b.pGroup,b.fOk
FROM tUsers a,tUsers b;
GRANT ALL ON vUsers TO PUBLIC;
VACUUM ANALYZE pg_proc;
CREATE RULE rUsersI0 AS ON INSERT TO vUsers DO INSTEAD
(
INSERT INTO tUsers(fId,fName,pRole,pGroup,fOk)
VALUES (new.fId,new.fName,new.pRole,new.pGroup,new.fOk);
INSERT INTO hUsers(hUser,hAction,hWhen,kUser,fId,fName,pRole,pGroup,fOk)
SELECT a.kUser,'i',CURRENT_TIMESTAMP,(SELECT
last_value FROM nUser),new.fId,new.fName,new.pRole,new.pGroup,new.fOk
FROM tUsers a WHERE a.fId
= new.hUser;
);
CREATE RULE rUsersD0 AS ON DELETE TO vUsers DO INSTEAD
(
INSERT INTO hUsers(hUser,hAction,hWhen,kUser,fId,fName,pRole,pGroup,fOk)
SELECT a.kUser,'d',CURRENT_TIMESTAMP,old.kUser,old.fId,old.fName,old.pRole,old.pGroup,old.fOk
FROM tUsers a WHERE a.fId
= old.hUser;
DELETE FROM tUsers WHERE kUser = old.kUser;
);
CREATE RULE rUsersU0 AS ON UPDATE TO vUsers DO INSTEAD
(
INSERT INTO hUsers(hUser,hAction,hWhen,kUser,fId,fName,pRole,pGroup,fOk)
SELECT a.kUser,'u',CURRENT_TIMESTAMP,new.kUser,new.fId,new.fName,new.pRole,new.pGroup,new.fOk
FROM tUsers a WHERE a.fId
= new.hUser;
UPDATE tUsers SET
fId = new.fId,
fName = new.fName,
pRole = new.pRole,
pGroup = new.pGroup,
fOk = new.fOk
WHERE kUser = old.kUser;
);
Re: [SQL] using a self referencing table
[EMAIL PROTECTED] wrote: > i have a table with this structure > > name (varchar)|category id (int4)|parent category id (int4)|leaf node > (bool) > > im trying to make a perl script that should tree the info > > parent cat > subcat > subcat2 > subcat2 > subcat > subcat2 > ... > > but im having troubles wrapping my head around this > > im using the Pg modules to access postgres > > if anyone has any suggestions please lemme know thanks! The trick is to be able to sort all the elements of the hierarchy so that they come out in the right order, that is, grouped by parent, and then indent them. You can do this with two extra redundant fields, clevel int and csort text. Every time you insert a node in the tree, include the calculated level of the node (i.e. clevel = parent->clevel + 1) and a string which ensures that the children of a given node are grouped together (i.e. csort = parent->csort + category id::text). Probably best to left pad the category id with an appropriate number of zeroes for this calculation. When you retrieve the nodes, order by csort, and convert clevel into the appropriate number of indents.
[SQL] Using a rule as a trigger.
Hi all,
I have looked at some previous posting and thought that I had found
exactly what I need. What I need is to insert an id (from a sequence)
when a new record is inserted. The following example almost does what I
need :
CREATE TABLE topics (id int, topic varchar(50), descriotion text);
CREATE SEQUENCE nextid start 1;
CREATE RULE ins_topic AS ON INSERT TO topics WHERE id ISNULL DO UPDATE
topics SET id=nextval('nextid') WHERE id ISNULL;
This example updates the last insert. I need it to update the currnet
insert. How do I do this?
Kind regards
Andrew Higgs
[SQL] "Subclassing" in SQL
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
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)?
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?
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?
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?
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?
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
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
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
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
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"
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
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
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
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
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
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?
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
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
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] simple recursive function in plpgsql fails
ians=# SELECT version();
version
---
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4
My goal is to find the last occurance of a pattern in a string. As a
helper function, I wrote this:
DROP FUNCTION reverse(text);
CREATE FUNCTION reverse(text) RETURNS text AS
'DECLARE str ALIAS FOR $1;
BEGIN IF length(str) > 1 THEN
RETURN reverse(substr(str, 2)) || substr(str, 1, 1);
ELSE
RETURN str;
END IF;
END;' LANGUAGE 'plpgsql'
ians=# SELECT reverse('q');
reverse
-
q
(1 row)
ians=# SELECT reverse('qw');
reverse
-
wq
(1 row)
ians=# SELECT reverse('qwe');
reverse
-
ewq
(1 row)
ians=# SELECT reverse('qwer');
reverse
-
rerq
(1 row)
Ooops...
----
Andrew G. Hammond [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
http://xyzzy.dhs.org/~drew/ <http://xyzzy.dhs.org/%7Edrew/>
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481
5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1
---(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] is it easy to change the create sequence algorithm?
Well, the quickest solution I can think of off hand is to not use
SERIAL. Instead, do it manually, like this:
DROP SEQUENCE my_seq;
CREATE SEQUENCE my_seq;
DROP TABLE my_table;
CREATE TABLE my_table (
my_table_id INTEGER DEFAULT nextval('my_seq') PRIMARY KEY,
...
);
Kevin Brannen wrote:
> I see in the docs that when I create a column that is of type SERIAL,
> the engine automatically creates the sequence for me, named
> TABLE_COLUMN_seq. That's great until the table name + column name
> lengths are > 27 chars, then it starts chopping, and you guessed it, I
> have multiple table/column combinations that don't differ until after
> that length.
>
> Is there a way to influence the "create sequence" generator with a
> directive, hint, set value, whatever, to be something else? (e.g.
> COLUMN_seq if I guarantee all the columns are unique)
>
> Yes I know that I could create the sequence myself, but the engine
> does such a good job. :-)
>
> Thanks,
> Kevin
>
>
> ---(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] bytea etc.
Hi , All!
Could someone clarify me :
When I want to store BLOB's internally in database (for example jpeg )
should I use bytea or OID? Is OID something like BFILE in Oracle 8i?
If both are appropriate for internal BLOB why I can't use
update image set picture=lo_import('Myfile') where image_code='blablabla' ;
with bytea column type but only with OID column type?
And one more thing:
Where is function byteain documented?
TIA,
Andrew.Do You Yahoo!?
HotJobs, a Yahoo! service - Search Thousands of New Jobs
[SQL] bytea ,etc.
Hi , All!
Could someone clarify me :
When I want to store BLOB's internally in database
(for example jpeg )
should I use bytea or OID? Is OID something like
BFILE in Oracle 8i?
If both are appropriate why I can't
use
update image set picture=lo_import('Myfile') where
image_code='blablabla'
with bytea column type but only with OID column
type?
And one more thing:
Where is function byteain documented?
TIA,
Andrew.
[SQL] LIMIT within 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 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?
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?
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?
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?
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?
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?
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?
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
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
*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
>
[SQL] "Best practice" advice
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] Help with a query for charting
I'm trying to do a query to count the number of tickets opened on each day
of a month. It'll always be from the 1st to the end of the month. This does
it:
SELECT TO_CHAR(DATE_TRUNC('day',date_opened),'DD') AS day, COUNT(*)
FROM ticket GROUP BY DATE_TRUNC('day', date_opened)
WHERE ;
But it doesn't give me a zero for the days when no tickets were opened -
which I really need because I want to graph the result.
I could do this in the application code but that doesn't seem right.
Help would be great.
Andrew
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [NOVICE] For each record in SELECT
On Sat, 2003-02-01 at 10:21, Luis Magaña wrote:
> Hi,
>
> I have a question here:
>
> I have a table with this fields:
>
> month
> description
> amount
>
> now I have to write a query that retrieves the sum of the amount from
> the minimum month to the maximum month registered for each diferent
> description.
>
> Of course there are cases when a particular description has not record
> for all the months in that period. I mean, suppouse you have this
> records:
>
> month description amount
> ---
> June description1100
> July description1500
> Augustdescription1600
> June description2300
> Augustdescription2400
>
> how you write a query that outputs something like this:
>
> JuneJulyAugust
> --
> description1 |100 500 600
> description2 |300 0 400
>
> My problem is for the 0 value.
If you have another table with columns like:
month description
Junedescription1
Julydescription1
August description1
Junedescription2
Julydescription2
August description2
Then you will be able to do an outer join to it like:
SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.month = t2.month AND
t1.description = t2.description
This will give you a NULL, rather than a zero, but your application
should be able to handle that detail.
You can also do this having two tables: one for the months, and another
for the descriptions:
SELECT * FROM months m CROSS JOIN descriptions d FULL OUTER JOIN values
v ON m.month = v.month AND d.description = v.description
I hope this is some use,
Andrew.
--
-
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694OFFICE: +64(4)499-2267
Survey for nothing with http://survey.net.nz/
-
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [GENERAL] ALTER TABLE ... DROP CONSTRAINT
--- Elielson Fontanezi <[EMAIL PROTECTED]> wrote: > Hi all! > > Who can tell me what postgres version supports > ALTER TABLE... DROP > CONSTRAINT without > the need of droping the table to remove a simple > coinstraint. (link) > > Elielson Fontanezi > PRODAM - Technical Support > Analyst I performed that task yesterday using PostgreSQL 7.3.3: Alter table mkt drop constraint mkt_pkey; I don't know if it's supported in earlier versions. Best regards, Andrew Gould ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] User-defined SQL function has slower query on 7.3.3 than
Mr. Lane:
QUERY
-
SELECT ZIPCODE
FROM LOCATIONS
WHERE
COUNTRY = 'USA' AND ZIP_DIST_MI('07306', ZIPCODE) <= 25;
I found that the 7.1.3 server performed QUERY very slowly after a VACUUM
ANALYZE. (I can't just ANALYZE in this version, right?) It's performance
was comparable to the 7.3.3 server for awhile. Then, it improved.
I don't know how to prove that an SPI query uses an index. I do know that
this SQL: select latitude, longitude from geo_zipdata where zip = $1
uses the index through PSQL.
I use an elog(NOTICE, ...) to print when the SQL get prepared, and it is
just once. geo_zipdata is never changed for the life of the database.
db=> explain
db-> select latitude, longitude from geo_zipdata where zip = '07306';
QUERY PLAN
-
Index Scan using geo_zipdata_zip_idx on geo_zipdata (cost=0.00..17.07
rows=5 width=16)
Index Cond: (zip = '07306'::character varying)
(2 rows)
I expect QUERY to need a single full table scan for each ZIPCODE. I just
think that 7500 rows should never take over a minute. PG 7.3.3 takes 9
minutes (the one time we waited for it to finish).
How many data pages could 7500 rows need? With 2 or 3 page reads, it can't
take up much memory or I/O to do that.
- Andrew
On Wed, 6 Aug 2003, Tom Lane wrote:
> Andrew Droffner <[EMAIL PROTECTED]> writes:
> > I have this QUERY (below) that PostgreSQL 7.3.X servers run MUCH slower
> > than
> > the 7.1.3 server does.
>
> I know of no reason for that to happen. Have you vacuum analyzed the
> 7.3 database?
>
> > It finds the ZIPs locations with a prepared
> > (and saved) SPI query, which uses an index:
> > "select latitude, longitude from geo_zipdata where zip = $1"
>
> How do you know it's using the index?
>
> regards, tom lane
>
--
[ Andrew Droffner
[ Advance Publications Internet
[
[ [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
[SQL] User-defined SQL function has slower query on 7.3.3 than 7.1.3
I have this QUERY (below) that PostgreSQL 7.3.X servers run MUCH slower
than
the 7.1.3 server does. It makes sense that both servers have to do a
sequential scan over the ZIPCODE column. There are over 7,500 rows in the
LOCATIONS table.
Does anyone know what changed in the planner or optimizer? Can I change
the
postgresql.conf file to improve 7.3.3 performance?
Situation
-
Here is the situation...
PG 7.1.3 returns QUERY in about 4 seconds. The EXPLAIN plan says it uses
the index on country.
PG 7.3.3 simply does not return QUERY. I've waited over 3 minutes. With
the extra condition WHERE STATE = 'NJ' it takes almost 5 seconds. Other
states are much worse.
QUERY
-
SELECT ZIPCODE
FROM LOCATIONS
WHERE
COUNTRY = 'USA' AND ZIP_DIST_MI('07306', ZIPCODE) <= 25;
The function is written in C, using SPI. Given two US ZIP codes, it
returns the distance in miles. For example, it is 78 miles from Jersey
City to Philadelphia:
db=> select ZIP_DIST_MI('07306', '19130');
zip_dist_mi
-
78.801595557406
(1 row)
ZIP_DIST_MI() uses a geo_zipdata table to get the latitude and longitude.
Using those, it can calculate the "great circle distance" between ZIPs
with C double arithmetic. It finds the ZIPs locations with a prepared
(and saved) SPI query, which uses an index:
"select latitude, longitude from geo_zipdata where zip = $1"
FUNCTION
CREATE FUNCTION ZIP_DIST_MI(TEXT, TEXT)
RETURNS DOUBLE PRECISION...
ZIP DATA TABLE
--
CREATE TABLE GEO_ZIPDATA (
ZIP VARCHAR(5) NOT NULL,
STATEVARCHAR(2) NOT NULL,
CITY VARCHAR(64) NOT NULL,
COUNTY VARCHAR(64) NOT NULL,
LATITUDE FLOAT NOT NULL,
LONGITUDE FLOAT NOT NULL,
FIPS NUMERIC(10) NOT NULL
);
CREATE INDEX GEO_ZIPDATA_ZIP_IDX ON GEO_ZIPDATA (ZIP);
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] [HACKERS] plpgsql doesn't coerce boolean expressions to boolean
Tom Lane wrote: Following up this gripe http://archives.postgresql.org/pgsql-sql/2003-09/msg00044.php I've realized that plpgsql just assumes that the test expression of an IF, WHILE, or EXIT statement is a boolean expression. It doesn't take any measures to ensure this is the case or convert the value if it's not the case. This seems pretty bogus to me. However ... with the code as it stands, for pass-by-reference datatypes any nonnull value will appear TRUE, while for pass-by-value datatypes any nonzero value will appear TRUE. I fear that people may actually be depending on these behaviors, particularly the latter one which is pretty reasonable if you're accustomed to C. So while I'd like to throw an error if the argument isn't boolean, I'm afraid of breaking people's function definitions. Here are some possible responses, roughly in order of difficulty to implement: 1. Leave well enough alone (and perhaps document the behavior). 2. Throw an error if the expression doesn't return boolean. 3. Try to convert nonbooleans to boolean using plpgsql's usual method for cross-type coercion, ie run the type's output proc to get a string and feed it to bool's input proc. (This seems unlikely to avoid throwing an error in very many cases, but it'd be the most consistent with other parts of plpgsql.) 4. Use the parser's coerce_to_boolean procedure, so that nonbooleans will be accepted in exactly the same cases where they'd be accepted in a boolean-requiring SQL construct (such as CASE). (By default, none are, so this isn't really different from #2. But people could create casts to boolean to override this behavior in a controlled fashion.) Any opinions about what to do? It won't bite me so maybe I don't have a right to express an opinion :-) plpgsql is not C - it appears to be in the Algol/Pascal/Ada family, which do tend to avoid implicit type conversion. On that basis, option 2 seems like it might be the right answer and also the one most likely to break lots of existing functions. Maybe the right thing would be to deprecate relying on implicit conversion to boolean for one release cycle and then make it an error. cheers andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Unique Constraint Based on Date Range
I'm looking to apply a unique constraint to a table such that field A must be unique based on the date range from Field B to Field C. This is for a rate based service model whereby, for example, $5 is worth 1 hour of Internet access. But next week, because of increased competition, $5 is worth 2 hours of Internet access. I want to maintain a history what $5 bought during a specific period of time. create table rates ( effective_date AS timestamp, expiry_date AS timestamp, cost AS numeric (12,2), access_time AS integer (in minutes) ); So for a given cost, there may not be a record where the effective date of one record overlaps the expiry date of another (and vice versa). Example record set (effective date, expiry date, cost, access_time): 2003-01-01 | 2003-01-15 | 5.00 | 60 2003-01-15 | infinity | 5.00 | 120 2003-01-01 | infinity | 1.00 | 10 An attempt to insert another 5.00 rate effective now would fail, because a 5.00 rate exists that doesn't expire (i.e. the expiry date would have to be updated to the effective date of the new record minus 1 second). I can enforce this from the front end, but a db constraint would be great. ---(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] Function ROWTYPE Parameter with NEW/OLD
Postgres 7.2.1 I'm trying to create a function that takes a ROWTYPE parameter that I can then call from a rule by passing NEW/OLD, but am having problems. CREATE OR REPLACE FUNCTION "some_boolean_function" (mytablename) RETURNS boolean AS ' DECLARE mytable ALIAS FOR $1; BEGIN -- IF SOME CONDITION RETURN TRUE ELSE RETURN FALSE; END;' LANGUAGE 'plpgsql'; This works fine. CREATE RULE some_rule AS ON UPDATE TO mytablename WHERE (some_boolean_function(new, old)) DO INSTEAD (some other statements); It cacks on the new and old parameters - misunderstanding on my part? potential bug? I have to check most of the fields in this table, so would rather pass the whole record rather than individual fields. Thanks, Andrew ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Trace for postgreSQL
On Tue, Feb 10, 2004 at 12:04:42PM +, beyaNet Consultancy wrote: > Hi, > can anyone tell me whether there is a trace facility (application) > available for postgreSQL version 7.4.1 which will enable me to see all > incoming requests being made to the database (ala SQL Server)? Sure. Alter your configuration to echo queries, and then watch your log file. Alternatively, you can enable the command string statistics function, and then you get the queries in near to real time in pg_stat_activity. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(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] pg_restore - don?t restore. Why?
On Fri, Feb 20, 2004 at 12:24:46AM -0300, 2000info wrote: > pg_dump, ok. > pg_restore, don?t restore. Why? If you didn't use a non-ASCII format from pg_dump, you don't need pg_restore. Just use psql. A -- Andrew Sullivan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Import from Ms Excel
On Tue, Mar 16, 2004 at 03:13:38PM +0530, Kumar wrote: > Dear Friends, > > Is possible to import data from MS Excel sheet into postgres > database 7.3.4 running on Linux 7.2 Yes. I find the easiest way is to export a delimited file from Excel and use the \copy command in psql. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Import from Ms Excel
On Tue, Mar 16, 2004 at 01:42:45PM +0200, Achilleus Mantzios wrote: > Another fancy lib (although not necessarilly pgsql specific), > is the POI project from jakarta. > You can read/write M$ XLS documents from java, and > subsequently (via jdbc) manipulate pgsql tables. > > The good part is that you can dynamically generate an excel file > with arbitary content. You can do the same thing (I have done it) with Perl using the Pg and WriteExcel modules. Excel has a lot of pretty annoying limitations, though, and it's fairly dangerous to get too dependent on it for this sort of thing, as you can easily run into its limitations. I suppose it depends on how big your resulting spreadsheets are going to be. In my experience, though, the first thing that happens when you deliver someone a summary spreadsheet is, they ask you for the raw data so they can double-check it. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(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] conversion of postgres database to oracle
On Tue, Mar 16, 2004 at 02:13:57PM +0200, cristi wrote: > How should I convert a postgres database to oracle? Send it out to ASCII and then import it to Oracle. But if you want support for going _to_ Oracle, you probably ought to get support from Oracle people, right? A -- Andrew Sullivan | [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] transaction
On Tue, Apr 20, 2004 at 09:14:48PM +0200, H.J. Sanders wrote: > > - BEGIN WORK > > - INSERT ROW > > - IF FAILED THEN UPDATE ROW > > - COMMIT WORK You can do it the other way. Begin, update; if 0 rows are updated then insert. A -- Andrew Sullivan | [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] EXPORT / IMPORT
On Fri, May 07, 2004 at 10:20:52AM +0200, Freddy Villalba Arias wrote: > > Is there a simple way to generate an export / import script in > PostgreSQL (i.e. a script that contains INSERT statements for every row > in a table) ??? > > COPY is NOT a good option (personal opinion). pg_dump -d or -D. Note that restoring from this is going to be a whole lot slower than restoring from a COPY based dump. A -- Andrew Sullivan | [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] not really SQL but I need info on BLOBs
On Thu, May 06, 2004 at 04:46:22AM -0700, Theodore Petrosky wrote: > Thanks for the reply. Are there (in your opinion) > reasons why you would choose to store the images in > the db? Transactional integrity. If there's a risk that people are going to be deleting, &c. these images, then you can end up with references in the database to files that don't exist, because the filesystem operations can't be made subject to the transactions of the database. A -- Andrew Sullivan | [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] typecasting numeric(18,4) to varchar/text
On Tue, May 04, 2004 at 09:35:31AM -0400, [EMAIL PROTECTED] wrote: > create table a (a numeric(18,4)); > create table b (b varchar(25)); > insert into a values(12000.43); > insert into b select (a.a)::varchar; Which version is that? Here's my session: andrewtest=# create table a (a numeric(18,4)); CREATE TABLE andrewtest=# create table b (b varchar(25)); CREATE TABLE andrewtest=# insert into a values(12000.43); INSERT 17168 1 andrewtest=# insert into b select (a.a)::varchar; INSERT 17169 1 That's on 7.4.2. You might want to try casting to text first. A -- Andrew Sullivan | [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] view running query
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 There are two ways to do it. The server-side approach is to increase logging levels in the config file and then "pg_ctl reload". See http://www.postgresql.org/docs/7.4/interactive/runtime-config.html#RUNTIME-CONFIG-LOGGING for the stuff involved. The other way to do it is client side. In this case, you have to increase the logging level of your database connection. For example, if you have a perl DBD application, find the initilization of the database handle (by convention named $dbh) and then add $dbh->trace(2); After it. Drew Eric Anderson Vianet SAO wrote: | I run an application which connects to my pgsql DB. | | How could I see which query is sent to DB when, an example, i push some | application button (such ´find´). | | sds | | Eric Anderson | CPD Via Net SAO | 11-66432800 | | | ---(end of broadcast)--- | TIP 4: Don't 'kill -9' the postmaster -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAqMzogfzn5SevSpoRAnDwAJ4+y6xBwD9hXQ2k7V4mJbUf26rKLQCeP74Q HdgdLOV8bpqh5z4hgcUX52M= =dVN9 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] feature request ?
On Thu, Jun 24, 2004 at 12:32:59PM -0500, Jaime Casanova wrote: > > Why not disallow the ability of boolean fields to be null? Why not do it yourself? That's what the NOT NULL constraint is for. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Complicated "group by" question
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
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
Re: [SQL] Preserving column order when recreating table.
On Wed, Sep 15, 2004 at 02:24:45PM +0200, Stef wrote: > I'm struggling with a situation where I > want to recreate a table (in more than 30 databases) to > fix the column order (attnum sequence) and in another case, > fix different definitions for the same column in a table e.g. > amount numeric(16,2) > in stead of : > amount numeric(16,5) I'm not sure why you want to do the former, but in any case, it's possible by creating a new table which has things the way you want; select all the old data from the old table into the new table (using the column names to get everything in the order you like, of course), and then rename the old table, rename the new table to the old table name, and drop the old table if you like. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(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] Preserving column order when recreating table.
On Wed, Sep 15, 2004 at 04:22:01PM +0200, Stef wrote: > Andrew Sullivan mentioned : > => I'm not sure why you want to do the former, but in any case, it's > Because lazy people write inserts without specifying column names. Ugh. Sorry to say so, but this sounds to me really a lot like the cure is worse than the disease. The answer to "Bob did something incredibly stupid" is not "We'll bend ourselves into contortions to support it." (This is not to say I don't sympathise. You wouldn't believe how much I do.) > => possible by creating a new table which has things the way you want; > => select all the old data from the old table into the new table (using > => the column names to get everything in the order you like, of course), > > I like this idea, but each database may have a different table definition > for the same table, and if I want to automate this, I need to figure out the > column names on the fly. That's a little trickier, but you could figure it out with some queries from pg_class and pg_attribute. > Thanks!! No problem, but I think you need to have a long talk with your developers. Possibly while holding a baseball bat or something. Furrfu. This no-column-names thing is bound to bite you some day, and probably in tender bits where such bites would be unpleasant. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Preserving column order when recreating table.
On Wed, Sep 15, 2004 at 05:07:00PM +0200, Stef wrote: > Funny you say. It looks like "Bob" had a hand in pg_dump -d , 'cause I've > many times wished there were column names specified there, too :) > (I'm talking Prior 7.4 here, dunno if it's changed already) Dunno about previous, but pg_dump -D does what you want. I think the -d switch did it this way because you can get away with that if you're also creating the schema in the same breath. I agree that "Bob's" fingers have left their grotty marks in plenty of places. > is why I didn't use this solution originally. But I figured out a way to > modify pieces of the "create table" statement to drop all the indexes > and constraints first. > > Is there an easier way around this? I doubt it. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(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] How to check postgres running or not ?
On Sun, Sep 19, 2004 at 12:25:00PM -0400, Tom Lane wrote: > ps aux | grep postmaster | grep -v grep > (or use "ps -ef" if using a SysV-ish ps). Except that on Solaris, ps -ef _always_ shows "postmaster", even for the individual back ends. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to check postgres running or not ?
On Sun, Sep 19, 2004 at 01:12:07PM -0400, Tom Lane wrote: > > Except that on Solaris, ps -ef _always_ shows "postmaster", even for > > the individual back ends. > > Right, but if you see a backend then you can figure the system is up. Oops, good point. (And in any case, on Solaris you also have the ucb ps, so it makes no difference.) A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] CREATE TABLE AS SELECT....
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Arash Zaryoun wrote:
| Hi All,
|
| I have a table which has 'SERIAL' datatype. When I use 'create table X
| as select * from my-table' , it creates the table but without serial
| datatype. I mean without implicit sequence.
|
| test=> \d qptuser
| Table "public.qptuser"
| Column | Type |Modifiers
|
-
---+---+--
| srl | integer | not null default
| nextval('public.qptuser_srl_seq'::text)
| login_nme | character varying(35) | not null
| password | character varying(30) | not null
| Indexes:
| "pk_qptuser" primary key, btree (srl)
| "i1_qptuser_login_nme" unique, btree (login_nme)
|
| test=> create table x as select * from qptuser;
| test=> \d x
|
| Table "public.a"
| Column | Type | Modifiers
| ---+---+---
| srl | integer |
| login_nme | character varying(35) |
| password | character varying(30) |
|
| Can you help me on this?
I usually use CREATE TABLE ... AS SELECT ... to create temporary tables
or for reporting tables. I've never used it to create a table with the
intent of inserting new data into it. I don't really know what you're
trying to accomplish, but I'll guess that you want to set a default
value out of a sequence here. There's two ways you can do this. You can
share the same sequence as the qptuser table uses or you can create your
own sequence. To share the sequence:
ALTER TABLE a ALTER srl SET DEFAULT nextval('qptuser_srl_seq');
To create your own sequence:
CREATE SEQUENCE a_srl_seq START (SELECT srl FROM a ORDER BY srl DESC
LIMIT 1);
ALTER TABLE a ALTER slr SET DEFAULT nextval('a_srl_seq');
You'll probably want to throw some NOT NULL constraints on the table
while you're at it:
ALTER TABLE a SET srl NOT NULL;
ALTER TABLE a SET login_name NOT NULL;
ALTER TABLE a SET password NOT NULL;
I'll just assume that you're using hased passwords, and not storing them
in cleartext...
- --
Andrew Hammond416-673-4138[EMAIL PROTECTED]
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.5 (GNU/Linux)
iD8DBQFBT1Iagfzn5SevSpoRAl46AJ4iWqAN8LrdpxIX8PXSwyqs14ftKQCfbnTm
aui95Jq7i2zNzTTgMDS3nNY=
=ZFeW
-END PGP SIGNATURE-
---(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] SQL confusion
I'm trying to figure out how to do a particular query, and I'm beating my head against a wall. Here's my situation: I'm running postgres 7.3.2 on linux, and making my requests from Perl scripts using DBD::Pg. My table structure is as follows (irrelevant cols removed) CREATE TABLE name ( namecounter integer NOT NULL, firstmiddle character varying(64) NOT NULL, lastname character varying(64) NOT NULL, birthdate date, hh smallint, famnu integer, ); Each row represents a person with a unique namecounter. Families share a famnu, and usually one person in a family is marked as head of household (hh>0), with everyone else hh=0. However, there are a few families with nobody marked as hh, and I'd like to elect one by age. The query I'm trying to do is to pull one person from each household, either the head of household if available, or the eldest if not. I want them sorted by last name, so I'd prefer to find them all in one query, no matter how ugly and nested it has to be. I can pull the list with hh>0 easily enough, but I'm not sure how to pull out the others. I realize that this could be done through some looping in the Perl script, but I'd like to avoid pulling the whole list into memory in case the list gets long. My preference is to just handle one record at a time in Perl if possible. Help? Andrew Ward [EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] JOIN not being calculated correctly
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Posting the EXPLAIN is a good, an EXPLAIN ANALYZE would be better
(assuming your dataset is small enough for it to complete in this
lifetime). You also need to include the following information:
1) The schema involved, including information about indexes being used.
2) Have you vacuumed / analyzed the tables involved recently?
3) Have you modified the stats on any of the tables / columns involve or
are you using defaults?
Drew
Scott Pederick wrote:
| Hi all!
|
| I'm having a problem with Postgresql 8.0.0-beta4 on a Win2K machine and a
| particular join.
|
| I've got two tables - a list of customers and jobs they've had. A customer
| can have multiple jobs.
|
| The query always scans the entire jobs table for each customer - I need it
| the other way around so I can get a list of the customers who have at
least
| one job.
|
| The EXPLAIN shows the jobs table is being scanned for some reason:
|
| Quarry=# EXPLAIN SELECT Customers.CustomerId, Jobs.JobId FROM Customers
| INNER JOIN Jobs USING (CustomerId);
|QUERY PLAN
| -
| Hash Join (cost=78.54..4908.71 rows=70727 width=8)
|Hash Cond: ("outer".customerid = "inner".customerid)
|-> Seq Scan on jobs (cost=0.00..3769.27 rows=70727 width=8)
|-> Hash (cost=76.03..76.03 rows=1003 width=4)
| -> Seq Scan on customers (cost=0.00..76.03 rows=1003 width=4)
| (5 rows)
|
|
|
| Even if I reverse the JOIN I get the exact same result:
|
| Quarry=# EXPLAIN SELECT Customers.CustomerId, Jobs.JobId FROM Jobs INNER
| JOIN Customers USING (CustomerId);
|QUERY PLAN
| -
| Hash Join (cost=78.54..4908.71 rows=70727 width=8)
|Hash Cond: ("outer".customerid = "inner".customerid)
|-> Seq Scan on jobs (cost=0.00..3769.27 rows=70727 width=8)
|-> Hash (cost=76.03..76.03 rows=1003 width=4)
| -> Seq Scan on customers (cost=0.00..76.03 rows=1003 width=4)
| (5 rows)
|
|
| How can I force it to operate as I need it to? It seems the query
engine is
| a little smarter than it needs to be.
|
| If anyone can shed some light on this problem, it would be greatly
| appreciated. I've taken it as far as I can and don't really know where to
| move from here.
- --
Andrew Hammond416-673-4138[EMAIL PROTECTED]
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.5 (GNU/Linux)
iD8DBQFBh9YQgfzn5SevSpoRAg0LAKCg5K7IccFIOvdTc8DEl2YaUMcUCgCfRt2Q
CI1Vo6yxHkrWcoTQMQ/EvOw=
=m15B
-END PGP SIGNATURE-
---(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] vacuum analyze slows sql query
On Tue, Nov 02, 2004 at 06:50:31PM -0800, patrick ~ wrote: > We have a nightly "garbage collection" process that runs and purges > any old data. After this process a 'vacuum analyze' is kicked off > (regardless of whether or not any data was actually purged). > > At this point I should mention that our customer sites are running > PostgreSQL 7.1.3; however, I am able to reproduce the issue on 7.4.2. A 7.1 system takes an exclusive lock on any VACUUM. It's the same as VACUUM FULL in 7.4. Nothing you can do to make that not be sluggish. You want to get those sites off 7.1 anyway. At the very least, you should be aware of xid exhaustion which can be prevented in 7.1 only with an initdb and complete restore. Failure to accommodate that will mean that one day your databases will just disappear. Current VACUUM certainly does impose a serious I/O load; this is the reason for the vacuum setting tweaks in 8.0. See the -hackers archives (from more than a year ago now) for (for instance) Jan Wieck's discussion of his feature and the subsequent debates. > I noticed that a freshly created db with freshly inserted data (from > a previous pg_dump) would result in quite fast results. However, > after running 'vacuum analyze' the very same query slowed down about > 1250x (Time: 1080688.921 ms vs Time: 864.522 ms). > My best guess is that there's something going on inside your function. I'd be looking for locks here, though. That makes no sense, given that you've only 78 rows being returned. BTW, this topic should probably be better pursued on -performance. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] vacuum analyze slows sql query
On Wed, Nov 03, 2004 at 10:31:33AM -0800, patrick ~ wrote: > Just to clarify, the sliggishness isn't only during the vacuum > period. There are more more serious issues during the vacuum, > but i am not touching on those. The sluggishness is persistant > throughout daily operations. Then I suspect you have two problems, not one. The locking in 7.1 will certainly cause the timeouts during vacuum, but won't explain the other items. Someone else suggested REINDEX, also, which is likely needed. But again, you'll get more useful remarks from the -performance list. > Yes, the plan is to upgrade them with new release of our product. > I didn't know about the xid exhaustion problem. I'll need to > search the mailing list archives. You can learn a bit about this in the _current_ version of the docs under regular maintenance. > Again to clarify, the output I pasted was from my standalone > PostgreSQL box. That is, it wasn't being used other than those > quries being executed. That's even worse. I certainly can't explain your results, then. You really want to move to the -performance list for that. > I don't know if you looked at my stored function, but there are > no locks in it (no explicit ones anyway). Foreign keys are one well-known area of locking surprises. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Move table between schemas
On Tue, Nov 16, 2004 at 10:02:34AM +0100, Markus Schaber wrote: > Hello, > > Is there an easy way to move a table to another schema in PostgreSQL 7.4? > > ALTER TABLE and ALTER SCHEMA don't have this options. CREATE TABLE newschema.newtable AS SELECT * FROM oldschema.oldtable oughta work. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Move table between schemas
On Tue, Nov 16, 2004 at 02:30:09PM +0200, Achilleus Mantzios wrote: > > > > CREATE TABLE newschema.newtable AS SELECT * FROM oldschema.oldtable > > > > oughta work. > > What about indexes, constraints, sequences,etc...??? You'll have to create those too, I'm afraid. I don't know of a way to move tables from one schema to another otherwise. You could do all the dependencies with a pg_dump -t, I suspect. Not tested that, though. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(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] NULLS and string concatenation
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Gregory S. Williamson wrote: | Someone on this list provided me with a rather elegant solution to this a few weeks ago: | | CREATE OR REPLACE FUNCTION text_concat_nulls_with_an_embedded_space(text, text) | RETURNS text | AS 'SELECT CASE WHEN $1 IS NULL THEN $2 WHEN $2 IS NULL THEN $1 ELSE $1 || '' '' || $2 END' | LANGUAGE sql; Ugly. As the previous poster mentioned, handling NULLs is what COALESCE is for. CREATE OR REPLACE FUNCTION text_concat_nulls_with_an_embedded_space(text,text) IMMUTABLE CALLED ON NULL INPUT RETURNS text AS 'SELECT COALESCE($1 || '' '' || $2, $2, $1);' LANGUAGE sql; | CREATE OPERATOR ||~ (PROCEDURE = text_concat_nulls_with_an_embedded_space, LEFTARG = text, RIGHTARG = text); | | And I call it as: | SELECT (trim(s_directio) ||~ trim(s_house) ||~ trim(s_post_dir) ||~ trim(s_street) ||~ trim(s_suffix)) as street ... (yadda yadda) | | Deals quite neatly with the NULLs in some of the columns. Or my personal favourite: CREATE OR REPLACE FUNCTION comma_concat (text,text) IMMUTABLE CALLED ON NULL INPUT RETURNS text AS 'SELECT COALESCE ($1 || '','' || $2, $2);' LANGUAGE sql; CREATE AGGREGATE comma_concat ( ~BASETYPE=text, ~SFUNC=comma_concat, ~STYPE=text ); Which is handy for 1:n reports like SELECT grade, comma_concat($name) AS members FROM test_results GROUP BY grade; - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (GNU/Linux) iD8DBQFBo4yHgfzn5SevSpoRAgjrAJ9M5WwQE1FOaxcs7o45KjdKZF6AQACgkCKS V+qljFHFtYbOMcRU+7SawmY= =xqTu -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] HowTo change encoding type....
Hi, how do I change the encoding type in postgreSQL (8) from UTF-8 to ISO-8859-1? many thanks Andrew <> +The home of urban music + http://www.beyarecords.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Type Inheritance
Does anyone know how to implement type inheritance in postgresql? in oracle you just use the word UNDER in ur code i.e: CREATE TYPE test2_UDT UNDER test1_UDT AS (abc INT); any ideas? -- __ Check out the latest SMS services @ http://www.linuxmail.org This allows you to send and receive SMS through your mailbox. Powered by Outblaze ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] tutorials for complex.sql & complex.c
hi can anyone inform me where to get the postgreSQL complex.sql & complex.c tutorials from, cos i have no idea. in the 7.4.2-A4 doc it says: The examples in this section can be found in complex.sql and complex.c in the src/tutorial directory of the source distribution. See the README file in that directory for instructions about running the examples. im still unsure, where to look. can anyone solve my prob? -- __ Check out the latest SMS services @ http://www.linuxmail.org This allows you to send and receive SMS through your mailbox. Powered by Outblaze ---(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] Way to stop recursion?
On Fri, Nov 26, 2004 at 01:03:38PM -0800, Jonathan Knopp wrote: > UPDATE rules work perfectly for what I need to do except I need them to > only run once, not try and recurse (which of course isn't allowedby > postgresql anyway). Triggers seem a less efficient way to do the same > thing, though I understand they would run recursively too. Here's the > table structure in question: You have to do this with a trigger. The problem is that the rule is expanded inline like a macro, so you can't prevent the behaviour you're seeing. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Way to stop recursion?
On Fri, Nov 26, 2004 at 04:31:11PM -0500, Tom Lane wrote: > > Seems to me that your real problem is a bogus database layout. If there > should only be one "common" value for a parent and children, then only > store one value ... that is, "common" should exist only in the parent. Tom's answers always make me realise that I should think harder before I talk. He's right, of course: one common value means store it once. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Type Inheritance
hi tom thanks for reply. im trying to provide inheritance for data types (domains), not tables. i will look into the syntax associated with domains as data types does not include an inheritance function (or so i think). regards andy. - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Andrew Thorley" <[EMAIL PROTECTED]> Subject: Re: [SQL] Type Inheritance Date: Fri, 26 Nov 2004 13:14:07 -0500 > > "Andrew Thorley" <[EMAIL PROTECTED]> writes: > > Does anyone know how to implement type inheritance in postgresql? in oracle > > you just use the word UNDER in ur code i.e: > > CREATE TYPE test2_UDT UNDER test1_UDT AS (abc INT); > > If you had said what this *does*, we might be better able to help. > > But take a look at CREATE DOMAIN, as well as the INHERITS and LIKE > options in CREATE TABLE. Some part of that might be close to what > you are looking for. > > regards, tom lane > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > -- __ Check out the latest SMS services @ http://www.linuxmail.org This allows you to send and receive SMS through your mailbox. Powered by Outblaze ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Type Inheritance
"If you had said what this *does*, we might be better able to help" basically i have a series of types (or data types), of which one is the root and each of the other types appear under the root as in generalisation/specialisation style, inheriting the above types attributes etc. this is what im trying to achieve, although at present, to no avail :( - Original Message ----- From: "Andrew Thorley" <[EMAIL PROTECTED]> To: "Tom Lane" <[EMAIL PROTECTED]> Subject: Re: [SQL] Type Inheritance Date: Sat, 27 Nov 2004 22:04:55 +0800 > > hi tom thanks for reply. > > im trying to provide inheritance for data types (domains), not tables. i will > look into the syntax associated with domains as data types does not include > an inheritance function (or so i think). > > regards andy. > > > - Original Message - > From: "Tom Lane" <[EMAIL PROTECTED]> > To: "Andrew Thorley" <[EMAIL PROTECTED]> > Subject: Re: [SQL] Type Inheritance > Date: Fri, 26 Nov 2004 13:14:07 -0500 > > > > > "Andrew Thorley" <[EMAIL PROTECTED]> writes: > > > Does anyone know how to implement type inheritance in postgresql? in > > > oracle you just use the word UNDER in ur code i.e: > > > CREATE TYPE test2_UDT UNDER test1_UDT AS (abc INT); > > > > If you had said what this *does*, we might be better able to help. > > > > But take a look at CREATE DOMAIN, as well as the INHERITS and LIKE > > options in CREATE TABLE. Some part of that might be close to what > > you are looking for. > > > > regards, tom lane > > > > ---(end of broadcast)--- > > TIP 9: the planner will ignore your desire to choose an index scan if your > > joining column's datatypes do not match > > > > -- > __ > Check out the latest SMS services @ http://www.linuxmail.org > This allows you to send and receive SMS through your mailbox. > > > Powered by Outblaze > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > -- __ Check out the latest SMS services @ http://www.linuxmail.org This allows you to send and receive SMS through your mailbox. Powered by Outblaze ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] inserting values into types
Hi, ive generated a user defined type: CREATE TYPE qwerty_UDT AS (abc INT); & table as: CREATE TABLE t (col1 qwerty_UDT); my prob is that when i try to insert into the type i.e: INSERT INTO t (col1) Values (qwerty_UDT(123)); i get the error: ERROR: function test_x(integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. which is quite confusing, does anyone have any ideas or exp with this scenario, or offer help in any way? ty in advance :) -- __ Check out the latest SMS services @ http://www.linuxmail.org This allows you to send and receive SMS through your mailbox. Powered by Outblaze ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] inserting values into types
hi mike, sorry for confusion, the test_x is my mistake, its from another type i created which is executing the same code. my error i get from: CREATE TYPE qwerty_UDT AS (abc INT); CREATE TABLE t (col1 qwerty_UDT); INSERT INTO t (col1) VALUES (qwerty_UDT(123)); is: ERROR: function qwerty_udt(integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. - Original Message - From: "Michael Fuhr" <[EMAIL PROTECTED]> To: "Andrew Thorley" <[EMAIL PROTECTED]> Subject: Re: [SQL] inserting values into types Date: Wed, 1 Dec 2004 10:53:01 -0700 > > On Wed, Dec 01, 2004 at 06:07:34PM +0800, Andrew Thorley wrote: > > > > Did you type the SQL statements and/or error messages instead > > > of cutting and pasting? > > > > yes i C&P'ed the SQL code & error code. > > But did you copy the error message associated with the SQL code you > copied, or did you copy some other error message? I ask because > the function name in the error doesn't match the function name in > the INSERT statement. Here's what I get when I execute the statements > in your message: > > test=> CREATE TYPE qwerty_UDT AS (abc INT); > CREATE TYPE > test=> CREATE TABLE t (col1 qwerty_UDT); > CREATE TABLE > test=> INSERT INTO t (col1) Values (qwerty_UDT(123)); > ERROR: function qwerty_udt(integer) does not exist > > You said the error you got was: > > ERROR: function test_x(integer) does not exist > > The error is the same but the detail differs: your error refers > to test_x(integer) instead of qwerty_udt(integer). So where is > test_x coming from? > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ -- __ Check out the latest SMS services @ http://www.linuxmail.org This allows you to send and receive SMS through your mailbox. Powered by Outblaze ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] find the "missing" rows
On 2004-12-02, Tom Lane <[EMAIL PROTECTED]> wrote: > "Kevin B." <[EMAIL PROTECTED]> writes: >> Select a.i, b.i >> from t as a >> left join t as b on a.i = b.i >> where a.n = 'a' and b.n = 'b' and b.i is null > > This can't succeed since the b.n = 'b' condition is guaranteed to fail > when b.* is nulled out ... You can make it work by moving parts of the condition into the explicit join clause: select a.i from t as a left join t as b on a.n='a' and b.n='b' and a.i=b.i where a.n='a' and b.i is null; (notice you still need the check on a.n='a' outside the join condition) -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Failed system call was shmget(key=1, size=1155072, 03600).
Hi, when building postgreSQL 8 I get the following error message: DETAIL: Failed system call was shmget(key=1, size=1155072, 03600). HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 1155072 bytes), reduce PostgreSQL's shared_buffers parameter (currently 50) and/or its max_connections parameter (currently 10) What is the best way to resolve this? max_connections = 10? Does that figure auto increase as more users request data? regards Andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Failed system call was shmget(key=1, size=1155072, 03600).
On Fri, Dec 03, 2004 at 09:00:53AM +, Andrew M wrote: > DETAIL: Failed system call was shmget(key=1, size=1155072, 03600). > HINT: This error usually means that PostgreSQL's request for a shared > memory segment exceeded available memory or swap space. To reduce the > request size (currently 1155072 bytes), reduce PostgreSQL's > shared_buffers parameter (currently 50) and/or its max_connections > parameter (currently 10) > > What is the best way to resolve this? max_connections = 10? Does that > figure auto increase as more users request data? What are you trying to run this on? It might be that you need to alter your kernel settings. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] sum query
[if replying, please do so to the list / newsgroup only, not to me] On 2004-12-03, "Keith Worthington" <[EMAIL PROTECTED]> wrote: > Hi All, > > I am trying to join three tables and sum the quantities. > > The first table contains all of the possible items. The second table > contains orders and the third table contains the items on each order. > > For all items found in tbl_item I need the total quantity on open orders. > If an item is not on any open order the quantity should be zero. > > Can someone please explain the best way to do this? First, notice that what you're asking for involves a row of output for each item in tbl_item regardless of whether it appears in the other tables at all. This is an indication that says "try an outer join here". So bearing that indication in mind, we work out what the other side of the outer join should look like. This would be a simple join on the other two tables to give the quantity in open orders. (Note that there are two ways to do the grouping/summation in this query; either on the result of the two-table join or on the final result.) You used the same table name twice in your example data, I'll assume that was an error and that the third table should have been called tbl_order_item. Here then is how to construct the query: Start by working out the quantities: select oi.id,sum(oi.quantity) from tbl_order_item oi join tbl_order o using (order_id) where o.closed=false group by id; id | sum --+- GH12 | 4 AB12 | 15 CD34 | 5 (3 rows) Now outer-join that against tbl_item: select id,quantity from (select oi.id,sum(oi.quantity) as quantity from tbl_order_item oi join tbl_order o using (order_id) where o.closed=false group by id) as oj right join tbl_item i using (id); id | quantity --+-- AB12 | 15 CD34 |5 EF34 | GH12 |4 JK56 | (5 rows) However this gives us NULL for the quantities not appearing on any open order. Since we want zero instead, we remove the nulls with COALESCE: select id,coalesce(quantity,0) as quantity from (select oi.id,sum(oi.quantity) as quantity from tbl_order_item oi join tbl_order o using (order_id) where o.closed=false group by id) as oj right join tbl_item i using (id); id | quantity --+-- AB12 | 15 CD34 |5 EF34 |0 GH12 |4 JK56 |0 (5 rows) And we have the desired result. Notice that I have not used ORDER BY; if you want results in a given order, add that yourself. I mentioned that the grouping could be done in two ways. Here is the other way: Start with the ungrouped quantity figures: select oi.id,oi.quantity from tbl_order_item oi join tbl_order o using (order_id) where o.closed=false; id | quantity --+-- AB12 | 10 CD34 |5 GH12 |4 AB12 |5 (4 rows) Outer-join against tbl_item: select id,quantity from (select oi.id,oi.quantity from tbl_order_item oi join tbl_order o using (order_id) where o.closed=false) as oj right join tbl_item i using (id); id | quantity --+-- AB12 | 10 AB12 |5 CD34 |5 EF34 | GH12 |4 JK56 | (6 rows) Unlike with the previous version, this time we can flatten out the inner select (should make no difference to performance but may be more readable): select id,quantity from tbl_order_item oi join tbl_order o on (o.order_id=oi.order_id and o.closed=false) right join tbl_item i using (id); id | quantity --+-- AB12 | 10 AB12 |5 CD34 |5 EF34 | GH12 |4 JK56 | (6 rows) And then group the values and handle nulls: select id,coalesce(sum(quantity),0) as quantity from tbl_order_item oi join tbl_order o on (o.order_id=oi.order_id and o.closed=false) right join tbl_item i using (id) group by id; id | quantity --+-- AB12 | 15 CD34 |5 EF34 |0 GH12 |4 JK56 |0 (5 rows) Notice I haven't used either IN or UNION. Using IN in place of a join is unwise (even though recent versions can sometimes plan it as though it were a join); using UNION in place of an outer join is _very_ unwise. (In fact UNION / INTERSECT / EXCEPT should normally be reserved for those cases where there is simply no alternative.) -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Breadth first traversal in PLSQL (How to implement Queue?)
On 2004-12-15, Richard Rowell <[EMAIL PROTECTED]> wrote: > I have a table with a unary (recursive) relationship that represents a > hierarchy. With the gracious help of Mike Rylander I was able to port a > TSQL function that would traverse "up" the hierarchy to PL/SQL. Now I > need help porting the "down" the hierarchy function. Have you looked at contrib/tablefunc's connectby() function? -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Implementing queue semantics (novice)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The name for what you're looking to build is a concurrent batch processing system. Here's a basic one. - -- adding processes BEGIN; INSERT INTO queue (queue_id, processing_pid, processing_start, ~ processing_status, foreign_id) VALUES (DEFAULT, NULL, NULL, ~ (SELECT queue_status_id FROM queue_status WHERE name = 'pending'), ~ foreign_id); COMMIT; - -- removing processes BEGIN; SELECT queue_id, foreign_id FROM queue WHERE processing_status = (SELECT queue_status_id FROM queue_status ~ WHERE name = 'pending') ORDER BY queue_id LIMIT 1 FOR UPDATE; UPDATE queue SET processing_pid = ?, ~ processing_start = now(), ~ processing_status = (SELECT queue_status_id FROM queue_status WHERE ~ name = 'active') WHERE id = ?; COMMIT; - -- client code does whatever it's going to do here BEGIN; SELECT 1 FROM queue WHERE queue_id = ? AND processing_pid = ? FOR UPDATE; - -- confirm that it exists DELETE FROM queue WHERE queue_id = ? INSERT INTO queue_history (queue_id, processing_pid, processing_start, ~ processing_complete, processing_status, foreign_id) VALUES (queue_id, processing_pid, processing_start, now(), ~ (SELECT queue_status_id FROM queue_status WHERE name = 'done'), ~ foreign_id); COMMIT; - -- a seperate process reaps orphaned entries should processing fail. BEGIN; SELECT queue_id, processing_pid FROM queue WHERE now() - processing_start > 'some reasonable interval'::interval AND processing_status = (SELECT queue_status_id FROM queue_status WHERE ~ name = 'active' FOR UPDATE; - -- for each entry, check to see if the PID is still running UPDATE queue SET ~ processing_pid = NULL, ~ processing_start = NULL, ~ processing_status = (SELECT id FROM queue_status WHERE name = 'pending') WHERE id = ?; COMMIT; There are more complicated approaches available. If you plan to have multiple machines processing, you probably want to add a processing_node entry too. KÖPFERL Robert wrote: | Hi, | | since I am new to writing stored procedures I'd like to ask first bevore I | do a mistake. | | I want to implement some kind of queue (fifo). There are n users/processes | that add new records to a table and there are m consumers that take out | these records and process them. | It's however possible for a consumer to die or loose connection while | records must not be unprocessed. They may rather be processed twice. | | This seems to me as a rather common problem. But also with atomicy-holes to | fall into. | How is this commonly implemented? | | | I can imagine an 'add' and a 'get' function together with one aditional | 'processed' timestamp-column? | | | | Thanks for helping me do the right. | | ---(end of broadcast)--- | TIP 4: Don't 'kill -9' the postmaster - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (GNU/Linux) iD8DBQFB5U3kgfzn5SevSpoRAoesAKCAZkr61I5knCw9tIr8rlO0xri7YACgifrn N01nXZY8UKmIlTnGkngHKUo= =UXRk -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] failed to find conversion function from "unknown" to text
On Wed, Jan 12, 2005 at 05:52:42PM +0100, Sz?cs Gábor wrote: > Question: is there a way to tell the server to convert unknown to something > (text or varchar, don't really care), or to write such a "conversion > function"? You ought to be able to cast (e.g. "SELECT case 'a'::text. . .) A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Syntax error while altering col-type
On Thu, Jan 13, 2005 at 09:48:02AM +0100, KÖPFERL Robert wrote: > I'm a little bit perplexed now... is it really the case that pre 8.0 systems > aren't able to change col-types? It really is. In fact, the feature was (IIRC) somewhat controversial, because there are all sorts of decisions that need to be made about what to do with incompatible types. What if you change from int8 to int4? What about varchar(4) to char(4)? Just to name two simple-minded examples. See the -general and -hackers archives for plenty of previous discussion of this stuff. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
