Re: Insert data in two columns same table - Mailing list pgsql-general

On 03/16/2016 07:07 PM, drum.lucas@gmail.com wrote:

    I see a lot of other problems: you have 3 independet tables. Your 2
    queries
    (selects) returns 2 independet results, you can't use that for
    insert into the
    3rd table. And i think, you are looking for an update, not insert.
    So you have
    to define how your tables are linked together (join).

    Can you explain how these tables are linked together?




Hi Andreas!

Well...

There are two tables that I need to get data from(dm.billables /
public.ja_mobiusers), and a third table (dm.billables_links) that I need
to insert data from those two tables.

The table dm.billables has four (important) columns:

*billable_id / customer_id / role_id / mobiuser_id*

I wanna add data there. The data is not there yet, so it's not an UPDATE.

*1 -* select the billable_id: (SELECT1)
SELECT billable_id FROM junk.wm_260_billables2 WHERE info ilike '%Alisha%'

*2 -* select the mobiuser_id: (SELECT2)
SELECT id FROM public.ja_mobiusers WHERE name_first LIKE 'Alisha%' AND
name_last LIKE 'Dadryl%'

*3 -* Insert those two data into the dm.billables_links table (EXAMPLE):
INSERT INTO dm.billables_links (billable_id, mobiuser_id) VALUES
(SELECT1, SELECT2);


    CREATE TABLE
    *billables*
         (
             billable_id BIGINT DEFAULT
    "nextval"('"dm"."billables_billable_id_seq"'::"regclass") NOT
             NULL,
             account_id BIGINT NOT NULL,
             code CHARACTER VARYING(64) NOT NULL,
             info "TEXT",
             CONSTRAINT pk_billables PRIMARY KEY (billable_id),
             CONSTRAINT uc_billable_code_unique_per_account UNIQUE
    ("account_id", "code"),
         );
    CREATE TABLE
    *billables_links*
             (
                 billable_link_id BIGINT DEFAULT
    "nextval"('"dm"."billables_links_billable_link_id_seq"'::
                 "regclass") NOT NULL,
                 billable_id BIGINT NOT NULL,
                 customer_id BIGINT,
                 role_id BIGINT,
                 mobiuser_id BIGINT,
                 CONSTRAINT pk_billables_links PRIMARY KEY
    (billable_link_id),
                 CONSTRAINT fk_billable_must_exist FOREIGN KEY
    (billable_id) REFERENCES billables
                 (billable_id),
                 CONSTRAINT cc_one_and_only_one_target CHECK
    ((((("customer_id" IS NOT NULL))::INTEGER + (
                 ("role_id" IS NOT NULL))::INTEGER) + (("mobiuser_id" IS
    NOT NULL))::INTEGER) = 1)

Would it not be easier if instead of customer_id, role_id, mobiuser_id you had id_type('customer', 'role', 'mobi') and user_id(id). Then you could eliminate the CHECK, which as far as I can see is just restricting entry to one user id anyway.

             );
    CREATE TABLE
    *ja_mobiusers*
                 (
                     id BIGINT DEFAULT
    "nextval"('"ja_mobiusers_id_seq"'::"regclass") NOT NULL,
                     clientid BIGINT DEFAULT 0,
    [...]
                     PRIMARY KEY (id),
                     CONSTRAINT fk_account_must_exist FOREIGN KEY
    (clientid) REFERENCES ja_clients (id),
                 );

  

INSERT INTO dm.billables_links (billable_id, mobiuser_id)
SELECT billable_id
    , (SELECT id
       FROM   public.ja_mobiusers
       WHERE  name_first LIKE 'Anthon%'
       AND    name_last LIKE 'Swile%') AS foo  -- alias irrelevant
FROM   dm.billables
WHERE  info ILIKE '%Anthon%' AND info ILIKE '%Swile%' AND account_id = 32152 ;