[SQL] crosstab
Title: crosstab Hi I have looked into the tablefunc / crosstab contrib for postgres and it appears like it can't perform what I need. The crosstab function converts this : row_name cat value --+---+--- row1 cat1 val1 row1 cat2 val2 row1 cat3 val3 row1 cat4 val4 row2 cat1 val5 row2 cat2 val6 row2 cat3 val7 row2 cat4 val8 To this : row_name category_1 category_2 -++ row1 val1 val2 row2 val5 val6 Is it possible to do the opposite and go from a column(denormalized) structure to a row(normalized) structure. I have a table that is similarly stuctured like so: Melbourne_figures Sydney_figures Adelaide_figures etc... 10 20 22 10 22 29 ... However I wish to convert like so : Melbourne 20 Sydney 42 Adelaide 51 I have tried using unions or subselects however the table is quite large and it takes far too long to run. The most efficient way would be to create a stored proc that uses a cursor to loop through the table transforming the data into the new table structure. However I would appreciate your feeback before writing this procedure? Theo __ This email, including attachments, is intended only for the addressee and may be confidential, privileged and subject to copyright. If you have received this email in error, please advise the sender and delete it. If you are not the intended recipient of this email, you must not use, 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 content owner.
Re: [SQL] crosstab
THeo, > I have tried using unions or subselects however the table is quite large > and it takes far too long to run. The most efficient way would be to create > a stored proc that uses a cursor to loop through the table transforming the > data into the new table structure. However I would appreciate your feeback > before writing this procedure? A proc won't run any faster ... much slower, in fact.Unless you mean that you want to use it to correct the actual table structure, which is what you should be doing? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Performance of Views
Hello there, i have a short question ... I have a few tables (at the moment "only" 3 for testing), over which will by made a query in this form: SELECT a.orderitem_id, a.transaction_id, a.order_id, a.shop_id, a.quantity, a.price, b.affiliate_id, c."type" FROM ss_order_orderitems a LEFT JOIN ss_order_affiliate_tracking b ON a.order_id = b.order_id, ss_shops c WHERE (a.order_id = b.order_id OR b.order_id IS NULL) AND a.shop_id = c.shop_id; The query will get later a few more conditions ... Now is the problem, that the performance is not realy good ... and i think about the best solution for such a query and found three possibilitys: 1. a simple select over the three tables (one of them contains 16 entrys in the moment and it's growing) in the form like above (the db is mysql 4.1.x) I think this solution is not very perfomant ... 2. move the data to a postgresql-db with the same structur and create a view, wich makes the same query ... Is the performance for the same query different between a simple select and a view!? If so, i can forget the view ... 3. put the data with the above query in one big table ... I know, thats no good db-structur, but i don't know how i could make it better ... The main-question at the moment iss ... Is the performance of the View-Method better then the first Method on the existing tables!? I hope of a view hints ... Thanks Steffen ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Advanced SELECT
Hi, Tom Lane schrieb: > [ experiments... ] This works reliably in 7.4 and up. Before that, > the optimizer didn't make the connection between the sort ordering of > the inner query and that needed by the outer, so it would repeat the > sort step using only key1 and very possibly destroy the key2 ordering. *Thanks* this was the same idea, that I had. Combined with a DISTINCT there are no double entries. It works here perfect with 7.39. If someone is interested, this is the full SELECT: - SELECT case when lower(substring(f.marketingid,1,1))='d' then 'Deutschland' when lower(substring(f.marketingid,1,1))='s' then 'Schweiz' else '' end AS bereich, f.fid AS fid, f.marketingid AS marketingid, f.firmalang AS unternehmen, case when fp.www IS NULL then '' else fp.www end AS url, case when fp.jobwww IS NULL then '' else fp.jobwww end AS joburl, case when b.branchen IS NULL then '' else b.branchen end AS branchen, case when j.jobtypen IS NULL then '' else j.jobtypen end AS gesuchte_jobtypen, case when g.funktionen IS NULL then '' else g.funktionen end AS gesuchte_funktionen, case when a.faecher IS NULL then '' else a.faecher end AS gesuchte_fachrichtungen, case when s.zusatz IS NULL then '' else s.zusatz end AS adresse_zusatz, case when s.strasse IS NULL then '' else s.strasse end AS strasse, case when s.plz IS NULL then '' else s.plz end AS plz, case when s.ort IS NULL then '' else s.ort end AS ort, case when s.land IS NULL then '' else s.land end AS land, case when m.ansprechpartner IS NULL then '' else m.ansprechpartner end AS ansprechpartner FROM firma f JOIN ( SELECT js.fid, group_concat(js.jobtyp) AS jobtypen FROM ( SELECT DISTINCT f.fid, jt.jobtyp FROM firma f LEFT OUTER JOIN (jobtext j JOIN jobtyp jt ON (j.jobtypid=jt.jobtypid)) ON (f.fid=j.fid) WHERE (j.status>-1 OR j.status IS NULL) AND (jt.status>-1 OR jt.status IS NULL) AND f.status>-1 ORDER BY jobtyp ) js GROUP BY js.fid ) j ON (f.fid=j.fid) JOIN ( SELECT bs.fid, group_concat(bs.fbranche) AS branchen FROM ( SELECT DISTINCT f.fid, b.fbranche FROM firma f LEFT OUTER JOIN (firma_fbranche fb JOIN fbranche b ON (fb.fbrancheid=b.fbrancheid)) ON (f.fid=fb.fid) WHERE (b.status>-1 OR b.status IS NULL) AND f.status>-1 ORDER BY fbranche ) bs GROUP BY bs.fid ) b ON (f.fid=b.fid) JOIN ( SELECT gs.fid, group_concat(gs.bfunktion) AS funktionen FROM ( SELECT DISTINCT f.fid, b.bfunktion FROM firma f LEFT OUTER JOIN (firma_bfunktion bf JOIN bfunktion b ON (bf.bfunktionid=b.bfunktionid)) ON (f.fid=bf.fid) WHERE (b.status>-1 OR b.status IS NULL) AND f.status>-1 ORDER BY b.bfunktion ) gs GROUP BY gs.fid ) g ON (f.fid=g.fid) JOIN ( SELECT au.fid, group_concat(au.fach) AS faecher FROM ( SELECT DISTINCT f.fid, a.fach FROM firma f LEFT OUTER JOIN (firma_fach fa JOIN fach a ON (fa.fachid=a.fachid)) ON (f.fid=fa.fid) WHERE (a.status>-1 OR a.status IS NULL) AND f.status>-1 ORDER BY a.fach ) au GROUP BY au.fid ) a ON (f.fid=a.fid) JOIN ( SELECT ms.fid, group_concat(ms.ansprechpartner) AS ansprechpartner FROM ( SELECT DISTINCT f.fid, CASE WHEN (m.titel IS NULL OR length(m.titel)<2) THEN '' ELSE m.titel || ' ' END || CASE WHEN m.vname IS NULL THEN '' ELSE m.vname || ' ' END || CASE WHEN m.nname IS NULL THEN '' ELSE m.nname END AS ansprechpartner FROM firma f LEFT OUTER JOIN ( sitz s JOIN (SELECT * FROM mitarb WHERE ffunktionid=1 AND status>-1) m ON (s.sitzid=m.sitzid)) ON (f.fid=s.fid) WHERE (s.status>-1 OR s.status IS NULL) AND f.status>-1 ORDER BY ansprechpartner ) AS ms GROUP BY ms.fid ) m ON (f.fid=m.fid) LEFT OUTER JOIN ( SELECT f.fid, group_concat(s.zusatz) AS zusatz, group_concat(s.strasse) AS strasse, group_concat(s.plz) AS plz, group_concat(s.ort) AS ort, group_concat(l.land) AS land FROM firma f JOIN sitz s ON (f.fid=s.fid) JOIN land l ON s.landid=l.landid WHERE s.status>-1 AND s.hauptsitz=1 AND f.status>-1 GROUP BY f.fid ) s ON (f.fid=s.fid) LEFT OUTER JOIN fplus fp ON (f.fid=fp.fid) ORDER BY lower(substring(f.marketingid,1,1)), lower(f.firmalang); -- *greets* Kai -- GnuPG-PublicKey -> http://www.hobsons.de/pgp/kai_hessing.asc Gut ist nicht Nichtfreveln, sondern nicht einmal freveln wollen. (Demokrit, um 460 v. Chr.) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Postgresql FK to MS SQL triggers
Hi, If you are moving from Postgres to MS SQL you will most likely will find that you can not recreate your PostgreSQL FK to MSSQL FK because this enterprise class database will NOT allow you to create all 3 FK which are exist in your PGSQL: table users(user_id PK) table journal(created_by, modified_by, deleted_by) ADD CONSTRAINT fk_created_by FOREIGN KEY (created_by) REFERENCES users(user_id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE; ADD CONSTRAINT fk_modified_by FOREIGN KEY (modified_by) REFERENCES users(user_id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE; ADD CONSTRAINT fk_deleted_by FOREIGN KEY (deleted_by) REFERENCES users(user_id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE; For interested people I wrote a PHP script which: 1) Extracts all underlying triggers from pg_trigger table in Postgres used to support FK (3 triggers for each FK) 2) Generates a MSSQL script file which recreates all triggers in MSSQL Hope it will save some time for somebody. Igor ---(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] psql encoding problem
Hello,
I am trying to insert the following record:
INSERT INTO item (name,retail_price) VALUES ('Cheese Soufflé',7.95,);
(I presume you see the accented character in *Soufflé*)
psql comes back with "invalid byte sequence for encoding "UNICODE": 0xe9"
If I do this via DbVisualizer, the record is inserted fine.
Is there any way around this problem?
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Postgres performance
> Not always, AFAICT. The four most common reasons why PG tests slower > than Mysql are: > 1. You haven't configured or have misconfigured PostgreSQL. > 2. You are testing a MySQL-tuned application (lots of small, simple > queries, no views, no subselects etc) > 3. You are only testing one connection (try 10,20,50 simultaneous users > and see who wins then). > 4. You are not testing the transaction-safe storage systems in MySQL > > See if you can answer some of the questions above and I'm sure we'll be > able to get your database server running smoothly. Hi, I've used the benchmark http://genecensus.org/yeast/cluster/database/mysql/sql-bench/ (ok, it's without bench on views, sub-select, transaction,..) The database files are in stripe (RAID 0) on two SATA hd (transfer rate 50Mb), the filesystem is reiserfs (3.6 format - with checks off), no optimation on I/O scheduler, DBMS are in default configuration (so I don't benefit nobody). Total time: Pgsql: 7h 20' MySQL: 14' (!!) This is the configuration where is running Postgres 8.0 and MySql: Linux version 2.6.9-1.667 ([EMAIL PROTECTED]) (gcc version 3.4.2 20041017 (Red Hat 3.4.2-6.fc3)) #1 Tue Nov 2 14:50:10 EST 2004 Memory: 1024128k/1047744k available (2389k kernel code, 22900k reserved, 1276k d ata, 164k init) CPU: L1 I Cache: 64K (64 bytes/line), D cache 64K (64 bytes/line) CPU: L2 Cache: 512K (64 bytes/line) CPU: AMD Athlon(tm) 64 Processor 3000+ stepping 00 powernow-k8: Found 1 AMD Athlon 64 / Opteron processors (version 1.00.09b) powernow-k8: BIOS error - no PSB hda: SAMSUNG SP0411N, ATA DISK drive hda: max request size: 1024KiB hda: 78242976 sectors (40060 MB) w/2048KiB Cache, CHS=16383/255/63, UDMA(133) ata1: SATA max UDMA/133 cmd 0xE800 ctl 0xE402 bmdma 0xD400 irq 193 ata2: SATA max UDMA/133 cmd 0xE000 ctl 0xD802 bmdma 0xD408 irq 193 cpuinfo processor : 0 vendor_id : AuthenticAMD cpu family : 15 model : 12 model name : AMD Athlon(tm) 64 Processor 3000+ stepping: 0 cpu MHz : 2002.652 cache size : 512 KB fpu : yes fpu_exception : yes cpuid level : 1 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 syscall nx mmxext lm 3dnowext 3dnow bogomips: 3932.16 TLB size: 1088 4K pages clflush size: 64 cache_alignment : 64 address sizes : 40 bits physical, 48 bits virtual power management: ts fid vid ttp The analisys: TestPostgres (sec) Mysql (sec) alter_table_add 1 34 alter_table_drop27 connect 36 4 connect+select_1_row45 5 connect+select_simple 38 4 count 21 5 count_distinct 9 7 count_distinct_217 11 count_distinct_big 87 26 count_distinct_group57 16 count_distinct_group_on_key 19 7 count_distinct_group_on_key_parts 56 16 count_distinct_key_prefix 38 6 count_group_on_key_parts7 7 count_on_key226 53 create+drop 145 3 create_MANY_tables 50 3 create_index1 1 create_key+drop 98 5 create_table0 0 delete_all 13 2 delete_all_many_keys11 10 delete_big 0 0 delete_big_many_keys11 10 delete_key 6 1 drop_index 0 0 drop_table 0 drop_table_when_MANY_tables 23 2 insert 177 24 insert_duplicates 59 6 insert_key 695 21 insert_many_fields 84 2 insert_select_1_key 6 1 insert_select_2_keys8 1 min_max 9 3 min_max_on_key 114527 multiple_value_insert 1 order_by_big25 19 order_by_big_key19 14 order_by_big_key2 17 14 order_by_big_key_desc 19 15 order_by_big_key_diff 17 18 order_by_big_key_prefix 17 15 order_by_key2_diff 2 2 order_by_key_prefix 0 1 order_by_range 1 1 outer_join 40 8 outer_join_found38 8 outer_join_not_found37
Re: [SQL] table constraints
foreign keys and primary keys have to be defined as unique at the table /
column level if you want to implement a check constraint -- your contrived
example doesn't stand up all that well -- If you want to use constraints --
then your database schema should conform to traditional RDBMS theory and
data normalization by having primary and foreign keys instead of just
trying to create arbitrary contraints on a non-normalized schema and
implement constraints as a user-defined function...
""Casey T. Deccio"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi,
>
> I am running PostgreSQL 7.4.7. I am having some issues with a
> constraint for one of my database tables. The code snippet below
> outlines the code and its output (output is commented).
>
> In this case each bldg has an owner associated to it, and each animal
> lives in some bldg. Each owner has exactly one own favorite animal out
> of all the bldgs owned by him. So the constraint added to each zoo row
> is that the boolean field 'favorite' is true for exactly once for each
> group of animals in the zoo that have a common owner.
>
> The unique_favorite(text) function is created to help with this
> constraint. The function returns what it is supposed to (see select
> statement in the code), but doesn't hold when used as a constraint.
>
> There may be a better way to do this, but I'm not looking for that right
> now. I would like to know if I am using this constraint wrong, or if
> there is there something wrong with the table constraints in the
> database system.
>
> Thanks,
> Casey
>
> -- Code snippet ---
> CREATE TABLE owner (owner varchar(50));
> -- CREATE TABLE
> INSERT INTO owner VALUES ('steve');
> -- INSERT 13193166 1
>
> CREATE TABLE bldg (bldg varchar(50), owner varchar(50));
> -- CREATE TABLE
> INSERT INTO bldg VALUES ('bldg1', 'steve');
> -- INSERT 13193169 1
>
> CREATE TABLE zoo (animal varchar(50), bldg varchar(50), favorite
> boolean);
> -- CREATE TABLE
>
> CREATE OR REPLACE FUNCTION unique_favorite(text) RETURNS BOOLEAN AS '
> DECLARE
> temp RECORD;
> BEGIN
> SELECT into temp * FROM
> (SELECT b.owner, sum(CASE WHEN favorite = TRUE THEN 1 ELSE 0 END) AS
> num_favorites
>FROM (SELECT * FROM zoo WHERE animal = $1) z INNER JOIN bldg b
> USING(bldg) GROUP BY b.owner) sub
> WHERE sub.num_favorites <> 1;
> RETURN NOT FOUND;
> END;
> ' LANGUAGE plpgsql;
> -- CREATE FUNCTION
>
> ALTER TABLE zoo ADD check(unique_favorite(animal));
> -- ALTER TABLE
>
> INSERT into zoo VALUES ('monkey', 'bldg1', false);
> -- INSERT 13193173 1
> -- (This shouldn't be allowed!!!)
>
> SELECT *, unique_favorite(animal) FROM zoo;
> -- animal | bldg | favorite | unique_favorite
> -- +---+--+-
> -- monkey | bldg1 | f| f
>
> INSERT into zoo VALUES ('monkey', 'bldg1', false);
> -- ERROR: new row for relation "zoo" violates check constraint "$1"
>
> INSERT into zoo VALUES ('monkey', 'bldg1', true);
> -- ERROR: new row for relation "zoo" violates check constraint "$1"
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
