PostgreSQL Error – Why Am I Getting Table Does Not Exist Error?

postgresql

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…

Best Answer

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.

Related Question