Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2010
    Location
    Netherlands
    Posts
    6

    Unanswered: Updating a sequence of datetimes in fields with unique contrains

    Dear people,
    I am faced with the following problem; I need to update all fields of type datetime without ... with the following sort data:

    01-01-2010 12:10:30.256 - this is just an example

    these fields are restricted with a unique constrained. My question is how can I generate the above sort data, especially the last three numbers with leading point. After that how can I update a table with existing data with this newly generated data so that the fields really do carry unique data.
    As you probably can read out of my question, I read through the help in pgAdmin and came as far as generating date, maybe datetime but not the .123 number. And a little insert script I found in the help, that I modified as UPDATE resulted in updating only the last generated data in all required fields.
    So any help is appreciated for a script that resolves above problem.

    With thanks in advanced.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Please:

    Post the DDL for your table(s)
    Post sample DATA as INSERT statements
    Post the expected outcome of the statement you are looking for.

  3. #3
    Join Date
    Mar 2010
    Location
    Netherlands
    Posts
    6
    Hi,

    thanks for replying. Here is what I need to insert (actually update as there is already data in other collums of the table):

    Code:
    INSERT INTO res_parnter_event (create_date) VALUES (01-01-2010 12:10:30.256)
    This has to be down with 32000 fields with a unique value. The script I found in the PgADMIN help was this which I modified with not much success.

    Code:
    DECLARE  @J, @T, @G;
    SET @G1 = DATETIME('2008-05-01 14:00:00', '2008-05-05 15:00:00', 1); /* Random date generator
                              
    BEGIN
        SET @T = 'res_partner_event SET create_date';
    
        SET @J = 0;
        WHILE @J < 20
        BEGIN
            UPDATE @T = (@G1);
            SET @J = @J + 1;
        END
    
    END
    The expected outcome should be as mentioned earlier something like this:

    1920-10-29 00:00:00.100
    1920-10-30 00:00:00.101
    1920-10-31 00:00:00.102
    1920-11-01 00:00:00.103
    1920-11-02 00:00:00.104
    1920-11-03 00:00:00.105
    1920-11-04 00:00:00.106
    1920-11-05 00:00:00.107
    1920-11-06 00:00:00.108
    1920-11-07 00:00:00.109

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    I don't understand you example. Why would you want to advance 1 day and 1 millisecond? I'm going to assume you really wanted to advance just 1 millisecond.
    Code:
    SELECT '1920-10-01'::timestamp + '1ms'::interval * i
    FROM generate_series(0,32000) i

  5. #5
    Join Date
    May 2008
    Posts
    277
    I'm assuming that the column 'create_date' is already of type TIMESTAMP(3). If not, that's the first thing you need to do.

    You haven't specified what you want to initialize the blank values to. Initializing the values can be as easy as:
    Code:
    UPDATE res_parnter_event 
    SET create_date = CURRENT_TIMESTAMP
    WHERE create_date IS NULL
    In terms of enforcing uniqueness, timestamps are not a great choice. By design, CURRENT_TIMESTAMP uses the start time of the transaction, so every time it's used within the same transaction, the same time will be returned.

    The (non-standard) clock_timestamp() function returns the _actual_ current time, but 3 units of precision is too coarse to show differences in time (my computer can update at least 20 rows in less than .001 sec). I have to set the column to at least TIMESTAMP(5), but I imagine this will vary from computer to computer depending on the processor speed.

    I suppose you can try to create a function to artificially enforce unique times, but if you're just gonna make up times anyway, then I'd suggest taking a moment and asking yourself what you're actually trying to accomplish. Unless you have a need for extremely accurate timestamps, I think it's more likely you'll just want to add a unique SERIAL column to your table and not worry about unique timestamps.

    And for what it's worth, the script that you apparently found in pgAdmin was, uh, apparently not written for PostgreSQL. I'd recommend having a look at the documentation on procedural languages for how to properly write a function.

  6. #6
    Join Date
    Mar 2010
    Location
    Netherlands
    Posts
    6
    Hi futurity,

    thank you very much. The timestamp is not my choice it is a field that OpenERP uses. I am transferering data from SQLBase to PostgreSQL that OpenERP uses.

    The result that I get is this:

    "2010-03-16 16:13:14.747"
    "2010-03-16 16:13:14.747"
    "2010-03-16 16:13:14.747"
    "2010-03-16 16:13:14.747"
    "2010-03-16 16:13:14.747"

    what I need is something like this:

    "2010-03-16 16:13:14.745"
    "2010-03-16 16:13:14.746"
    "2010-03-16 16:13:14.747"
    "2010-03-16 16:13:14.748"
    "2010-03-16 16:13:14.749"

    to comply with the restrained of uniqueness.

  7. #7
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Ok, now I see where you are going with this.

    This is a tricky one. You didn't mention which version you were using but here is a solution that will work on 8.4.

    Code:
    UPDATE dbf_softsmid t
    SET ts = new_ts
    FROM (
        SELECT ts, ctid, ts + interval '1 ms' * 
        (rank()  
        OVER (PARTITION BY ts ORDER BY ctid) - 1) new_ts
        FROM dbf_softsmid 
    ) sub
    WHERE t.ctid = sub.ctid
    The query uses the system column ctid (its hidden but its there) to order by and to uniquely identify each row. That will ensure that your values remain ordered by their original insertion order. If you have some other column that uniquely identifies each row feel free to substitute that.

  8. #8
    Join Date
    Mar 2010
    Location
    Netherlands
    Posts
    6
    artacus72, thanks for the script. Unfourtunatly I am stuck with 8.3 I modified the script like this:

    UPDATE res_partner_event t
    SET create_date = new_ts
    FROM (
    SELECT create_date, ctid, create_date + interval '1 ms' *
    (rank()
    OVER (PARTITION BY create_date ORDER BY ctid) - 1) new_ts
    FROM res_partner_event
    ) sub
    WHERE t.ctid = sub.ctid
    and the script gives me this output:

    ERROR: syntax error at or near "OVER"
    LINE 6: OVER (PARTITION BY create_date ORDER BY ctid) - 1) new_ts
    ^

    ********** Fout **********

    ERROR: syntax error at or near "OVER"
    SQL status:42601
    Karakter:126
    Something I did wrong with the substitution or is it the older version of PostgreSQL.

    Thanks for helping

  9. #9
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Yeah, the analytic functions (rank over partition) were introduced in 8.4.

    Unfortunately there is not an elegant way to do this w/o them. So now you'll have to walk a cursor.

  10. #10
    Join Date
    Mar 2010
    Location
    Netherlands
    Posts
    6
    Well,

    I think I better upgrade, as we are talking about 40000 steps to walk. Is upgrading as simple as saving/dumping the database and installing the v8.4 to then reload the database?

    Any pittfalls?

  11. #11
    Join Date
    May 2008
    Posts
    277
    Try this:
    Code:
    CREATE FUNCTION update_timestamp(ref_time TIMESTAMP)
    RETURNS VOID
    AS $$
    DECLARE
        curr_ctid   res_partner_event.ctid%TYPE;
        offset      INTERVAL := '.001 second';
        curr_time   TIMESTAMP := ref_time;
    BEGIN
        FOR curr_ctid IN SELECT ctid FROM res_partner_event ORDER BY ctid LOOP
            UPDATE res_partner_event
            SET create_date = curr_time
            WHERE ctid = curr_ctid;
    
            curr_time := curr_time + offset;
        END LOOP;
    END;
    $$ LANGUAGE plpgsql;
    Run as follows (or with your own timestamp, if you want):
    Code:
    SELECT update_timestamp(CURRENT_TIMESTAMP::timestamp)
    Since this function's probably only useful for intializing your table, you'll probably just want to drop the function after you've used it.

  12. #12
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    This will work but you need to add some functions to 8.3... which are nice to have anyhow.
    Code:
    -- add an array aggregator
    CREATE AGGREGATE array_agg(anyelement) (
    SFUNC=array_append,
    STYPE=anyarray,
    INITCOND=’{}’
    );
    
    -- Add function to find index in array
    CREATE OR REPLACE FUNCTION idx(anyarray, anyelement)
      RETURNS int AS 
    $$
      SELECT i FROM (
         SELECT generate_series(array_lower($1,1),array_upper($1,1))
      ) g(i)
      WHERE $1[i] = $2
      LIMIT 1;
    $$ LANGUAGE sql IMMUTABLE;
    
    -- now we can update
    UPDATE dbf_softsmid sm
    SET ts = sm.ts + interval '1ms' * idx
    FROM (
        SELECT t.ts, t.ctid, idx(sub.ctids, t.ctid) - 1 AS idx
        FROM dbf_softsmid t
        JOIN (
            SELECT ts, array_agg(ctid) ctids
            FROM dbf_softsmid 
            GROUP BY ts
            HAVING COUNT(1) > 1
        ) sub ON t.ts = sub.ts
    ) sub2 WHERE sm.ts = sub2.ts AND sm.ctid = sub2.ctid

  13. #13
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Futurity, I think he still wants to keep the original timestamps... to within a few milliseconds. Your proc would wipe them out.

  14. #14
    Join Date
    May 2008
    Posts
    277
    I'm under the impression he's attempting to generate initial, unique timestamp values from scratch, although maybe something's getting lost in translation. Well, whatever he's trying to do, looks like he's got a solution either way.

  15. #15
    Join Date
    Mar 2010
    Location
    Netherlands
    Posts
    6
    futurity,

    this is the output I am getting, there are three digits to many. Thanks to both of you.

    "2010-03-16 20:06:53.377958"
    "2010-03-16 20:06:53.376958"
    "2010-03-16 20:06:53.375958"
    "2010-03-16 20:06:52.816965"
    "2010-03-16 20:06:52.815965"
    "2010-03-16 20:06:51.981975"
    "2010-03-16 20:06:51.922976"

    artacus72

    Your idea is nice to keep the original date, somehow I do not get your script to work.
    I am still checking it out.

Posting Permissions

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