Subject: Re: Fail to create distributed table in Citus
To: pljava-dev@lists.postgresql.org
References: 
 <CAB0C2fn42hT7RQGK+MwTG-rnxj6eKEhnBr3vyjvZsVOu0gv7qQ@mail.gmail.com>
 <5FB6AB35.5060101@anastigmatix.net>
 <CAB0C2fnf6yw6vgtrCkmD6Y8tTV-o3Vh1YQAvjY7DtqTQGTMqbw@mail.gmail.com>
From: Chapman Flack <chap@anastigmatix.net>
Message-ID: <5FB6BF4C.3060209@anastigmatix.net>
Date: Thu, 19 Nov 2020 13:54:04 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.7.0
MIME-Version: 1.0
In-Reply-To: 
 <CAB0C2fnf6yw6vgtrCkmD6Y8tTV-o3Vh1YQAvjY7DtqTQGTMqbw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Precedence: bulk

On 11/19/20 13:20, Erik Mata wrote:
> ...
>     stmt.executeQuery("SELECT create_distributed_table('test1', 'a',
> colocate_with => 'none')");
> }
> ...
> I was wondering if it could be something with the single quotes surrounding
> the parameters, but I've tried to use double single quotes and also to
> escape the single quotes, but then it really fails with an error. Looking

No, that's fine the way you've written it, as long as those values are
constant strings as you've shown.

The times you want to be careful are when the parameters are being passed
to you and you don't know what characters they might contain. In those cases
you would typically use

  ps = connection.prepareStatement(
    "SELECT create_distributed_table(?, ?, colocate_with => ?)");
  ps.setString(1, tableName);
  ps.setString(2, columnName);
  ps.setString(3, colocated);
  ps.execute();

so the driver takes care of how to pass the parameters correctly.

With data-definition language like CREATE TABLE, it's a little different,
because it can't be prepared and parameterized like a DML statement.
Those are the cases where, if you were being passed the table name or
column name, you would have to be careful about constructing the
CREATE TABLE and using the right quoting rules, getting familiar with
Statement.enquoteIdentifier() or, even better,
org.postgresql.pljava.sqlgen.Lexicals.Identifier.deparse(). And there are
more tricks to getting that right than I should go into here, because
they're probably off-topic for this particular issue.

Regards,
-Chap