Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    9

    Unanswered: Min/Max Hstore Type

    Hi...

    I have a table with two columns type is hstore.
    Here is the table code :

    CREATE TABLE calendar_resource
    (
    id serial NOT NULL,
    primary_resource character varying(100),
    status character varying(25),
    efficiency character varying(5),
    cal_max character varying(5),
    cal_min character varying(15),
    start_time character varying(15),
    end_time character varying(15),
    usage hstore NOT NULL DEFAULT '"h1"=>"0", "h2"=>"0", "h3"=>"0", "h4"=>"0", "h5"=>"0", "h6"=>"0", "h7"=>"0", "h8"=>"0", "h9"=>"0", "h10"=>"0", "h11"=>"0", "h12"=>"0", "h13"=>"0", "h14"=>"0", "h15"=>"0", "h16"=>"0", "h17"=>"0", "h18"=>"0", "h19"=>"0", "h20"=>"0", "h21"=>"0", "h22"=>"0", "h23"=>"0", "h24"=>"0"'::hstore,
    planned hstore NOT NULL DEFAULT '"h1"=>"0", "h2"=>"0", "h3"=>"0", "h4"=>"0", "h5"=>"0", "h6"=>"0", "h7"=>"0", "h8"=>"0", "h9"=>"0", "h10"=>"0", "h11"=>"0", "h12"=>"0", "h13"=>"0", "h14"=>"0", "h15"=>"0", "h16"=>"0", "h17"=>"0", "h18"=>"0", "h19"=>"0", "h20"=>"0", "h21"=>"0", "h22"=>"0", "h23"=>"0", "h24"=>"0"'::hstore,
    dates date,
    jam_sisa integer DEFAULT 0,
    CONSTRAINT fkey_calequip PRIMARY KEY (id)
    )
    WITH (
    OIDS=FALSE
    );
    ALTER TABLE calendar_resource OWNER TO ipscs;

    What's the query in postgresql if i want to know the minimum h in planned column?
    For example (data for planned column):
    ""h1"=>"2", "h2"=>"2", "h3"=>"2", "h4"=>"2", "h5"=>"2", "h6"=>"0", "h7"=>"0", "h8"=>"0", "h9"=>"0", "h10"=>"0", "h11"=>"0", "h12"=>"0""

    ""h1"=>"5", "h2"=>"5", "h3"=>"5", "h4"=>"0", "h5"=>"0", "h6"=>"0", "h7"=>"0", "h8"=>"0", "h9"=>"0", "h10"=>"0", "h11"=>"0", "h12"=>"0""

    If it's primary_resource,status,efficiently have same record for the two records above, what's the query to select the second record as a result because it's 0 value (h4) is lower than the first record (h6)

    Any help will be appreciated...thank's in advanced.

    Ricky

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I'm not sure I completely understood your question, but I think something like this (at least it should give you a headstart):

    Code:
    select primary_resource, 
           value, 
           key_, 
           first_value(key_) over (partition by primary_resource order by value asc)
    from (
      select primary_resource, 
             cast((each(planned)).value as integer) as value, 
             (each(planned)).key as key_
      from calendar_resource
    ) t
    where value = 0
    order by key_ 
    limit 1
    At least with my very limited simulated test data, this gives the result you expect but then I'm not sure if my test data is correct nor if the expected result is correct.

    For the future: posting the DDL for the table is a very good idea, but you should also supply some sample INSERT statements and the expected output based on the sample rows.

  3. #3
    Join Date
    Mar 2010
    Posts
    9

    Min/Max Hstore Type

    Thank's for your reply Mr Shammat..

    I'm making a work_scheduling module with php as front-end and postgresql as backend.

    I've created a table called calendar_resource that contains the time schedule for machine usage.

    code :
    CREATE TABLE calendar_resource
    (
    id serial NOT NULL,
    primary_resource character varying(100),
    status character varying(25),
    efficiency character varying(5),
    cal_max character varying(5),
    cal_min character varying(15),
    start_time character varying(15),
    end_time character varying(15),
    usage hstore NOT NULL ,
    planned hstore NOT NULL,
    dates date,
    jam_sisa integer DEFAULT 0,
    CONSTRAINT fkey_calequip PRIMARY KEY (id)
    )

    And for data example,
    code :
    insert into calendar_resource(id,primary_resource,status,effic iency,usage,planned,dates) values (1,'ROLL4','Day Shift','100%','"h1"=>"0", "h2"=>"0", "h3"=>"0", "h4"=>"0", "h5"=>"0", "h6"=>"0","h7"=>"0", "h8"=>"0", "h9"=>"0", "h10"=>"0", "h11"=>"0", "h12"=>"0"','"h1"=>"AA1", "h2"=>"AA1", "h3"=>"AA1", "h4"=>"AA1", "h5"=>"0", "h6"=>"0","h7"=>"0", "h8"=>"0", "h9"=>"0", "h10"=>"0", "h11"=>"0", "h12"=>"0"','2010-03-12');

    insert into calendar_resource(id,primary_resource,status,effic iency,usage,planned,dates) values (2,'ROLL4','Night Shift','100%','"h1"=>"0", "h2"=>"0", "h3"=>"0", "h4"=>"0", "h5"=>"0", "h6"=>"0","h7"=>"0", "h8"=>"0", "h9"=>"0", "h10"=>"0", "h11"=>"0", "h12"=>"0"','"h1"=>"0", "h2"=>"0", "h3"=>"0", "h4"=>"0", "h5"=>"0", "h6"=>"0","h7"=>"0", "h8"=>"0", "h9"=>"0", "h10"=>"0", "h11"=>"0", "h12"=>"0"','2010-03-12');

    insert into calendar_resource(id,primary_resource,status,effic iency,usage,planned,dates) values (3,'ROLL5','Day Shift','100%','"h1"=>"0", "h2"=>"0", "h3"=>"0", "h4"=>"0", "h5"=>"0", "h6"=>"0","h7"=>"0", "h8"=>"0", "h9"=>"0", "h10"=>"0", "h11"=>"0", "h12"=>"0"','"h1"=>"A20", "h2"=>"A20", "h3"=>"A20", "h4"=>"0", "h5"=>"0", "h6"=>"0","h7"=>"0", "h8"=>"0", "h9"=>"0", "h10"=>"0", "h11"=>"0", "h12"=>"0"','2010-03-12');

    insert into calendar_resource(id,primary_resource,status,effic iency,usage,planned,dates) values (4,'ROLL5','Night Shift','100%','"h1"=>"0", "h2"=>"0", "h3"=>"0", "h4"=>"0", "h5"=>"0", "h6"=>"0","h7"=>"0", "h8"=>"0", "h9"=>"0", "h10"=>"0", "h11"=>"0", "h12"=>"0"','"h1"=>"0", "h2"=>"0", "h3"=>"0", "h4"=>"0", "h5"=>"0", "h6"=>"0","h7"=>"0", "h8"=>"0", "h9"=>"0", "h10"=>"0", "h11"=>"0", "h12"=>"0"','2010-03-12');

    Roll 4 and Roll 5 is the machine (resource to be used) and they're divided into 2 shifts. Now the condition is Roll 4 is used from h1 (hour 1) to h4 (hour 4) in day shift. And Roll 5 is used from h1 (hour 1) to h3 (hour 3) also in day shift.
    If there's another job is queuing that use ROLL4 or ROLL5, the quickest resource available is ROLL5 starting at h4 (hour 4), since the ROLL4 is not available until h5 (hour 5).
    What's the query to select the h4 of ROLL5 from those available resource data (from calendar_resource table) above.

    Thank's for any help and reply.

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    I think you should normalize this stuff into another table rather than use hstore.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by artacus72 View Post
    I think you should normalize this stuff into another table rather than use hstore.
    Very true...

Posting Permissions

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