Difference Between Single Quotes and Double Quotes in Postgresql

What is the difference between single quotes and double quotes in PostgreSQL?

Double quotes are for names of tables or fields. Sometimes You can omit them. The single quotes are for string constants. This is the SQL standard. In the verbose form, your query looks like this:

select * from "employee" where "employee_name"='elina';

What is the difference between single and double quotes in SQL?

Single quotes are used to indicate the beginning and end of a string in SQL. Double quotes generally aren't used in SQL, but that can vary from database to database.

Stick to using single quotes.

That's the primary use anyway. You can use single quotes for a column alias — where you want the column name you reference in your application code to be something other than what the column is actually called in the database. For example: PRODUCT.id would be more readable as product_id, so you use either of the following:

SELECT PRODUCT.id AS product_id
SELECT PRODUCT.id 'product_id'

Either works in Oracle, SQL Server, MySQL… but I know some have said that the TOAD IDE seems to give some grief when using the single quotes approach.

You do have to use single quotes when the column alias includes a space character, e.g., product id, but it's not recommended practice for a column alias to be more than one word.

single vs double quotes in WHERE clause returning different results

Just to expand on Gordon's answer a little. Your first query:

SELECT
  file_name
FROM table
WHERE file_name = "file_name"

In this case, the double quotes are causing the query engine to treat "file_name" as a column identifier, not a value, so that query is functionally the same as:

SELECT
  file_name
FROM table
WHERE file_name = file_name

Obviously (when written that way) the condition is always true, so the full table is returned.

Replace with single quote to double single quote not working in PostgreSQL 12

This has nothing to do with your replacement, because the character in question is not an apostrophe ' (U+0027), but a “right single quotation mark” character ’ (U+2019).

Probably the client encoding for your connection to PostgreSQL v12 isn't set correctly, so that the character is translated to something undesirable. There may also have been a mistake in transferring the character to v12.

To diagnose this, try

SELECT note::bytea FROM patient;

If this contains e28099, the data in your database are fine, and the problem is your client encoding. This assumes that the server encoding of the databases is UTF8.

Are double and single '' quotes (always) interchangeable in R?

> print(""hi"")
Error: unexpected symbol in "print(""hi"
> print("'hi'")
[1] "'hi'"
> print("hi")
[1] "hi"

Double vs single quotes

" " allows you to do string interpolation, e.g.:

world_type = 'Mars'
"Hello #{world_type}"

When to use single quotes, double quotes, and backticks in MySQL

Backticks are to be used for table and column identifiers, but are only necessary when the identifier is a MySQL reserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set (see below) It is often recommended to avoid using reserved keywords as column or table identifiers when possible, avoiding the quoting issue.

Single quotes should be used for string values like in the VALUES() list. Double quotes are supported by MySQL for string values as well, but single quotes are more widely accepted by other RDBMS, so it is a good habit to use single quotes instead of double.

MySQL also expects DATE and DATETIME literal values to be single-quoted as strings like '2001-01-01 00:00:00'. Consult the Date and Time Literals documentation for more details, in particular alternatives to using the hyphen - as a segment delimiter in date strings.

So using your example, I would double-quote the PHP string and use single quotes on the values 'val1', 'val2'. NULL is a MySQL keyword, and a special (non)-value, and is therefore unquoted.

None of these table or column identifiers are reserved words or make use of characters requiring quoting, but I've quoted them anyway with backticks (more on this later...).

Functions native to the RDBMS (for example, NOW() in MySQL) should not be quoted, although their arguments are subject to the same string or identifier quoting rules already mentioned.

Backtick (`)
table & column ───────┬─────┬──┬──┬──┬────┬──┬────┬──┬────┬──┬───────┐
                      ↓     ↓  ↓  ↓  ↓    ↓  ↓    ↓  ↓    ↓  ↓       ↓
$query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`, `updated`) 
                       VALUES (NULL, 'val1', 'val2', '2001-01-01', NOW())";
                               ↑↑↑↑  ↑    ↑  ↑    ↑  ↑          ↑  ↑↑↑↑↑ 
Unquoted keyword          ─────┴┴┴┘  │    │  │    │  │          │  │││││
Single-quoted (') strings ───────────┴────┴──┴────┘  │          │  │││││
Single-quoted (') DATE    ───────────────────────────┴──────────┘  │││││
Unquoted function         ─────────────────────────────────────────┴┴┴┴┘

Variable interpolation

The quoting patterns for variables do not change, although if you intend to interpolate the variables directly in a string, it must be double-quoted in PHP. Just make sure that you have properly escaped the variables for use in SQL. (It is recommended to use an API supporting prepared statements instead, as protection against SQL injection).

// Same thing with some variable replacements
// Here, a variable table name $table is backtick-quoted, and variables
// in the VALUES list are single-quoted 
$query = "INSERT INTO `$table` (`id`, `col1`, `col2`, `date`) VALUES (NULL, '$val1', '$val2', '$date')";

Prepared statements

When working with prepared statements, consult the documentation to determine whether or not the statement's placeholders must be quoted. The most popular APIs available in PHP, PDO and MySQLi, expect unquoted placeholders, as do most prepared statement APIs in other languages:

// PDO example with named parameters, unquoted
$query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`) VALUES (:id, :col1, :col2, :date)";

// MySQLi example with ? parameters, unquoted
$query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`) VALUES (?, ?, ?, ?)";

Characters requring backtick quoting in identifiers:

According to MySQL documentation, you do not need to quote (backtick) identifiers using the following character set:

ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)

You can use characters beyond that set as table or column identifiers, including whitespace for example, but then you must quote (backtick) them.

Also, although numbers are valid characters for identifiers, identifiers cannot consist solely of numbers. If they do they must be wrapped in backticks.

Insert text with single quotes in PostgreSQL

String literals

Escaping single quotes ' by doubling them up → '' is the standard way and works of course:

'user's log'     -- incorrect syntax (unbalanced quote)
'user''s log'

Plain single quotes (ASCII / UTF-8 code 39), mind you, not backticks `, which have no special purpose in Postgres (unlike certain other RDBMS) and not double-quotes ", used for identifiers.

In old versions or if you still run with standard_conforming_strings = off or, generally, if you prepend your string with E to declare Posix escape string syntax, you can also escape with the backslash \:

E'user\'s log'

Backslash itself is escaped with another backslash. But that's generally not preferable.

If you have to deal with many single quotes or multiple layers of escaping, you can avoid quoting hell in PostgreSQL with dollar-quoted strings:

'escape '' with '''''
$$escape ' with ''$$

To further avoid confusion among dollar-quotes, add a unique token to each pair:

$token$escape ' with ''$token$

Which can be nested any number of levels:

$token2$Inner string: $token1$escape ' with ''$token1$ is nested$token2$

Pay attention if the $ character should have special meaning in your client software. You may have to escape it in addition. This is not the case with standard PostgreSQL clients like psql or pgAdmin.

That is all very useful for writing plpgsql functions or ad-hoc SQL commands. It cannot alleviate the need to use prepared statements or some other method to safeguard against SQL injection in your application when user input is possible, though. @Craig's answer has more on that. More details:

SQL injection in Postgres functions vs prepared queries

Values inside Postgres

When dealing with values inside the database, there are a couple of useful functions to quote strings properly:

quote_literal() or quote_nullable() - the latter outputs the string NULL for null input.

There is also quote_ident() to double-quote strings where needed to get valid SQL identifiers.
format() with the format specifier %L is equivalent to quote_nullable().

Like: format('%L', string_var)
~~concat()~~ or ~~concat_ws()~~ are typically no good for this purpose as those do not escape nested single quotes and backslashes.