Getting started with PostgreSQL

[Ambrose Bonnaire-Sergeant]

Hi,

I am installing a VARS with PostgreSQL following the instructions on
this page:

http://vars.sourceforge.net/Development/howto_postgresql.shtml

I have successfully imported the .ddl files into my database, but when
I try and
import vars-insertInitialUniqueID.sql via `psql' I get an error:


psql:vars-insertInitialUniqueID.sql:56: ERROR: syntax error at or
near "GO"
LINE 3: GO

Am I mean to import this full sql file?

I am running postgres 8.4.

Thanks,
Ambrose

[Brian Schlining]

Hi Ambrose,

>
> I am installing a VARS with PostgreSQL following the instructions on
> this page:
>
> http://vars.sourceforge.net/Development/howto_postgresql.shtml
>
> I have successfully imported the .ddl files into my database, but when
> I try and
> import vars-insertInitialUniqueID.sql via `psql' I get an error:
>
>
> psql:vars-insertInitialUniqueID.sql:56: ERROR: syntax error at or
> near "GO"
> LINE 3: GO

You may need to change the format of the SQL. It might be that your version of Postgresql or the tool your using may need to use a different statement separator. For example you might have to replace the GO statments with a semi-colon so that it looks more like:

INSERT INTO UniqueID(tablename, nextid)
VALUES('VideoArchiveSet', 100);

INSERT INTO UniqueID(tablename, nextid)
VALUES('CameraPlatformDeployment', 100);

...
I ran my tests on postgresql using PostreSQL 9.0 as the database and using Aqua Data Studio (http://www.aquafold.com/) as the front end and did not have any problems.

>
> Am I mean to import this full sql file?

YES!! That SQL initializes a table that's used to generate the primary keys. If you're missing one of those rows in the UniqueID table VARS will bork at some point while your using it.

Also, a little disclaimer about using a table for generating primary keys. It's probably not an ideal technique, but we run VARS on multiple replicated databases (a database on each of our ships, and a master one on shore). By seeding the UniqueID table with wildly different 'nextid' values we keep the databases from creating duplicate primary keys, which would cause replication to fail.

>
> I am running postgres 8.4.
>

Let me know how it goes. I don't expect that you'll have any problems. But feedback is appreciated.

Cheers

-- Brian Schlining

[Ambrose Bonnaire-Sergeant]

Thanks Brian.

I changed the SQL as you suggested and it seemed to import correctly.

I'll add it here for reference.

INSERT INTO UniqueID(tablename, nextid) 

    VALUES('VideoArchiveSet', 100);

INSERT INTO UniqueID(tablename, nextid) 

    VALUES('CameraPlatformDeployment', 100);

INSERT INTO UniqueID(tablename, nextid) 

    VALUES('VideoArchive', 100);

INSERT INTO UniqueID(tablename, nextid) 

    VALUES('VideoFrame', 100);

INSERT INTO UniqueID(tablename, nextid) 

    VALUES('CameraData', 100);

INSERT INTO UniqueID(tablename, nextid) 

    VALUES('PhysicalData', 100);

INSERT INTO UniqueID(tablename, nextid) 

    VALUES('Observation', 100);

INSERT INTO UniqueID(tablename, nextid) 

    VALUES('Association', 100);

INSERT INTO UniqueID(tablename, nextid) 

    VALUES('Artifact', 100);

INSERT INTO UniqueID(tablename, nextid) 

    VALUES('Concept', 100);

INSERT INTO UniqueID(tablename, nextid) 

    VALUES('ConceptDelegate', 100);

INSERT INTO UniqueID(tablename, nextid) 

    VALUES('ConceptName', 100);

INSERT INTO UniqueID(tablename, nextid) 

    VALUES('History', 100);

INSERT INTO UniqueID(tablename, nextid) 

    VALUES('LinkRealization', 100);

INSERT INTO UniqueID(tablename, nextid) 

    VALUES('LinkTemplate', 100);

INSERT INTO UniqueID(tablename, nextid) 

    VALUES('Media', 100);

INSERT INTO UniqueID(tablename, nextid) 

    VALUES('SectionInfo', 100);

INSERT INTO UniqueID(tablename, nextid) 

    VALUES('UserName', 100);