Issue #145 invalid

PostgreSQL statements and ARRAY[] values return error

Anonymous created an issue

The Pg gem does not seem to play nicely with the ARRAY[] data columns supported by a modern PostgreSQL.

Using the '{"this","that"}' syntax works as expected, but using ARRAY['this','that'] does not. PostgreSQL returns an error saying that the first element must be a { or a dimension.

Prepared statements work as expected on the console, so I am unsure what is going on deeper, but have not yet had time to look either. I wanted to get the defect recorded just in case someone else got to it before I did.

Here is a console session which works:

foo_development=# CREATE TABLE foo (strings character varying[]);
CREATE TABLE
foo_development=# PREPARE stmt AS INSERT INTO foo VALUES ($1);
PREPARE
foo_development=# EXECUTE stmt (ARRAY['this', 'that']);
INSERT 0 1

Here is a Ruby script which fails:

require 'pg'
c = PG::Connection.new(dbname: 'foo_development')
c.exec("INSERT INTO foo VALUES ($1)", ["ARRAY['this','that']"])
# this one works:
#c.exec("INSERT INTO foo VALUES ($1)", ['{this,that}'])

in `exec_prepared': ERROR:  array value must start with "{" or dimension
information (PG::Error)
from pg-prepare-defect.rb:3:in `<main>'

Comments (4)

  1. Michael Granger repo owner

    As you point out above, the error in question comes from PostgreSQL itself, not from the gem. The gem just maps methods into the equivalent functions of the client library. You can see the same error from the console if you use the equivalent commands instead of prepared statements:

    test=> CREATE TABLE foo (strings character varying[]);
    CREATE TABLE
    
    test=> INSERT INTO foo VALUES (ARRAY['this','that']);
    INSERT 0 1
    
    test=> \set values ARRAY['this', 'that']
    test=> INSERT INTO foo VALUES(:'values');
    ERROR:  array value must start with "{" or dimension information
    LINE 1: INSERT INTO foo VALUES('ARRAY[this,that]');
                                   ^
    test=> \set values {this,that}
    test=> INSERT INTO foo VALUES(:'values');
    INSERT 0 1
    

    However, I wasn't able to use the 'ARRAY[]' syntax with PG::Connect#prepare and #exec_prepared, either. I tried:

    require 'pg'
    
    c = PG.connect( dbname: 'test' )
    
    # this one works:
    c.exec( "DROP TABLE IF EXISTS foo" )
    c.exec( "CREATE TABLE foo (strings character varying[]);" )
    
    # But using a prepared statement works:
    c.set_error_verbosity( PG::PQERRORS_VERBOSE )
    c.prepare( 'stmt', "INSERT INTO foo VALUES ($1);" )
    c.exec_prepared( 'stmt', ["ARRAY['this','that']"] )
    

    which results in the same error:

    $ ruby sample/array_insert.rb 
    sample/array_insert.rb:16:in `exec_prepared': ERROR:  22P02: array value must
     start with "{" or dimension information (PG::Error)
    LOCATION:  array_in, arrayfuncs.c:276
    from sample/array_insert.rb:16:in `<main>'
    

    Looking into the PostgreSQL source (from the LOCATION, which shows up if you set PG::PQERRORS_VERBOSE), and the source explicitly states:

    /*
     * If the input string starts with dimension info, read and use that.
     * Otherwise, we require the input to be in curly-brace style, and we
     * prescan the input to determine dimensions.
     *
     * Dimension info takes the form of one or more [n] or [m:n] items. The
     * outer loop iterates once per dimension item.
     */
    

    and the section of the manual covering Array input and output seems to say the same thing:

    The external text representation of an array value consists of items that are interpreted according to the I/O conversion rules for the array's element type, plus decoration that indicates the array structure. The decoration consists of curly braces ({ and }) around the array value plus delimiter characters between adjacent items.

    As to why it works to use the ARRAY[] form from the console, I don't know.

    Hope this helps.

  2. Log in to comment