[Gambas-user] Gambas & PostgreSQL cannot create index. relation XXX does not exist.

[richard terry]

On Fri, 23 Jan 2009 02:01:15 am Randy wrote:
> Hello.
>
> New to Gambas and having problems with following code:
>
> STATIC PUBLIC FUNCTION CreateGLTables()
>
>   DIM hTable AS Table
>
>   hTable = Global.$hConn.Tables.Add("glcoa")
>   WITH hTable
>     .Fields.Add("company", db.String, 3)
>     .Fields.Add("account", db.String, 12)
>     .Fields.Add("description", gb.String, 32)
>     .Fields.Add("gltype", gb.String, 1)
>     .Fields.Add("glsort", gb.Integer)
>     .Fields.Add("status", gb.String, 1)
>     .PrimaryKey = ["account"]
>     .Indexes.Add("glcoa_account", "account", TRUE)
>     .Indexes.Add("glcoa_sort", "glsort", TRUE)
>     .Update
>    END WITH
>
> CATCH
>
>   Message.Error("CreateGLTables Function: " & DConv(Error.Text))
> END
>
>
> The error is "Cannot create index. relation glcoa does not exist." Why?
> Can not find any information on the internet about this error.
>
> Help :-(
Randy, though I use gambas extensively with a huge postgres database, I 
personally never use the gambas data controls, but that's just a matter of 
preference. 

I suspect  that if you delted the .Indexes lines it would work, and add them 
later.

However I do have a suggestion to you until someone else helps you. Simply 
duplicate the code in pgAdmin and look at whot error message it throws up

For example I guarentee that this will work in pgadmin (cause I just typed it 
in!):

--drop table public.glcoa cascade;
--drop table public.lu_status;
--drop table public.lu_glcoa_type;


create table public.glcoa
(pk SERIAL PRIMARY KEY,
company text ,
account text,
description text,
fk_gltype integer not null,
glsort integer,
fk_status integer not null)
;

CREATE TABLE public.lu_status (
  pk SERIAL primary key, 
  status TEXT NOT NULL);
  
CREATE TABLE public.lu_glcoa_type (
  pk SERIAL primary key, 
  gltype TEXT NOT NULL);

CREATE INDEX glcoa_idx ON public.glcoa
  USING btree (company, account);



  Create view 
public."vwGlcoa"  as (
SELECT 
  public.glcoa.pk,
  public.glcoa.company,
  public.glcoa.account,
  public.glcoa.description,
  public.glcoa.fk_gltype,
  public.glcoa.glsort,
  public.glcoa.fk_status,
  public.lu_glcoa_type.gltype,
  public.lu_status.status
FROM
  public.glcoa
  INNER JOIN public.lu_glcoa_type ON (public.glcoa.fk_gltype = 
public.lu_glcoa_type.pk)
  INNER JOIN public.lu_status ON (public.glcoa.fk_status = 
public.lu_status.pk));
  
Note I've used pk as the primary key, which seems to be good practice in 
postgres and allows you to do all sorts of things later. Note also that there 
seems in my research no advantage to use anything more than a straight TEXT 
field  i.e though you can use say char (2) char(20) etc, using straight TEXT 
dosn't penalise you.

Another comment. Its good programming practice to normalise your database. For 
example though I've no idea what text  you will have in glType  it could be 
removed to a lookup table as per above

Similarly with status.

Note I've put a couple of not null's in there, which you should also do for 
your primary table if you want to be able to enforce referential integrity on 
your data eg the column company could be company not null, or if you wanted 
to allow a null description it could be 'description default null'

Oh, and views, wonderful views. Use them extensively where you can.

Hope this is not off track and a little useful, I've quickly dashed this off 
in pgadmin and it compiled ok for me.

Regards

Richard



> Randy
>
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6)
> AntiSpam:  SpamAssassin 3.2.3
> AntiVirus: ClamAV 0.91.2/8885 - Wed Jan 21 12:48:08 2009
> by Markus Madlener @ http://www.copfilter.org
>
> ---------------------------------------------------------------------------
>--- This SF.net email is sponsored by:
> SourcForge Community
> SourceForge wants to tell your story.
> http://p.sf.net/sfu/sf-spreadtheword
> _______________________________________________
> Gambas-user mailing list
> Gambas-user at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user