3

I have a table in Postgres that I would like to copy into from a csv file. I usually do as so:

\copy my_table from '/workdir/some_file.txt' with null as 'NULL' delimiter E'|' csv header;

The problem is now however that my_table has one column extra that I would like to fill in manually on copy, with the same value 'b'. Here are my tables:

some_file.txt:
col1 | col2 | col3
  0     0      1
  0     1      3

my_table :
xtra_col | col1 | col2 | col3
   a        5      2       5
   a        6      2       5
   a        7      2       5

Desired my_table after copy into:
xtra_col | col1 | col2 | col3
   a        5      2       5
   a        6      2       5
   a        7      2       5
   b        0      0       1
   b        0      1       3

Is there a way to mention the persisting 'b' value in the copy statement for column `xtra_col'. If not, how should I approach this problem?

Improve this question

2 Answers 2

Reset to default
5

I usually load a file into a temporary table then insert (or update) from there. In this case,

CREATE TEMP TABLE input (LIKE my_table);
ALTER TABLE input DROP xtra_col;

\copy input from 'some_file.txt' ...

INSERT INTO my_table
SELECT 'b', * FROM input;

The INSERT statement looks tidy, but that can only really be achieved when the columns you want to exclude are on either end of my_table. In your (probably simplified) example, xtra_col is at the front so we can quickly append the remaining columns using *.

If the arrangement of CSV file columns differs my_table much more than that, you'll need to start typing out column names.

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

Comments

3

You could set a (temporary) default value for the xtra_col:

ALTER TABLE my_table ALTER COLUMN xtra_col SET DEFAULT 'b';
COPY my_table (col1, col2, col3) FROM '/workdir/some_file.txt' WITH (FORMAT CSV, DELIMITER '|', NULL 'NULL', HEADER true);
ALTER TABLE my_table ALTER COLUMN xtra_col DROP DEFAULT;

is there a way to not repeat columns in my_table? the real my_table has 20 columns and i wouldnt want to call all of them.

If my_table has a lot of columns and you wish to avoid having to type out all the column names, you could dynamically generate the COPY command like this:

SELECT format($$COPY my_table(%s) FROM '/workdir/some_file.txt' WITH (FORMAT CSV, DELIMITER '|', NULL 'NULL', HEADER true);$$
              , string_agg(quote_ident(attname), ','))
FROM   pg_attribute
WHERE attrelid = 'my_table'::regclass
    AND attname != 'xtra_col'
    AND attnum > 0

you could then copy-and-paste the SQL to run it.

Or, for totally hands-free operation, you could create a function to generate the SQL and execute it:

CREATE OR REPLACE FUNCTION test_func(filepath text, xcol text, fillval text)
RETURNS void
LANGUAGE plpgsql
AS $func$
DECLARE sql text;
BEGIN
  EXECUTE format($$ALTER TABLE my_table ALTER COLUMN %s SET DEFAULT '%s';$$, xcol, fillval);

  SELECT format($$COPY my_table(%s) FROM '%s' WITH (FORMAT CSV, DELIMITER '|', NULL 'NULL', HEADER true);$$
        , string_agg(quote_ident(attname), ','), filepath)
  INTO sql
  FROM   pg_attribute
  WHERE attrelid = 'my_table'::regclass
      AND attname != 'xtra_col'
      AND attnum > 0;
  EXECUTE sql;

  EXECUTE format($$ALTER TABLE my_table ALTER COLUMN %s DROP DEFAULT;$$, xcol);
END;
$func$;

SELECT test_func('/workdir/some_file.txt', 'xtra_col', 'b');

This is the sql I used to test the solution above:

DROP TABLE IF EXISTS test;
CREATE TABLE test (
    xtra_col text
    , col1 int
    , col2 int
    , col3 int
);
INSERT INTO test VALUES
('a', 5, 2, 5)
, ('a', 6, 2, 5)
, ('a', 7, 2, 5);

with the contents of /tmp/data being

col1 | col2 | col3
  0  |   0  |   1
  0  |   1  |   3

Then

SELECT test_func('/tmp/data', 'xtra_col', 'b');
SELECT * FROM test;

results in

+----------+------+------+------+
| xtra_col | col1 | col2 | col3 |
+----------+------+------+------+
| a        |    5 |    2 |    5 |
| a        |    6 |    2 |    5 |
| a        |    7 |    2 |    5 |
| b        |    0 |    0 |    1 |
| b        |    0 |    1 |    3 |
+----------+------+------+------+
(5 rows)

Regarding the pg.dropped column:

The test_func call does not seem to produce the pg.dropped column, at least on the test table used above:

unutbu=# SELECT *
    FROM pg_attribute
    WHERE attrelid = 'test'::regclass;

+----------+----------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+-------------+--------------+------------+-------------+--------------+--------+------------+---------------+
| attrelid | attname  | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | attidentity | attisdropped | attislocal | attinhcount | attcollation | attacl | attoptions | attfdwoptions |
+----------+----------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+-------------+--------------+------------+-------------+--------------+--------+------------+---------------+
|    53393 | tableoid |       26 |             0 |      4 |     -7 |        0 |          -1 |        -1 | t        | p          | i        | t          | f         |             | f            | t          |           0 |            0 |        |            |               |
|    53393 | cmax     |       29 |             0 |      4 |     -6 |        0 |          -1 |        -1 | t        | p          | i        | t          | f         |             | f            | t          |           0 |            0 |        |            |               |
|    53393 | xmax     |       28 |             0 |      4 |     -5 |        0 |          -1 |        -1 | t        | p          | i        | t          | f         |             | f            | t          |           0 |            0 |        |            |               |
|    53393 | cmin     |       29 |             0 |      4 |     -4 |        0 |          -1 |        -1 | t        | p          | i        | t          | f         |             | f            | t          |           0 |            0 |        |            |               |
|    53393 | xmin     |       28 |             0 |      4 |     -3 |        0 |          -1 |        -1 | t        | p          | i        | t          | f         |             | f            | t          |           0 |            0 |        |            |               |
|    53393 | ctid     |       27 |             0 |      6 |     -1 |        0 |          -1 |        -1 | f        | p          | s        | t          | f         |             | f            | t          |           0 |            0 |        |            |               |
|    53393 | xtra_col |       25 |            -1 |     -1 |      1 |        0 |          -1 |        -1 | f        | x          | i        | f          | f         |             | f            | t          |           0 |          100 |        |            |               |
|    53393 | col1     |       23 |            -1 |      4 |      2 |        0 |          -1 |        -1 | t        | p          | i        | f          | f         |             | f            | t          |           0 |            0 |        |            |               |
|    53393 | col2     |       23 |            -1 |      4 |      3 |        0 |          -1 |        -1 | t        | p          | i        | f          | f         |             | f            | t          |           0 |            0 |        |            |               |
|    53393 | col3     |       23 |            -1 |      4 |      4 |        0 |          -1 |        -1 | t        | p          | i        | f          | f         |             | f            | t          |           0 |            0 |        |            |               |
+----------+----------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+-------------+--------------+------------+-------------+--------------+--------+------------+---------------+
(10 rows)

As far as I know, the pg.dropped column is a natural result of how PostgreSQL works when a column is dropped. So no fix is necessary. Rows whose attname contains pg.dropped also have a negative attnum.

This is why attnum > 0 was used in test_func -- to remove such rows from the generated list of column names.

My experience with Postgresql is limited, so I might be wrong. If you can produce an example which generates a pg.dropped "column" with positive attnum, I'd very much like to see it.

Improve this answer

8 Comments

that look interesting, i'll have to try it out. One small thing, is there a way to not repeat columns in my_table? the real my_table has 20 columns and i wouldnt want to call all of them
there is a problem with your function because it replaces 'xtra_col' with '...pg.dropped27...'. It looks like it is not replacing the column with nothing, but replacing it with the number. Any ideas how I can fix this?
AFAIK, pg.dropped "columns" occur when a column is dropped from the table and are a natural part of how PostgreSQL operates, so they shouldn't cause any problem. I believe pg_attribute.attnum should always be negative when the pg_attribute.attname contains pg.dropped, so those rows can handled by using the condition attnum > 0. Moreover, since test_func does not drop any columns, I don't think it is generating the pg.dropped column.
I added code showing how I tested test_func and the results I am seeing -- no pg.dropped column. If you can show a setup where test_func generates a pg.dropped column I'd very much like to see it.
ya, i don't know what the problem is, anyways, I've addedAND NOT attname LIKE '%.....% and seems to solve it. But the other error I am bumping into now is the could not open file "/some_filepath", though file exists. Do you happen to know what might be causing this?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.