Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Nov 2010
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Problems Creating Tables in postgreSQL

    Hello, I'm steve and I've just registered here (one of the only forums that actually allowed me activate )

    Anyway, as part of a University project, I have to create a Database (PostgreSQL) driven website which also utilizes Java Servlets and SQL.

    However, creating tables and inserting test content into the database is prooving tricky and I've just about done nothing but swear for the past two days...

    I'm no SQL guru, but the SQL code I'm using is:
    Code:
    -- DROP TABLE comments;
    -- DROP TABLE photos;
    -- DROP TABLE churches;
    
    
    CREATE TABLE churches (
    	church_id CHAR(4) UNIQUE NOT NULL,
    	name VARCHAR(40),
    	address VARCHAR(500),
    	minister VARCHAR(40),
    	denomination VARCHAR(40), 
    	year_built INT, 
    	history VARCHAR(1000), 
    	PRIMARY KEY(church_id)
    	);
    
    INSERT INTO churches VALUES ('SPM','St Peter Mancroft','Chantry Road, Norwich','Peter Nokes','Church of England',1455,'History');
    INSERT INTO churches VALUES ('SANC','Norwich Citadel','Lower Goat Lane, Norwich','Ian McBride','Salvation Army',1892,'History');
    INSERT INTO churches VALUES ('SS','St Stephens','Rampant Horse Street, Norwich','Hereward Cooke','Church of England',1350,'History');
    INSERT INTO churches VALUES ('KCC','Kings Community Church','King Street, Norwich','Goff Hope','New Frontiers',1987,'History');
    
    
    CREATE TABLE photos (
    	photo_id CHAR(5) UNIQUE NOT NULL,
    	church_id CHAR(4) REFERENCES churches(church_id),
    	photo_name VARCHAR(40),
    	photo_url VARCHAR(50),
    	);
    
    INSERT INTO photos VALUES ('STM1','STM','St Peter Mancroft','StPeterMancroft1.JPG');
    INSERT INTO photos VALUES ('SANC1','SANC1','Norwich Citadel','SalArmy-Norwich1.JPG');
    INSERT INTO photos VALUES ('SS1','SS','St Stephens','StStephens1.JPG');
    INSERT INTO photos VALUES ('KCC1','KCC','Kings Church','Kings1.JPG');
    
    
    CREATE TABLE comments (
    	comment_id CHAR(4) REFERENCES churches(church_id),
    	comment_date DATE,	
    	author VARCHAR(40),
    	comment_body VARCHAR(1000),
    	);
    
    INSERT INTO comments VALUES ('SPM','26 Nov 2010','Steve Jones','Comment for St Peter Mancroft');
    INSERT INTO comments VALUES ('SANC','26 Nov 2010','Olly Crocombe','Comment for Norwich Citadel');
    INSERT INTO comments VALUES ('SS','26 Nov 2010','Alec Walker','Comment for St Stephens');
    INSERT INTO comments VALUES ('KCC','26 Nov 2010','Kira Ovington','Comment for Kings Community Church');
    It creates the Churches table and inserts all data with no problems at all, but then has a moan telling me that ERROR: syntax error at or near ")"... I have no idea whats going on, I can't see anything wrong with the code and I'm starting to panic that I'm going to fail this assignment...

    The message I get from postgreSQL is:
    Code:
    postgres=# \i project_db.txt
    psql:project_db.txt:15: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit
     index "churches_pkey" for table "churches"
    CREATE TABLE
    INSERT 0 1
    INSERT 0 1
    INSERT 0 1
    INSERT 0 1
    
    psql:project_db.txt:28: ERROR:  syntax error at or near ")"
    LINE 6:  );
             ^
    psql:project_db.txt:30: ERROR:  relation "photos" does not exist
    LINE 1: INSERT INTO photos VALUES ('STM1','STM','St Peter Mancroft',...
                        ^
    psql:project_db.txt:31: ERROR:  relation "photos" does not exist
    LINE 1: INSERT INTO photos VALUES ('SANC1','SANC1','Norwich Citadel'...
                        ^
    psql:project_db.txt:32: ERROR:  relation "photos" does not exist
    LINE 1: INSERT INTO photos VALUES ('SS1','SS','St Stephens','StSteph...
                        ^
    psql:project_db.txt:33: ERROR:  relation "photos" does not exist
    LINE 1: INSERT INTO photos VALUES ('KCC1','KCC','Kings Church','King...
                        ^
    psql:project_db.txt:41: ERROR:  invalid byte sequence for encoding "UTF8": 0xa0
    psql:project_db.txt:43: ERROR:  relation "comments" does not exist
    LINE 1: INSERT INTO comments VALUES ('SPM','26 Nov 2010','Person1...
                        ^
    psql:project_db.txt:44: ERROR:  relation "comments" does not exist
    LINE 1: INSERT INTO comments VALUES ('SANC','26 Nov 2010','Person2...
                        ^
    psql:project_db.txt:45: ERROR:  relation "comments" does not exist
    LINE 1: INSERT INTO comments VALUES ('SS','26 Nov 2010','Person3...
                        ^
    psql:project_db.txt:46: ERROR:  relation "comments" does not exist
    LINE 1: INSERT INTO comments VALUES ('KCC','26 Nov 2010','Person4...
    I will greatly appreciate any help with this issue.

    Steve
    Last edited by sjjones85; 11-27-2010 at 07:07 PM. Reason: Fixed Issue

  • #2
    New to the CF scene
    Join Date
    Nov 2010
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Just playing with the SQL now and have somehow managed to fixed the issue!

    I didn't realise that "," before the ");" had an impact...

    I removed the commas from the photos and comments tables and the Database built ok.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •