"column not allowed here" error in INSERT statement

sql oracle sql-insert
john picture john · May 8, 2012 · Viewed 160.9k times · Source

I created this table called LOCATION by doing this:

CREATE TABLE LOCATION(
POSTCODE VARCHAR(10) PRIMARY KEY,
STREET_NAME VARCHAR(20),
CITY VARCHAR(20));

and when I try to add some date within the table it doesn’t work saying there is an error

INSERT INTO LOCATION VALUES(PQ95VM,'HAPPY_STREET','FRANCE');

error is saying

column not allowed here

Answer

skaffman picture skaffman · May 8, 2012

You're missing quotes around the first value, it should be

INSERT INTO LOCATION VALUES('PQ95VM', 'HAPPY_STREET', 'FRANCE');

Incidentally, you'd be well-advised to specify the column names explicitly in the INSERT, for reasons of readability, maintainability and robustness, i.e.

INSERT INTO LOCATION (POSTCODE, STREET_NAME, CITY) VALUES ('PQ95VM', 'HAPPY_STREET', 'FRANCE');

Related questions

Best way to do multi-row insert in Oracle?

I'm looking for a good way to perform multi-row inserts into an Oracle 9 database. The following works in MySQL but doesn't seem to be supported in Oracle. INSERT INTO TMP_DIM_EXCH_RT (EXCH_WH_KEY, EXCH_NAT_KEY, EXCH_…

Read More
SQL Error: ORA-01861: literal does not match format string 01861

I am trying to insert data into an existing table and keep receiving an error. INSERT INTO Patient ( PatientNo, PatientFirstName, PatientLastName, PatientStreetAddress, PatientTown, PatientCounty, PatientPostcode, DOB, Gender, PatientHomeTelephoneNumber, PatientMobileTelephoneNumber ) VALUES ( 121, 'Miles', 'Malone', '64 Zoo Lane', 'Clapham', 'United Kingdom', 'SW4 9LP', …

Read More
In Oracle SQL: How do you insert the current date + time into a table?

I've written below code, but it only seems to insert the current date and not the current time. Anyone knows how to do that? insert into errortable (dateupdated,table1id) values (TO_DATE(sysdate, 'dd/mm/yyyy hh24:mi:ss'),1083);

Read More