Re: [SQL] INSERT INTO ... SELECT
Thanks, Christoph for your thought. An alternative solution I have is to fetch the user table first and act according with the retured value. It doesn't seem to have a single query solution. v. -- - Original Message - DATE: Wed, 13 Aug 2003 13:40:53 From: Christoph Haller <[EMAIL PROTECTED]> To: [EMAIL PROTECTED], [EMAIL PROTECTED] Cc: >> >> Coincidentally, I have a very similar case using some twists. >> >> The table I want to insert data is something like >> >> table A ( >> user01 int, >> user02 int, >> ... >> primary key (user01, user02), >> CHECK ( user01 < user02 ) >> ); >> >> And the user table is: >> >> tabe user_table ( >> user int constraint pk_user primary key UNIQUE, --PK >> ..., >> email varchar(25) not null, >> ... >> }; >> >> In the insertion statement, the user02 is obtained by a subselect >statement: "select user from user where email=?". >> >> There may not exist the email in the user table. And if yes, the check >condition may not be met. If the condition is n >ot met, the two user IDs need to be switched. >> >> How the query shall be construted? >> >> Thanks for any suggestions. >> > >I'd say this conditional insert into user02 column can be done by a >PL/pgSQL function >combined by CASE statements. >I'm thinking of something like (untested of course) >INSERT INTO ... >SELECT >CASE WHEN user01 < get_user02(email=?) >THEN user01 ELSE get_user02(email=?) END, >CASE WHEN user01 < get_user02(email=?) >THEN get_user02(email=?) ELSE user01 END, >... >FROM table A ... ; > >You might think calling the function four times is a waste, >but as far as I understand it PL/pgSQL functions are run via >prepared execution plans (see PL/pgSQL - SQL Procedural Language - >Overview), >so it shouldn't be too bad. >Don't know what to do about >> There may not exist the email in the user table. >Insert a NULL? > >HTH > >Regards, Christoph > > > Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail! http://login.mail.lycos.com/r/referral?aid=27005 ---(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] INSERT INTO ... SELECT
Coincidentally, I have a very similar case using some twists. The table I want to insert data is something like table A ( user01 int, user02 int, ... primary key (user01, user02), CHECK ( user01 < user02 ) ); And the user table is: tabe user_table ( user int constraint pk_user primary key UNIQUE, --PK ..., email varchar(25) not null, ... }; In the insertion statement, the user02 is obtained by a subselect statement: "select user from user where email=?". There may not exist the email in the user table. And if yes, the check condition may not be met. If the condition is not met, the two user IDs need to be switched. How the query shall be construted? Thanks for any suggestions. v. -- - Original Message - DATE: Mon, 11 Aug 2003 21:57:05 From: Christopher Browne <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Cc: >Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (Silke Trissl) would write: >> I would like to insert into a table values from a table and user >> defined ones. Here is the example: >> >> I found this statement to insert values from another table: >> >> INSERT INTO test_table (cust_id, cust_name) SELECT id, name from CUSTOMER; >> >> But the test_table has another column, which should have the same >> value for all the customers. >> >> Is there something like >> >> INSERT INTO test_table (int_id, cust_id, cust_name) '1', SELECT id, >> name from CUSTOMER: >> >> and if so, what ist the correct statement? If not, what is an >> alternative to insert a single row at a time? > >You're close. > >The constant term needs to be inside the SELECT. > >Try: > insert into test_table (int_id, cust_id, cust_name) > select '1', id, name from customer; >-- >select 'cbbrowne' || '@' || 'acm.org'; >http://www.ntlug.org/~cbbrowne/sap.html >(eq? 'truth 'beauty) ; to avoid unassigned-var error, since compiled code > ; will pick up previous value to var set!-ed, > ; the unassigned object. >-- from BBN-CL's cl-parser.scm > >---(end of broadcast)--- >TIP 4: Don't 'kill -9' the postmaster > Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail! http://login.mail.lycos.com/r/referral?aid=27005 ---(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] Inheritance or no inheritance, there is a question
I am working on an application system refinement. There is a user profile table in the
current system. After the refinement, there are new separated roles, A and B, of the
users. The role A only has a few valid fields of the original profile table while the
role B still has the whole profile table. In regarding of the DB scheme design, that
should be an ideal case of using inheritance. I can have something like
table PROFILE_A (
ID int – PK,
...
):
table PROFILE_B {
...
inherits (A)
);
However, there is a problem. There are some multi-valued field tables, such as
languages the person can speak, associated with the profile table referred by ID in
the profile table. These tables are still needed for the A and B. But, the ID is not
accessible from the PROFILE_B.
What is the best solution for this DB scheme problem?
p.s. I can't use array data type for the multi-valued fields since they need to be
workable with a selection statement, nor xml or comma separated format for
maintainablity concern.
Thanks,
v.
Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail!
http://login.mail.lycos.com/r/referral?aid=27005
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
