Re: [SQL] Problem with n to n relation
Am Freitag, 5. Oktober 2001 14:30 schrieb Morgan Curley: > just get rid of the serial_id in person2adress -- there is no > reason for it. Make the pk of that table a composite --> person_id, > address_id <-- that way you have added some additional integrity to > your structure. Only one record can exist ffor a given person at a > given address. However any person can have any number of address > and any address can have any number of people living at it. ok fine, i understood it after i figured out what pk means :-) but how do i reach my goal. It should not be allowed to have a person without any address?? janning > At 12:39 PM 10/5/2001 +0200, Janning Vygen wrote: > >create table person ( > > idserial, > > name text > >); > > > >create table address ( > > id serial, > > street text > > ... > >); > > > >create table person2adress ( > > id serial, > > person_id integer not null references person(id), > > address_id integer not null references address(id), > >); > > > >than i can select all adresses from one person with id =1 with > >select street > >from address > >where id = > > ( > > select adress_id > > from person2adress > > where person_id = 1 > > ); > > > >ok so far so good. but you can still insert persons without any > >adress. so its a 0..n relation. But how van i achieve that you > > can´t insert any person without adress??? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Problem with n to n relation
Am Dienstag, 9. Oktober 2001 17:38 schrieb Stephan Szabo: > > Yes you are right! It doesnt work. i thought i have tested it... > > > > But i dont understand your solution. Why did you insert foo in > > the person table? Dou want to use it as a foreign key? Maybe you > > just mistyped your alter table statement i guess. you create a > > unique person2address id and references foo to it. So 'foo' will > > always refernce the first address inserted. Right? Ok thats a > > fine solution, but there is more work to do like a trigger when > > deleting this address... > > Yeah, miscopied the statement. And you're right, I'd forgotten > about delete. I think you'd probably be better off faking the > check constraint in a deferred constraint trigger. Thank you very much for your very comprehensive answers. I guess i know enough to solute my problem. Thanks Janning > > Is this the preferable solution?? I am wondering about tutorials > > never explaining stuff like that. Is it too uncommon to have a > > person with at least one address? > > Well, the *best* way (that doesn't work in postgres) is probably > to have a check constraint with a subselect, something like > check exists(select * from person2address where ...) initially > deferred. But we don't support subselect in check directly, and ^^^ ups, your are a developer of postgresql?? thanks for this great database. > its not likely to happen soon (it's a potentially very complicated > constraint). > There are locking issues, but one could probably use a constraint > trigger (a postgres specific thing I think, but...) and have the > trigger do a select * from person2address where... and raise an > exception if no matches are found. The locking issues are due to > the fact that you could run into problems with multiple backends > trying to do stuff to the same rows if you're not careful, although > I think it might work out with for update. ---(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] Problem with n to n relation
Am Montag, 8. Oktober 2001 18:09 schrieb Stephan Szabo:
> On Mon, 8 Oct 2001, Janning Vygen wrote:
>
> > but how do i reach my goal. It should not be allowed to have a
> > person without any address??
>
> Hmm, do you always have at least one known address at the time
> you're inserting the person?
>
> I can think of a few somewhat complicated ways. Person getting a
> column that references person2adress with initially deferred, the
> problem here is that you don't know one of the tables' serial
> values unless you're selecting it yourself which would mean you'd
> have to change how you were getting your incrementing numbers
> (getting currval of some sequence presumably and using that to
> insert into person2adress).
yeah, thats a way which works. dont know if its cool to do it like
this, but you cant insert a person without any address. so you are
forced to use a transaction.
create table person (
id serial,
name text
);
create table address (
id serial,
street text NOT NULL
);
create table person2address (
id int4,
address_id int4 NOT NULL REFERENCES address (id),
person_id int4 NOT NULL REFERENCES person (id)
);
ALTER TABLE person ADD CONSTRAINT person_has_adress FOREIGN KEY (id)
REFERENCES person2address(id) DEFERRABLE INITIALLY DEFERRED;
begin;
insert into person (name) values ('janning');
insert into address (street) values ('Sesamestreet');
insert into person2address values(1,1,1);
commit;
> You could probably also make your own deferred constraint trigger
> (although I'm not sure that it's documented since I don't think it
> was really meant as a user feature) which does the check at the end
> of any transaction in which rows were inserted into person.
>
> > > At 12:39 PM 10/5/2001 +0200, Janning Vygen wrote:
> > > >create table person (
> > > > idserial,
> > > > name text
> > > >);
> > > >
> > > >create table address (
> > > > id serial,
> > > > street text
> > > > ...
> > > >);
> > > >
> > > >create table person2adress (
> > > > id serial,
> > > > person_id integer not null references person(id),
> > > > address_id integer not null references address(id),
> > > >);
> > > >
--
Planwerk 6 /websolutions
Herzogstraße 86
40215 Düsseldorf
fon 0211-6015919
fax 0211-6015917
http://www.planwerk6.de
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Problem with n to n relation
Am Montag, 8. Oktober 2001 19:33 schrieb Stephan Szabo:
> On Mon, 8 Oct 2001, Janning Vygen wrote:
> > Am Montag, 8. Oktober 2001 18:09 schrieb Stephan Szabo:
> > > On Mon, 8 Oct 2001, Janning Vygen wrote:
> > > > but how do i reach my goal. It should not be allowed to have
> > > > a person without any address??
> > >
> > > Hmm, do you always have at least one known address at the time
> > > you're inserting the person?
> > >
> > > I can think of a few somewhat complicated ways. Person getting
> > > a column that references person2adress with initially deferred,
> > > the problem here is that you don't know one of the tables'
> > > serial values unless you're selecting it yourself which would
> > > mean you'd have to change how you were getting your
> > > incrementing numbers (getting currval of some sequence
> > > presumably and using that to insert into person2adress).
> >
> > yeah, thats a way which works. dont know if its cool to do it
> > like this, but you cant insert a person without any address. so
> > you are forced to use a transaction.
> >
> > create table person (
> > id serial,
> > name text
> > );
> >
> > create table address (
> > id serial,
> > street text NOT NULL
> > );
> >
> > create table person2address (
> > id int4,
> > address_id int4 NOT NULL REFERENCES address (id),
> > person_id int4 NOT NULL REFERENCES person (id)
> > );
> >
> > ALTER TABLE person ADD CONSTRAINT person_has_adress FOREIGN KEY
> > (id) REFERENCES person2address(id) DEFERRABLE INITIALLY DEFERRED;
>
> This unfortunately will fail on 7.1 and higher due to the fact that
> a target of a foreign key constraint must have a unique constraint
> on it. The problem is that if you make id effectively the same
> as person's id and unique you can't have two addresses for one
> person. I think you might need to do something like (untested and I
> think I got some syntax confused, but enough for the idea)
Yes you are right! It doesnt work. i thought i have tested it...
But i dont understand your solution. Why did you insert foo in the
person table? Dou want to use it as a foreign key? Maybe you just
mistyped your alter table statement i guess. you create a unique
person2address id and references foo to it. So 'foo' will always
refernce the first address inserted. Right? Ok thats a fine solution,
but there is more work to do like a trigger when deleting this
address...
Is this the preferable solution?? I am wondering about tutorials
never explaining stuff like that. Is it too uncommon to have a person
with at least one address?
Janning
> create table person (
> id serial,
> name text,
> foo int4
> );
>
> create table address(
> id serial,
> street text NOT NULL
> );
>
> create table person2address (
> id int4,
> address_id int4 NOT NULL REFERENCES address (id),
> person_id int4 NOT NULL REFERENCES person (id)
> );
>
> create sequence person2address_seq;
>
> ALTER TABLE person ADD CONSTRAINT person_has_adress FOREIGN KEY
> (id) REFERENCES person2address(id) DEFERRABLE INITIALLY DEFERRED;
> begin;
> select next_val('person2address_seq');
> -- I'll refer to this as below
> insert into person (name, foo) values ('janning', );
> insert into address (street) values ('Sesamestreet');
> insert into person2address values (,
> currval('person_id_seq'), currval('address_id_seq')); commit;
--
Planwerk 6 /websolutions
Herzogstraße 86
40215 Düsseldorf
fon 0211-6015919
fax 0211-6015917
http://www.planwerk6.de
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Problem with n to n relation
Hi, i create n to n relations like this, right? create table person ( idserial, name text ); create table address ( id serial, street text ... ); create table person2adress ( id serial, person_id integer not null references person(id), address_id integer not null references address(id), ); than i can select all adresses from one person with id =1 with select street from address where id = ( select adress_id from person2adress where person_id = 1 ); ok so far so good. but you can still insert persons without any adress. so its a 0..n relation. But how van i achieve that you can´t insert any person without adress??? thanks in advance janning ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Joining three data sources.
Am Mittwoch, 19. Juni 2002 16:09 schrieb Masaru Sugawara: > On Wed, 19 Jun 2002 12:33:47 +0200 > > Janning Vygen <[EMAIL PROTECTED]> wrote: > > - > > Result Inter Mailand vs. AC ROM 2:1 > > team1_id|team2_id|goals1|goals2 > > 1 2 2 1 > > SELECT go1.game_id, go1.team1_id, go1.team2_id, > SUM(CASE WHEN go2.team_id = go1.team1_id > THEN go2.n ELSE 0 END) AS goals1, > SUM(CASE WHEN go2.team_id = go1.team2_id > THEN go2.n ELSE 0 END) AS goals2 > FROM (SELECT game_id, > min(team_id) AS team1_id, > max(team_id) AS team2_id > FROM goal > GROUP BY 1) AS go1, > (SELECT game_id, team_id, count(*) AS n >FROM goal > GROUP BY 1, 2) AS go2 > WHERE go1.game_id = go2.game_id > GROUP BY 1, 2, 3; Oh thanks a lot. You pushed me in the right direction. i still get headache when trying to write complicated selects. there was something wrong in your statement but i was able to correct it by myself. Thanks for your help!! Are you able to type those queries in minutes?? It seems so ... amazing! > As for Goal table, if it has a large number of the rows, you maybe > need to create a unique index on it. of course. it was just an example... kind regards janning ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] manipulating the database in plsql
Am Sonntag, 7. Juli 2002 20:40 schrieb teknokrat: > Is there any way to work with tables etc, in plsql? Can i get a get > a database handle to the local database? If not, is there any > chance for this to be implemented in the near future? I guess you mean plpgsql. you dont need a database handle inside plpgsql. you just can do SQL operations or do other stuff like inserting, updating and so on (sometimes you need the plpgsql PERFORM statement). look at the programmers guide about procedural languages (plpgsql.html) janning ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Function error
Am Dienstag, 13. August 2002 08:06 schrieb Sugandha Shah: > Hi , > > I'm porting MS- SQL stored procedure to postgres . I'm getting this > error : > > Error occurred while executing PL/pgSQL function > sel_free_disk_space line 7 at SQL statement > SELECT query has no destination for result data. > If you want to discard the results, use PERFORM instead. you need to SELECT INTO if you want to set a varaible in plpgsql try this: DECLARE var_free integer; -- var_free is used just to name it differently from the column name BEGIN SELECT INTO var_free free from logical_drive where computer_id = $1 and letter = upper($2); IF var_free THEN ... Janning > CREATE FUNCTION sel_free_disk_space(int4,bpchar) RETURNS integer > AS ' DECLARE > -- Declare variable to store the free space. > free INTEGER; > > BEGIN >select free from logical_drive where computer_id = $1 and > letter = upper($2); > IF free IS NULL THEN >RETURN -1; > END IF; > > RETURN free; > END; > 'LANGUAGE 'plpgsql'; > > > I'm not able to understand what I'm missing ? > > Secondly is there any equivalent of exec for postgres ? > > Any help will be highly appreciated. > > Regards, > -Sugandha -- Planwerk 6 /websolutions Herzogstraße 86 40215 Düsseldorf fon 0211-6015919 fax 0211-6015917 http://www.planwerk6.de ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Few Queries
Am Mittwoch, 14. August 2002 07:05 schrieb Sugandha Shah:
> 1. I 'm firing a query and it returns the value in variable which I
> need to pass to other query . Is this a right way to pass the
> value ? I'm newbie to this Database and hence facing lot of
> syntax problems.
>
> CREATE FUNCTION del_old_history() RETURNS bool AS '
> declare
>var_history_age_limit int4;
>set_timedatetime;
> BEGIN
> select into var_history_age_limit history_age_limit from
> database_info; IF (var_history_age_limit is not null) THEN
> set_time := select current_date()+ INTERVAL ' '
If you do a aselect you need select into. Normal assignment is only
possible with simple expression. Try:
SELECT INTO set_time current_date()+ INTERVAL ' ';
> 2. Is there any equiavlent of MS -SQLServer 'trancount ' in
> postgres ?
you should only post one question per mail and i dont know waht
trancount is.
do you mean something like getting the affected rows?
Look at this file in the postgresdocs (7.2.1)
plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
> 3. if object_id ('database_info') is null
>how is above statement verified in postgres . I tried looking
> for OID .
same as answer to question number 2.
something like
GET DIAGNOSTICS var_oid = RESULT_OID;
IF var_oid IS NULL THEN
janning
--
PLANWERK 6 /websolutions
Herzogstraße 86
40215 Düsseldorf
fon 0211-6015919
fax 0211-6015917
http://www.planwerk6.de
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Results from EXECUTE
Am Freitag, 16. August 2002 18:26 schrieb Alexander M. Pravking: > How can I obtain results from an EXECUTE statement > within a pl/PgSQL function? >From the docs: plpgsql-statements.html postgres 7.2.1 "The results from SELECT queries are discarded by EXECUTE, and SELECT INTO is not currently supported within EXECUTE. So, the only way to extract a result from a dynamically-created SELECT is to use the FOR-IN-EXECUTE form described later." Look at the example in the docs. janning -- Planwerk 6 /websolutions Herzogstrasse 86 40215 Duesseldorf fon 0211-6015919 fax 0211-6015917 http://www.planwerk6.de ---(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] Informing end-user of check constraint rules
Am Sonntag, 22. Juni 2003 14:45 schrieb [EMAIL PROTECTED]:
> I have not used column check constraints before, but I'd like to start
> using then and so would I'll like to know if there is a direct way to
> provide feedback to the end user about data validation rules expressed in
> column check constraints?
>
> For instance, say that I wanted to use a RE to check e-mail address
> format validity and then the data entry clerk typed in invalid data. My
> understanding is that when the check constraint returns FALSE, the row
> will not insert, and an ExecAppend: rejected due to CHECK constraint
> "table_column " exception is raised. That at least tells the column
> (albeit in language that would scare the computer-phobe), but I like the
> exception message to tell the end user what the format is supposed to be.
> Is my only option to have the end-user application (as opposed to the
> database) inform the end-user what the correct data format is? If THAT is
> so, then it seems I might as well also perform the data formatting
> validation in the application, too, so that at least they'ld both be in
> the same place and not two separate places.
>
> What I'd like is to be able to specify some kind of error message telling
> the user what the correct format should be, and since the proper format
> is specified in the database, i.e., in the check constraint, it seems
> that the proper place to raise an exception providing the remedial
> instructions would also be in the database.
you can use a trigger on insert and write your own error handling function
like below. then you have everything at one place. I think postgres should
have better ways to report errors but i am not a database guru and dont know
how other databases do their error handling.
Maybe its better to have some kind of "middleware" to keep the business logic
and use the database just to store data... i thought about it a lot and tried
to find relevant informations about how to model data/businesslogic/frontend
in a convienient way...
here is an example to check different columns and return an explanation of on
or more errors. of course your frontend has to parse this errormsg for the
relevant part shown to the user. its just copied but a little bit modified
code from a working example. but this code below isn't tested and
errormessages are in german.
what i like most is not having good error message but you can show all errors
at once.
kind regards
janning
CREATE TRIGGER tg_user BEFORE INSERT OR UPDATE ON USER FOR EACH ROW EXECUTE
PROCEDURE tg_user_col_check();
CREATE FUNCTION tg_user_col_check () RETURNS TRIGGER AS '
DECLARE
var_errmsg text := ''TIPPER'';
var_errorboolean;
rec_any RECORD;
var_countint4;
var_maxmitgl int4 := 1000; ---
-- email --
---
NEW.email := btrim(NEW.email);
IF NEW.email !~ ''[EMAIL PROTECTED]'' THEN
var_error := ''true'';
var_errmsg := var_errmsg || ''#name:''
|| ''Die E-Mail Adresse darf nur aus Buchstaben, Zahlen und einigen
Sonderzeichen ("_", "-", "@", ".") bestehen. '';
END IF;
IF length(NEW.name) < 3 THEN
var_error := ''true'';
var_errmsg := var_errmsg || ''#name:''
|| ''Der Benutzername muss mindestens drei Zeichen lang sein";
END IF;
IF length(NEW.email) > 50 THEN
var_error := ''true'';
var_errmsg := var_errmsg || ''#email:''
|| ''Die E-Mail Adresse darf nicht länger als 50 Buchstaben sein'';
END IF;
IF var_error THEN
RAISE EXCEPTION ''%'', var_errmsg;
END IF;
RETURN NEW;
END;
' language 'plpgsql';
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Is there an easy way to normalize-space with given string functions
Hi,
i am looking for something like
$ SELECT btrim(replace(' too many spaces! ', '\s+',' '), '');
too many spaces
i searched the function list and tried to combine to or more functions, but i
miss a replace function which uses regular expressions.
Do i have to write my own function or did i miss something? trimming is well
supported at the start and end of string, but no trimmin in the middle seems
to be possible.
kind regards
janning
---(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] Is there an easy way to normalize-space with given string functions
Am Freitag, 23. April 2004 04:34 schrieb Tom Lane:
> Janning Vygen <[EMAIL PROTECTED]> writes:
> > i searched the function list and tried to combine to or more
> > functions, but i miss a replace function which uses regular
> > expressions.
>
> There isn't one in the SQL standard. Most people who need one write a
> one-liner function in plperl or pltcl.
Thank you.
> (Mind you, I don't know why we don't offer a built-in one --- the needed
> regex engine is in there anyway. I guess no one has gotten around to
> getting agreement on a syntax.)
My suggestion:
Syntax:
substitute(string text, from text, to text);
Example:
substitute(' too many spaces ', '\s+', ' ');
Result:
' too many spaces '
But maybe its a bad idea to create new function names...
kind regards
janning
---(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] surrogate key or not?
Am Freitag, 23. Juli 2004 12:02 schrieb Kenneth Gonsalves: > ok, i'll rephrase the whole thing: > > i have a master table with two fields: > id serial unique > name varchar not null (and will be unique) > > i always make 'name' the primary key, and since it is the primary key, i > dont explicitly specify it as unique, and after postgres 7.3 came out, i > have added the 'unique' constraint to the 'id' > > on looking at the gnumed schema, i saw that although 'name' was unique, the > serial key, 'id' was made the primary key. So i wondered why and whether > there were advantages in doing it this way. Does your question relates to surrogate vs natural keys discussion? I made some researches a few months ago and read a lot including: http://www.intelligententerprise.com/print_article_flat.jhtml?article=/030320/605celko1_1.jhtml http://www.winnetmag.com/Articles/Print.cfm?ArticleID=5113 http://www.dbpd.com/vault/9805xtra.htm http://www.sapior.com/Resources/Surrogate_Keys/surrogate_keys.html http://www.bits-pilani.ac.in/dlp-home/online/studymat/sszg515/lecture3.htm http://www.bcarter.com/intsurr1.htm i decided to use natural keys wherever possible and i have many primary keys spanning up to 4 attributes. And it works really fine. Performance is great, the schema is easy to use and i am so glad to use the natural key approach. Writing SQL queries and php code is much easier! By now i try to avoid surrogate keys (like with SERIALs datatype) wherever possible. Most articles advocate surrogate keys and at first it looks like an advantage in a web environment because selecting and transmitting a multi-column primary key in a form field ist very difficult. Imagine a element, but you have only one value to be returned. My trick here is to have the primary keys used in the select element saved in a session array and using the session array index as a select element value. But the strongest argument for me is: All candidate keys have to be unique anyway. And postgresql builds an index anyway for every UNIQUE key, because thats the way postgresql checks uniqueness. So why add another artifical key with another index when you can use the one which is given anyway. Think of usergroups identified by name and members which are identified by user groups name and email adress, then you've got the pseudo schema create table usergroups ( ug_name text, CONSTRAINT uq_ug UNIQUE (ug_name) ); create table members ( ug_name text, mb_email text, CONSTRAINT uq_mb UNIQUE (ug_name, mb_email), CONSTRAINT fk_ug_name FOREIGN KEY ug_name REFERENCES usergoups (ug_name) ); so you have to indexes uq_mb and uq_ug anyway. So why dont use them as Primary Keys?? With two more attribute for a surrogate key like ug_id SERIAL PRIMARY KEY in table usergroups and mb_id SERIAL PRIMARY KEY you have additional 4 bytes to store for each table row and one more index for each table. So my conclusion is: i dont see any benefit in using surrogate keys. But this must be wrong because so many people are using and advocating surrogate keys. They might only be useful in circumstances where no natural key is given. kind regards, janning ---(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] How do FKs work?
Am Sonntag, 10. Oktober 2004 15:01 schrieb Marc G. Fournier: > On Sun, 10 Oct 2004, Janning Vygen wrote: > > Am Sonntag, 10. Oktober 2004 02:43 schrieb Marc G. Fournier: > >> On Sat, 9 Oct 2004, Tom Lane wrote: > >>> "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > >>>> Have a table with two FKs on it ... 2 different fields in the table > >>>> point to the same field in another table ... > >>>> > >>>> When I do an 'EXPLAIN ANALYZE DELETE FROM table WHERE field = #;', it > >>>> never comes back ... or, at lesat, takes a *very* long time ... > >>> > >>> Do you have indexes on the referencing columns? Are they exactly the > >>> same datatype as the referenced column? You can get really awful plans > >>> for the FK-checking queries if not. > >> > >> Yup, that was my first thought ... running SELECT's joining the two > >> tables on the FK fields shows indices being used, and fast times ... Could you please show me your schema design regarding those two tables. I had this problem too and it just lacks from an index on the foreign key. janning ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Query aid
Am Donnerstag, 16. Dezember 2004 11:34 schrieb Roberto Fichera: > Hi all, > > I have a table acct as (username, terminatedate, terminatecause) > I would like to build a query which returns three columns orderd by data > like: > > date_trunc( 'day', terminatedate ) | count(cause1) | count(cause2) > > where cause1/2 are two type of termination cause from the field > terminatecause. > > for example acct table could be: > > user1|01/01/2004 01:01:01| error > user2|01/01/2004 01:02:01| error > user1|01/01/2004 02:00:01| normal > user3|02/01/2004 10:00:01| normal > user2|02/01/2004 10:10:01| error > > I would like to obtain: > > date |normal| error > 01/01/2004| 1 | 2 > 02/01/2004| 1 | 1 try something like this: SELECT date_trunc( 'day', terminatedate ) AS day, SUM( CASE WHEN cause = 'error' THEN 1 ELSE 0 END ) AS error_count, SUM( CASE WHEN cause = 'normal' THEN 1 ELSE 0 END ) AS normal_count, FROM acct AS acct1 GROUP BY day ORDER BY day ASC; kind regards, janning ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Double query (limit and offset)
Am Dienstag, 11. Oktober 2005 17:11 schrieb Michael Landin Hostbaek: > List, > > I'm using the OFFSET / LIMIT combo in order to split up my query, so it > only parses 20 rows at a time (for my php-scripted webpage). > > I'm using two queries; the first basically doing a select count(*) from > [bla bla]; the second grabbing the actual data while setting LIMIT and > OFFSET. > > In addition, I'm using the first query plus some calculations to parse > total hits to the query, and number of pages etc etc. > > Now, my problem is this, the first query is simply counting all rows > from the main table, whereas the second query has plenty of JOINS, and a > GROUB BY statement - it's a fairly heavy query. The total (reported by > the first query), it not at all the same as the amount of rows returned > by the second query. I'd like to avoid having to run the "heavy" query > twice, just in order to get the number of rows. > > Is there a smarter way of doing it ? There is a smarter way of asking: Show us the queries! But it also depends on what you expect the user to do. Some hints: In generell if you count table A and afterwards you join and group your tables A,B,C,D the number of rows in the resultset may vary, of course. - You could fetch ALL rows with the second query, count them (pg_numrows), show the first ten results and keep all other results in cache for the next webpage. (if we are talking about a smal set of rows not if we are talking about 1 billion rows, of course) - You can rewrite your first query to return the correct number and see if it has a real performance impact. Optimize our query and you will be fine. Postgresql is very fast. - You can show the user an estimated count, if the correct number isn't of any interest (like google) - If you ever look at the CURSOR thing in postgresql and it looks attractive to you ( http://www.postgresql.org/docs/8.0/static/sql-fetch.html ): I think it isn't useful in a normal web environment, but it could be nice together with AJAX scripting. kind regards, janning ---(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] Need help: Find dirty rows, Update, Delete SQL
Am Samstag, 18. Februar 2006 18:41 schrieb [EMAIL PROTECTED]:
> Hello,
>
> I need a bit of help with some SQL.
> I have two tables, call them Page and Bookmark.
> Each row in Page can have many Bookmarks pointing to it, and
> they are joined via a FK (Page.id = Bookmark.page_id).
>
> Page has a 'url' column: Page.url, which has a unique index on it.
>
> My Page.url column got a little dirty, and I need to clean it up,
> and that's what I need help with.
>
> Here is an example of dirtiness:
>
> Page:
>
> id=1 url = 'http://example.com/'
> id=2 url = 'http://example.com/#' -- dirty
> id=3 url = 'http://example.com/#foo' -- dirty
>
> The last two rows are dirty. Normally I normalize URLs before
> inserting them, but these got in, and now I need to clean them.
>
> The problem is that rows in Bookmark table may point to dirty
> rows in Page, so I can't just remove the dirty rows, and I can't
> just update 'url' column in Page to 'http://example.com/',
> because that column is unique.
>
> Is there some fancy SQL that I can use them to find the dirty
> rows in page (... where url like '%#%') and then find rows in
> Bookmark table that point to them, then point those rows to
> good rows in Page (e.g. id=1 row above), and finally remove the
> dirty rows from Page?
try this. But please check if it really does its job. I just wrote it down in
a minute or two. There will be an easier way or nicer written SQL but a sit
is just a one time operation you shoudn't care too much. One more hint: you
should add a CHECK clause to your page_url like "page_url text NOT NULL
UNIQUE CHECK (page_url !~ '#')"
here is my test code
CREATE TABLE pages (
page_id SERIAL PRIMARY KEY,
page_url text NOT NULL UNIQUE
);
CREATE TABLE bookmarks (
bm_id SERIAL PRIMARY KEY,
bm_text text not null,
page_id int4 NOT NULL REFERENCES pages (page_id)
);
INSERT INTO pages (page_url) VALUES ('http://example.com/');
INSERT INTO pages (page_url) VALUES ('http://example.com/#');
INSERT INTO pages (page_url) VALUES ('http://example.com/#foo');
INSERT INTO pages (page_url) VALUES ('http://example2.com/#foo');
INSERT INTO pages (page_url) VALUES ('http://example3.com/#foobar');
insert into bookmarks (bm_text, page_id) values ('test1', 1);
insert into bookmarks (bm_text, page_id) values ('test2', 1);
insert into bookmarks (bm_text, page_id) values ('test3', 2);
insert into bookmarks (bm_text, page_id) values ('test4', 2);
insert into bookmarks (bm_text, page_id) values ('test5', 3);
insert into bookmarks (bm_text, page_id) values ('test6', 3);
insert into bookmarks (bm_text, page_id) values ('test7', 4);
BEGIN;
UPDATE bookmarks set page_id = pages2.page_id
FROM
pages AS pages1,
pages AS pages2
WHERE
pages1.page_id = bookmarks.page_id
AND pages2.page_url = split_part(pages1.page_url, '#', 1)
;
DELETE FROM pages WHERE page_id IN (
SELECT
pages1.page_id
FROM
pages AS pages1
JOIN pages AS pages2 ON (
pages1.page_id != pages2.page_id
AND pages2.page_url = split_part(pages1.page_url, '#', 1)
)
WHERE position('#' in pages1.page_url) > 0
AND pages1.page_id NOT IN (SELECT page_id FROM bookmarks)
);
;
UPDATE pages SET page_url = split_part(page_url, '#', 1)
WHERE position('#' in pages.page_url) > 0
;
select * from bookmarks;
select * from pages;
COMMIT;
kind regards,
janning
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Update in all tables
Am Mittwoch, 22. Februar 2006 19:08 schrieb Judith: >Hello everybody I need to update a field with the same value in the > tables of my data base but this field exists in almost all tables and > has the same value, I don't want to code a script, so my question is if > there is some way to update that field with a query and affects all the > tables that contain the field? no. but you can do with a trigger ON UPDATE but what is so evil about a script like this: #!/bin/sh TABLES="A B C D" for TAB in $TABLES do psql -c "UPDATE $TAB set field = 'new' where field = 'old'" mydbname; done ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Bitfields always atomic? Other way to store attributes?
Am Sonntag, 26. März 2006 23:47 schrieb Bryce Nesbitt:
> Dear List;
>
> If I have two threads modifying the same "bit" field:
> thread1=> update table set bf=bf | '01000'
> thread2=> update table set bf=bf | '1'
> Will this operation always be safe (e.g. result in bf='11000')?
yes, Thats what "ACID" (http://en.wikipedia.org/wiki/ACID) is all about.
> Or must
> I wrap things in
> explicit transactions?
every statement is in it's own transaction as long as you dont start one by
yourself.
> My application is to give attributes to an address table. But maybe
> there is a better way?
>
> I want to mark each addresses with attributes, e.g. the person may be a
> "friend", "on my holiday card list", "owe me money", be an "employee", a
> "volunteer on the xxx project", or none of the above.
>
> I could assign each role a bit.
>
> Or, create a string field: "Friend,Money, Emp,VolXXX".
>
> Or, create related tables:
> friend_pk, address_id
> cardlist_pk, address_id
> money_pk,address_id,amount_owed
> volunteer_pk,address_id
>
> Any thoughts?
create a table with attributes and a table with addresse "address" and then
link them via a third table address_addressattributes, something like this:
create table address (
add_id serial not null primary key,
add_name text not null,
add_street ...
...
);
create table addressattributes (
aa_id serial not null primary key,
aa_name text not null unique
);
insert into address_attributes (aa_name) values ('Friend');
insert into address_attributes (aa_name) values ('Money');
create table address_addressattributes (
add_aa_id serial primary key,
aa_id int4 not null references address_attributes (aa_id),
add_id int4 not null references address (add_id)
)
this is called a many-to-many relation.
kind regards,
janning
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
