Issue inserting string datatype into database

Question

I have the following insert query with a subquery:

INSERT INTO Order (time_of_purchase, cust_id) 
VALUES (NOW(), (SELECT cust_id FROM Customer WHERE first_name = "John" AND last_name = "Doe")) RETURNING reference_number

When I execute the query, postgres returns the error: "ERROR: column "John" does not exist. SQL state: 42703. Character: 110

What could be the problem?

Unlike MySQL default settings, proper SQL syntax dictates "identifier" and 'string'. — user2864740
– user2864740, Commented Mar 28, 2015 at 2:58

Pரதீப் · Accepted Answer · 2015-03-28 02:58:07Z

3

Use Single Quotes instead of double quotes. " makes the compiler to consider it as identifier. Try this insert

INSERT INTO Order (time_of_purchase, cust_id) 
SELECT NOW(),cust_id FROM Customer WHERE first_name = 'John' AND last_name = 'Doe'

answered Mar 28, 2015 at 2:58

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jpineds Over a year ago

it works. but what if the first_name and last_name are parameters? Example: INSERT INTO Order (time_of_purchase, cust_id) SELECT NOW(),cust_id FROM Customer WHERE first_name = $var1 AND last_name = $var2

user2864740 Over a year ago

@jpineds Use parameter bindings (aka "prepared statements") then. Refer to the documentation of the Pg database adapter you are using (in PHP).

jpineds Over a year ago

can it be achieved without using prepared statements?

Collectives™ on Stack Overflow

Issue inserting string datatype into database

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related