Re: [SQL] Few Queries
Hello Richard, I'm really thankful to you. It worked. Somehow I wasn't getting it to work. This line did the trick. set_time := current_date() - (var_history_age_limit::text || '' days '')::interval; Regards, -Sugandha - Original Message - From: "Richard Huxton" <[EMAIL PROTECTED]> To: "Sugandha Shah" <[EMAIL PROTECTED]>; "Janning Vygen" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, August 14, 2002 7:04 PM Subject: Re: [SQL] Few Queries On Wednesday 14 Aug 2002 1:29 pm, Sugandha Shah wrote: > Hi , > > No luck . Even with Select Into . Please if any body has faced similar > problem and knows a solution. You don't need select into here - the sample below deletes everything older than one day. You should be able to adapt it to your needs. Your example had a + rather than a - which would delete things in the future, that might have been what you intended, or it might not. The RAISE NOTICE line prints a useful debug value. I like to scatter these around while I'm testing. CREATE FUNCTION del_old_history() RETURNS int4 AS ' DECLARE var_history_age_limit int4; set_timetimestamp; BEGIN var_history_age_limit:=1; set_time := current_date() - (var_history_age_limit::text || '' days '')::interval; RAISE NOTICE ''set time = %'',set_time; delete from history where complete_time <= set_time; return var_history_age_limit; END;' LANGUAGE 'plpgsql'; -- Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Ordering with GROUPs
From: "Tom Lane" <[EMAIL PROTECTED]> > ISTM the problem here is the lack of any ordering operator for POINT, > which defeats GROUP BY, *plus* the lack of any aggregate you might use > for an aggregate-based solution. This is not really a language failing > but a problem with an impoverished datatype. Yes, I agree completely. If I were doing this again from scratch I'd be using PostGIS, but I've got a lot of data that depends on POINT. > So, if you don't like > Bruno's subselect-based workaround, the dummy aggregate seems the way > to go. I've actually implemented the dummy aggregate now, and it works fine. So does Bruno's subselect (thank you Bruno), and the efficiency seems to be similar in each case. > SQL99 contains a whole bunch of verbiage whose intent seems to be that > if you GROUP BY a unique or primary-key column, you can reference the > other columns of that table without aggregation (essentially, the > system treats them as implicitly GROUP BY'd). Sooner or later we'll > probably get around to implementing that, and that would solve your > problem as long as you declare location.ident properly. That makes a lot of sense, though I imagine there are higher priorities. Thanks for your help. Julian Scarfe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] performance comparison: DISTINCT and GROUP BY
Hi, We have two different queries: SELECT name,surname FROM my_table GROUP BY name; and SELECT DISTINCT on(name) name,surname * FROM my_table; which gives us the same result. Do these queries differ by their performances? I mean, which one works faster? DISTINCT or GROUP BY? Best regards. -- Devrim GUNDUZ [EMAIL PROTECTED] Tel : (312) 295 9318 [EMAIL PROTECTED] Faks : (312) 295 9494 Web : http://devrim.oper.metu.edu.tr - ---(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] performance comparison: DISTINCT and GROUP BY
Hi Devrim, You can use the EXPLAIN ANALYZE syntax to find out quite easily. Chris - Original Message - From: "Devrim GUNDUZ" <[EMAIL PROTECTED]> To: "PostgreSQL Mailing Lists-SQL" <[EMAIL PROTECTED]> Sent: Monday, August 19, 2002 7:22 PM Subject: [SQL] performance comparison: DISTINCT and GROUP BY > > Hi, > > We have two different queries: > > SELECT name,surname FROM my_table GROUP BY name; > > and > > SELECT DISTINCT on(name) name,surname * FROM my_table; > > which gives us the same result. > > Do these queries differ by their performances? I mean, which one works > faster? DISTINCT or GROUP BY? > > Best regards. > > -- > > Devrim GUNDUZ > > [EMAIL PROTECTED] Tel : (312) 295 9318 > [EMAIL PROTECTED] Faks : (312) 295 9494 > > Web : http://devrim.oper.metu.edu.tr > - > > > ---(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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] recursive function returning "setof"
Hello all, I'd like to create a recursive function returning a "setof". See the following situation: CREATE TABLE sections( is serial, data text, contained_in int NOT NULL REFERENCES sections(id) DEFERRABLE ); INSERT INTO sections VALUES(0,'ROOTSECTION',0) I have triggers, that prevent loops and so on, so these "sections" form a tree. Now I want - for a given section - to define a function, that finds the *SETOF* all "ancestor"sections up to the "ROOTSECTION". That would need something recursive. The problem is: A SQL-Function cannot be recursive because it cannot call itself, and it can perform no loops. A PLPGSQL-Function cannot return sets. I know I can do it using a temporary table, but I don't like that for various reasons: - I don't want complicated handling of the temp table's name in case of simultaneous calls - The webserver, that operates on the DB shouldn't have "CREATE TABLE" "DROP TABLE" or "DELETE" permissions - It's not the natural, at least no "clean" solution Yes - I thought about returning a refcursor, but it seemed not to help. A cursor can only reference one static query. I've been reading the manuals for quite a time now - no way! but I'm sure I can't be the first one with that kind of a problem, so ...? Greetings Fritz ---(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] recursive function returning "setof"
> The problem is: > A SQL-Function cannot be recursive because it cannot call itself, and it can > perform no loops. > A PLPGSQL-Function cannot return sets. It can perform loops. Check the manual- you can do FOR and WHILE loops. 7.3 will be able to return sets from PLPGSQL funcs it seems. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] need assistance with multi-row matching expression
Hello, I'm using Postgres 7.1 and have been enjoying the upgraded "sub-select" support in this release versus older versions. At the moment, I'm stuck with a SQL issue that I haven't run into before. I need to select the data for all the "parks" that match some search criteria. The parks are stored in a "parks" table with a park_id as a primary key. Part of the search includes the logic of "match parks that include all these features". The "features" are stored in their own table, and are related to the parks table with a park_feature_map table, which contains a park_id column and a feature_id column. A user can use 0 to N to features, and each park might have 0 to N entries in the park_feature_map table. Where I'm stuck is that I'm used to putting together SQL statements to match a given row. This is different-- to create a successful match for a park_id, I need to check to match against N rows, where N is the number of feature_ids provided. How do I do that? Can I do it in one query? Thanks! -mark http://mark.stosberg.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] recursive function returning "setof"
Thanx Chris, but I can't find it. My Programmer's Guide (.../pgsql/doc/html/xfunc-sql.html) says "12.2. Query Language (SQL) Functions SQL functions execute an arbitrary list of SQL statements," Nothing about control structures in SQL functions additional to the normal command-line syntax. Where can I find it ? and (.../pgsql/doc/html/plpgsql.html#PLPGSQL-ADVANTAGES) "23.1.1.1. Better Performance SQL is the language PostgreSQL (and most other Relational Databases) use as query language. It's portable and easy to learn. But every SQL statement must be executed individually by the database server. That means that your client application must send each query to the database server, wait for it to process it, receive the results, do some computation, then send other queries to the server." Isn't it just the thing in plpgsql to add control structures to SQL ? Fritz Christopher Kings-Lynne wrote: >>The problem is: >>A SQL-Function cannot be recursive because it cannot call itself, and it > > can > >>perform no loops. >>A PLPGSQL-Function cannot return sets. > > > It can perform loops. Check the manual- you can do FOR and WHILE loops. > 7.3 will be able to return sets from PLPGSQL funcs it seems. > > Chris > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(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] recursive function returning "setof"
http://www3.us.postgresql.org/users-lounge/docs/7.2/postgres/plpgsql-control -structures.html Chris - Original Message - From: "Fritz Lehmann-Grube" <[EMAIL PROTECTED]> To: "Christopher Kings-Lynne" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, August 19, 2002 11:22 PM Subject: Re: [SQL] recursive function returning "setof" > > Thanx Chris, > > but I can't find it. > My Programmer's Guide (.../pgsql/doc/html/xfunc-sql.html) says > >"12.2. Query Language (SQL) Functions > SQL functions execute an arbitrary list of SQL statements," > > Nothing about control structures in SQL functions additional to the normal > command-line syntax. Where can I find it ? > > and (.../pgsql/doc/html/plpgsql.html#PLPGSQL-ADVANTAGES) > >"23.1.1.1. Better Performance >SQL is the language PostgreSQL (and most other Relational Databases) use as > query language. It's portable and easy to learn. But every SQL statement > must be executed individually by the database server. > > That means that your client application must send each query to the database > server, wait for it to process it, receive the results, do some computation, > then send other queries to the server." > > Isn't it just the thing in plpgsql to add control structures to SQL ? > > Fritz > > Christopher Kings-Lynne wrote: > >>The problem is: > >>A SQL-Function cannot be recursive because it cannot call itself, and it > > > > can > > > >>perform no loops. > >>A PLPGSQL-Function cannot return sets. > > > > > > It can perform loops. Check the manual- you can do FOR and WHILE loops. > > 7.3 will be able to return sets from PLPGSQL funcs it seems. > > > > Chris > > > > > > > > ---(end of broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > ---(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] sql subqueries problem
Hi
I have my accounting in a database, and I have a problem with subqueries,
here is what I have :
SELECT f.numero,
f.id_client,
f.date_creation,
(f.date_creation + (f.echeance_paiement||' days')::interval)::date
AS echeance,
f.montant_ttc,
ROUND(CASE WHEN remise IS NULL THEN 0 ELSE remise END - CASE WHEN
facture IS NULL THEN 0 ELSE facture END,2) AS solde,
CASE WHEN (f.date_creation + (f.echeance_paiement||'
days')::interval)::date < 'now'::date
THEN round(f.montant_ttc * 10 / 100 * ('now'::date -
(f.date_creation + (f.echeance_paiement||' days')::interval)::date)::int /
365, 2)
ELSE NULL
END AS penalite
FROM facture AS f
JOIN (SELECT ff.id_client,
SUM(ff.montant_ttc / df.taux) AS facture
FROM facture AS ff
JOIN devise AS df USING (id_devise)
GROUP BY ff.id_client
) AS fff USING (id_client)
LEFT OUTER JOIN (SELECT rr.id_client,
SUM(rr.montant / dr.taux) AS remise
FROM remise AS rr
JOIN devise AS dr USING (id_devise)
GROUP BY rr.id_client
) AS rrr USING (id_client)
WHERE ROUND(CASE WHEN remise IS NULL THEN 0 ELSE remise END - CASE WHEN
facture IS NULL THEN 0 ELSE facture END,2) < 0
GROUP BY f.numero, f.date_creation, f.date_creation +
(f.echeance_paiement||' days')::interval, f.id_client, f.montant_ttc,
rrr.remise, fff.facture
ORDER BY f.id_client, f.numero
Table "facture"
Column | Type
---+---
id_facture| integer
date_creation | date
date_modif| date
echeance_paiement | integer
id_client | integer
id_devise | integer
genere| integer
montant_ht| double precision
montant_tva | double precision
montant_ttc | double precision
solde_anterieur | double precision
total_a_payer | double precision
numero| character varying(15)
ref | character varying(60)
responsable | character varying(60)
contact | character varying(60)
num_tva | character varying(60)
adresse | text
pied | text
commentaire | text
email | text
Table "remise"
Column | Type
+--
id_remise | integer
date_paiement | date
date_remise| date
id_client | integer
id_type_remise | integer
id_devise | integer
id_banque | integer
montant| double precision
commentaire| text
Table "devise"
Column | Type
---+---
id_devise | integer
taux | double precision
devise| character varying(30)
symbole | character varying(15)
It finds the invoices (facture) from my customers who forgot to pay me.
but, the probem is that it gives me all the invoices and not only the ones
which are not paid, so, I wanted to add something like :
WHERE ff.date_creation <= f.date_creation
in the first subselect, and
WHERE rr.date_paiement <= f.date_creation
in the second subselect, but I can't because postgresql does not seem to be
able to do it. Any idea ?
--
Mathieu Arnold
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Modify column type
Is there a way to modify the type of an existing column? All I actually want to do is extend the length of the type from a VARCHAR(6) to VARCHAR(100). Column | Type | Modifiers +---+ email | character varying(6) | not null gold_program=> ALTER TABLE student MODIFY email varchar(100); ERROR: parser: parse error at or near "modify" ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] sql subqueries problem
On Mon, 19 Aug 2002, Mathieu Arnold wrote:
> Hi
>
> I have my accounting in a database, and I have a problem with subqueries,
> here is what I have :
>
>
>
> SELECT f.numero,
> f.id_client,
> f.date_creation,
> (f.date_creation + (f.echeance_paiement||' days')::interval)::date
> AS echeance,
> f.montant_ttc,
> ROUND(CASE WHEN remise IS NULL THEN 0 ELSE remise END - CASE WHEN
> facture IS NULL THEN 0 ELSE facture END,2) AS solde,
> CASE WHEN (f.date_creation + (f.echeance_paiement||'
> days')::interval)::date < 'now'::date
>THEN round(f.montant_ttc * 10 / 100 * ('now'::date -
> (f.date_creation + (f.echeance_paiement||' days')::interval)::date)::int /
> 365, 2)
>ELSE NULL
> END AS penalite
> FROM facture AS f
>JOIN (SELECT ff.id_client,
> SUM(ff.montant_ttc / df.taux) AS facture
> FROM facture AS ff
> JOIN devise AS df USING (id_devise)
> GROUP BY ff.id_client
> ) AS fff USING (id_client)
>LEFT OUTER JOIN (SELECT rr.id_client,
> SUM(rr.montant / dr.taux) AS remise
> FROM remise AS rr
>JOIN devise AS dr USING (id_devise)
> GROUP BY rr.id_client
>) AS rrr USING (id_client)
> WHERE ROUND(CASE WHEN remise IS NULL THEN 0 ELSE remise END - CASE WHEN
> facture IS NULL THEN 0 ELSE facture END,2) < 0
> GROUP BY f.numero, f.date_creation, f.date_creation +
> (f.echeance_paiement||' days')::interval, f.id_client, f.montant_ttc,
> rrr.remise, fff.facture
> ORDER BY f.id_client, f.numero
>
> Table "facture"
> Column | Type
> ---+---
> id_facture| integer
> date_creation | date
> date_modif| date
> echeance_paiement | integer
> id_client | integer
> id_devise | integer
> genere| integer
> montant_ht| double precision
> montant_tva | double precision
> montant_ttc | double precision
> solde_anterieur | double precision
> total_a_payer | double precision
> numero| character varying(15)
> ref | character varying(60)
> responsable | character varying(60)
> contact | character varying(60)
> num_tva | character varying(60)
> adresse | text
> pied | text
> commentaire | text
> email | text
> Table "remise"
> Column | Type
> +--
> id_remise | integer
> date_paiement | date
> date_remise| date
> id_client | integer
> id_type_remise | integer
> id_devise | integer
> id_banque | integer
> montant| double precision
> commentaire| text
> Table "devise"
> Column | Type
> ---+---
> id_devise | integer
> taux | double precision
> devise| character varying(30)
> symbole | character varying(15)
>
> It finds the invoices (facture) from my customers who forgot to pay me.
> but, the probem is that it gives me all the invoices and not only the ones
> which are not paid, so, I wanted to add something like :
> WHERE ff.date_creation <= f.date_creation
> in the first subselect, and
> WHERE rr.date_paiement <= f.date_creation
> in the second subselect, but I can't because postgresql does not seem to be
> able to do it. Any idea ?
I don't think f is in scope on those subqueries.
Can you put the clauses on the outer where or as part of the
join conditions?
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] need assistance with multi-row matching expression
Mark- This may not be the best way, but I couldn't resist taking a shot at it... If I understand correctly, your user selects 0-n features, so you are essentially querying against park_feature_map with your known parameters being the number of features and a feature_id list. suppose your park_feature_map was created like this: create table pfm (pid integer, fid integer); If a user wants all of the parks with features 1,2 & 3 then the feature list is (1,2,3) and the number of features is 3. I think this select would work: select case when count(pid) = 3 then pid end from pfm where fid in (1,2,3) group by pid or to illustrate the query better, you could use this: select pid, case when count(pid) = 3 then 'yes' else 'no' end from pfm where fid in (1,2,3) group by pid; It seems like you might also want to rank matches, so you could also do: select pid, count(pid) from pfm where fid in (1,2,3) group by pid order by count(pid) desc; The last one doesn't pinpoint matches, but might end up making a better user interface. You could combine the two to only list parks with at least N-1 matches like so: select case when count(pid) > (3-1) then pid end from pfm where fid in (1,2,3) group by pid order by count(pid) desc; -Nick -- Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Mark Stosberg > Sent: Monday, August 19, 2002 10:21 AM > To: [EMAIL PROTECTED] > Subject: [SQL] need assistance with multi-row matching expression > > > > Hello, > > I'm using Postgres 7.1 and have been enjoying the upgraded "sub-select" > support in this release versus older versions. > > At the moment, I'm stuck with a SQL issue that I haven't run into > before. > > I need to select the data for all the "parks" that match some search > criteria. The parks are stored in a "parks" table with a park_id as a > primary key. > > Part of the search includes the logic of "match parks that include all > these features". The "features" are stored in their own table, and are > related to the parks table with a park_feature_map table, which contains > a park_id column and a feature_id column. > > A user can use 0 to N to features, and each park might have 0 to N > entries in the park_feature_map table. > > Where I'm stuck is that I'm used to putting together SQL statements to > match a given row. This is different-- to create a successful match for > a park_id, I need to check to match against N rows, where N is the > number of feature_ids provided. > > How do I do that? Can I do it in one query? > > Thanks! > > -mark > > http://mark.stosberg.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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] need assistance with multi-row matching expression
On Mon, 19 Aug 2002, Nick Fankhauser wrote: > > This may not be the best way, but I couldn't resist taking a shot at it... Thanks for the response Nick. If only I knew I was going to get a response from a block away, I would have just come down to say hi. :) I had an "a ha" moment about this over lunch. I was making the problem much harder than it needed to me, having assured myself I was going to need some advanced SQL feature to solve the problem. Some testing seems to reveal that I can address this problem simply by joining against the park_feature_map table N times. This way I only need to match against 1 row each of these tables, which is easy in SQL. Here's my statement I tested with for N=2: SELECT p.park_id, park_name FROM parks p JOIN park_feature_map map_4 ON (p.park_id = map_4.park_id AND map_4.feature_id=4) JOIN park_feature_map map_15 ON (p.park_id = map_15.park_id AND map_15.feature_id=15); In this way, I'm only returned the parks that match all the features. Thanks again for your help! -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
Re: [ADMIN] [SQL] Urgent - SQL Unique constraint error (long)
Stephen, I preemptivelty sensed your reply ;-) I took another look at the ERD and quickly revised phone, address, and email so they all reference the person without having to become intertwined with their extended "class". Ultimately this should prove more flexible as it will allow persons to shift from class to class if their roles change. Darrin Inheritance would have made things so much easier . -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Stephan Szabo Sent: August 19, 2002 1:56 PM To: Darrin Domoney Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [ADMIN] [SQL] Urgent - SQL Unique constraint error (long) On Mon, 19 Aug 2002, Darrin Domoney wrote: > Thanks for the response but the answer is no. Owing to the > ongoing issue with inherited tables in 7.x I have opted to create three > tables: > Contains generic traits regardless of "class or role". > Person -> PK person_id > > Staff as certain "class" of person. > Staff -> PK staff_id > FK person_id > > Contact as another "class" of person. > Contact -> PK contact_id >FK person_id > > Phone numbers relate to any "class" but are related back to > the originator by using "person_id". I now see what you're doing, but it won't work. ALTER TABLE phone_number ADD CONSTRAINT staff_phone FOREIGN KEY ( person_id ) REFERENCES staff ( person_id ) NOT DEFERRABLE; ALTER TABLE phone_number ADD CONSTRAINT contact_phone_number FOREIGN KEY ( person_id ) REFERENCES contact ( person_id ) NOT DEFERRABLE; means that the person_id in phone number must be in *both* contact and staff. Are there classes of person that you don't want phone_number to be able to reference? If not, you should be referencing person(person_id). If so, I'm not sure I have an answer for you apart from hacking triggers since even if inheritance worked, it wouldn't really help you there. ---(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] how to refer to tables in another database( or schema as oracle refersto)
Hello, I'm still new here and new to PostgreSQL, I'd like to know that after I have created two databases on my site, such as one is called backend, and another one is called admin, how do I refer(query) the table from backend while I'm connected to admin database, or is it possible to do that in PostgreSQL? any answer is appreciated. JJW. 8/19/2002 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Urgent - SQL Unique constraint error (long)
An admitted newbie to postgresql I am trying to commit a new design my development server using pgAdminII. Everything appears to work OK but I am having real grief with my SQL generating errors - most of which I have cleared myself but one that I am unsure how to handle: UNIQUE constraint for matching given keys for referenced table "staff" not found Below is the SQL code that I am tring to load to build out my database skeleton: CREATE TABLE person ( person_id serial NOT NULL, fname text NOT NULL, lname text NOT NULL, aka_name text, PRIMARY KEY (person_id) ); CREATE TABLE phone_number ( phone_number_id serial NOT NULL, person_id int NOT NULL, phone_type_id int NOT NULL, area_code varchar(3), phone_number varchar(7) NOT NULL, phone_extension varchar(4), PRIMARY KEY (phone_number_id) ); CREATE TABLE phone_type ( phone_type_id serial NOT NULL, phone_type_desc text NOT NULL, PRIMARY KEY (phone_type_id) ); CREATE TABLE address ( address_id serial NOT NULL, address_type_id int NOT NULL, person_id int NOT NULL, address1 text, address2 text, address3 text, post_code varchar(10), city_id int, province_id int, country_id int, PRIMARY KEY (address_id) ); CREATE TABLE city ( city_id serial NOT NULL, city_name text NOT NULL, PRIMARY KEY (city_id) ); CREATE TABLE address_type ( address_type_id serial NOT NULL, address_type_desc text NOT NULL, PRIMARY KEY (address_type_id) ); CREATE TABLE province ( province_id serial NOT NULL, province varchar(2) NOT NULL, PRIMARY KEY (province_id) ); CREATE TABLE country ( country_id serial NOT NULL, country text NOT NULL, PRIMARY KEY (country_id) ); CREATE TABLE email ( email_id serial NOT NULL, email_type_id int NOT NULL, person_id int NOT NULL, email text NOT NULL, PRIMARY KEY (email_id) ); CREATE TABLE email_type ( email_type_id serial NOT NULL, email_type text NOT NULL, PRIMARY KEY (email_type_id) ); CREATE TABLE skills ( staff_id int NOT NULL, skill_type_id int NOT NULL, PRIMARY KEY (staff_id,skill_type_id) ); CREATE TABLE skills_type ( skills_type_id serial NOT NULL, skill_desc text NOT NULL, PRIMARY KEY (skills_type_id) ); CREATE TABLE leave ( leave_id serial NOT NULL, staff_id int NOT NULL, leave_type_id int NOT NULL, date_from date NOT NULL, date_to date NOT NULL, time_from time NOT NULL, time_to time NOT NULL, PRIMARY KEY (leave_id) ); CREATE TABLE leave_type ( leave_type_id serial NOT NULL, leave_type text NOT NULL, PRIMARY KEY (leave_type_id) ); CREATE TABLE event ( event_id serial NOT NULL, staff_id int NOT NULL, client_id int NOT NULL, requestor_id int NOT NULL, assign_type_id int NOT NULL, assign_subtype_id int, requested_date date NOT NULL, requested_start time NOT NULL, requested_end time NOT NULL, location text NOT NULL, notes text, event_status_id int NOT NULL, probono boolean, sys_date timestamp NOT NULL, PRIMARY KEY (event_id) ); CREATE TABLE organization ( organization_id serial NOT NULL, org_type_id int NOT NULL, organization_name text NOT NULL, department text, short_name text NOT NULL, PRIMARY KEY (organization_id) ); CREATE TABLE staff ( staff_id serial NOT NULL, person_id int NOT NULL, active_staff boolean NOT NULL, pay_rate decimal(8,2), discounted_rate decimal(8,2), discount_break int, organization_id int NOT NULL, PRIMARY KEY (staff_id) ); CREATE TABLE contact ( contact_id serial NOT NULL, person_id int NOT NULL, organization_id int, client boolean NOT NULL, PRIMARY KEY (contact_id) ); CREATE TABLE assignment_type ( assign_type_id serial NOT NULL, assign_type_desc text NOT NULL, PRIMARY KEY (assign_type_id) ); CREATE TABLE assignment_subtype ( assign_subtype_id serial NOT NULL, assign_subtype_desc text NOT NULL, PRIMARY KEY (assign_subtype_id) ); CREATE TABLE resource ( resource_id serial NOT NULL, event_id int NOT NULL, requested_resource_type_id int NOT NULL, assigned_resource_id int, scheduled_date date, scheduled_start time, scheduled_end time, actual_start time, actual_end time, PRIMARY KEY (resource_id) ); CREATE TABLE event_status ( event_status_id serial NOT NULL, event_status_desc text NOT NULL, PRIMARY KEY (event_status_id) ); CREATE TABLE organization_type ( org_type_id serial NOT NULL, org_type_desc text NOT NULL, PRIMARY KEY (org_type_id) ); CREATE TABLE event_replication ( trigger_id int NOT NULL, result_event_id int NOT NULL, replication_id serial NOT NULL, PRIMARY KEY (replication_id) ); -- +- -- | FOREIGN KEYS -- +- ALTER TABLE phone_number ADD CONSTRAINT staff_phone FOREIGN KEY ( person_id ) REFERENCES staff ( person_id ) NOT DEFERRABLE; ALTER TABLE phone_number ADD CONSTRAINT contact_phone_number FOREIGN KEY ( person_id ) REFERENCES contact ( person_id ) NOT
Re: [SQL] Urgent - SQL Unique constraint error (long)
On Mon, 19 Aug 2002, Darrin Domoney wrote: > An admitted newbie to postgresql I am trying to commit a new design > my development server using pgAdminII. > > Everything appears to work OK but I am having real grief with my > SQL generating errors - most of which I have cleared myself but > one that I am unsure how to handle: > > UNIQUE constraint for matching given keys for referenced table "staff" > not found > > Below is the SQL code that I am tring to load to build out my database > skeleton: > > CREATE TABLE staff > ( > staff_id serial NOT NULL, > person_id int NOT NULL, > active_staff boolean NOT NULL, > pay_rate decimal(8,2), > discounted_rate decimal(8,2), > discount_break int, > organization_id int NOT NULL, > PRIMARY KEY (staff_id) > ); > ALTER TABLE phone_number ADD CONSTRAINT staff_phone > FOREIGN KEY ( person_id ) >REFERENCES staff ( person_id ) > NOT DEFERRABLE; The target of a references constraint must be in a unique constraint. Here you're referencing person_id which is not the key of staff. Are you sure you don't want to be linking staff_id instead? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Urgent - SQL Unique constraint error (long)
Stephan, Thanks for the response but the answer is no. Owing to the ongoing issue with inherited tables in 7.x I have opted to create three tables: Contains generic traits regardless of "class or role". Person -> PK person_id Staff as certain "class" of person. Staff -> PK staff_id FK person_id Contact as another "class" of person. Contact -> PK contact_id FK person_id Phone numbers relate to any "class" but are related back to the originator by using "person_id". Darrin -Original Message- From: Stephan Szabo [mailto:[EMAIL PROTECTED]] Sent: August 19, 2002 12:41 PM To: Darrin Domoney Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [SQL] Urgent - SQL Unique constraint error (long) On Mon, 19 Aug 2002, Darrin Domoney wrote: > An admitted newbie to postgresql I am trying to commit a new design > my development server using pgAdminII. > > Everything appears to work OK but I am having real grief with my > SQL generating errors - most of which I have cleared myself but > one that I am unsure how to handle: > > UNIQUE constraint for matching given keys for referenced table "staff" > not found > > Below is the SQL code that I am tring to load to build out my database > skeleton: > > CREATE TABLE staff > ( > staff_id serial NOT NULL, > person_id int NOT NULL, > active_staff boolean NOT NULL, > pay_rate decimal(8,2), > discounted_rate decimal(8,2), > discount_break int, > organization_id int NOT NULL, > PRIMARY KEY (staff_id) > ); > ALTER TABLE phone_number ADD CONSTRAINT staff_phone > FOREIGN KEY ( person_id ) >REFERENCES staff ( person_id ) > NOT DEFERRABLE; The target of a references constraint must be in a unique constraint. Here you're referencing person_id which is not the key of staff. Are you sure you don't want to be linking staff_id instead? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [NOVICE] Urgent - SQL Unique constraint error (long)
Paul, See my earlier comments relating to the reasons behind the structure for the database. I know this is an issue but if inheritance is "not working" it seems like my only option. Darrin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of paul butler Sent: August 19, 2002 1:11 PM To: [EMAIL PROTECTED] Subject: Re: [NOVICE] Urgent - SQL Unique constraint error (long) From: "Darrin Domoney" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> Subject:[NOVICE] Urgent - SQL Unique constraint error (long) Date sent: Mon, 19 Aug 2002 12:34:53 -0500 This is just a guess but the message indicates that you're not referencing another key, or unique value, so it can't really tell which tuple it should be referencing. Without wanting to offend, It looks like a design problem. To the immediate question I would suggest merging the person and staff tables, make the person_id (you've got an awful lot of these artificial keys there, but I suppose that is a matter of taste) the primary key and dump the staff _id, and reference that. either that or reference person (person_id) UNIQUE constraint for matching given keys for referenced table "staff" not found Below is the SQL code that I am tring to load to build out my database skeleton: CREATE TABLE person ( person_id serial NOT NULL, fname text NOT NULL, lname text NOT NULL, aka_name text, PRIMARY KEY (person_id) ); CREATE TABLE phone_number ( phone_number_id serial NOT NULL, person_id int NOT NULL, phone_type_id int NOT NULL, area_code varchar(3), phone_number varchar(7) NOT NULL, phone_extension varchar(4), PRIMARY KEY (phone_number_id) ); CREATE TABLE phone_type ( phone_type_id serial NOT NULL, phone_type_desc text NOT NULL, PRIMARY KEY (phone_type_id) ); CREATE TABLE address ( address_id serial NOT NULL, address_type_id int NOT NULL, person_id int NOT NULL, address1 text, address2 text, address3 text, post_code varchar(10), city_id int, province_id int, country_id int, PRIMARY KEY (address_id) ); CREATE TABLE city ( city_id serial NOT NULL, city_name text NOT NULL, PRIMARY KEY (city_id) ); CREATE TABLE address_type ( address_type_id serial NOT NULL, address_type_desc text NOT NULL, PRIMARY KEY (address_type_id) ); CREATE TABLE province ( province_id serial NOT NULL, province varchar(2) NOT NULL, PRIMARY KEY (province_id) ); CREATE TABLE country ( country_id serial NOT NULL, country text NOT NULL, PRIMARY KEY (country_id) ); CREATE TABLE email ( email_id serial NOT NULL, email_type_id int NOT NULL, person_id int NOT NULL, email text NOT NULL, PRIMARY KEY (email_id) ); CREATE TABLE email_type ( email_type_id serial NOT NULL, email_type text NOT NULL, PRIMARY KEY (email_type_id) ); CREATE TABLE skills ( staff_id int NOT NULL, skill_type_id int NOT NULL, PRIMARY KEY (staff_id,skill_type_id) ); CREATE TABLE skills_type ( skills_type_id serial NOT NULL, skill_desc text NOT NULL, PRIMARY KEY (skills_type_id) ); CREATE TABLE leave ( leave_id serial NOT NULL, staff_id int NOT NULL, leave_type_id int NOT NULL, date_from date NOT NULL, date_to date NOT NULL, time_from time NOT NULL, time_to time NOT NULL, PRIMARY KEY (leave_id) ); CREATE TABLE leave_type ( leave_type_id serial NOT NULL, leave_type text NOT NULL, PRIMARY KEY (leave_type_id) ); CREATE TABLE event ( event_id serial NOT NULL, staff_id int NOT NULL, client_id int NOT NULL, requestor_id int NOT NULL, assign_type_id int NOT NULL, assign_subtype_id int, requested_date date NOT NULL, requested_start time NOT NULL, requested_end time NOT NULL, location text NOT NULL, notes text, event_status_id int NOT NULL, probono boolean, sys_date timestamp NOT NULL, PRIMARY KEY (event_id) ); CREATE TABLE organization ( organization_id serial NOT NULL, org_type_id int NOT NULL, organization_name text NOT NULL, department text, short_name text NOT NULL, PRIMARY KEY (organization_id) ); CREATE TABLE staff ( staff_id serial NOT NULL, person_id int NOT NULL, active_staff boolean NOT NULL, pay_rate decimal(8,2), discounted_rate decimal(8,2), discount_break int, organization_id int NOT NULL, PRIMARY KEY (staff_id) ); CREATE TABLE contact ( contact_id serial NOT NULL, person_id int NOT NULL, organization_id int, client boolean NOT NULL, PRIMARY KEY (contact_id) ); CREATE TABLE assignment_type ( assign_type_id serial NOT NULL, assign_type_desc text NOT NULL, PRIMARY KEY (assign_type_id) ); CREATE TABLE assignment_subtype ( assign_subtype_id serial NOT NULL, assign_subtype_desc text NOT NULL, PRIMARY KEY (assign_subtype_id) ); CREATE TABLE resource ( resource_id serial NOT NULL, event_id int NOT NULL, requested_resource_type_id int NOT NULL, assigned_resource_id int,
Re: [SQL] Urgent - SQL Unique constraint error (long)
On Mon, 19 Aug 2002, Darrin Domoney wrote: > Thanks for the response but the answer is no. Owing to the > ongoing issue with inherited tables in 7.x I have opted to create three > tables: > Contains generic traits regardless of "class or role". > Person -> PK person_id > > Staff as certain "class" of person. > Staff -> PK staff_id > FK person_id > > Contact as another "class" of person. > Contact -> PK contact_id >FK person_id > > Phone numbers relate to any "class" but are related back to > the originator by using "person_id". I now see what you're doing, but it won't work. ALTER TABLE phone_number ADD CONSTRAINT staff_phone FOREIGN KEY ( person_id ) REFERENCES staff ( person_id ) NOT DEFERRABLE; ALTER TABLE phone_number ADD CONSTRAINT contact_phone_number FOREIGN KEY ( person_id ) REFERENCES contact ( person_id ) NOT DEFERRABLE; means that the person_id in phone number must be in *both* contact and staff. Are there classes of person that you don't want phone_number to be able to reference? If not, you should be referencing person(person_id). If so, I'm not sure I have an answer for you apart from hacking triggers since even if inheritance worked, it wouldn't really help you there. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] how to refer to tables in another database( or schema as oracle refers to)
Hi Jiaqing, Basically - you can't. There is a program in the contrib/dblink directory that can help you though. Regards, Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Jiaqing > Sent: Tuesday, 20 August 2002 5:53 AM > To: [EMAIL PROTECTED] > Subject: [SQL] how to refer to tables in another database( or schema as > oracle refers to) > > > Hello, > I'm still new here and new to PostgreSQL, I'd like to know that after I > have created two databases on my site, such as one is called backend, and > another one is called admin, how do I refer(query) the table from backend > while I'm connected to admin database, or is it possible to do that in > PostgreSQL? any answer is appreciated. > > JJW. > 8/19/2002 > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Modify column type
Hi Scott, I believe you can hack the catalogs: (disclaimer) update pg_attribute set atttypmod=104 where attname='email' and attrelid=(select oid from pg_class where relname='student'); Do this in a transaction and then \d the table to check that it has worked before committing. Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Scott David Walter > Sent: Tuesday, 20 August 2002 12:46 AM > To: [EMAIL PROTECTED] > Subject: [SQL] Modify column type > > > Is there a way to modify the type of an existing column? All I actually > want to do is extend the length of the type from a VARCHAR(6) to > VARCHAR(100). > > > Column | Type | Modifiers > +---+ > email | character varying(6) | not null > > > gold_program=> ALTER TABLE student MODIFY email varchar(100); > ERROR: parser: parse error at or near "modify" > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ---(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 to refer to tables in another database( or schema as oracle refers to)
> Basically - you can't. There is a program in the contrib/dblink directory > that can help you though. > My take on this dblink program is to create SQL as a view. For example coming from Oracle you might do "SELECT one, two, FROM foo.bar". Convert that to a VIEW called simply "bar" or "foo_bar" that contains your dblink query and there will be minimal chnages you'll have to make to your actual code. This could also be used to get around synonyms as well. Cheers, Graeme ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] how to refer to tables in another database( or schema as
On Mon, 19 Aug 2002, Jiaqing wrote: > Hello, > I'm still new here and new to PostgreSQL, I'd like to know that after I > have created two databases on my site, such as one is called backend, and > another one is called admin, how do I refer(query) the table from backend > while I'm connected to admin database, or is it possible to do that in > PostgreSQL? any answer is appreciated. In addition to previous answers (dblink related), in 7.3 schemas will be implemented and you may be able to use one database with two schemas in which case normal sql should work. This isn't out yet, so it's a future concern. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] sql subqueries problem
--On lundi 19 août 2002 09:45 -0700 Stephan Szabo
<[EMAIL PROTECTED]> wrote:
>
> On Mon, 19 Aug 2002, Mathieu Arnold wrote:
>
>> Hi
>>
>> I have my accounting in a database, and I have a problem with subqueries,
>> here is what I have :
>>
>>
>>
>> SELECT f.numero,
>> f.id_client,
>> f.date_creation,
>> (f.date_creation + (f.echeance_paiement||'
>> days')::interval)::date AS echeance,
>> f.montant_ttc,
>> ROUND(CASE WHEN remise IS NULL THEN 0 ELSE remise END - CASE
>> WHEN facture IS NULL THEN 0 ELSE facture END,2) AS solde,
>> CASE WHEN (f.date_creation + (f.echeance_paiement||'
>> days')::interval)::date < 'now'::date
>>THEN round(f.montant_ttc * 10 / 100 * ('now'::date -
>> (f.date_creation + (f.echeance_paiement||' days')::interval)::date)::int
>> / 365, 2)
>>ELSE NULL
>> END AS penalite
>> FROM facture AS f
>>JOIN (SELECT ff.id_client,
>> SUM(ff.montant_ttc / df.taux) AS facture
>> FROM facture AS ff
>> JOIN devise AS df USING (id_devise)
>> GROUP BY ff.id_client
>> ) AS fff USING (id_client)
>>LEFT OUTER JOIN (SELECT rr.id_client,
>> SUM(rr.montant / dr.taux) AS remise
>> FROM remise AS rr
>>JOIN devise AS dr USING (id_devise)
>> GROUP BY rr.id_client
>>) AS rrr USING (id_client)
>> WHERE ROUND(CASE WHEN remise IS NULL THEN 0 ELSE remise END - CASE WHEN
>> facture IS NULL THEN 0 ELSE facture END,2) < 0
>> GROUP BY f.numero, f.date_creation, f.date_creation +
>> (f.echeance_paiement||' days')::interval, f.id_client, f.montant_ttc,
>> rrr.remise, fff.facture
>> ORDER BY f.id_client, f.numero
>>
>> Table "facture"
>> Column | Type
>> ---+---
>> id_facture| integer
>> date_creation | date
>> date_modif| date
>> echeance_paiement | integer
>> id_client | integer
>> id_devise | integer
>> genere| integer
>> montant_ht| double precision
>> montant_tva | double precision
>> montant_ttc | double precision
>> solde_anterieur | double precision
>> total_a_payer | double precision
>> numero| character varying(15)
>> ref | character varying(60)
>> responsable | character varying(60)
>> contact | character varying(60)
>> num_tva | character varying(60)
>> adresse | text
>> pied | text
>> commentaire | text
>> email | text
>> Table "remise"
>> Column | Type
>> +--
>> id_remise | integer
>> date_paiement | date
>> date_remise| date
>> id_client | integer
>> id_type_remise | integer
>> id_devise | integer
>> id_banque | integer
>> montant| double precision
>> commentaire| text
>> Table "devise"
>> Column | Type
>> ---+---
>> id_devise | integer
>> taux | double precision
>> devise| character varying(30)
>> symbole | character varying(15)
>>
>> It finds the invoices (facture) from my customers who forgot to pay me.
>> but, the probem is that it gives me all the invoices and not only the
>> ones which are not paid, so, I wanted to add something like :
>> WHERE ff.date_creation <= f.date_creation
>> in the first subselect, and
>> WHERE rr.date_paiement <= f.date_creation
>> in the second subselect, but I can't because postgresql does not seem to
>> be able to do it. Any idea ?
>
> I don't think f is in scope on those subqueries.
> Can you put the clauses on the outer where or as part of the
> join conditions?
>
I've tried, but, as the subselect is an aggregate, I can't get it (maybe I
don't know enough about it to do it :)
--
Mathieu Arnold
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
