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

Question

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;

Aditya · Accepted Answer · 2013-12-24 12:55:47Z

17

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.

edited Dec 24, 2013 at 12:55

answered Apr 17, 2013 at 2:08

Aditya

2,2563 gold badges22 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Midwire · Accepted Answer · 2021-02-17 00:10:37Z

6

On Posix systems (Linux, MacOS)...

You may access environment variables for the system user matching the postgresql role you are using, usually the system postgres user (or whatever system user is running the postgres server).

Because env uses '=' as delimiter, splitting k=v means you have to prevent the presence of '=' in the string values.

You can do:

DROP TABLE IF EXISTS env;
DROP TABLE IF EXISTS env_tmp;
CREATE TEMP TABLE env_tmp(e text);
CREATE TEMP TABLE env(k text, v text);

COPY env_tmp ( e ) FROM PROGRAM 'env';

INSERT INTO env
SELECT (regexp_split_to_array(e,'={1,1}'))[1],
  (regexp_match(e, '={1,1}(.*)'))[1]
FROM env_tmp;

SELECT * FROM env;

            k             |         v                                                             
--------------------------+----------------------------
 LC_TIME                  | C
 DEPLOY_ENV               | local
 PG_VERSION               | 12.4-1.pgdg100+1
 LC_CTYPE                 | en_US.utf8
 LC_COLLATE               | en_US.utf8
 LANG                     | en_US.utf8
 LC_MESSAGES              | en_US.UTF-8
 PG_MAJOR                 | 12
 LC_NUMERIC               | C
 SERVICE_COMMAND          | postgres -c "config_file=postgresql.conf" -c "port=5432"

[...]

It should also possible to do something using the "CSV" version of COPY, I haven't tried that way.

edited Feb 17, 2021 at 0:10

Midwire

1,1009 silver badges25 bronze badges

answered Oct 10, 2020 at 14:31

LittleEaster

6269 silver badges10 bronze badges

2 Comments

arthur Over a year ago

great comment. On windows I use set instead of env. In addition, I use COPY env_tmp ( e ) FROM PROGRAM 'set' with delimiter E'\001' escape E'\\' quote E'\003' CSV; to avoid "eat up" of some important characters

Midwire Over a year ago

This should be the accepted answer as it is not tied to using psql and it works cross-platform.

hd1 · Accepted Answer · 2013-04-17 02:06:09Z

3

If you install PL/R, you can use the plr_environ() function to get a list of environment variables and their values as COPY MyTable from (SELECT VALUE FROM plr_environ() WHERE name = 'MyPath') ; other stored procedure languages probably have similar ways -- in plpython, I'd imagine one could use os.environ['MyPath'], plperlu has access to the %ENV hash, and so on. If you need further help, please leave a comment and I'll be happy to look into this further for you.

answered Apr 17, 2013 at 2:06

hd1

34.9k5 gold badges83 silver badges95 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related