4

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?

2
  • 1
    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
    – user1822
    Commented Oct 24, 2016 at 7:38
  • 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. Commented Oct 24, 2016 at 8:58

3 Answers 3

4

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:~$
1
  • Pg also supports cidr/inet types, not to complicate things further though. Commented Nov 23, 2016 at 18:23
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').

2

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$$)'
3
  • "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)
    – Randall
    Commented Nov 23, 2020 at 15:51
  • @Randall just don't quote it, ever and it'll never be upper cased. Commented Nov 23, 2020 at 15:57
  • 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! :-)
    – Randall
    Commented Nov 23, 2020 at 16:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.