Re: [SQL] Advice for index design
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Jorge Maldonado wrote: ... What is a good index design approach? Maybe, setting simple separate indexes (one for each field) would work fine if I need to retrieve data in different combinatios, but I am not sure. Yes, just create separate indexes and you will be fine, especially given the very small size of the table. If you find your queries going slow, you could start investigating compound indexes (or in this case, partial indexes). - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201304111933 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAlFnSPYACgkQvJuQZxSWSshm6wCggdl2FyowAbca93hYKXGgcXoE iN0AniOL8OS3teTgk6thxkJjUGqEf15k =1Eb6 -END PGP SIGNATURE- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How can this INSERT fail?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I can only assume it is possible for multiple transactions to overlap and one of them to miss the row so it doesn't appear in the EXCEPT SELECT but the row appears before the transaction commits and so an error occurs. My first thought was SELECT ... FOR UPDATE, but I can't do that within an EXCEPT. Can someone confirm whether I could avoid these errors by some form of SET TRANSACTION ISOLATION LEVEL, or let me know if I am just Doing It Wrong? Yes, there is a race condition. You can solve it by setting the isolation to repeatable read. As long as your application doesn't *need* the default read committed level, it should be logically safe and not affect performance. (But make sure you use repeatable read and not serializable, as they are distinct in recent versions of Postgres and the latter does carry a potential performance penalty). - -- Greg Sabino Mullane g...@turnstep.com PGP Key: 0x14964AC8 201204251322 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk+YM6IACgkQvJuQZxSWSshlSQCg8qVLbaYcEEJ9vOU43f2Irawv 2nwAnAxidDPeAohXOOTPa7mK0ORz2wc9 =xIfz -END PGP SIGNATURE- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] compare table names
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 tmp_staging1229 This query is part of a larger script where I want to dynamically select tablenames older than 10 days and drop them. The tables are created in a tmp_stagingMMDD format. I know postgres does not maintain object create times, how can I write this to select tables from pg_tables that are older than 10 days? First, be aware that MMDD alone is a suboptimal choice, for you will get burned by year boundaries, unless you go to crazy efforts to look at the current year, devine if 1230 should be 'less' than 0102 because it's January, etc. Assuming you change it to MMDD, you could run a simple query like this: SELECT tablename FROM pg_tables WHERE tablename ~ '^tmp_staging' AND substring(tablename from '\d+')::date now() - '10 days'::interval; - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201201091144 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk8LGuUACgkQvJuQZxSWSshD0QCcDipiHcgchfQMHMC6jC9ExkCv K44Anjy7eRg0uVNOoZ3AbHecf1nn6TmT =v/9C -END PGP SIGNATURE- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] 9.0+ way of determining if a LISTEN channel has a backend listening?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Is there a way in post-9.0 postgres for one session to determine if a LISTEN channel currently has a backend registered to receive notifications? No, there is no way. As mentioned on a similar thread on -hackers, you could possibly use advisory locks if you really need to know what another session is listening to. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201106011822 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk3mu54ACgkQvJuQZxSWSsiAYwCg/Oa00qCfiRI3jxhml6a2fnkz 8IUAn3Re+3IUo2IynMIRLLqzH4+uyN9n =YL88 -END PGP SIGNATURE- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Performance problem with row count trigger
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 A separate table just for that one column? Would that really help, given that I'd have to add the foreign key dataset_id to the related table? How does splitting activity across dataset and, say, dataset_counts help things? Well, it reduce the size (and bloat) of the main table and let's you offload the indexes as well. (especially important for pre-HOT systems). It's also a win if there are many queries against the main table that *don't* hit the summary count column. The cost of another table join for queries that *do* hit it is probably offset by keeping the main table small and only updated when it really needs to be. Still, it depends a lot on your particular circumstances; the thread was started because of the pain of updating this one column, but only you can make the final call about whether a separate table is a Good Idea or a Silly Microoptimization. 2) Do you really need bigint for the counts? Probably not. Still, changing to INTEGER hasn't changed the performance in any measurable way. I was simply thinking about the amount of space used here, not speed. Of course, if there is any conceivable way that the amounts in question would *ever* exceed two billion, you should keep it BIGINT, as changing it later would be painful. I'm not sure I understand the danger you're talking about here. Doesn't putting the whole start_bulkload_assoc_count(), bulk insert, and end_bulkload_assoc_count() process in a transaction save me from any of these problems? Or is there some more subtle problem I'm not seeing? No, that would work perfectly fine. The danger I was referring to was someone calling the first function and then committing before they remembered to call the second one. It pays to be paranoid around databases :), but if you have control of the environment, and that scenario seems unlikely, it should be fine the way it is. Oh, and I just remembered that the end_.. function should be clearing the temporary hash we build up - I think the version I emailed neglected to do that. Wouldn't want those numbers to stick around in the session. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200904061028 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAknaEdIACgkQvJuQZxSWSsgeeACfQRXopdyHdYoj5SLTiwedIYAc bDUAoNvouyYtixHeXLowWqYr9Oc/jS/t =sJ+s -END PGP SIGNATURE- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How would I get rid of trailing blank line?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 report at the bottom. There is a blank line at the bottom, however. Is there any way to have psql not give me that blank line? Now that my presenation on psql is over :), I'll share my solution: psql -AX -qt -c SELECT ... | perl -pe 's/^\n// if $.2' This strips a newline from the first line only of the output, and only if the line consists of nothing else. Highly recommended for cron. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200904052221 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAknZZ0YACgkQvJuQZxSWSsiDAwCglJS9/juQLe8asY3sG9fagbeo 2V4An0p5U6UHGI1KXoe2qQvURX5E5BZo =Yy0J -END PGP SIGNATURE- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Performance problem with row count trigger
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
I was looking to speed up a count(*) query
A few things spring to mind:
1) Use a separate table, rather than storing things inside of
dataset itself. This will reduce the activity on the dataset table.
2) Do you really need bigint for the counts?
3) If you do want to do this, you'll need a different approach as
Tom mentioned. One way to do this is to have a special method for
bulk loading, that gets around the normal updates and requires that
the user take responsiblity for knowing when and how to call the
alternate path. The basic scheme is this:
1. Disable the normal triggers
2. Enable special (perl) triggers that keep the count in memory
3. Do the bulk changes
4. Enable normal triggers, disable special perl one
5. Do other things as needed
6. Commit the changes to the assoc_count field.
Number 6 can be done anytime, as long as you are in the same session. The danger
is in leaving the session without calling the final function. This can be
solved with some deferred FK trickery, or by careful scripting of the events.
All this doesn't completely remove the pain, but it may shift it around enough
in useful ways for your app.
Here is some code to play with:
- -- Stores changes into memory, no disk access:
CREATE OR REPLACE FUNCTION update_assoc_count_perl()
RETURNS TRIGGER
LANGUAGE plperlu
AS $_$
use strict;
my $event = $_TD-{event};
my ($oldid,$newid) = ($_TD-{old}{dataset_id},$_TD-{new}{dataset_id});
if ($event eq 'INSERT') {
$_SHARED{foobar}{$newid}++;
}
elsif ($event eq 'DELETE') {
$_SHARED{foobar}{$oldid}--;
$_SHARED{foobar}{$oldid}||=-1;
}
elsif ($oldid ne $newid) {
$_SHARED{foobar}{$oldid}--;
$_SHARED{foobar}{$oldid}||=-1;
$_SHARED{foobar}{$newid}++;
}
return;
$_$;
- -- Quick little debug function to view counts:
CREATE OR REPLACE FUNCTION get_assoc_count(int)
RETURNS INTEGER
LANGUAGE plperlu
AS $_$
my $id = shift;
return $_SHARED{foobar}{$id} || 0;
$_$;
- -- Create, then disable, the perl trigger
CREATE TRIGGER update_assoc_count_perl
AFTER INSERT OR UPDATE OR DELETE
ON assoc
FOR EACH ROW
EXECUTE PROCEDURE update_assoc_count_perl();
ALTER TABLE assoc DISABLE TRIGGER update_assoc_count_perl;
- -- Switches the main triggers off, and the memory triggers on
- -- Use deferred constraints to ensure that stop_bulkload_assoc_count is called
CREATE OR REPLACE FUNCTION start_bulkload_assoc_count()
RETURNS TEXT
LANGUAGE plperlu
AS $_$
spi_exec_query(ALTER TABLE assoc DISABLE TRIGGER
update_assoc_count_insert); ## x 3 as needed
spi_exec_query(ALTER TABLE assoc ENABLE TRIGGER update_assoc_count_perl);
-- Put foreign key magic here
return 'Ready to bulkload';
$_$;
- -- Switches the triggers back, and allows a commit to proceed
CREATE OR REPLACE FUNCTION end_bulkload_assoc_count()
RETURNS TEXT
LANGUAGE plperlu
AS $_$
my $sth = spi_prepare(
'UPDATE dataset SET assoc_count = assoc_count + $1 WHERE dataset_id = $2',
'INTEGER', 'INTEGER');
for my $id (keys %{$_SHARED{foobar}}) {
my $val = $_SHARED{foobar}{$id};
spi_exec_prepared($sth,$val,$id);
}
spi_exec_query(ALTER TABLE assoc ENABLE TRIGGER update_assoc_count_insert);
## x3 etc.
spi_exec_query(ALTER TABLE assoc DISABLE TRIGGER update_assoc_count_perl);
-- Put FK magic here
return 'Bulk load complete';
$_$;
- -- Usage:
SELECT start_bulkload_assoc_count();
- -- Lots of inserts and updates
SELECT end_bulkload_assoc_count();
- --
Greg Sabino Mullane g...@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200904021644
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iEYEAREDAAYFAknVJiUACgkQvJuQZxSWSsisTQCg4iPr4fepGc/wA3LBUMLz13Gj
aEsAoLFB/KbA572VNKooa2a82Ok4DKUy
=Z95U
-END PGP SIGNATURE-
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] GROUP BY on a column which might exist in one of two tables
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Where the totals are counts of the the rows in the hits and views tables. There should be no rows for partners without hits or views. How about something like this?: SELECT partner_id, COUNT(view_id) AS total_views, COUNT(hit_id) AS total_hits FROM ( SELECT partner_id, hit_id, NULL AS view_id FROM hits UNION ALL SELECT partner_id, NULL, view_id FROM views ) AS foo GROUP BY 1; - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200806251019 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkhiU+8ACgkQvJuQZxSWSsgNiACgmrUWfTv1ZSiiexOKja64p1F8 1hYAn3i+tYoEOIs2NIcSrExlvoyfJE+X =ryrm -END PGP SIGNATURE- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select into
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Ie. I want to copy the contents of a row (but for the id column - of course) into a record in the same table. BEGIN; CREATE TEMP TABLE tempfoo AS SELECT * FROM foo WHERE id = 123; UPDATE tempfoo SET id = 456; DELETE FROM foo WHERE id = 456; INSERT INTO foo SELECT * FROM tempfoo; COMMIT; - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200803200737 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkfiTIYACgkQvJuQZxSWSsiCMwCdESkEe8Hc5xHhJ2B3qX3V7EqX Z2IAoMy65D2OhdUpYVtfEq182PhfsEfZ =fx5V -END PGP SIGNATURE- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Funny date-sorting task
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Every task has a timestamp X that may be NULL or a date. ... Sometimes it has date and the time-part, too. The list should be like this: 1) X sometime today should come first in ascending time order. 2) X in the past should show up after (1) in descending order so that not so long back dates come first 3) X = NULL 4) X sometime in the future Assuming you mean the literal sense of today, and that future dates show with the least furthest away first: SELECT * FROM yourtable ORDER BY CASE WHEN X::date = now()::date THEN 1 WHEN X::date now()::date THEN 2 WHEN X IS NULL THEN 3 ELSE4 END, CASE WHEN X::date-now()::date 0 THEN now()-X ELSE X-now() END; - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200705130942 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFGRxZIvJuQZxSWSsgRAwrsAJ9HzZXzf3sQs0FVNSrhxN5UpGhc+wCcDygQ obe5G3b58+pXhqy4Ybh/OM8= =rJpn -END PGP SIGNATURE- ---(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] Seeking quick way to clone a row, but give it a new pk.
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I need to create some nearly identical copies of rows in a complicated table. Is there a handy syntax that would let me copy a existing row, but get a new primary key for the copy? http://people.planetpostgresql.org/greg/index.php?/archives/45-Making-a-copy-of-a-unique-row.html - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200702081114 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFFy0xcvJuQZxSWSsgRA8vFAJsHMFhngWGCSSi8okO9j9H0++hajgCgrUz+ aKTnhaHuQHv1qetAmPt/ufM= =HDF8 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: 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] cli in sql?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message
If my database has column containing a filename, can I use sql to present
this filename and datemodified (as output from 'ls -l' or from mtime()
fuction) or *must* it be done after the query in interface such as php or
perl?
Neither. You can do it inside the db with a pl language such as plperlu:
CREATE OR REPLACE FUNCTION filemodtime(TEXT) RETURNS TEXT LANGUAGE plperlu AS
$$
my $filename = shift;
-e $filename or elog(ERROR, qq{The file $filename does not exist\n});
return localtime($^T - (60*60*24* -M _));
$$;
SELECT filemodtime('/var/log/messages');
SELECT filemodtime('/dark/matter');
--
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 20051457
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFDdPkRvJuQZxSWSsgRAufUAJ9f4/IaYrJDMi3Yg74x0tkN4tmUcQCgmlu9
wAkqRHgYQY9DtdIIfH/g7xY=
=n/J7
-END PGP SIGNATURE-
---(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] Need help with `unique parents` constraint
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thank you for an excellent answer. I think I will have to study your code for a while. But is it such a bad idea to have a separate column for the primary key here? I see that there are two schools on this, with diametrically opposed views. For my own part, I feel that it at least doesn't hurt to have a surrogate key. Secondly, a single key value is easier to reference from another table than a composite key. Not bad, but perhaps slightly inefficient and redundant. It depends on how your table is actually structured, but if the only way your app will ever refer to that table is in the context of those 2 foreign keys, then it makes sense to go ahead and make them a primary key. If there are other important fields in the table, /and/ if it is referenced from other tables, then I might add another column. But generally, this should be the exception and not the rule. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200509122031 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFDJh99vJuQZxSWSsgRAiRFAJwKiGVsJhcbxIe0nQ3bnxJUZupucACgnUa/ 57e9UDfVkv/4AMp2wpqEa3c= =20d1 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Need help with 'unique parents' constraint
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Now, I want to ensure that each person_id can be assigned only one
father (gender=1) and one mother (gender=2). (Yes, this is old-
fashioned, but I'm working with 18th century people). How do I do it?
Not just old-fashioned, it's the biological law! (among homo sapiens anyway).
I'd approach this with a trigger, as you can do complex checks and get back
nice customized error messages. A sample script follows. Hard to tell without
seeing your whole schema, but I see no need for a relation_id primary key
if you already have a unique constraint on child_fk and parent_fk, so I
made those into the primary key for the relations table:
DROP TABLE relations;
DROP TABLE persons;
DROP FUNCTION relation_check();
DROP SEQUENCE persons_seq_id;
CREATE SEQUENCE persons_seq_id;
CREATE TABLE persons (
person_id INTEGER PRIMARY KEY NOT NULL DEFAULT nextval('persons_seq_id'),
gender SMALLINT NOT NULL DEFAULT 0
CHECK (gender IN (0,1,2,9))
);
COMMENT ON COLUMN persons.gender IS 'ISO Gender code 1=father 2=mother';
CREATE TABLE relations (
child_fk INTEGER REFERENCES persons (person_id) ON DELETE CASCADE,
parent_fk INTEGER REFERENCES persons (person_id) ON DELETE CASCADE,
PRIMARY KEY (child_fk, parent_fk)
);
CREATE FUNCTION relation_check() RETURNS TRIGGER LANGUAGE plpgsql AS
$$
DECLARE
xy SMALLINT;
trace INTEGER;
BEGIN
- -- Assume that child or parent has changed, since this version has no other
columns
IF NEW.child_fk = NEW.parent_fk THEN
RAISE EXCEPTION 'Bioethics error: Human cloning not supported yet';
END IF;
SELECT gender FROM persons WHERE person_id = NEW.parent_fk INTO xy;
- -- More than one father?
IF xy = 1 THEN
SELECT parent_fk FROM relations r, persons p WHERE r.child_fk = NEW.child_fk
AND r.parent_fk = p.person_id AND p.gender = 1 INTO trace;
IF trace IS NOT NULL THEN
IF TG_OP = 'UPDATE' THEN
IF OLD.parent_fk != trace THEN
RAISE EXCEPTION 'Error: Cannot change parent: person % is already
assigned as the father', trace;
END IF;
ELSE
RAISE EXCEPTION 'Error: Person % is already assigned as the father',
trace;
END IF;
END IF;
END IF;
- -- More than one mother?
IF xy = 2 THEN
SELECT parent_fk FROM relations r, persons p WHERE r.child_fk = NEW.child_fk
AND r.parent_fk = p.person_id AND p.gender = 2 INTO trace;
IF trace IS NOT NULL THEN
IF TG_OP = 'UPDATE' THEN
IF OLD.parent_fk != trace THEN
RAISE EXCEPTION 'Error: Cannot change parent: person % is already
assigned as the mother', trace;
END IF;
ELSE
RAISE EXCEPTION 'Error: Person % is already assigned as the mother',
trace;
END IF;
END IF;
END IF;
RETURN NEW;
END;
$$;
CREATE TRIGGER relation_check BEFORE INSERT OR UPDATE ON relations
FOR EACH ROW EXECUTE PROCEDURE relation_check();
INSERT INTO persons(gender) VALUES (1);
INSERT INTO persons(gender) VALUES (2);
INSERT INTO persons(gender) VALUES (1);
INSERT INTO persons(gender) VALUES (2);
INSERT INTO persons(gender) VALUES (0);
INSERT INTO persons(gender) VALUES (1);
INSERT INTO relations VALUES (3,1);
INSERT INTO relations VALUES (3,2);
SELECT 'Cloning test' AS Test should fail;
INSERT INTO relations VALUES (3,3);
SELECT 'Change father to another mother' AS Test should fail;
UPDATE relations SET parent_fk = 4 WHERE child_fk = 3 AND parent_fk = 1;
SELECT 'Add in a second father' AS Test should fail;
INSERT INTO relations VALUES (3,6);
SELECT 'Change fathers' AS Test should pass;
UPDATE relations SET parent_fk = 6 WHERE child_fk = 3 AND parent_fk = 1;
SELECT 'Change mother to another father' AS Test should fail;
UPDATE relations SET parent_fk = 6 WHERE child_fk = 3 AND parent_fk = 2;
SELECT 'Add in a second mother' AS Test should fail;
INSERT INTO relations VALUES (3,4);
SELECT 'Change mothers' AS Test should pass;
UPDATE relations SET parent_fk = 4 WHERE child_fk = 3 AND parent_fk = 2;
SELECT 'Add non-mother/father' AS Test should pass;
INSERT INTO relations VALUES (3,5);
SELECT 'Change non-mother/father to mother' AS Test should fail;
UPDATE relations SET parent_fk = 2 WHERE child_fk = 3 AND parent_fk = 5;
SELECT * FROM relations;
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200509110958
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFDJDkzvJuQZxSWSsgRAryTAJ90oT0LWl2ch6c7T7tPsj1/+JpRFwCeOLsV
ceYzuVEHbZPjdCgaMCG65rQ=
=wh38
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] How do I copy part of table from db1 to db2 (and rename the columns)?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I want to copy several columns of a source table from db1 to db2, and create the target table and rename the columns in the process. Ignoring the db part for now, you can do the first part of your request like this: CREATE TABLE mytable2 AS SELECT mycol AS newcolname, id, foo AS foo2 FROM mytable WHERE id 100; Then pg_dump -t mytable2 and pg_restore, as the rest of this thread indicates. Note that you'll lose any triggers, indexes, etc. so only use this if all you really want is the table. Otherwise, it's probably easiest to pg_dump the whole database and then drop/rename columns in the new database. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200508310915 https://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEARECAAYFAkMVrhIACgkQvJuQZxSWSsg3ZwCgs5kG/5P+RFr315+v3ia0g+Tr bAEAoKHfRhJYynxVqyo/U06WaSeDXh3s =hZIK -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] ORDER records based on parameters in IN clause
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 fair enough. but a simple order by id would never work. That was me, sorry, I must have been asleep when I wrote it. :) - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200506300636 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCw8uCvJuQZxSWSsgRAlVbAKCcJ9ktDZggHeICw/gZTBXoeAcK8gCghDKN 7jWWr2T1diDLeEmhzLhogCQ= =Yjrr -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] ORDER records based on parameters in IN clause
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 when I say select * from table where id IN (2003,1342,799, 1450) I would like the records to be ordered as 2003, 1342, 799, 1450. Just say: select * from table where id IN (2003,1342,799, 1450) ORDER BY id; If that doesn't work, you will have to be more specific and send us the exact query. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200506282010 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCwedPvJuQZxSWSsgRAsC0AKD2UrMtQJ6RRxbeZ8J2n68ewRt+EgCeN2UP Qttr1dX9soeBp5HxIp+vz/c= =cGiG -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] people who buy A, also buy C, D, E
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The goal of my query is: given a book, what did other people who bought this book also buy? I plan the list the 5 most popular such books. I've been playing with this a little bit, and I don't think you are going to get better than you already have. Certainly, the caching won't work either as any insert into the watch_list_element has the potential to change a very large number of pre-compiled lists. However, there are some minor optimizations that can be made to speed up the existing query quite a bit. One note first: the LIMIT should be 6 not 5 if you really want the five other books and the book itself will more than likely appear in the list. Picking it out is something the client app can do. * Make sure the tables are freshly analyzed. Might want to bump up the default stats a bit too. * Looks like you already have indexes on the watch_list_element table. The watch_list_element_element_id index could be broken into multiple conditional indexes, but your explain shows this would not really gain us much: actual time=37.957..41.789 * One big gain would be to cluster the table on watch_list_id: CREATE INDEX watch_index ON watch_list_element (watch_list_id); CLUSTER watch_index ON watch_list_element; I got about a 25% speedup on my queries by doing this. YMMV, as I don't know enough about your conditions to do more than make an approximate test database. But it should help this query out. * Finally, you should upgrade if at all possible. Going from 7.4.7 to 8.0.1 gave me a 10% speed increase, while going from 8.0.1 to 8.1.0 (e.g. the upcoming version) gave me an additional 25% speed boost, mostly due to the new bitmap stuff. So, making the jump to 8.0.1 will be good practice for the 8.1.0 jump, right? :) Overall, I was able to get the query to go about a third faster than when I started. Hope this helps. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200506242328 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCvNCrvJuQZxSWSsgRAmkDAJ44z/Ei27HuEBqx/htmCRHJZWi8VQCfV2mm upeE0p3z4h11NJzl5aOqCkc= =LVqI -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SELECT with sum on groups ORDERING by the subtotals
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I need an subtotal for all the products with the same group and that the query be ordered by the bigger subtotal. (please proofread: the subtotals in your example output did not add up) By same group I presume you mean the same code, as you don't actually use the group varchar(10) column you created in your example. A major problem you have is that you have no other way of ordering the rows except by the code. So having a running subtotal is fairly pointless, as the items within each code will appear randomly. Since only the grand total for each code is significant, you could write something like this: SELECT t.*, s.subtotal FROM (SELECT code, SUM(total) AS subtotal FROM test GROUP BY 1) s, test t WHERE s.code = t.code ORDER BY subtotal desc; code | description | quant | price | total | subtotal - ---+-+---+---+---+-- 99120 | PRODUCT C |10 | 0.8 | 8 | 338 99120 | PRODUCT C | 100 | 0.8 |80 | 338 99120 | PRODUCT C | 200 | 0.8 | 160 | 338 99120 | PRODUCT C | 100 | 0.9 |90 | 338 92110 | PRODUCT A |10 | 1 |10 | 120 92110 | PRODUCT A | 5 | 0.9 | 9 | 120 92110 | PRODUCT A | 100 | 0.9 |90 | 120 92110 | PRODUCT A |10 | 1.1 |11 | 120 92190 | PRODUCT b |10 | 1.1 |11 | 41 92190 | PRODUCT b |10 | 1.1 |11 | 41 92190 | PRODUCT b |10 | 1.1 |11 | 41 92190 | PRODUCT b |20 | 0.8 | 8 | 41 If you don't need all that intermediate stuff: SELECT code, SUM(total) AS subtotal FROM test GROUP BY 1 ORDER BY 2 DESC; code | subtotal - ---+-- 99120 | 338 92110 | 120 92190 | 41 If you do need the other rows, you will have to specify a way of ordering the rows within a code group. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200506161458 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCscxavJuQZxSWSsgRAubaAKDXtwvbX4FyvSMeOYqCWAYfStv3xgCfX+XM 79gJZ2hUgDk1jL3LDQv3le0= =mpnW -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] getting details about integrity constraint violation
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Simply name the table constraints yourself with a descriptive name, so you
always know exactly what is going on:
And then I keep a list of all the constraint names and scan the error
message for it?
Don't keep a list: just come up with a standard naming scheme, such as:
tablename|colname|is_not_unique
which should be human and machine parseable (perl example):
if ($error =~ m#^(.+)\|(.+)\|is_not_unique$#o) {
die qq{Whoops : looks like column $2 of table $1 needs to be
unique\n};
}
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200506142204
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFCr4zivJuQZxSWSsgRAgGPAJ0awkoBmus6z1cLBRpsR5xmQPTfiACgpJxG
Ld90hEGDPrebBE3JGGL11L4=
=smQJ
-END PGP SIGNATURE-
---(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] getting details about integrity constraint violation
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Markus Bertheau asked: how would I find out details about for example what constraint was violated by an insert statement? The SQL state tells me, that a unique constraint was violated, but it doesn't say which one. Simply name the table constraints yourself with a descriptive name, so you always know exactly what is going on: greg=# create table unitest(a int, b text); CREATE TABLE greg=# alter table unitest add constraint unitest_column_a_is_not_unique unique(a); NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index unitest_column_a_is_not_unique for table unitest greg=# insert into unitest (a) values (1); INSERT 0 1 greg=# insert into unitest (a) values (1); ERROR: duplicate key violates unique constraint unitest_column_a_is_not_unique - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200506121520 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCrIsevJuQZxSWSsgRAh+gAJ94AsB7rZzpxT7pogC1tgbPaQJzJQCg5YkC E9dXkQk4qP8r8zjCEucxpt0= =NDgJ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] UPDATE WITH ORDER BY
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
(Please send email as text, not HTML)
I need to make an UPDATE on a column reordering it with a sequence
using order by a description
...
BEGIN;
CREATE SEQUENCE fruit_seq;
CREATE TABLE newfruit AS SELECT nextval('fruit_seq')::int AS newid, * FROM
fruit ORDER BY lower(description);
ALTER TABLE newfruit DROP COLUMN id;
ALTER TABLE newfruit RENAME COLUMN newid TO id;
DROP TABLE fruit;
ALTER TABLE newfruit RENAME TO fruit;
DROP SEQUENCE fruit_seq;
COMMIT;
SELECT * FROM fruit ORDER BY id ASC;
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200504270805
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFCb4ArvJuQZxSWSsgRAnSGAJwMbp6qoN3H2wFedsgn8N55kV6zUQCg77Qn
VWsBmACCUFIdzRDRRalG6KI=
=y3G9
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How do I do this?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have a table with column filepath with contents that look like the following: .. Is there anyway to retrieve the directory information only regarding those filepaths? SELECT rtrim(substring(filepath from '.+/'),'/') FROM mytable; - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200503232033 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCQhkMvJuQZxSWSsgRAjTaAJ9zmgSOBH/Nre/XMw+JajBni8YyDwCg1DyV Cd5rIhi026KFoFZEFjMOYvI= =V9ov -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] equivalent of oracle rank() in postgres
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jus wanted the equivalent for rank() as in tis example.. SELECT * FROM ( SELECT employee_id, last_name, salary, RANK() OVER (ORDER BY salary DESC) EMPRANK FROM employees) WHERE emprank = 3; There is no direct equivalent to rank(), but there are certainly other ways to get the results. The above query can be written in PostgreSQL as: SELECT employee_id, last_name, salary FROM employees WHERE salary = (SELECT DISTINCT salary FROM employees ORDER BY salary DESC OFFSET 2 LIMIT 1); - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200503212152 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCP4hwvJuQZxSWSsgRAoKPAKDE0pB4NueE0Dh9EfJiXw79SvCDoACcC4xb ydxVgK9DgGHQXJqFIrlHIIo= =GRIX -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Making NULL entries appear first when ORDER BY field ASC
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Now, as you see, touples with NULL in the start_time-field appear after the others. I would like to make all entries where start_time IS NULL apear *before* all the others. ORDER BY start_date, CASE WHEN start_time IS NULL THEN 0 ELSE 1 END, start_time - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200502152309 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCEseYvJuQZxSWSsgRAlipAJwKAyqAyLbo9hfpoWkz0SOlTY3feACfa+ng DqNY4DAJ5TeeGQbI+smNilg= =LRhP -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])
Re: [SQL] 'show databases' in psql way?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 To be pedantic about it, use: SELECT pg_catalog.quote_ident(datname) AS database FROM pg_catalog.pg_database ORDER BY 1; or if using version 8 or higher: SELECT pg_catalog.quote_ident(datname) AS database, pg_catalog.quote_ident(spcname) AS tablespace FROM pg_catalog.pg_database JOIN pg_catalog.pg_tablespace t ON (dattablespace=t.oid) ORDER BY 1; - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200411022120 -BEGIN PGP SIGNATURE- iD8DBQFBiFTSvJuQZxSWSsgRApflAJ0RiVndbc6u//cXX/S7uM8K91lWbgCfYVbC MsRNWlP446AcvHm0UaGgEVo= =S8Ft -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] 'show databases' in psql way?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
how can I list the databases in a postgresish way? I know
about the '-l' switch of 'psql' but is there a DBI/SQL-query way?
Yes, the DBI way is to use the data_sources function. Works
just fine for Postgres:
my @dbs = DBI-data_sources('Pg');
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200411012240
-BEGIN PGP SIGNATURE-
iD8DBQFBhwIHvJuQZxSWSsgRArd1AJ9hKXD+cSaM2L3RUXQdabuRofNFjwCfaHT0
+bRPuYhuED0mnlp1FRtvQQw=
=tsqe
-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])
Re: [SQL] colored PL with emacs
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have SQL highlighting, but what I want are colors for the PL/pgSQL key words. It would make PL programming much easier. There's nothing out there. but writing one is on my long-term TODO list. KDE's Kate has PostgreSQL highlighting. Unfortunately, the config is XML so it's not transferrable to Emacs ... Send me (or the list, of it's short) a copy, I'll see if I can do anything with it. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200408311920 -BEGIN PGP SIGNATURE- iD8DBQFBNQfgvJuQZxSWSsgRAl8CAJ9bmcZG/pTx9Pg5H+sjxT77RbWsLwCg0b1u oB/ZJocEC42tdahkIrlBIZs= =eF8t -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] SQL Challenge: Arbitrary Cross-tab
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Nope; Gaetano's right, you cannot assume that. It's entirely possible for the planner to choose different plans depending on the OFFSET. (Maybe not very likely, with such small offsets, but could happen.) Interesting. I realized that there was a finite chance of different plans being chosen, but it seemed neigh-impossible since there is no WHERE clause and the offsets only vary from 0-7. What sort of different plans would it choose, out of curiosity? Seq-scan vs. index-scan? Are there any particular cases where the same plan is guaranteed to be used? - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200408192216 -BEGIN PGP SIGNATURE- iD8DBQFBJV9xvJuQZxSWSsgRAp74AJ96mtrKC1J53y0TPqTPdq2Xost0fACg4DnJ 7P+dgpHWBazGNE9+SR7uxLY= =MZuM -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] SQL Challenge: Arbitrary Cross-tab
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Don't you miss for each subselect an order by tid ? No: since all the SELECTs are part of one statement, they will have the same (pseudo-random) implicit order. Since Josh's requirement said the order of the tids* was not important, I can be lazy and get away with it in this case. An order by would not hurt, of course. * As far as PG goes, this is not an ideal abbreviation! :) - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200408180745 -BEGIN PGP SIGNATURE- iD8DBQFBI0InvJuQZxSWSsgRApW7AKCpFN6TMQ3WjcJgZse5f+Ap6/Y7RACfSSlc MZusqEadF2xZrE4PLOhmMek= =pnQz -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] SQL Challenge: Arbitrary Cross-tab
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Names shortened to spare the line lengths: SELECT bob.cid, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1) AS tk1, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 1) AS tk2, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 2) AS tk3, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 3) AS tk4, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 4) AS tk5, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 5) AS tk6, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 6) AS tk7, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 7) AS tk8, FROM (SELECT DISTINCT cid FROM ats) AS bob; - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200408172335 -BEGIN PGP SIGNATURE- iD8DBQFBIs7AvJuQZxSWSsgRAkglAJ9mNEmOYlLPynygMmelvzlqkYoHlwCeJqTb g5gyh9LztONPCZj32aOEuGI= =Yy7m -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Is there a faster way to do this?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hard to imagine it taking that long, even for a table that large, but we don't know the details of your system. I got a sample table with 800,000 records down to 92 seconds using the function below. See how it does for you. Notes follow. CREATE OR REPLACE FUNCTION percentile_calc(INTEGER) RETURNS text AS ' DECLARE v_tile ALIAS FOR $1; v_interval integer; v_record record; v_rowcount integer := 0; v_percentile integer := 1; BEGIN SELECT COUNT(*)/v_tile FROM cdm_indiv_mast WHERE val_purch_com 0 INTO v_interval; FOR v_record IN SELECT ctid FROM cdm_indiv_mast WHERE val_purch_com0 ORDER BY val_purch_com DESC LOOP v_rowcount := v_rowcount + 1; UPDATE cdm_indiv_mast SET percentiler=v_percentile WHERE ctid = v_record.ctid; IF v_rowcount = v_interval THEN v_percentile := v_percentile + 1; v_rowcount := 0; END IF; END LOOP; RETURN \'DONE\'; END; ' LANGUAGE plpgsql STABLE STRICT; CREATE OR REPLACE FUNCTION percentile_calc() RETURNS text AS 'SELECT new10(100);' LANGUAGE SQL; Notes: Since 100 seemed to be hard-coded into the original function, there was no need for v_count. Instead, I made v_tile a variable, with a default of 100 if the function is called with no argument. There may be a false assumption here. If the values of val_purch_com are not unique, then two items with the same val_purch_com may have different percentiler values. If this is the case, you may want to at least enforce some ordering of these values by adding more to the ORDER BY clause. Without knowing the full table structure, I can't recommend what columns to add there. To really speed this up, make sure that you do not have any indexes on the table. By using tids, we neatly avoid having to use any indexes in the function itself. Unless you are using oids and really need them (highly unlikely because of the indiv_key column), you should remove them: ALTER TABLE cdm_indiv_mast SET WITHOUT OIDS; Of course, vacuuming completely and often is recommended for a table this size as well, especially when updating this many rows at once. I'd recommend a VACUUM FULL immediately before running it. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200406162303 -BEGIN PGP SIGNATURE- iD8DBQFA0QpdvJuQZxSWSsgRAk88AKDtGrs6+/ypaaNU6DWqvhCgtsrM0gCg+2ve J8JKOPgxp42c54Nx/rzHdxs= =sNFW -END PGP SIGNATURE- ---(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] Order by YYYY MM DD in reverse chrono order trouble
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am trying to select distinct dates and order them in the reverse chronological order. Although the column type is TIMESTAMP, in this case I want only , MM, and DD back. If you don't need them separated (which I suspect may be the case), you can do something like this: SELECT DISTINCT TO_CHAR(add_date,'-MM-DD') AS bb FROM tt ORDER BY bb DESC; - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200404240716 -BEGIN PGP SIGNATURE- iD8DBQFAikzOvJuQZxSWSsgRAgqbAKDC75SQd2aExYaniSJIzovOlVjvCACgyOAl Q2KMp3YGBkQwy5y4h9r/96A= =4idZ -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])
Re: [SQL] Design Problem...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The problem is that it is possible that the service can be switched to a different business unit, and then possibly back to the original later on. First, you will get more responses if you do not create a new topic in the middle of an existing thread. Here is a simplified answer to your problem. Basically, you need to create a link between a business and a service, and note when that link was created. You can grab the highest creation time for a service to see which business currently owns it. Depending on how often things change around, you may want to simply have a trigger on the bs_map table that updates a business column in the services table, rather than having to compute the max creation time constantly. CREATE TABLE business ( idSERIAL UNIQUE, bname VARCHAR ); CREATE TABLE service ( idSERIAL UNIQUE, sname VARCHAR ); CREATE TABLE bs_map ( business INTEGER NOT NULL, service INTEGER NOT NULL, assigned TIMESTAMPTZ NOT NULL DEFAULT now() ); ALTER TABLE bs_map ADD CONSTRAINT bs_map_business_fk FOREIGN KEY (business) REFERENCES business(id) ON DELETE RESTRICT ON UPDATE CASCADE; ALTER TABLE bs_map ADD CONSTRAINT bs_map_service_fk FOREIGN KEY (service) REFERENCES service(id) ON DELETE RESTRICT ON UPDATE CASCADE; - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200404241255 -BEGIN PGP SIGNATURE- iD8DBQFAipwPvJuQZxSWSsgRApPVAJwPvc2aTadzTfKBJIge+2bh+IQ7qwCeN2rZ MTilGUtbg0y4DOAENUzXc80= =Jw5D -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] SQL challenge--top 10 for each key value?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
How would you go about getting the top N (say, the top 10) for each query?
Assume you have a table ch and three sequences 'aa', 'bb', and 'cc'.
(Only 'aa' and 'bb' need to be initially set)
SELECT setval('aa',1,'f');
SELECT setval('bb',1,'f');
SELECT nextval('cc') AS rating,q2 AS query, s2 AS score FROM
(
SELECT 0 AS q1, 0 AS s1, NULL AS cs, nextval('aa') AS v1
UNION ALL
(SELECT *, nextval('aa') AS v1 FROM
(SELECT query AS q1, MAX(score) AS s1, checksum AS cs FROM ch GROUP BY 1,3 ORDER BY
1 ASC, 2 DESC) AS foo)
) AS uno,
(
(SELECT *, nextval('bb') AS v2 FROM
(SELECT query AS q2, MAX(score) AS s2, checksum AS cs FROM ch GROUP BY 1,3 ORDER BY
1 ASC, 2 DESC) AS foo)
UNION ALL
SELECT NULL AS q2, 0 AS s2, NULL AS cs, nextval('bb') AS v2
) AS dos
WHERE v1 = v2 AND q2 IS NOT NULL
AND (
(CASE WHEN q1 != q2 THEN setval('cc',1,'f') ELSE 0 END 0)
OR
(CASE WHEN currval('cc')10 THEN 1 ELSE 0 END 0)
);
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200404101029
-BEGIN PGP SIGNATURE-
iD8DBQFAeAZ1vJuQZxSWSsgRAqYuAJ9HaYLotPYkyi1U76I9xnvi8AhLTQCfUyJq
+iVdbz5U7HKep89z0kp49U0=
=6+OH
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Counting rows from two tables in one query
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 What I am trying to get is a list that shows how many records from 'sub_a' and 'sub_b' are referencing 'main': SELECT id, (SELECT COUNT(*) FROM sub_a WHERE sub_a.main_id=main.id), (SELECT COUNT(*) FROM sub_b WHERE sub_b.main_id=main.id) FROM main ORDER BY id; - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200404092128 -BEGIN PGP SIGNATURE- iD8DBQFAd03pvJuQZxSWSsgRAuDuAJ0elm2bPjgC1bGPHnrotzXrPKCt4ACdFytf BglMm6IouFFZt1c19zST5ac= =7DbY -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] Last day of month
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 How to find the last sunday/mon/sat of any given month. There is probably a smoother way to do it, but here is a quick little function to do what you ask. Feed it a date and a number, where 0 is Sunday, 1 is Monday, etc. CREATE OR REPLACE FUNCTION lastday(date,int) RETURNS DATE AS ' DECLARE match date; tomorrow date; BEGIN SELECT TO_DATE( (SELECT EXTRACT(\'year\' FROM $1) || \' \' || EXTRACT(\'month\' FROM $1) || \' 01\'), \' MM DD\') INTO tomorrow; LOOP tomorrow := tomorrow + \'24 hours\'::interval; IF (1 = EXTRACT(\'day\' FROM tomorrow) ) THEN RETURN match; END IF; IF ($2 = EXTRACT(\'dow\' FROM tomorrow)) THEN match := tomorrow; END IF; END LOOP; END; ' LANGUAGE plpgsql; - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200402252206 -BEGIN PGP SIGNATURE- iD8DBQFAPWL1vJuQZxSWSsgRAu0tAKDO7oKbxOmfDpCUYpeDSwCwyALs7QCgvKT3 x+aqhBqzm9F87ESbsMe6HdQ= =AriI -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Atomic query and update of sequence generators
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The goal is to get a sequence of 20 values that are all +1 from each other. If you don't care about the rest of the values being +1 from each other, you could create the sequence with an INTERVAL of 20. Other than that, you would probably have to create your own sequence generator. Another alternative would be a function like this: get nextval as x set sequence to x+20 get nextval as y repeat if y != x+20 (i.e. someone else grabbed a value) - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200401150746 -BEGIN PGP SIGNATURE- iD8DBQFABovdvJuQZxSWSsgRAnxeAKCWK3tFCE3u8NfXG5LG3H0smDLyhACglrr7 08ke6k8B8MSKVipRb2aSWQg= =PSzM -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] Different query results in psql and Perl DBI with Postgres 7.2.1
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Brendan LeFebvre writes:
Even more perplexing: the 0E0 occurs ONLY when setting status to
'STORE', and not 100% of the time. (though it happens far more often
than not.) It seems to operate in stretches, too: when I am getting
1-row updates back from the latter query type, it happens several
times in succession.
0E0 is perl's way of saying 0 but true, which means that the
statement succeeded, but did not return any rows. If it simply
returned a 0, we would not be able to differentiate between
an error (false) and no rows affected (0).
Where do I even begin to attempt a diagnosis?
It looks as thogh the row you are trying to update does not
exist. Not much more advice is possible without seeing a more
complete sample of the code.
boyd writes:
$info =~ s/0//g; # the get_info adds extraneous '0' to the version number
It is not extraneous: it is required per the ODBC spec. For a simpler
number, try using this:
my $pgversion = $dbh-{private_dbdpg}{server_version};
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 20040401
-BEGIN PGP SIGNATURE-
iD8DBQFAAaEwvJuQZxSWSsgRAk59AJ4zeNNm225TdecB2wgcQnFIJNqpmgCePmdX
XAPC7vVv+517CR2g3p/6U6c=
=oSYK
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
