19

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?

user905747
  • 613
  • 1
  • 9
  • 18
  • The file is supposed to contain only one line=row=column=field ? – wildplasser Jun 10 '12 at 10:49
  • Yes, the file contains only one line, that contains nothing else but that string. – user905747 Jun 10 '12 at 10:56
  • Ok, see may answer. (I guessed) – wildplasser Jun 10 '12 at 10:58
  • Do you need that with PostgreSQL built-in tools or are other tools an option? –  Jun 10 '12 at 11:01
  • @a_horse_with_no_name: this is indeed the kind of work that makes you want a scripting language, or even a shellscript, especially if you have to manipulate the filenames. – wildplasser Jun 10 '12 at 11:19
  • @wildplasser: depends on the SQL client. The one that I'm using can do this with some extended SQL syntax (when the file to be stored is available on the client). –  Jun 10 '12 at 11:24
  • @a_horse_with_no_name: yep, if the file is stored at the client side you eiher have to hack some VALUES(...) thing, use FROM STDIN, or use some different frontend, like python/psychopg, or even ODBC. – wildplasser Jun 10 '12 at 11:52
  • I would have titled this 'Inserting the value from a file into a *field*'. It reads like you're trying to insert a whole column. – Noumenon Feb 04 '18 at 19:05

2 Answers2

30

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:

vog
  • 23,517
  • 11
  • 59
  • 75
3

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

wildplasser
  • 43,142
  • 8
  • 66
  • 109
  • 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? – user905747 Jun 10 '12 at 13:25
  • 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. – wildplasser Jun 10 '12 at 14:38