Postgres Pro
Downloads
Home   >   mailing lists

Thread: OOO and postgres

OOO and postgres

From
Bernhard Rohrer
Date:
Hi guys

I am using the postgres driver for OOO and just ran into the following
error:

Error code: 1

pq_driver: [PGRES_FATAL_ERROR]ERROR:  array value must start with "{" or
dimension information
LINE 1: ...O "public"."Bladetypes" ( "ID","type") VALUES ( '1','Knife')
                                                                ^
  (caused by statement 'INSERT INTO "public"."Bladetypes" ( "ID","type")
VALUES ( '1','Knife')')

the table looks like this:

CREATE TABLE "Bladetypes"
(
   "ID" integer NOT NULL,
   "type" character varying[] NOT NULL,
   CONSTRAINT "Bladetypes_pkey" PRIMARY KEY ("ID")
)

ALTER TABLE "Bladetypes" ADD COLUMN "ID" integer;
ALTER TABLE "Bladetypes" ALTER COLUMN "ID" SET NOT NULL;

ALTER TABLE "Bladetypes" ADD COLUMN "type" character varying[];
ALTER TABLE "Bladetypes" ALTER COLUMN "type" SET NOT NULL;

is this for this list?

Thanks

Bernhard

Re: OOO and postgres

From
Adrian Klaver
Date:
On 01/07/2011 09:40 AM, Bernhard Rohrer wrote:
> Hi guys
>
> I am using the postgres driver for OOO and just ran into the following
> error:
>
> Error code: 1
>
>

If you are referring to OpenOffice and the native SDBC driver then from
the docs:

http://dba.openoffice.org/drivers/postgresql/index.html#features

"data types like clobs, blobs and arrays are not yet supported. The
whole datatype handling for non-standard datatypes is crippled
currently, here needs to be developed a concept first.


--
Adrian Klaver
adrian.klaver@gmail.com

Re: OOO and postgres

From
Rich Shepard
Date:
On Fri, 7 Jan 2011, Bernhard Rohrer wrote:

> CREATE TABLE "Bladetypes"
> (
>  "ID" integer NOT NULL,
>  "type" character varying[] NOT NULL,
>  CONSTRAINT "Bladetypes_pkey" PRIMARY KEY ("ID")
> )
>
> ALTER TABLE "Bladetypes" ADD COLUMN "ID" integer;
> ALTER TABLE "Bladetypes" ALTER COLUMN "ID" SET NOT NULL;
>
> ALTER TABLE "Bladetypes" ADD COLUMN "type" character varying[];
> ALTER TABLE "Bladetypes" ALTER COLUMN "type" SET NOT NULL;

   Don't use double quotes in your create table stanza. You can use them on
the table name with alter table and insert into.

   The data type is VARCHAR(), not character varying[].

   Why are you altering the table to be exactly how you defined it?

   Use single quotes to define text strings in your values statements.

   Perhaps you'll find value in reading a book on SQL. Rick van der Lans and
Joe Celko both write outstanding books on the language and its use.

Rich

Re: OOO and postgres

From
Susan Cassidy
Date:
Per the error message, you need to enclose array values in braces.  For example, something like:
INSERT into "Bladetypes" ("ID", "type"), values ('1', '{"Knife"}');

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Bernhard Rohrer
Sent: Friday, January 07, 2011 9:41 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] OOO and postgres

Hi guys

I am using the postgres driver for OOO and just ran into the following
error:

Error code: 1

pq_driver: [PGRES_FATAL_ERROR]ERROR:  array value must start with "{" or
dimension information
LINE 1: ...O "public"."Bladetypes" ( "ID","type") VALUES ( '1','Knife')
                                                                ^
  (caused by statement 'INSERT INTO "public"."Bladetypes" ( "ID","type")
VALUES ( '1','Knife')')

the table looks like this:

CREATE TABLE "Bladetypes"
(
   "ID" integer NOT NULL,
   "type" character varying[] NOT NULL,
   CONSTRAINT "Bladetypes_pkey" PRIMARY KEY ("ID")
)

ALTER TABLE "Bladetypes" ADD COLUMN "ID" integer;
ALTER TABLE "Bladetypes" ALTER COLUMN "ID" SET NOT NULL;

ALTER TABLE "Bladetypes" ADD COLUMN "type" character varying[];
ALTER TABLE "Bladetypes" ALTER COLUMN "type" SET NOT NULL;

is this for this list?

Thanks

Bernhard

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: OOO and postgres

From
Thomas Kellerer
Date:
Rich Shepard wrote on 07.01.2011 18:56:
> The data type is VARCHAR(), not character varying[].

character varying is a synonym for varchar, so the definition character varying[] is valid.
It defines an array of varchar and is equivalent to varchar[]

But I doubt that this is what the OP meant ;)

Regards
Thomas

Re: OOO and postgres

From
Bernhard Rohrer
Date:
What I am not getting is - as far as I can see none of the fields _is_
an array! So why would this error message appear?

On 07/01/11 17:40, Bernhard Rohrer wrote:
> Hi guys
>
> I am using the postgres driver for OOO and just ran into the following
> error:
>
> Error code: 1
>
> pq_driver: [PGRES_FATAL_ERROR]ERROR:  array value must start with "{"
> or dimension information
> LINE 1: ...O "public"."Bladetypes" ( "ID","type") VALUES ( '1','Knife')
>                                                                ^
>  (caused by statement 'INSERT INTO "public"."Bladetypes" (
> "ID","type") VALUES ( '1','Knife')')
>
> the table looks like this:
>
> CREATE TABLE "Bladetypes"
> (
>   "ID" integer NOT NULL,
>   "type" character varying[] NOT NULL,
>   CONSTRAINT "Bladetypes_pkey" PRIMARY KEY ("ID")
> )
>
> ALTER TABLE "Bladetypes" ADD COLUMN "ID" integer;
> ALTER TABLE "Bladetypes" ALTER COLUMN "ID" SET NOT NULL;
>
> ALTER TABLE "Bladetypes" ADD COLUMN "type" character varying[];
> ALTER TABLE "Bladetypes" ALTER COLUMN "type" SET NOT NULL;
>
> is this for this list?
>
> Thanks
>
> Bernhard
>

Re: OOO and postgres

From
Adrian Klaver
Date:
On 01/07/2011 10:16 AM, Bernhard Rohrer wrote:
> What I am not getting is - as far as I can see none of the fields _is_
> an array! So why would this error message appear?ion

The "type" field is an array type because of the square brackets after
the data type.

"type" character varying[] NOT NULL

The INSERT is not inserting the VALUES for an array correctly. To get
back to my original question, what driver are you using? If it is the
native SDBC one it does not know about arrays and is probably the cause
of the problem.

>
> On 07/01/11 17:40, Bernhard Rohrer wrote:
>> Hi guys
>>
>> I am using the postgres driver for OOO and just ran into the following
>> error:
>>
>> Error code: 1
>>
>> pq_driver: [PGRES_FATAL_ERROR]ERROR: array value must start with "{"
>> or dimension information
>> LINE 1: ...O "public"."Bladetypes" ( "ID","type") VALUES ( '1','Knife')
>> ^
>> (caused by statement 'INSERT INTO "public"."Bladetypes" ( "ID","type")
>> VALUES ( '1','Knife')')
>>
>> the table looks like this:
>>
>> CREATE TABLE "Bladetypes"
>> (
>> "ID" integer NOT NULL,
>> "type" character varying[] NOT NULL,
>> CONSTRAINT "Bladetypes_pkey" PRIMARY KEY ("ID")

>> Thanks
>>
>> Bernhard
>>
>
>


--
Adrian Klaver
adrian.klaver@gmail.com