Postgres table with encrypted fields and the psql copy command. ERROR: Wrong key or corrupt data SQL...
up vote
0
down vote
favorite
Problem after postgres psql copy to csv and psql copy back field is no longer encrypted. After loading back it says ERROR: Wrong key or corrupt data SQL state:39000
I have a PostreSQL table with a encrypted field in it (using pgcrypto extension). The field is defined with bytea. I used the command:
psql -d dbname -U username -w -c "copy (select * from sch.table) to 'C:/directory/subdirectory/file.csv' '|' CSV HEADER;"
command to put it in a csv file.
Then I tried to put it in another table defined the exact way (same create definitions with bytea on same fields), because I am creating a new table with this data and more to be added.
Then I used:
psql -d dbname -U username -w -c "copy sch.newtable FROM 'C:/directory/subdirectory/file.csv' DELIMITER '|' CSV HEADER;" to reload it.
I assumed the bytea column would be dumped and loaded back the same, but it is not. After loading back it says ERROR: Wrong key or corrupt data SQL state:39000
Do I need some extra options on the psql commands to take this into account?
postgresql csv copy psql
asked Nov 21 at 16:37
klmbear
213
add a comment |
up vote
0
down vote
favorite
Problem after postgres psql copy to csv and psql copy back field is no longer encrypted. After loading back it says ERROR: Wrong key or corrupt data SQL state:39000
I have a PostreSQL table with a encrypted field in it (using pgcrypto extension). The field is defined with bytea. I used the command:
psql -d dbname -U username -w -c "copy (select * from sch.table) to 'C:/directory/subdirectory/file.csv' '|' CSV HEADER;"
command to put it in a csv file.
Then I tried to put it in another table defined the exact way (same create definitions with bytea on same fields), because I am creating a new table with this data and more to be added.
Then I used:
psql -d dbname -U username -w -c "copy sch.newtable FROM 'C:/directory/subdirectory/file.csv' DELIMITER '|' CSV HEADER;" to reload it.
I assumed the bytea column would be dumped and loaded back the same, but it is not. After loading back it says ERROR: Wrong key or corrupt data SQL state:39000
Do I need some extra options on the psql commands to take this into account?
postgresql csv copy psql
asked Nov 21 at 16:37
klmbear
213
Most of all, you need to tell up theCREATE TABLEstatements and the data in there. In other words, we need a reproducible test case.
– Laurenz Albe
Nov 21 at 18:44
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
Problem after postgres psql copy to csv and psql copy back field is no longer encrypted. After loading back it says ERROR: Wrong key or corrupt data SQL state:39000
I have a PostreSQL table with a encrypted field in it (using pgcrypto extension). The field is defined with bytea. I used the command:
psql -d dbname -U username -w -c "copy (select * from sch.table) to 'C:/directory/subdirectory/file.csv' '|' CSV HEADER;"
command to put it in a csv file.
Then I tried to put it in another table defined the exact way (same create definitions with bytea on same fields), because I am creating a new table with this data and more to be added.
Then I used:
psql -d dbname -U username -w -c "copy sch.newtable FROM 'C:/directory/subdirectory/file.csv' DELIMITER '|' CSV HEADER;" to reload it.
I assumed the bytea column would be dumped and loaded back the same, but it is not. After loading back it says ERROR: Wrong key or corrupt data SQL state:39000
Do I need some extra options on the psql commands to take this into account?
postgresql csv copy psql
asked Nov 21 at 16:37
klmbear
213
Problem after postgres psql copy to csv and psql copy back field is no longer encrypted. After loading back it says ERROR: Wrong key or corrupt data SQL state:39000
I have a PostreSQL table with a encrypted field in it (using pgcrypto extension). The field is defined with bytea. I used the command:
psql -d dbname -U username -w -c "copy (select * from sch.table) to 'C:/directory/subdirectory/file.csv' '|' CSV HEADER;"
command to put it in a csv file.
Then I tried to put it in another table defined the exact way (same create definitions with bytea on same fields), because I am creating a new table with this data and more to be added.
Then I used:
psql -d dbname -U username -w -c "copy sch.newtable FROM 'C:/directory/subdirectory/file.csv' DELIMITER '|' CSV HEADER;" to reload it.
I assumed the bytea column would be dumped and loaded back the same, but it is not. After loading back it says ERROR: Wrong key or corrupt data SQL state:39000
Do I need some extra options on the psql commands to take this into account?
postgresql csv copy psql
postgresql csv copy psql
asked Nov 21 at 16:37
klmbear
213
asked Nov 21 at 16:37
klmbear
213
asked Nov 21 at 16:37
klmbear
213
asked Nov 21 at 16:37
klmbear
213
asked Nov 21 at 16:37
klmbear
213
213
Most of all, you need to tell up theCREATE TABLEstatements and the data in there. In other words, we need a reproducible test case.
– Laurenz Albe
Nov 21 at 18:44
add a comment |
Most of all, you need to tell up theCREATE TABLEstatements and the data in there. In other words, we need a reproducible test case.
– Laurenz Albe
Nov 21 at 18:44
Most of all, you need to tell up the
CREATE TABLE statements and the data in there. In other words, we need a reproducible test case.– Laurenz Albe
Nov 21 at 18:44
Most of all, you need to tell up the
CREATE TABLE statements and the data in there. In other words, we need a reproducible test case.– Laurenz Albe
Nov 21 at 18:44
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53416710%2fpostgres-table-with-encrypted-fields-and-the-psql-copy-command-error-wrong-key%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Most of all, you need to tell up the
CREATE TABLEstatements and the data in there. In other words, we need a reproducible test case.– Laurenz Albe
Nov 21 at 18:44