syntax error at or near - INSERT

Question

This might be a little silly, but can't figure out why this insert is not working, I did surround the IP with single / double quotes!

psql -U dbuser hosts -h dbhost -c 'INSERT INTO HOSTS ('type','name') VALUES ('"test"', '"10.100.133.1"')'
Password for user dbusr:
ERROR: syntax error at or near ".133"
LINE 1: INSERT INTO HOSTS (type,name) VALUES (test, 10.100.133.1)
 ^

Do I need to escape anything?

• user1822: The syntax itself is wrong. 'type' is a string literal, not a column name (and double quotes are not used for string literals, so "test" looks wrong as well). The correct syntax would be: INSERT INTO HOSTS (type,name) VALUES ('test', '10.100.133.1') - but I don't know how you need to escape that using the command line
• András Váczi: OTOH, using keywords as object names is rarely a good idea. As seen below, you don't even need quoting in this very case, but it's because the parser is smart enough, not because "type" is a good column name.

Answer 1

This works fine:

postgres=# create table hosts ( type varchar(20), name varchar(20));
CREATE TABLE
postgres=# \q
postgres@ironforge:~$ psql -c "insert into hosts (type,name) values ('test','10.100.133.1')"
INSERT 0 1
postgres@ironforge:~$

• Evan Carroll: Pg also supports cidr/inet types, not to complicate things further though.

Answer 2

Besides what @a_horse_with_no_name says your escaping is causing the problem.

tl;dr This should work:

psql -c "INSERT INTO HOSTS (\"type\",\"name\") VALUES ('test', '10.100.133.1')"

---

What you need to do is to have string literal passed as parameter to psql. Here you need to use " because you can escape " (quotation mark or double quote) inside " but you cannot escape ' (apostrophe or single quote) inside '.

In SQL (and Postgres) strings are marked with single quotes and and identifiers with double:

PostgreSQL uses only single quotes for this (i.e. WHERE name = 'John'). Double quotes are used to quote system identifiers; field names, table names, etc. (i.e. WHERE "last name" = 'Smith').

• https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
• https://wiki.postgresql.org/wiki/Things_to_find_out_about_when_moving_from_MySQL_to_PostgreSQL

Answer 3

A couple of notes.

• you never have to quote columns names (identifiers) and you never should quote them where it isn't required.
  1. create the table with them unquoted
  2. never quote them in your queries
• you can always use $$ DOLLAR QUOTED STRING LITERALS to get around shell quoting escaping.

So this should work,

psql -c 'INSERT INTO HOSTS (type,name) VALUES ($$test$,ドル $10ドル.100.133.1$$)'

• Randall: "you never have to quote column names" unless you need the name to be case sensitive (ie, you are using a name with upper case letters)
• Evan Carroll: @Randall just don't quote it, ever and it'll never be upper cased.
• Randall: True; I only mentioned it, since existing projects/databases may already be using upper cased column names, which makes quoting necessary (though irritating). I've inherited projects like that. For new projects, though - absolutely, don't quote! :-)