OOO and postgres

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 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