Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2002
    Location
    Toronto, Canada
    Posts
    76

    Unanswered: duplicating data in tables with dependencies

    I have a database that has 43 tables in it right now. Now, there are several referential constraints amongst the tables.

    At the moment, I have a user table which has a lot of dependencies. Suppose I have 2 users in the user table (i.e. user1 and user2), what's the best way to insert a new user called user3, make a copy of all the data refering to user2, and insert that data for user3?

    That is, make a copy of all the data for user2, and have it under user3.

  2. #2
    Join Date
    Sep 2002
    Location
    Toronto, Canada
    Posts
    76

    Example of what I wanna do

    BEFORE CHANGES

    Employees(emp_id, user_id, other):

    emp_id user_id other
    -----------------------
    1 1 user1_data1
    2 1 user1_data2
    3 1 user1_data3

    subordinates(id, emp_id, sample):

    id emp_id sample
    ------------------------
    1 2 a
    2 3 b


    AFTER ADDING another user with the first user's data

    Employees(emp_id, user_id, other):

    emp_id user_id other
    -----------------------
    1 1 user1_data1
    2 1 user1_data2
    3 1 user1_data3
    4 2 user1_data1
    5 2 user1_data2
    6 2 user1_data3

    subordinates(id, emp_id, sample):

    id emp_id sample
    ------------------------
    1 2 a
    2 3 b
    3 5 a
    4 6 b

  3. #3
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    pl/pgsql script

    I think the best solutioj for this is a pl/pgsql script

    parameter is the id from the user you want to copy the data:


    And then you can make such as this:

    SELECT DATA FROM TABLE WHERE USER=XXX

    Read the data and make inserts into the tables


    INSERT INTO ....
    INSERT INTO ....


    You call only from as sql SELECT newuser(2);
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •