Обсуждение: 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
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
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
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
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
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
>
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