PostgreSQL: pass path from psql to create table name dynamically

Question

Suppose I have two paths, /data/dev and /data/prod. When developing, I want to use the dev directory, and in prod I want to use the prod directory.

I need to upload data at some interval (upload_to_tables.sql):

file := :basedir || 'file.csv';
COPY my_table FROM :file WITH (FORMAT csv);

I tried running this, $ environment=dev; psql -d my_database -v basedir="'/data/$environment'" -f upload_to_tables.sql

I get this error:

2019-07-15 09:24:52.302 CDT [90820] ERROR:  syntax error at or near "file" at character 1

How can I path the directory from which these data need to be loaded in a dynamic fashion?

fphilipe · Accepted Answer · 2019-07-17 20:04:21Z

1

You were quite close. The following should work.

$ environment=dev && psql -d my_database -v basedir="/data/$environment/" -f upload_to_tables.sql

\set file :basedir 'file.csv'
COPY my_table FROM :'file' WITH (FORMAT csv);

Usually you shouldn't bother with quotes when setting variables. You can access the variable's content as a string with :'var' or as an identifier with :"var".

To concatenate variables and store it as a new variable you can simply list multiple values after the variable name in \set var value.

answered Jul 17, 2019 at 20:04

fphilipe

10.1k1 gold badge42 silver badges55 bronze badges

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

Comments

Kaushik Nayak · Accepted Answer · 2019-07-15 15:29:21Z

1

Create a shell script which displays file contents, something like

$cat ts.sh  

#!/bin/bash
basedir=/data/dev
file=${basedir}"/file.csv"
cat ${file}

Now, the \copy or COPY can use the PROGRAM option

\copy t from program 'ts.sh' with delimiter ',' CSV

answered Jul 15, 2019 at 15:29

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: pass path from psql to create table name dynamically

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related