Postgresql: inserting value of a column from a file

Question

For example, there is a table named 'testtable' that has following columns: testint (integer) and testtext (varchar(30)).

What i want to do is pretty much something like that:

INSERT INTO testtable VALUES(15, CONTENT_OF_FILE('file'));

While reading postgresql documentation, all I could find is COPY TO/FROM command, but that one's applied to tables, not single columns.

So, what shall I do?

Answer 1

If this SQL code is executed dynamically from your programming language, use the means of that language to read the file, and execute a plain INSERT statement.

However, if this SQL code is meant to be executed via the psql command line tool, you can use the following construct:

\set content `cat file`
INSERT INTO testtable VALUES(15, :'content');

Note that this syntax is specific to psql and makes use of the cat shell command.

It is explained in detail in the PostgreSQL manual:

• psql / SQL Interpolation
• psql / Meta-Commands

Answer 2

If I understand your question correctly, you could read the single string(s) into a temp table and use that for insert:

DROP SCHEMA str CASCADE;
CREATE SCHEMA str;

SET search_path='str';

CREATE TABLE strings
    ( string_id INTEGER PRIMARY KEY
    , the_string varchar
    );
CREATE TEMP TABLE string_only
    ( the_string varchar
    );

COPY string_only(the_string)
FROM '/tmp/string'
    ;   

INSERT INTO strings(string_id,the_string)
SELECT 5, t.the_string
FROM string_only t
    ;   

SELECT * FROM strings;

Result:

NOTICE:  drop cascades to table str.strings
DROP SCHEMA
CREATE SCHEMA
SET
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "strings_pkey" for table "strings"
CREATE TABLE
CREATE TABLE
COPY 1
INSERT 0 1
 string_id |     the_string      
-----------+---------------------
         5 | this is the content
(1 row)

Please note that the file is "seen" by the server as the server sees the filesystem. The "current directory" from that point of view is probably $PG_DATA, but you should assume nothing, and specify the complete pathname, which should be reacheable and readable by the server. That is why I used '/tmp', which is unsafe (but an excellent rendez-vous point ;-)