Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2013
    Posts
    6

    Unanswered: Table with mor than 1600 columns

    Hey!
    It is possible to create postgre tables with more than 1600 columns?
    If true:
    How must I my PG conconfigure?

    If false:
    Is there any freeware db with table row numer greater than 10000?

    Thanks

    Horst

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why would a table need 1600 columns? This is often (not always) an indication of incorrect design.

    There are a number of free database platforms that allow for more than 10,000 rows of data...
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2013
    Posts
    6
    Quote Originally Posted by gvee View Post
    Why would a table need 1600 columns? This is often (not always) an indication of incorrect design.
    I know it is incorrect design.
    I need it for one analysis solution!
    Can you tell How must I my PG conconfigure?

    Quote Originally Posted by gvee View Post
    There are a number of free database platforms that allow for more than 10,000 rows of data...
    Can you tell me what they are?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by horst_skoff View Post
    It is possible to create postgre tables with more than 1600 columns?
    That's not only a bad design it is a totally broken design. There is absolutely no way to do this in Postgres.

    You might want to look into the hstore datatype. It's essentially a key/value datatype that let's you store multiple keys (up to 65000 if I'm not mistaken) in a single column.

    Is there any freeware db with table row numer greater than 10000?
    Any DBMS - including Postgres - can store more than 10.000 rows (you probably confused rows with columns in that sentence)

    Why don't you tell us your real problem. Most probably with a good DB design you don't need 10000 columns.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Jun 2013
    Posts
    6
    Quote Originally Posted by shammat View Post
    That's not only a bad design it is a totally broken design. There is absolutely no way to do this in Postgres..
    I know too.

    Quote Originally Posted by shammat View Post
    You might want to look into the hstore datatype. It's essentially a key/value datatype that let's you store multiple keys (up to 65000 if I'm not mistaken) in a single column.
    .
    I have a table named product with more than 1600 entries. (simplified)
    And I have a table order with more than 1600 entries. (simplified)
    The tabel I want to create:
    each column == one product_id
    each row == one order_id

    So I want to fill the table to use later as an analysis matrix to see
    any product is orded in any order.
    If true set 1, if false set 0.
    Like:
    0;1;0;0;1...
    0;0;0;1;0...
    ...

    when it is finished export the table entries in a textfile.

    The the file want to anaysis to other external program.

    You undderstand what I mean.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You apparently have never heard the term "normalisation".

    This is a simple case of an n:m relationship. Actually it's the "hello world" example for this and is probably the first lesson in any textbook regarding this pattern.

    The solution is pretty simply:
    Code:
    create table products
    ( 
       product_id integer not null primary key, 
       product_name text not null
    );
    
    create table orders 
    (
       order_id integer not null primary key,
       order_date date not null
    );
    
    create table product_orders
    (
       product_id integer not null,
       order_id integer not null,
       amount integer not null,
       primary key (product_id, order_id),
       constraint fk_po_products
          foreign key (product_id)
          references products (product_id),
       constraint fk_po_orders
          foreign key (order_id)
          references orders (order_id)
    );
    You can check if a product is in an order by simply querying the product_orders table.

    I don't understand the example output you gave.

    Please show us some sample data and the expected output. Iideally as a http://www.sqlfiddle]SQLFiddle example.

    I strongly recommend you read up on database design and normalization before you continue with your project.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  7. #7
    Join Date
    Jun 2013
    Posts
    6
    Hey!
    thanks a lot for your constant efforts.
    I try to explain again.
    First:
    The table product and order and other tables alreay exsits for a long time.
    In a function a want to create a new table neuronale_netze or so.
    than I make a loob to add for each product a new column. The name of the column is the id of the product.
    Code:
    CREATE OR REPLACE FUNCTION neuronale_netze()
      RETURNS integer AS
    $BODY$ 
        DECLARE  
             
             userProduct record;
             prod_id_str TEXT;
             count Integer;
             
    	 
        BEGIN 
    	CREATE Table neuronale_netze();
    	count := 1;
    	For userProduct IN (select prod_id from product where master_prod_id is not null and prod_id is not null and prba_id = 1 order by prod_id)
    	
    		LOOP 
    		
    		IF COUNT < 1599 THEN -- Postgres not more than 1600 columns
    			prod_id_str := '' || userProduct.prod_id;
    			-- Alter Table neuronale_netze add column
    
    			EXECUTE ' ALTER TABLE ' || 'neuronale_netze' || ' ADD COLUMN ' || quote_ident(prod_id_str) || ' ' || 'INTEGER';
    
    			count := count +1;
    
    		END IF;
    		END LOOP;
    
    		-- Last add two needed columns
    		Alter Table neuronale_netze add column DHL INTEGER;
    		Alter Table neuronale_netze add column GLS INTEGER;
    
    
    
    	RETURN 0;
             
       END;  $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100;
    Second:
    I want to fill the table neuronale_netze by an other function
    CREATE OR REPLACE FUNCTION neuronale_netze_order()
    RETURNS integer AS
    $BODY$
    DECLARE

    userOrder record;

    prodArrayStr TEXT;
    amountArrayStr Text;

    count Integer;
    productArray INTEGER[];
    amountArray INTEGER[];



    BEGIN

    Drop Table neuronalenetze_order if exsits;
    -- All order with available products
    CREATE table neuronalenetze_order AS (SELECT _order.ohed_id, _item.prod_id, _item.amount
    FROM order_head _order, order_item _item
    WHERE _order.purchase_date >= to_date('2013-01-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss') and (_order.clie_id = ANY (ARRAY[1, 2])) AND _order.ohed_id = _item.ohed_id AND (_order.osta_id = ANY (ARRAY[60])) AND (_item.prod_id IN ( SELECT product.prod_id
    FROM product
    WHERE product.master_prod_id IS NOT NULL and prba_id = 1 ))
    group by _order.ohed_id, _item.prod_id, _item.amount order by _item.prod_id, _order.ohed_id);


    count := 1;
    For userOrder IN (select * from neuronalenetze_bestellung order by ohed_id)

    LOOP

    count := count +1;
    amountArray := (select array(select amount from neuronalenetze_order where ohed_id = userOrder.ohed_id));
    productArray := (select array(select prod_id from neuronalenetze_order where ohed_id = userOrder.ohed_id));

    amountArrayStr := (select array_to_string(amountArray, ','));
    prodArrayStr := (select array_to_string(productArray, ','));


    -- INSERT INTO neuronale_netze (column1,column2,column3,...) VALUES (value1,value2,value3,...);
    EXECUTE ' INSERT INTO neuronale_netze (' || prodArrayStr || ')' || 'VALUES(' || amountArrayStr || ')';




    END LOOP; -- END LOOP;



    RETURN count;

    END; $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;
    At Last (not implementet yet)
    Export the neuronale_netze to a text or csv file separated by ';' or so.

    The finding:
    The limit of columns is 1600.
    That is the motivation to ask for mor than 1600 columns in PG tables.

    Can you follow my illustration?

    Horst
    Last edited by horst_skoff; 06-21-13 at 04:12.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Please show the definition (CREATE TABLE) for the two tables and how they relate together.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  9. #9
    Join Date
    Jun 2013
    Posts
    6
    Quote Originally Posted by shammat View Post
    Please show the definition (CREATE TABLE) for the two tables and how they relate together.
    Code:
    CREATE TABLE order_head
    (
      ohed_id integer NOT NULL,
      osta_id integer NOT NULL,
      pays_id integer NOT NULL,
      clie_id integer NOT NULL,
      cust_id integer NOT NULL,
      shme_id integer NOT NULL,
      pame_id integer NOT NULL,
      purchase_date timestamp without time zone
      
    )
    
    CREATE TABLE order_item
    (
      oitm_id integer NOT NULL,
      ohed_id integer NOT NULL,
      prod_id integer,
      
      amount integer NOT NULL,
      
      avai_id integer NOT NULL,
      
      master_prod_id integer,
      prgr_id integer,
     
      CONSTRAINT order_item_master_prod_id_fkey FOREIGN KEY (master_prod_id)
          REFERENCES product (prod_id) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION,
      CONSTRAINT order_item_ohed_id_fkey FOREIGN KEY (ohed_id)
          REFERENCES order_head (ohed_id) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION,
       CONSTRAINT order_item_prod_id_fkey FOREIGN KEY (prod_id)
          REFERENCES product (prod_id) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION
    ) 
    
    
    CREATE TABLE product
    (
      prod_id integer NOT NULL,
      master_prod_id integer,
      
      avai_id integer NOT NULL,
      prgr_id integer NOT NULL,
      
      active boolean NOT NULL DEFAULT false,
      
      available boolean NOT NULL DEFAULT false,
      priority boolean NOT NULL DEFAULT false,
     
      prba_id integer NOT NULL,
      prty_id integer NOT NULL,
      
      slave_name character varying(100)
    )
    It is only the importance statementems to see the relations I mean so.
    Order 1:n order_item (dissolve by object-model too)
    Order_item 1:1 product

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if the eventual target for this matrix is a CSV file then why not write a procedure that creates the CSV file using queries

    I don't see how you are going to present this data to a user in a single hit (there is no screen or printer that could handle all that so creating the table seems futile (even if the underlying SQL engine could represent that data matrix

    if you want to analyse on screen then develop your queries that only extraqct the data you need.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Jun 2013
    Posts
    6
    Quote Originally Posted by healdem View Post
    if the eventual target for this matrix is a CSV file then why not write a procedure that creates the CSV file using queries

    I don't see how you are going to present this data to a user in a single hit (there is no screen or printer that could handle all that so creating the table seems futile (even if the underlying SQL engine could represent that data matrix

    if you want to analyse on screen then develop your queries that only extraqct the data you need.
    An interesting option.

    Where can I find a tutorial for working with files in postgres functions?

Posting Permissions

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