Re: [SQL] UNIQUE constraint
Michael Glaesemann <[EMAIL PROTECTED]> writes: > On Aug 7, 2004, at 3:25 AM, Sascha Ziemann wrote: > > CREATE TABLE example ( > > a integer, > > b integer, > > c integer, > > UNIQUE (a, c) > > ); > > > > But it is not clean to me. Does the above example mean that the list > > of pairs must be unique > > Yes. Do you know wheather this is part of the SQL standard? I have to write an application that works on Postgresql and Oracle. Is this the same on Oracle? Sorry for asking but right now I have no access to the database to test it. cu Sascha -- secunet Security Networks AG, Im Teelbruch 116, 45219 Essen Tel: +49-2054-123-408 Fax: +49-2054-123-123 PGP: FBE2 A49B 6526 C1B4 7F10 24E4 5004 7C27 6E9A 9698 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] LIKE '%%' does not return NULL
A team developer has chosen the lazy way of not checking if a variable exists on his PHP page and has code which produces the following SQL SELECT * FROM mytable where myfield ilike '%%' I have noticed that this statement does not return null or empty myfield records. Is this the way (SQL) to do this?? Is there a better syntax to the SQL?? Is the better way to create the statement in PHP is to check if the passed value <> '' ?? Or is this issue being fixed in the new V8 version of postgreSQL. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Sum and count weird results
Christoph Haller wrote: Michalis Kabrianis wrote: Hi all, I have these tables a ( id integer, email varchar); b ( seat varchar, transactionid varchar); c ( transactionid varchar, totalprice numeric(8.2)); ---(end of broadcast)--- TIP 8: explain analyze is your friend And? Oops, accidental "send button" hit. I beleived it would never reach the list. Sorry for that. Here we go again: Hi all, I have these tables a ( id integer, email varchar); b ( seat varchar, transactionid varchar references c(varchar)); c ( transactionid varchar, a_id integer references a(id), totalprice numeric(8.2)); sample data : table a 1,[EMAIL PROTECTED] 2,[EMAIL PROTECTED] table b 1,123 2,123 3,123 4,125 5,125 6,127 table c 123,1,200 125,2,100 127,1,300 What I want is to count the seatnr located on table b, and sum the totalprice located on table c, group by email located on table a. I tried something like : select sum(totalprice), count(seatnr), email from a,b,c where c.transactionid=b.transactionid and c.a_id=a.id I get correct seatnr count, but wrong (and I understand why) totalprice sum. Any good ideas on how can that be accomplished in one query? Ideal results : SUM COUNT EMAIL 4 500 [EMAIL PROTECTED] 2 100 [EMAIL PROTECTED] Thanks in advance Michalis ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] COMMENT ON CONSTRAINT
Hi,
I have a problem with the COMMENT command. Here:
http://www.postgresql.org/docs/7.4/static/sql-comment.html
it is defined in this way:
COMMENT ON
{
TABLE object_name |
COLUMN table_name.column_name |
AGGREGATE agg_name (agg_type) |
CONSTRAINT constraint_name ON table_name |
DATABASE object_name |
DOMAIN object_name |
FUNCTION func_name (arg1_type, arg2_type, ...) |
INDEX object_name |
OPERATOR op (leftoperand_type, rightoperand_type) |
RULE rule_name ON table_name |
SCHEMA object_name |
SEQUENCE object_name |
TRIGGER trigger_name ON table_name |
TYPE object_name |
VIEW object_name
} IS 'text'
I would like to comment a constraint but it doesn't work. I did the
following:
create table tab3 (
a integer,
b integer,
constraint uni unique (a, b)
);
comment on constraint uni on table tab3 is 'unique pair';
Did I do anything wrong or is this a bug?
cu Sascha
--
secunet Security Networks AG, Im Teelbruch 116, 45219 Essen
Tel: +49-2054-123-408 Fax: +49-2054-123-123
PGP: FBE2 A49B 6526 C1B4 7F10 24E4 5004 7C27 6E9A 9698
---(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] Returning A Varchar From A Function
Hi
this has been puzzling me for a few days now
I have created a function that I want to use in a database to select a
value from a table based on the value passed in.
The table is fairly simple
CREATE TABLE public.feeder_next_status
(
status varchar NOT NULL,
previous_status varchar,
next_status varchar
) WITH OIDS;
The function is defined as
-- Function: public.spgetnextstatus(varchar)
-- DROP FUNCTION public.spgetnextstatus(varchar);
CREATE OR REPLACE FUNCTION public.spgetnextstatus(varchar)
RETURNS varchar AS
'
select cast(next_status as varchar)
from feeder_next_status
where trim(status) = trim(\'$1\')
order by next_status;'
LANGUAGE 'sql' STABLE;
However when i run the query
select spgetnextstatus('NEW')
in pgadmin
the dataoutput shows two columns
the row column shows a row number of '1' and the column header
spgetnextstatus(varchar) shows blank
I have tested the equivalent sql in the pgadmin query and it works
fine.
Hoping someone can point me inthe right direction
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] duplicate table in two databases
I use postgres 7.2.1 (debian woody) and PHP. I've two databases (foo and bar for example) with the same table (ie. stamps). It's possible with a single query transfer the data from the stamps table in db foo to the stamps table in db bar? Or I need a row by row copy? Or maybe a dump on temp file? (with one db and two tables with the same record I can use INSERT INTO foo SELECT * FROM bar). Thanks. ---(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] Sending errors from psql to error file
On Thu, 2004-08-12 at 16:09, Oliver Elphick wrote: > "2>" redirects standard error I forgot to say "&1" means the file open on file descriptor 1, which is always standard output. So "2>&1" means send standard error to standard output, so that a pipe (which just takes standard output) can see the errors as well. All that you can find in the man page for bash or sh or whatever your shell is. (If you used csh or tcsh, I think the syntax would be different.) Oliver ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Performance Problem With Postgresql!
Hi All,
We are having a performance problem with our database. The problem
exists when we include a constraint in GCTBALLOT. The constraint is as
follows:
alter table GCTBALLOT
add constraint FK_GCTBALLOT_GCTWEBU foreign key (GCTWEBU_SRL)
references GCTWEBU (SRL)
on delete restrict on update restrict;
The two tables that we insert into are the following:
GCTBALLOT:
Table "cbcca.gctballot"
Column |Type |
Modifiers
--+-+---
srl | integer | not null default
nextval('cbcca.gctballot_srl_seq'::text)
gctbwindow_srl | numeric(12,0) | not null
gctcandidate_srl | numeric(12,0) | not null
gctwebu_srl | numeric(12,0) |
gctphoneu_srl| numeric(12,0) |
ballot_time | timestamp without time zone | not null
ip_addr | character varying(15) |
Indexes:
"pk_gctballot" primary key, btree (srl)
"i1_gctballot_webusrl" btree (gctwebu_srl)
Foreign-key constraints:
"fk_gctbwindow_gctballot" FOREIGN KEY (gctbwindow_srl) REFERENCES
gctbwindow(srl) ON UPDATE RESTRICT ON DELETE RESTRICT
"fk_gctcandidate_gctballot" FOREIGN KEY (gctcandidate_srl)
REFERENCES gctcandidate(srl) ON UPDATE RESTRICT ON DELETE RESTRICT
"fk_gctphoneu_gctballot" FOREIGN KEY (gctphoneu_srl) REFERENCES
gctphoneu(srl) ON UPDATE RESTRICT ON DELETE RESTRICT
with the extra constraint:
"fk_gctballot_gctwebu" FOREIGN KEY (gctwebu_srl) REFERENCES
gctwebu(srl) ON UPDATE RESTRICT ON DELETE RESTRICT
GCTWEBU:
Table "cbcca.gctwebu"
Column |Type |
Modifiers
-+-+-
srl | integer | not null default
nextval('cbcca.gctwebu_srl_seq'::text)
gctlocation_srl | numeric(12,0) | not null
gctagerange_srl | numeric(12,0) | not null
email | character varying(255) | not null
uhash | character varying(255) | not null
sex | character varying(1)| not null
created_time| timestamp without time zone | not null
Indexes:
"pk_gctwebu" primary key, btree (srl)
"i1_gctwebu_email" unique, btree (email)
Foreign-key constraints:
"fk_gctagerang_gctwebu" FOREIGN KEY (gctagerange_srl) REFERENCES
gctagerange(srl) ON UPDATE RESTRICT ON DELETE RESTRICT
"fk_gctwebu_gctlocation" FOREIGN KEY (gctlocation_srl) REFERENCES
gctlocation(srl) ON UPDATE RESTRICT ON DELETE RESTRICT
To begin, GCTBALLOT has 6122546 rows and GCTWEBU has 231444 rows.
Now when we try and insert 100 entries into GCTBALLOT with the extra
constraint it
takes: 37981 milliseconds
Also, when we try and insert 100 entries into GCTBALLOT with the extra
constraint,
but insert 'null' into the column gctwebu_srl it takes: 286
milliseconds
However when we try and insert 100 entries into GCTBALLOT without the
extra constraint (no foreign key between GCTBALLOT & GCTWEBU)
it takes: 471 milliseconds
In summary, inserting into GCTBALLOT without the constraint or
inserting null for
gctwebu_srl in GCTBALLOT gives us good performance. However, inserting
into GCTBALLOT
with the constraint and valid gctwebu_srl values gives us poor
performance.
Also, the insert we use is as follows:
INSERT INTO GCTBALLOT (gctbwindow_srl, gctcandidate_srl, gctwebu_srl,
gctphoneu_srl,
ballot_time, ip_addr) VALUES (CBCCA.gcf_getlocation(?), ?,
CBCCA.gcf_validvoter(?,?),
null, ?, ?);
NOTE: "gcf_validvoter" find 'gctweb_srl' value
"
CREATE OR REPLACE FUNCTION gcf_validvoter (VARCHAR, VARCHAR)
RETURNS NUMERIC AS '
DECLARE
arg1 ALIAS FOR $1;
arg2 ALIAS FOR $2;
return_val NUMERIC;
BEGIN
SELECT SRL INTO return_val
FROM gctwebu
WHERE EMAIL = arg1
ANDUHASH = arg2;
RETURN return_val;
END;
' LANGUAGE plpgsql;
"
Where the question marks are filled in with values in our java code.
We are puzzled as to why there is this difference in performance when
inserting b/c we
believe that we have indexed all columns used by this constraint. And
we realize that
inserting 'null' into GCTBALLOT doesn't use this constraint b/c no look
up is necessary.
So this causes good performance. Why is it that when we use this
constraint that
the performance is effected so much?
Any help would be much appreciated.
Thanks
P.S. Even we added an index on 'gctwebu_srl' column and did
1- "Analyzed ALL TABLES"
2- "analyze GCTBALLOT(gctwebu_srl);"
but still have the same problem!
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAI
[SQL] Getting points from polygon
Hello, how can I get single xy-points from a polygon like for example: CREATE TABLE "public"."country" ( "country_id" INTEGER NOT NULL, "geo" polygon NOT NULL ) SELECT geo.x, geo.y FROM country WHERE geo.x=5 or SELECT geo[24th point].x FROM country ... Thanks in advance and best regards, Sebastian ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Wierd Error on update
> Original 7.3 release, or (I hope) 7.3.something? 7.3 > > I am getting two errors which are a bit confounding. > > ERROR: pg_class_aclcheck:relation 474653086 not found > > Are there any views involved? No views. Is the statement invoking any functions? The statement is not invoking any fuctions However heres the statements, UPDATE school_info_l SET ind_default_data = 't'; UPDATE school_info_l SET sis_system_id = 9 where school_id IN (492, 434, 436); which are amazingly simple. Both queries return the same exact error, same error. not your > How about triggers? Foreign keys? Rules? Neither of the updates touch any rules. sis_system_id is a foreign key to another table (but the first isnt touching anything that has a trigger or foreign key constraint attached to the column. The table school_info_l has many contraints that affect other tables however > Also, is it always the same number in the error, or does that change > from run to run? The number is the same for each time I run the update file on the same database. with this two queries having the same error. Same result to, if I run these two after the first update file finishes there is no problem, but if I try to run it at the end of hte first file, choke. Very Odd problem to me, since the update statemenets are so simple. -Tom ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Suggestions on storing re-occurring calendar events
>> I'd like to figure out a way to add simple re-occurrances of events. I can think of two ways, [a] Using application logic, create a finite number of future occurrences --for example, for 10 occurrences, 10 entries into ftr_cal_events will be created. This seems like an ugly hack. << No, not really; go with [a]. This is SQL and it is designed for tables, not computations. For example, to pull out one I recently looked at, say I am setting out a payment plan. I generate a list of 100 payments made up of (client_id, payment_due_date, payment_due_amt, actual_payment) rows by some simple temporal math -- "give me $100 every 30 days!" Now I go to my Calendar table (lots of posting about why you need a Calendar table on Google). If a payment date falls on a holiday, I move it up to the next business day. I do not try to calculate Easter or Chinese News Years on the fly; I cannot calculate somethings like national emergencies and disasters. I can print the whole plan out for the guy; his future actual payments are defaulted zero, and I can easily recompute his future amounts due from his past payments. Other advantages: portable data instead of proprietary temporal computations. The rows in this payment schedule are pretty small, so it runs fast. You can immediately see conflicts by having all the enterprised events in the same place and in the same format. For example, summing up the expected payements for any given date is trivial with a full payment schedule on everyone. ---(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] tablefunc's in 8.0 Beta Win32 - where?
Sorry, i found it. I'm Win32 user and use installation from pgInstaller. I compile it and it run fine :-) ! - Thomas Wegner Cabrio Meter - The Weather Plugin for Trillian http://trillian.wegner24.de/cabriometer "Thomas Wegner" <[EMAIL PROTECTED]> schrieb im Newsbeitrag news:[EMAIL PROTECTED] > Whera are tablefunc's like connectby in 8.0? > - > Thomas Wegner > Cabrio Meter - The Weather Plugin for Trillian > http://trillian.wegner24.de/cabriometer > > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] tablefunc's in 8.0 Beta Win32 - where?
Whera are tablefunc's like connectby in 8.0? - Thomas Wegner Cabrio Meter - The Weather Plugin for Trillian http://trillian.wegner24.de/cabriometer ---(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] LIKE '%%' does not return NULL
On Wed, 11 Aug 2004, Traci Sumpter wrote: > A team developer has chosen the lazy way of not checking if a variable > exists on his PHP page and has code which produces the following SQL > > SELECT * FROM mytable where myfield ilike '%%' > > I have noticed that this statement does not return null or empty myfield > records. It should not return true for NULLs but should for empty fields (which it does AFAICS). If you want to get the NULLs as well you'll need to explicitly OR myfield is NULL. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] LIKE '%%' does not return NULL
Hi, you should additionally check with IS NULL: SELECT * FROM mytable where myfield like '%%' OR myfield IS NULL if you want to get fields containing NULL as well. A field containing "NULL" in fact contains nothing, not even an empty string, so you cannot catch it with "%". BTW, this holds true especially for booleans: They can contain the values "true" or "false" or no value at all, which means, they contain "NULL" and are in fact undefined. You won´t catch them with something like "SELECT * FROM bla where blubb <> false", you will only get the fields containing true, not the NULLs. Regards, Frank. On Wed, 11 Aug 2004 13:22:00 +1200 "Traci Sumpter" <[EMAIL PROTECTED]> sat down, thought long and then wrote: > A team developer has chosen the lazy way of not checking if a variable > exists on his PHP page and has code which produces the following SQL > > SELECT * FROM mytable where myfield ilike '%%' > > I have noticed that this statement does not return null or empty myfield > records. > > Is this the way (SQL) to do this?? > > Is there a better syntax to the SQL?? > > Is the better way to create the statement in PHP is to check if the passed > value <> '' ?? > > Or is this issue being fixed in the new V8 version of postgreSQL. > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Getting points from polygon
[EMAIL PROTECTED] (Sebastian Albrecht) writes: > how can I get single xy-points from a polygon like for example: [ looks through pg_proc... ] Hmm, this seems to be an oversight in the polygon datatype. I don't think it'd be real practical to support an array-subscript-like notation, but certainly a function like point(polygon, n) wouldn't be too hard to add. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] CROSS-TAB query help? I have read it cant be done in one SQL, pro ve them wrong!
Title: CROSS-TAB query help? I have read it cant be done in one SQL, prove them wrong! Does anyone know how to perform a cross-tab query in ONE SQL without having to write a SP? The SQL at the end of this email attempts to display the subquery result-set in a cross-tab format, it does not group the content onto one row as it should in the sample below. SQL is below if it makes any sense, however the sub-query returns data as below. Examle: Name Value ID 1 Cola 10 Colb 20 Colc 30 Cold 40 Cole 50 I want to output as: ID, cola, colb, colb, cold, cole 1 10 30 30 40 50 This is how it should output: content_object_id | xpos | ypos | text | textangle | texttype | symbol | linktype ---+--+--+---+---+++-- 100473 | 93 | 77 | text1 | 0 | txt-pop3 | pop1_att | optional Actual Output: content_object_id | xpos | ypos | text | textangle | texttype | symbol | linktype ---+--+--+---+---+++-- 100473 | 93 | | | | | | 100473 | | 77 | | | | | | 100473 | | | text1 | | | | 100473 | | | | 0 | | | 100473 | | | | | txt-pop3 | | 100473 | | | | | | pop1_att| SQL: select distinct mapInfo.content_object_id, CASE WHEN mapInfo.node_id = 6957 THEN mapInfo.content END as xPos, CASE WHEN mapInfo.node_id = 6958 THEN mapInfo.content END as yPos, CASE WHEN mapInfo.node_id = 6959 THEN mapInfo.content END as text, CASE WHEN mapInfo.node_id = 6960 THEN mapInfo.content END as textAngle, CASE WHEN mapInfo.node_id = 6961 THEN mapInfo.content END as textType, CASE WHEN mapInfo.node_id = 6962 THEN mapInfo.content END as symbol, CASE WHEN mapInfo.node_id = 6963 THEN mapInfo.content END as linkType from ( SELECT child_node_names.node_id, child_content_facts.content_object_id,child_node_names.node_name, CASE WHEN child_node_names.node_id = 6962 THEN (select node_name from node_names where node_id = content_fact_versions.content) WHEN child_node_names.node_id = 6961 THEN (select node_name from node_names where node_id = content_fact_versions.content) WHEN child_node_names.node_id = 6963 THEN (select node_name from node_names where node_id = content_fact_versions.content) ELSE content_fact_versions.content END FROM content_objects_join INNER JOIN content_objects as parent_content_objects ON parent_content_objects.content_object_id = content_objects_join.parent_cobj_id INNER JOIN nodes as parent_nodes ON parent_nodes.node_id = parent_content_objects.node_id INNER JOIN node_names as parent_node_names ON parent_node_names.node_id = parent_nodes.node_id INNER JOIN content_facts as child_content_facts ON child_content_facts.content_object_id = content_objects_join.child_cobj_id INNER JOIN content_fact_versions ON content_fact_versions.fact_id = child_content_facts.fact_id INNER JOIN node_names as child_node_names ON child_node_names.node_id = child_content_facts.node_id WHERE parent_nodes.node_id = 341 --AND parent_content_objects.object_type_id in (1,2,3,4,6,8,9) AND parent_content_objects.object_type_id = 73 ORDER BY child_content_facts.content_object_id ) as mapInfo group by mapInfo.content_object_id, xPos, yPos, text, textAngle, textType, symbol, linkType __This email, including attachments, is intended only for the addresseeand may be confidential, privileged and subject to copyright. If youhave received this email in error, please advise the sender and deleteit. If you are not the intended recipient of this email, you must notuse, copy or disclose its content to anyone. You must not copy or communicate to others content that is confidential or subject to copyright, unless you have the consent of the cont
Re: [SQL] COMMENT ON CONSTRAINT
Sascha Ziemann writes: > it is defined in this way: [...] > CONSTRAINT constraint_name ON table_name | Well then... > comment on constraint uni on table tab3 is 'unique pair'; ^ ...why do you insert "table" when it is defined without? :-) regards Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
