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?

Solution 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

Solution 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 ;-)

Comments

[user905747]

At first, it worked. Then, I attemtped to do it myself. [CODE]CREATE TABLE test( testint INTEGER PRIMARY KEY NOT NULL, testtext TEXT ); CREATE TEMP TABLE loader( testtext TEXT NOT NULL ); COPY loader(testtext) FROM '/Users/netaro/Untitled.txt'; INSERT INTO test SELECT 4, t.testtext from loader t; SELECT * from test; [/CODE] At the INSERT INTO, I get [CODE] ERROR: duplicate key value violates unique constraint "test_pkey" SQL state: 23505 Detail: Key (testint)=(4) already exists. [/CODE] By the way, what's that about dropping schema and SET search_str?

[wildplasser]

I have the habit to create a temp schema for doing experiments like this. (this is intended to avoid pollution of the default schema, obviously) You can ignore the first three schema and search_path lines. Your error indicvtes that the key value "4" already exists when you intend to insert it. It is not an error, it is only the data being protected by the primary key ("unique") constraint.

[vog]

@francoisP Please don't forget to put the colon ":" in front of 'content'. More generally, I strongly recommend to apply examples by copy & paste and editing later, rather than typing everything by hand, to avoid this kind of issues.