CODEWITHSUNDEEP
shellubuntucommand-linepsqlpostgresql-10
frankie
frankie

Reputation: 43

Problem with quoted string interpretation when using psql command line

Suppose that one of the tables in PostgreSQL 10 database is called 'Measurement' and has (among others) a column called 'Name' of text type.

I would like to list all records with specific name, so sql statement:

SELECT * FROM public."Measurement" WHERE "Measurement"."Name" = 'nazwa1';

works fine in pgAdmin SQL editor.

I would like to ask server about the same but with psql working in command line on Ubuntu, something like this:

psql --host 127.0.0.1 --dbname BazaDanych --username postgres --port 5432 --echo-all --no-align --command 'SELECT * FROM public."Measurement" WHERE "Measurement"."Name" = 'nazwa1';' --log-file /home/user/wynik.log -o /home/user/dane.csv -P fieldsep=',' -P footer='off'

It does not work since single quotation mark from this statement is not recognized by shell and it causing problem reported as "column does not exist",as follows:

public."Measurement" WHERE "Measurement"."Name" = nazwa1

'nazwa1' string is not interpreted as a value to be compared with the records but as a column name.

I have tried many ways, as: \', doubling single quotation mark and many others but with no successful results.

How to workaround this ? Could someone correct my command ?

Upvotes: 0

Views: 2069

Answers (1)

frankie
frankie

Reputation: 43

Fortunately, I found it!

Two steps are needed:

  1. Replace external single quotation marks to quotation mark (' -> ")
  2. Add \ before internal quotation marks (" -> \")

So, code below works fine:

psql --host 127.0.0.1 --dbname BazaDanych --username postgres --port 5432 --echo-all --no-align --command "SELECT * FROM public.\"Measurement\" WHERE \"Measurement\".\"Name\" = 'nazwa1';" --log-file /home/user/wynik.log -o /home/user/dane.csv -P fieldsep=',' -P footer='off'

It might be helpful to someone. If not, admins, please delete my post.

Upvotes: 4

Related Questions