Unable to add a text value in PostgreSQL

Question

I get an error when inserting data into a text column using "", when I delete the quotes then postgres run and add the column with 0 value.

INSERT INTO tablex (dates, times, openp, high, low, closep, volume)
VALUES ("03/08/2021", 2300, 1680, 1688.6, 1680, 1685.1, 12074);

Columns dates and times were both defined as text columns. The other columns were defined as Float.

When I run the query with the above code, I get the following error:

ERROR: column "03/08/2021" does not exist
LINE 4: ("03/08/2021", 2300, 1680, 1688.6, 1680, 1685.1, 12074);

When the quotes are removed, the column dates populates with 0 value

How can I fix this problem?

Answer 1

INSERT INTO tablex
(dates, times, openp, high, low, closep, volume)
VALUES
('03/08/2021', 2300, 1680, 1688.6, 1680, 1685.1, 12074);

Try this and see if it works. Replace double quotes with single quote.

Answer 2

Single quotes are used to indicate that a token is a string. This is used in many different contexts throughout PostgreSQL.

Try surrounding your string value with ' (single quotes) and run the query.

INSERT INTO tablex (dates, times, openp, high, low, closep, volume)
VALUES ('03/08/2021', 2300, 1680, 1688.6, 1680, 1685.1, 12074);

This should work without errors.

If you need to include a single quote within your string, you can do so by instead inserting two sequential single quotes (Two single quotes, not a double quote).

For example, you could insert another string with an embedded single quote by typing:

INSERT INTO my_table(text) VALUES ('How''s it going?');

Single quoted strings are the appropriate means of assigning or checking the value of strings.

Answer 3

You should use single quotes as the other answers suggest.

If you use double quotes, the string is treated as an identifier (table or column name), hence the error message. Without any quoting, 03/08/2021 is treated as an arithmetic expression that yields 0 because of integer division.

You should never store a date in a text column. That prevented you from getting a helpful error message in the unquoted case, and it will lead to problems and inconsistencies later on. Use the appropriate data type date.