0

I have two CSV files which you can insert into the following PostgreSQL schema.
However, I cannot \COPY the data into the tables. Pseudocode

  1. Let data_id=1
  2. Show name and event_value from data.csv and log.csv, respectively
  3. Do this request from R with connection to PostgreSQL

Pseudocode in other words but not correct syntax

SELECT name, event_value 
    FROM data, log
    WHERE data_id=1;

CSV files

I enclose the header in double quotes in CSV files (thread PG COPY error: invalid input syntax for integer). data.csv where two integer/SERIAL + integer

"data_id", "event_value"
1, 777
1, 666
2, 111
4, 123 
3, 324
1, 245

log.csv where integer/SERIAL + string so I put enclosed the latter one in double quotes

"data_id", "name"
1, "leo"
2, "leopold"
3, "lorem"

PostgreSQL

Schema and workflow

DROP TABLE IF EXISTS data, log;    
CREATE TABLE data (
        data_id SERIAL NOT NULL, -- no primary key possible here, because possible have many events per data id
        event_value INTEGER NOT NULL
);
CREATE TABLE log (
        data_id SERIAL PRIMARY KEY NOT NULL,
        name INTEGER NOT NULL
);

-- https://stackoverflow.com/a/2987451/54964
-- https://soleil4716.wordpress.com/2010/08/19/using-copy-command-in-postgresql/
-- joanolo, HEADER
\COPY data FROM '/home/masi/Data/data.csv' DELIMITER ',' CSV HEADER;
\COPY log FROM '/home/masi/Data/log.csv' DELIMITER ',' CSV HEADER;

Work flow pseudocode

  1. Start from clean table (drop old tables) and Create new tables
  2. Insert two CSV files into the tables
  3. Do SELECT now with connection to PostgreSQL from R

Output

masi@masi:~$ psql -f schema.sql 
psql:schema.sql:1: NOTICE:  table "data" does not exist, skipping
DROP TABLE
CREATE TABLE
CREATE TABLE
COPY 6
psql:schema.sql:14: ERROR:  invalid input syntax for integer: " leo"
CONTEXT:  COPY log, line 2, column name: " leo"

I should not have mistakes in CSV files, based on the thread PG COPY error: invalid input syntax for integer.

OS: Linux Debian 8.7
PostgreSQL: 9.4
Related: R sqldf/... approach in the thread How to select on CSV files by R sqldf/data.table/dplyr?

closed as unclear what you're asking by Evan Carroll, Marco, joanolo, mustaccio, McNets May 15 '17 at 20:57

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    If your CSV files have a HEADER you want to specify the HEADER true option in your COPY statement. See COPY documentation – joanolo May 14 '17 at 12:48
  • @joanolo Please, see the body. I can use only HEADER, since HEADER true does not wark in \COPY. - - The original error still persists in the case. – Léo Léopold Hertz 준영 May 14 '17 at 14:34
1

Error is obvious since you're intending to add characters to integer defined column. Just change datatype for name column in log table from integer to varchar(10)

0

The wanted SELECT can be done as

-- https://unix.stackexchange.com/a/364970/16920
SELECT a.event_value, b.name
    FROM data a, log b
    WHERE a.data_id = 1
      AND a.data_id=b.data_id;

Not the answer you're looking for? Browse other questions tagged or ask your own question.