PostgreSQL is an object-relational database management system (ORDBMS) with very complete features. It is an object-relational database management system based on POSTGRES, version 4.2, developed by the Department of Computer Science at the University of California. Many of the leading concepts of POSTGRES appear in commercial website databases only at a relatively late time. PostgreSQL supports most SQL standards and provides many other modern features, such as complex queries, foreign keys, triggers, views, transaction integrity, multi-version concurrency control, etc. Similarly, PostgreSQL can also be extended with many methods, such as by adding new data types, functions, operators, clustered functions, index methods, procedural languages, etc. Additionally, because of the flexibility of the license, anyone can use, modify and distribute PostgreSQL for free for any purpose.
In the past, data extraction was extracted from other databases to the postgres database, so choose kettle. In the new project requirements, the csv file data given by the customer needs to be extracted to postgresql, and the relevant fields need to be cleaned. First, I declare that kettle can also be implemented, but I always feel that relying on third-party software to extract data is not as good as operating the database directly. Unless I have to force it, try not to use it (purely personal opinion). So we studied the need to directly connect to the database to process this part of the requirements. The code is as follows
#!/bin/bash # ***************************************************** # ** linux_schedule_test # ***************************************************** #\copy test from '/etl-script/' delimiter ',' csv header encoding 'UTF8'; # copy test_copy to 'D:\test_copy1.csv' delimiter ',' csv header encoding 'GBK'; export PATH=/usr/pgsql-10/bin:/usr/bin; psql "host=127.0.0.1 port=5434 user=admin password=123456 dbname=linux_schedule_test" << EOF #2>/dev/null \copy test from '/etl-script/' delimiter ',' csv header encoding 'GBK'; create table test$(date +%Y%m%d_%H%m%S) as select * from test; \q EOF
export PATH=/usr/pgsql-10/bin:/usr/bin: Because of environmental scope problems, it is best to add the PATH path to the command to be used at the beginning of the script.
psql "host=127.0.0.1 port=5434 user=admin password=123456 dbname=linux_schedule_test": Connect to the database
<< EOF: Stay on the psql client through redirection.
#2>/dev/null script errors and does not output. It is recommended to comment out during debugging, otherwise you will not know why the script is not running.
\copy test from '/etl-script/' delimiter ',' csv header encoding 'GBK';: Extract csv file data through the copy command.
create table test$(date +%Y%m%d_%H%m%S) as select * from test;: Back up the test table data, for testing.
\q: Exit psql. If you don't write this, you can also accept the psql client.
EOF: End the redirection.
This is all about this article about shell script operation postgresql. For more related shell script operation postgresql content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!