Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is it possible to reference an environmental variable within a postgres sql command?

Tags:

sql

postgresql

For example, say I wanted to import a CSV file from a path on the same machine the postgres server is running on.

There is an environmental variable MyPath set on the system to '/path/to/my/csv/file/'.

I could easily import this CSV file as follow:

COPY MyTable FROM
'/path/to/my/csv/file/myTable.csv'
DELIMITERS ','
CSV HEADER;

Is it possible to reference the MyPath variable from within this postgres sql command? Something along the following lines:

COPY MyTable FROM
get_environmental_variable('MyPath') || 'myTable.csv'
DELIMITERS ','
CSV HEADER;
like image 386
Ben Hamner Avatar asked Apr 17 '13 01:04

Ben Hamner


People also ask

How do I reference an environment variable from the command line?

To reference a variable in Windows, use %varname% (with prefix and suffix of '%' ). For example, you can use the echo command to print the value of a variable in the form " echo %varname% ".

How do I check environment variables in PostgreSQL?

psql -qAtc 'select * from pg_settings'; ? or, if you just want key/value: psql -qAtc 'SELECT name, setting FROM pg_settings';

Can you use variables in PostgreSQL?

PL/pgSQL variables can have any SQL data type, such as integer , varchar , and char . Here are some examples of variable declarations: user_id integer; quantity numeric(5); url varchar; myrow tablename%ROWTYPE; myfield tablename.

How do I echo an environment variable?

Select Start > All Programs > Accessories > Command Prompt. In the command window that opens, enter echo %VARIABLE%. Replace VARIABLE with the name of the environment variable.


1 Answers

Try this while starting

 psql --set 'var=foo' -c '\i thesqlscript' 

And this in the query

update table set column = :var; 

This is taken from this question on the forum

If you are using an older version of postgres, this looks like the same question asked in the postgres forum (though this is many years ago). There is no direct way, but they have given a couple of workarounds.

like image 143
Aditya Avatar answered Oct 11 '22 12:10

Aditya