Re: [SQL] data import via COPY, Rules + Triggers
Thank you for your help, Sergey. That certainly works. I was wondering whether the manager.id could maybe be obtained via INSERT ... RETURNING? -- Best Regards, Tarlika Elisabeth Schmitz On Thu, 5 May 2011 08:45:32 +0300 sergey kapustin wrote: >Try using (select id from manager where name=NEW.manager_name) to get >the newly inserted manager. >The "name" column in "manager" table should have unique constraint - >this will be good both for performance and consistency. > > > >CREATE OR REPLACE RULE zathlete_insert_1 AS > ON INSERT TO zathlete > DO ALSO > ( > INSERT INTO athlete > (id, name, _received) VALUES > (NEW.dad_id, NEW.dad_name, NEW._received); > INSERT INTO sponsor > (id, name, _received) VALUES > (NEW.sponsor_id, NEW.sponsor_name, NEW._received); > INSERT INTO manager > (name, _received) VALUES > (NEW.manager_name, NEW._received); > INSERT INTO athlete > (id, name, dad_fk, sponsor_fk, manager_fk, _received) VALUES > (NEW.id, NEW.name, NEW.dad_id, >NEW.sponsor_id, (select id from manager where >name=NEW.manager_name), NEW._received); >) >; > >On Thu, May 5, 2011 at 1:48 AM, Tarlika Elisabeth Schmitz < >postgres...@numerixtechnology.de> wrote: > >> [...] >> >> >> I created interim tables matching the structure of the CSV formats >> (about 6 of them). I want to import via COPY and distribute the data >> to the "proper" tables via rules + triggers. >> >> I just hit a wall with one of the rules, (see example below): how do >> I populate athlete.manager_fk, which is the result of the previous >> INSERT? >> >> >> >> >> >> -- interim table >> CREATE TABLE zathlete >> ( >> id integer NOT NULL, >> "name" character varying(50) NOT NULL, >> dad_id integer, >> dad_name character varying(50), >> sponsor_id integer, >> sponsor_name character varying(50), >> manager_name character varying(50), >> _received timestamp without time zone NOT NULL >> ) >> >> -- proper tables >> CREATE TABLE sponsor >> ( >> id integer NOT NULL, >> "name" character varying(50) NOT NULL, >> _received timestamp without time zone NOT NULL, >> CONSTRAINT sponsor_pkey PRIMARY KEY (id) >> ) >> >> CREATE TABLE manager >> ( >> id serial NOT NULL, >> "name" character varying(50) NOT NULL, >> _received timestamp without time zone NOT NULL, >> CONSTRAINT manager_pkey PRIMARY KEY (id) >> ) >> >> CREATE TABLE athlete >> ( >> id integer NOT NULL, >> "name" character varying(50) NOT NULL, >> dad_fk integer, >> sponsor_fk integer, >> manager_fk integer, >> _received timestamp without time zone NOT NULL, >> CONSTRAINT athlete_pkey PRIMARY KEY (id), >> CONSTRAINT manager_athlete_fk FOREIGN KEY (manager_fk) >> REFERENCES manager (id) MATCH SIMPLE >> ON UPDATE CASCADE ON DELETE RESTRICT, >> CONSTRAINT sponsor_athlete_fk FOREIGN KEY (sponsor_fk) >> REFERENCES sponsor (id) MATCH SIMPLE >> ON UPDATE CASCADE ON DELETE RESTRICT, >> CONSTRAINT dad_athlete_fk FOREIGN KEY (dad_fk) >> REFERENCES athlete (id) MATCH SIMPLE >> ON UPDATE CASCADE ON DELETE RESTRICT >> ) >> >> >> -- rules >> >> CREATE OR REPLACE RULE zathlete_insert_1 AS >>ON INSERT TO zathlete >>DO ALSO -- INSTEAD once all is working >>( >>INSERT INTO athlete >>(id, name, _received) VALUES >>(NEW.dad_id, NEW.dad_name, NEW._received); >>INSERT INTO sponsor >>(id, name, _received) VALUES >>(NEW.sponsor_id, NEW.sponsor_name, NEW._received); >>INSERT INTO manager >>(name, _received) VALUES >>(NEW.manager_name, NEW._received); >>INSERT INTO athlete >>(id, name, dad_fk, sponsor_fk, manager_fk, _received) >> VALUES (NEW.id, NEW.name, NEW.dad_id, >> NEW.sponsor_id, ?, NEW._received); >> ) >> >> >> >> >> >> >> System: PostgreSQL 8.3 >> no of users: 1 >> >> -- >> >> Best Regards, >> Tarlika Elisabeth Schmitz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Select and merge rows?
Hello! I have a doubt about a query that I tried to do, but I cant.. This is the scenario: I have a table, with this rows: order ID value -- 1 1000 3 2 1000 5 3 1000 6 1 1001 1 2 1001 2 1 1002 4 2 1002 4 I need to get this table, divided by ID, like this: id value1 value2 value3 1000 3 5 6 1001 1 2 1002 1 2 How I can do this? I tried with cursors and view, but i can't Any help is welcome! Thanks in advance! Regards,
Re: [SQL] Select and merge rows?
Howdy! Is there a maximum ceilling of three values per order ID? or an ID can have an arbitrary number of values? Best, Oliveiros 2011/5/5 Claudio Adriano Guarracino > Hello! > I have a doubt about a query that I tried to do, but I cant.. > This is the scenario: > I have a table, with this rows: > orderIDvalue > -- > 110003 > 210005 > 310006 > 110011 > 210012 > 110024 > 210024 > > I need to get this table, divided by ID, like this: > idvalue1value2value3 > > 1000356 > 100112 > 100212 > > How I can do this? > I tried with cursors and view, but i can't > Any help is welcome! > > Thanks in advance! > Regards, > >
[SQL] Select and merge rows?
Excuse me, The original table is: order ID value -- 1 1000 3 2 1000 5 3 1000 6 1 1001 1 2 1001 2 1 1002 4 2 1002 4 The result of table should be: id value1 value2 value3 1000 3 5 6 1001 1 2 1002 4 4 Thanks! Regards, --- On Thu, 5/5/11, Claudio Adriano Guarracino wrote: From: Claudio Adriano Guarracino Subject: Select and merge rows? To: pgsql-sql@postgresql.org Date: Thursday, May 5, 2011, 4:18 PM Hello! I have a doubt about a query that I tried to do, but I cant.. This is the scenario: I have a table, with this rows: order ID value -- 1 1000 3 2 1000 5 3 1000 6 1 1001 1 2 1001 2 1 1002 4 2 1002 4 I need to get this table, divided by ID, like this: id value1 value2 value3 1000 3 5 6 1001 1 2 1002 1 2 How I can do this? I tried with cursors and view, but i can't Any help is welcome! Thanks in advance! Regards,
[SQL] Re: [SQL] Select and merge rows?
While there is insufficient information provided (a couple of table snippets), you may consider and experiment with the snippet below to get you started. SELECT ids.id, f1.value AS value1, f2.value AS value2, f3.value AS value3 FROM ( SELECT DISTINCT id FROM foo ) AS ids LEFT JOIN foo f1 ON f1.id = ids.id AND f1.order = 1 LEFT JOIN foo f2 ON f2.id = ids.id AND f2.order = 2 LEFT JOIN foo f3 ON f3.id = ids.id AND f3.order = 3 ORDER BY ids.id; - Reply message - From: "Claudio Adriano Guarracino" Date: Thu, May 5, 2011 5:18 pm Subject: [SQL] Select and merge rows? To: Hello! I have a doubt about a query that I tried to do, but I cant.. This is the scenario: I have a table, with this rows: order ID value -- 1 1000 3 2 1000 5 3 1000 6 1 1001 1 2 1001 2 1 1002 4 2 1002 4 I need to get this table, divided by ID, like this: id value1 value2 value3 1000 3 5 6 1001 1 2 1002 1 2 How I can do this? I tried with cursors and view, but i can't Any help is welcome! Thanks in advance! Regards,
[SQL] slightly OT - Using psql from Emacs with sql.el
Hi, When working with psql via sql.el, multiple prompts accumulate in a single line when sending multi-line input to the SQLi buffer. For example, sending the following: SELECT a, b, c, FROM some_table; with 'C-c C-r' results in these lines in the SQLi buffer: database_name=# database_name-# database_name-# database_name-# before showing the output of the query. This doesn't happen when working with psql directly from a shell. Has someone dealt with this problem or can suggest some ideas to avoid this? Thanks, -- Seb -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] slightly OT - Using psql from Emacs with sql.el
On 05/05/2011 04:01 PM, Seb wrote: Hi, When working with psql via sql.el, multiple prompts accumulate in a single line when sending multi-line input to the SQLi buffer. For example, sending the following: SELECT a, b, c, FROM some_table; with 'C-c C-r' results in these lines in the SQLi buffer: database_name=# database_name-# database_name-# database_name-# before showing the output of the query. This doesn't happen when working with psql directly from a shell. Has someone dealt with this problem or can suggest some ideas to avoid this? Thanks, Doesn't appear to. I use sql-mode alot/daily. The multiple prompts never bothers me, though the output not starting at the left kind of does. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] slightly OT - Using psql from Emacs with sql.el
On Thu, 05 May 2011 16:47:09 -0600, Rob Sargent wrote: [...] > Doesn't appear to. I use sql-mode alot/daily. The multiple prompts > never bothers me, though the output not starting at the left kind of > does. I've adapted someone's suggestion at the Emacs Wiki for that: (defun sl/sql-add-newline-before-output (output) "Add newline to beginning of OUTPUT for `comint-preoutput-filter-functions'" (concat "\n" output)) (add-hook 'sql-interactive-mode-hook (lambda () (add-hook 'comint-preoutput-filter-functions 'sl/sql-add-newline-before-output))) ... but this breaks navigation (e.g. 'C-c C-p') -- Seb -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] slightly OT - Using psql from Emacs with sql.el
On 05/05/2011 04:55 PM, Seb wrote: On Thu, 05 May 2011 16:47:09 -0600, Rob Sargent wrote: [...] Doesn't appear to. I use sql-mode alot/daily. The multiple prompts never bothers me, though the output not starting at the left kind of does. I've adapted someone's suggestion at the Emacs Wiki for that: (defun sl/sql-add-newline-before-output (output) "Add newline to beginning of OUTPUT for `comint-preoutput-filter-functions'" (concat "\n" output)) (add-hook 'sql-interactive-mode-hook (lambda () (add-hook 'comint-preoutput-filter-functions 'sl/sql-add-newline-before-output))) ... but this breaks navigation (e.g. 'C-c C-p') Oooo, can't have that. Though my usage (sql-send-paragraph) does not get registered in the command history, I still like have a proper record of what I have typed into the *SQL* buffer. If I want a history of what I sent from my working buffer (of sql statements) I wipe/yank into *SQL*. Then the entire block returns on M-p. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select and merge rows?
Thank you very much! Your example help me a lot! The original query is more complex, but I can continue with this example. Thanks again! --- On Thu, 5/5/11, scorpda...@hotmail.com wrote: From: scorpda...@hotmail.com Subject: Re: [SQL] Select and merge rows? To: "Claudio Adriano Guarracino" , pgsql-sql@postgresql.org Date: Thursday, May 5, 2011, 5:41 PM While there is insufficient information provided (a couple of table snippets), you may consider and experiment with the snippet below to get you started. SELECT ids.id, f1.value AS value1, f2.value AS value2, f3.value AS value3 FROM ( SELECT DISTINCT id FROM foo ) AS ids LEFT JOIN foo f1 ON f1.id = ids.id AND f1.order = 1 LEFT JOIN foo f2 ON f2.id = ids.id AND f2.order = 2 LEFT JOIN foo f3 ON f3.id = ids.id AND f3.order = 3 ORDER BY ids.id; - Reply message - From: "Claudio Adriano Guarracino" Date: Thu, May 5, 2011 5:18 pm Subject: [SQL] Select and merge rows? To: Hello! I have a doubt about a query that I tried to do, but I cant.. This is the scenario: I have a table, with this rows: order ID value -- 1 1000 3 2 1000 5 3 1000 6 1 1001 1 2 1001 2 1 1002 4 2 1002 4 I need to get this table, divided by ID, like this: id value1 value2 value3 1000 3 5 6 1001 1 2 1002 1 2 How I can do this? I tried with cursors and view, but i can't Any help is welcome! Thanks in advance! Regards,
[SQL] check constraint bug?
I specified: ALTER TABLE h ADD CONSTRAINT val_h_stats CHECK (NOT (sex = 'f') AND (stats IS NOT NULL)); which was translated to: ALTER TABLE h ADD CONSTRAINT val_h_stats CHECK (NOT sex = 'f'::bpchar AND stats IS NOT NULL); Can the expression not be bracketed? I changed this to: ALTER TABLE horse ADD CONSTRAINT val_horse_stats CHECK (sex != 'f') OR (stats IS NULL)); -- Best Regards, Tarlika Elisabeth Schmitz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] check constraint bug?
On Thu, May 5, 2011 at 11:06 PM, Tarlika Elisabeth Schmitz wrote: > I specified: > > ALTER TABLE h ADD CONSTRAINT val_h_stats > CHECK (NOT (sex = 'f') AND (stats IS NOT NULL)); > > which was translated to: > > ALTER TABLE h ADD CONSTRAINT val_h_stats > CHECK (NOT sex = 'f'::bpchar AND stats IS NOT NULL); You need another level of parens: CHECK (NOT ((sex = 'f') AND (stats IS NOT NULL))); -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select and merge rows?
Hi again: I can did the same with crosstab: SELECT * FROM crosstab ( 'select id, order, value from test ORDER BY 1', 'select distinct order from test ORDER BY 1' ) AS ( id numeric(20), value1 text, value2 text, value3 text ); http://www.postgresql.org/docs/current/interactive/tablefunc.html In this case, i use: F.36.1.4. - crosstab(text, text). Thanks to Osvaldo Kussama for this help! --- On Thu, 5/5/11, Claudio Adriano Guarracino wrote: From: Claudio Adriano Guarracino Subject: Re: [SQL] Select and merge rows? To: pgsql-sql@postgresql.org, "scorpda...@hotmail.com" Date: Thursday, May 5, 2011, 9:06 PM Thank you very much! Your example help me a lot! The original query is more complex, but I can continue with this example. Thanks again! --- On Thu, 5/5/11, scorpda...@hotmail.com wrote: From: scorpda...@hotmail.com Subject: Re: [SQL] Select and merge rows? To: "Claudio Adriano Guarracino" , pgsql-sql@postgresql.org Date: Thursday, May 5, 2011, 5:41 PM While there is insufficient information provided (a couple of table snippets), you may consider and experiment with the snippet below to get you started. SELECT ids.id, f1.value AS value1, f2.value AS value2, f3.value AS value3 FROM ( SELECT DISTINCT id FROM foo ) AS ids LEFT JOIN foo f1 ON f1.id = ids.id AND f1.order = 1 LEFT JOIN foo f2 ON f2.id = ids.id AND f2.order = 2 LEFT JOIN foo f3 ON f3.id = ids.id AND f3.order = 3 ORDER BY ids.id; - Reply message - From: "Claudio Adriano Guarracino" Date: Thu, May 5, 2011 5:18 pm Subject: [SQL] Select and merge rows? To: Hello! I have a doubt about a query that I tried to do, but I cant.. This is the scenario: I have a table, with this rows: order ID value -- 1 1000 3 2 1000 5 3 1000 6 1 1001 1 2 1001 2 1 1002 4 2 1002 4 I need to get this table, divided by ID, like this: id value1 value2 value3 1000 3 5 6 1001 1 2 1002 1 2 How I can do this? I tried with cursors and view, but i can't Any help is welcome! Thanks in advance! Regards,
