0

so I'm trying to creating a database table using pg admin. I created a table in pg admin and then I was supposed to run the table using putty. But when I run it it says error table does not exist. The table does exist though so I don't know why I'm getting that error!

--Dropping tables clear out any existing data
DROP TABLE IF EXISTS automobiles;

CREATE TABLE automobiles(
    id INTEGER PRIMARY KEY,
    make VARCHAR(15)NOT NULL,
    model VARCHAR(20)NOT NULL,
    year INTEGER NOT NULL,
    owner CHAR(25) NOT NULL,
    msrp DECIMAL NOT NULL,
    purchase_date DATE NOT NULL
);



INSERT INTO automobiles(id, make, model, year, owner, msrp, purchase_date) VALUES(
    1,
    'Ferarri',
    'F40',
    '1987',
    '',
    '1690000',
    '3/12/2019');
INSERT INTO automobiles(id, make, model, year,owner, msrp, purchase_date)VALUES(
    2,
    'Tundra',
    '4X2',
    '2019',
    '',
    '39625',
    '3/12/2019');
INSERT INTO automobiles(id, make, model, year,owner, msrp, purchase_date) VALUES(
    3,
    'Mercedes-Benz',
    'AMG GT',
    '2014',
    '',
    '145000',
    '3/12/2019');
INSERT INTO automobiles(id, make, model, year,owner, msrp, purchase_date)VALUES(
    4,
    'Chrysler',
    '300',
    '2004',
    '',
    '800',
    '3/12/2019');
INSERT INTO automobiles(id, make, model, year,owner, msrp, purchase_date) VALUES(
    5,
    'Jeep',
    'Wrangler',
    '2019',
    '',
    '33695',
    '3/12/2019');

SELECT make, model, year, msrp FROM automobiles ORDER BY year ASC;

UPDATE automobiles
SET owner = 'Deanna Slotegraaf'
WHERE make = 'ferarri';

DELETE FROM automobiles WHERE make = 'Chrysler';

This is the full set of code that I have.

Last login: Mon Mar 11 18:02:19 2019 from 108.170.191.53
slotegraafd@opentech:~$ cd /var/www/html/webd2201/slotegraafd/sql
slotegraafd@opentech:/var/www/html/webd2201/slotegraafd/sql$ psql -d slotegraafd_db -f lab7_auto_records.sql
Password:
psql:lab7_auto_records.sql:9: NOTICE:  table "automobiles" does not exist, skipping
DROP TABLE
CREATE TABLE
psql:lab7_auto_records.sql:30: ERROR:  date/time field value out of range: "3/12/2019"
LINE 8:  '3/12/2019');
         ^
HINT:  Perhaps you need a different "datestyle" setting.
psql:lab7_auto_records.sql:38: ERROR:  date/time field value out of range: "3/12/2019"
LINE 8:  '3/12/2019');
         ^
HINT:  Perhaps you need a different "datestyle" setting.
psql:lab7_auto_records.sql:46: ERROR:  date/time field value out of range: "3/12/2019"
LINE 8:  '3/12/2019');
         ^
HINT:  Perhaps you need a different "datestyle" setting.
psql:lab7_auto_records.sql:54: ERROR:  date/time field value out of range: "3/12/2019"
LINE 8:  '3/12/2019');
         ^
HINT:  Perhaps you need a different "datestyle" setting.
psql:lab7_auto_records.sql:62: ERROR:  date/time field value out of range: "3/12/2019"
LINE 8:  '3/12/2019');
         ^
HINT:  Perhaps you need a different "datestyle" setting.
 make | model | year | msrp
------+-------+------+------
(0 rows)

UPDATE 0
DELETE 0

This is the putty session

Thanks...

  • What do you mean by "run the table using putty"? May be you could copy/paste the session where you "run the table". – mustaccio Mar 13 at 1:24
  • basically running it through a terminal i just edited the question and pasted the putty session – Dee Slotegraaf Mar 13 at 1:40
2

The table isn't missing. The message "table "automobiles" does not exist, skipping" stems from the DROP TABLE IF EXISTS statement and just informs you that there wasn't such a table to be dropped.

If you look at the message it starts with NOTICE: which tells you that it's not an error. Error messages start with ERROR: (as you can see later on in your script)

Your table was successfully created.


However, your INSERTs are failing as you are providing the values for the date column in the wrong format. The most robust way to supply a date constant is to use the ANSI SQL syntax for dates: DATE '2018-03-12', so your inserts should look like this:

INSERT INTO automobiles(id, make, model, year, owner, msrp, purchase_date) 
VALUES
(   1,
    'Ferarri',
    'F40',
    '1987',
    '',
    '1690000',
    DATE '2019-03-12'), 
(   2,
    'Tundra',
    '4X2',
    '2019',
    '',
    '39625',
    DATE '2019-03-12'), 
...;

If you don't like the ISO formatted date literals, you can also use the to_date() function instead: to_date('3/12/2019', 'mm/dd/yyyy').


The message

date/time field value out of range: "3/12/2019"
LINE 8:  '3/12/2019');
         ^
HINT:  Perhaps you need a different "datestyle" setting.

suggests to change the DateStyle setting in order to allow the implicit conversion of a string value like '3/12/2019' to a proper date.

I strongly discourage the use of the DateStyle parameter as that makes your SQL statements dependent on the environment and they might still fail when run on a different computer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.