Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2012
    Posts
    3

    Unanswered: Multi-dimensional columns

    Hi guys

    I'm a little confused about creating multi-dimensional columns.

    Is the two CREATE TABLE statements below the same?

    CREATE TABLE t_test ( id BIGSERIAL, sa1 text[]);

    CREATE TABLE t_test ( id BIGSERIAL, sa1 text[][]);

    sa1 is declared one-dimensional in the first and two-diamensional in the second.

    BUT taking a pg_dump of the two-dimensional sa1 is ONLY showing up as one-dimensional?

    Using pgAdmin also shows the two-dimensional sa1 as one-dimensional.

    Only EMS SQL Manager is showing sa1 as a two-dimensional.

    My question is: is there no need to declare an array as multidimensional? can you just use a single-dimensional array as a multi-dimensional?


    Kind regards, Ole

  2. #2
    Join Date
    Nov 2003
    Posts
    2,912
    Provided Answers: 7
    I think this is by design:
    Quote Originally Posted by The fine manual
    The current implementation does not enforce the declared number of dimensions either. [...] So, declaring the array size or number of dimensions in CREATE TABLE is simply documentation; it does not affect run-time behavior.
    From: http://www.postgresql.org/docs/curre...ic/arrays.html
    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

  3. #3
    Join Date
    Nov 2012
    Posts
    3
    Quote Originally Posted by shammat View Post
    I think this is by design:
    From: PostgreSQL: Documentation: 9.2: Arrays
    Thanks shammat

    Your right and shame on me.. I was browsing through this doc but obviously too fast.

    The funny thing is that EMS Manager somehow is able to save information about multidimensional arrays. But browsing information_schema.element_columns and information_schema.element_types does not reveal any information on multidimensional.

    Anyway, thanks again for showing me the light. Ole

  4. #4
    Join Date
    Nov 2003
    Posts
    2,912
    Provided Answers: 7
    Quote Originally Posted by tetzschner View Post
    The funny thing is that EMS Manager somehow is able to save information about multidimensional arrays. But browsing information_schema.element_columns and information_schema.element_types does not reveal any information on multidimensional.
    That's interesting indeed. psql also only shows integer[]. The JDBC driver simply returns _int4 for those kind of columns.
    I guess EMS Manager gets it from pg_attribute.attndims
    Last edited by shammat; 11-08-12 at 04:16.
    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
    Nov 2012
    Posts
    3
    Quote Originally Posted by shammat View Post
    That's interesting indeed. psql also only shows integer[]. The JDBC driver simply returns _int4 for those kind of columns.
    I guess EMS Manager gets it from pg_attribute.attndims
    Shammat you are sharp as a razorblade. It is in pg_attribute.attndims this information is stored.

    I'm not familiar with JDBC (using dotConnect from Devart and Npgsql) but looking in information_schema.columns, data_type=ARRAY and udt_name=_int4, so maybe JDBC is taking the information from udt_name.

    Anyway you can get the correct information from information_schema.element_types.

    PostgreSQL: Documentation: 9.2: element_types

    Kind regards, Ole

  6. #6
    Join Date
    Jan 2016
    Posts
    13
    Does NpgSQL 3.0+ support a way to pass multidimensional array?

  7. #7
    Join Date
    Jan 2016
    Posts
    13
    Never mind, I was able to do it. Thanks!

  8. #8
    Join Date
    Nov 2003
    Posts
    2,912
    Provided Answers: 7
    Quote Originally Posted by a_nndy View Post
    Does NpgSQL 3.0+ support a way to pass multidimensional array?
    Postgres does not have multidimensional arrays: As I have quoted right at the beginning:

    Quote Originally Posted by The fine manual
    So, declaring the array size or number of dimensions in CREATE TABLE is simply documentation; it does not affect run-time behavior.
    And because there is no such thing as a multidimensional array in Postgres, you don't need to pass one.
    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

Posting Permissions

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