Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    manila
    Posts
    9

    Unanswered: sql script how to?

    good day to all.
    can anybody teach me how to create a sql script. my main goal are:
    1. dump first a postgresql data from network to my local drive.
    2. then after dumping or copy the data it will create a update with update command.
    3. the last is to insert some data.

    right now im inserting a data one by one at a time to postgres server and it was really time consuming.
    i just heared this creating sql script to generate all my dump, update and insert inn one task, but i dont know how to create the script.

    it gives me idea like this i found in http://www.informit.com/articles/article.asp?p=24691

    [hs@athlon soundex]$ psql data < soundex.sql
    CREATE
    CREATE
    we write a small SQL script containing the data we want to insert into the database:

    CREATE TABLE persons(id int4, name varchar(30));
    COPY persons FROM stdin;
    1 Paul
    2 Alex
    3 Epi
    4 Eppi
    5 Ebi
    6 Everlast
    \.

    i post here my data for you:
    database name: polmap_akbayan
    tables:
    party_list_cocv[party_list_cocv_id, province_code, municipality_code,party_list, party_list_votes, election_year],
    municipalities[municipality_code, municipality_name, db_user],
    provinces[province_code, province_name, db_user],
    party_list_orgs[party_id, party_long_name, db_user]
    ----------------
    every day im updating this data by inserting lots of records that missed out from real time encoding.
    with this query:
    insert into party_list_cocv (party_list_cocv, province_code, municipality_code, party_list, party_list_votes, election_year) values ('20009', '030990000','030991000','01','200','2004');
    ---------------
    then again if i found another missing records iL insert again this and change those values one by one...

    hope u could help me to ease my life and save time from creating a SQL script..
    thanks in advance..

  2. #2
    Join Date
    Mar 2004
    Posts
    110
    I dont realy understand what your goal is.

    When you dump your db, it will create a .sql file.

    -Ed

  3. #3
    Join Date
    Mar 2004
    Location
    manila
    Posts
    9
    Originally posted by Edje
    I dont realy understand what your goal is.

    When you dump your db, it will create a .sql file.

    -Ed
    OK. thanks for reply.

    id try again to make it simple.
    my main goal is to create a script that will dump a data from our server too my local PC. the content of the script would be execute sql command in one shot:
    1. dump data
    2. update and insert some rows to table.

    i dump the table party_list_cocv_problematic from our server to my local as a copy and i used this command to dump my data.

    ]$ pg_dump -t party_list_cocv_problematic -h 192.100.0.98 -U vvbuenaventura polmap_akbayan;

    but it doesnt give me a result.

  4. #4
    Join Date
    Mar 2004
    Posts
    110
    You are able to make a dump file to your server?

    For example:
    pg_dump my_database_name > my_backup_file.sql

    Im using cygwin (im on a windows2003 server).

    Im still not sure what your trying to do tho... but lets take it one step at a time.

    -Ed

  5. #5
    Join Date
    Mar 2004
    Location
    manila
    Posts
    9
    Originally posted by Edje
    You are able to make a dump file to your server?

    For example:
    pg_dump my_database_name > my_backup_file.sql

    Im using cygwin (im on a windows2003 server).

    Im still not sure what your trying to do tho... but lets take it one step at a time.

    -Ed
    Hi edje thanks for your reply.

    from your given ssimple dump i used this:

    pg_dump polmap_akbayan > test_polmap.sql -h 192.000.00.100 -U buenaventura

    after the sql created i restored this by issuing:

    psql test < test_polmap.sql

    and give me this error result:
    You are now connected as new user dbadmin.
    SET
    NOTICE: CREATE TABLE will create implicit sequence 'party_list_cocv_party_list_cocv_id_seq' for SERIAL column 'party_list_cocv.party_list_cocv_id'
    ERROR: Relation 'party_list_cocv_party_list_cocv_id_seq' already exists
    ERROR: party_list_cocv: must be owner
    ERROR: party_list_cocv_party_list_cocv_id_seq: must be owner
    ERROR: party_list_cocv: permission denied
    invalid command \N
    invalid command \.
    ERROR: parser: parse error at or near "3186" at character 1
    NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
    ERROR: party_list_cocv: must be owner
    NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index 'party_list_cocv_election_year_key' for table 'party_list_cocv'
    ERROR: party_list_cocv: must be owner
    ----

    what went wrong or wht this could error mean? what should i do to fix them?
    is my table *CONSTRAINTS* has todo with this? shhould i remove it? how?
    hope you could help me again..

  6. #6
    Join Date
    Mar 2004
    Posts
    110
    Looks like you got several errors:
    the first one:

    NOTICE: CREATE TABLE will create implicit sequence 'party_list_cocv_party_list_cocv_id_seq' for SERIAL column 'party_list_cocv.party_list_cocv_id'
    ERROR: Relation 'party_list_cocv_party_list_cocv_id_seq' already exists

    The notice you get here is fine, it just tells you you are creating a sequence: 'party_list_cocv_party_list_cocv_id_seq'
    but the error that comes with it: ERROR: Relation 'party_list_cocv_party_list_cocv_id_seq' already exists

    means that the sequence: 'party_list_cocv_party_list_cocv_id_seq'
    already excists in the db you are trying to create

    The 3 errors below:

    ERROR: party_list_cocv: must be owner
    ERROR: party_list_cocv_party_list_cocv_id_seq: must be owner
    ERROR: party_list_cocv: permission denied

    looks to me are related to ownership. Every time someone makes a table he automaticly becomes the owner.

    Im i right to presume that you have a main server where ppl do inserts, updates and deletes on and you only want to backup the stuff on another db?
    If so, a pg_dump isnt the best way to do it.
    Actually, as far as i know, there is no log file like you have in MS SQL SERVER2000 where all inserts, updates and deletes are stored in a txt file.

    If you really want to use the current .sql file you have, you could destroy the complete db on the server you want to recreate is (not the main server) and then create the db and restore the .sql file.

    Am i right that you want only the insert, updates and deletes from the main db server to some kind of backup server on another location?
    If so, there are some better solutions i think.

Posting Permissions

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