partitioned table

2020-01-09 Thread Pepe TD Vo
I have table and partitioned for year year like this:
CREATE TABLE ecisdrdm.bnft_curr_fact (bnft_fact_id  numeric(38), 
bene_cntry_of_brth_id  numeric(38), bene_cntry_of_rsdc_id  numeric(38), 
bene_cntry_of_ctznshp_id  numeric(38), frm_id  numeric(38), svc_ctr_id  
numeric(38), actn_dt_in_id  numeric(38), actn_tm_in_id  numeric(38), src_sys_id 
 numeric(38), bnft_hist_actn_id  numeric(38), bene_id  numeric(38), 
bene_end_dt_id  numeric(38), petnr_app_id  numeric(38), atty_id  numeric(38), 
uscis_emp_id  numeric(38), application_id  numeric(38) default -100, 
rmtr_id  numeric(38), prpr_id  numeric(38), mig_filename  varchar(80), 
mig_insert_dt  timestamp, mig_modified_dt  timestamp) partition by range 
(actn_dt_in_id)TABLESPACE ecisdrdm_data;

CREATE INDEX bnftn_fact_frmid_bmx1 ON ecisdrdm.bnft_curr_fact 
(frm_id)TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_attyid_bmx1 ON ecisdrdm.bnft_curr_fact 
(atty_id)TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_applicatiid_bti1 ON ecisdrdm.bnft_curr_fact 
(applicatiON_id)TABLESPACE ecisdrdm_index;
CREATE INDEX src_sys_id_actn_dt_bmx1 ON ecisdrdm.bnft_curr_fact (src_sys_id, 
actn_dt_in_id)TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_bnftfactid_bti1 ON ecisdrdm.bnft_curr_fact 
(bnft_fact_id)TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_actndtinid_bmx1 ON ecisdrdm.bnft_curr_fact 
(actn_dt_in_id)TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_coposit3_bmx1 ON ecisdrdm.bnft_curr_fact (svc_ctr_id, 
uscis_emp_id)TABLESPACE ecisdrdm_index;
CREATE INDEX src_sys_id_actn_dt_saa ON ecisdrdm.bnft_curr_fact (src_sys_id, 
actn_dt_in_id, applicatiON_id)TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_beneid_bmx1 ON ecisdrdm.bnft_curr_fact 
(bene_id)TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_petnrappid_bti1 ON ecisdrdm.bnft_curr_fact 
(petnr_app_id)TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_uscisempid_bmx1 ON ecisdrdm.bnft_curr_fact 
(uscis_emp_id)TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_bnfhisactid_bmx1 ON ecisdrdm.bnft_curr_fact 
(bnft_hist_actn_id)TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_src_sys_id_bmx1 ON ecisdrdm.bnft_curr_fact 
(src_sys_id)TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_benenddtid_bmx1 ON ecisdrdm.bnft_curr_fact 
(bene_end_dt_id)TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_prprid_bmx1 ON ecisdrdm.bnft_curr_fact 
(prpr_id)TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_svcctrid_bmx1 ON ecisdrdm.bnft_curr_fact (svc_ctr_id)
TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_benctrysdcid_bmx1 ON ecisdrdm.bnft_curr_fact 
(bene_cntry_of_rsdc_id)
TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_benctrybrtid_bmx1 ON ecisdrdm.bnft_curr_fact 
(bene_cntry_of_brth_id)
TABLESPACE ecisdrdm_index;
as same as stg_bnft_curr_fact table, it's partitioned too.when I manually 
mocking the data into both tables are fine and when I run the procedure, I get 
errorcode: 42P10 MSG: thereis no unique or exclusion constraint matching on the 
CONFLICT specification
the procedure is 
CREATE OR REPLACE FUNCTION ecisdrdm.pr_mig_stg_bnft_curr_fact( OUT v_ret text)  
  RETURNS text    LANGUAGE 'plpgsql'
    COST 100    VOLATILE AS $BODY$ DECLARE  v_module           text = 
'pr_mig_stg_bnft_curr_fact '; host text = inet_server_addr(); errorcode     
  text; errormsg     text; errormsg_detail  text;    errormsg_hint text; 
BEGIN
-- MERGING: STG_BNFT_CURR_FACT into BNFT_CURR_FACT
INSERT INTO ecisdrdm.bnft_curr_fact AS prod (bnft_fact_id, 
bene_cntry_of_brth_id, bene_cntry_of_rsdc_id,  bene_cntry_of_ctznshp_id, 
frm_id, svc_ctr_id, actn_dt_in_id, actn_tm_in_id, src_sys_id,  
bnft_hist_actn_id, bene_id, bene_end_dt_id, petnr_app_id, atty_id, 
uscis_emp_id, application_id,  rmtr_id, prpr_id, mig_filename)SELECT 
stg.bnft_fact_id, stg.bene_cntry_of_brth_id, stg.bene_cntry_of_rsdc_id,  
stg.bene_cntry_of_ctznshp_id, stg.frm_id, stg.svc_ctr_id, stg.actn_dt_in_id, 
stg.actn_tm_in_id, stg.src_sys_id,  stg.bnft_hist_actn_id, stg.bene_id, 
stg.bene_end_dt_id, stg.petnr_app_id, stg.atty_id, stg.uscis_emp_id, 
stg.application_id,  stg.rmtr_id, stg.prpr_id, stg.mig_filenameFROM 
ecisdrdm.stg_bnft_curr_fact stgON CONFLICT ("bnft_fact_id") DO UPDATE SET 
(bnft_fact_id, bene_cntry_of_brth_id, bene_cntry_of_rsdc_id,  
bene_cntry_of_ctznshp_id, frm_id, svc_ctr_id, actn_dt_in_id, actn_tm_in_id, 
src_sys_id,  bnft_hist_actn_id, bene_id, bene_end_dt_id, petnr_app_id, atty_id, 
uscis_emp_id, application_id,  rmtr_id, prpr_id, mig_filename, 
mig_modified_dt)= (SELECT stg.bnft_fact_id, stg.bene_cntry_of_brth_id, 
stg.bene_cntry_of_rsdc_id,  stg.bene_cntry_of_ctznshp_id, stg.frm_id, 
stg.svc_ctr_id, stg.actn_dt_in_id, stg.actn_tm_in_id, stg.src_sys_id,  
stg.bnft_hist_actn_id, stg.bene_id, stg.bene_end_dt_id, stg.petnr_app_id, 
stg.atty_id, stg.uscis_emp_id, stg.application_id,  stg.rmtr_id, stg.prpr_id, 
stg.mig_filename, current_timestampFROM ecisdrdm.stg_bnft_curr_fact stgWHERE 
prod.application_id = stg.application_id);
   

migrate off oracle data to postgres

2020-03-31 Thread Pepe TD Vo
Hello,
I have a huge data on Oracle, would you please suggest how to migrate all data 
off Oracle to Postgres?  Do I need to export into csv and upload to postgres?  
Ora2pg is limited data from oracle to postgresql right?

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success

Re: migrate off oracle data to postgres

2020-03-31 Thread Pepe TD Vo
I have tables ddl data definitions already.  Only need to move the data over 
off Oracle to Postgres
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success 

On Tuesday, March 31, 2020, 11:46:58 AM EDT, JC JC 
 wrote:  
 
 #yiv3534479505 P {margin-top:0;margin-bottom:0;}In my view ora2pg can do most 
of the stuff you requested.ora2pg can be used to generate the DDL from oracle 
to postgres based on attributes set in ora2pg conf file (for example TYPE 
TABLE, will allow getting oracle DDL in an output file, TYPE COPY will bring 
the data). No doubt you need to set remaining attributes based on the 
requirement.
Export to CSV is not required, as mentioned TYPE COPY will do a load of data if 
you like it to do Direct Data Streaming.As of my limited experience I never 
encountered any issue on the limit on data being transferred, but definitely 
the load time vary depending on the environment.
Regardsjc



From: Pepe TD Vo 
Sent: 31 March 2020 15:15
To: Pgsql-admin ; Pgsql-general 
; PgAdmin Support List 

Subject: migrate off oracle data to postgres Hello,
I have a huge data on Oracle, would you please suggest how to migrate all data 
off Oracle to Postgres?  Do I need to export into csv and upload to postgres?  
Ora2pg is limited data from oracle to postgresql right?

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success  

Re: migrate off oracle data to postgres

2020-03-31 Thread Pepe TD Vo
@JC - I do have ora2pg set up last year in test environment already and that 
what I suggested team using insert script to run in Postgres.  And I am not 
sure how long it would take to load? 1T data. Though if there's a tool liked 
MySQL workbench to pull the oracle export data pump and import into MySQL.
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success 

On Tuesday, March 31, 2020, 12:09:13 PM EDT, JC JC 
 wrote:  
 
 #yiv7970034777 P {margin-top:0;margin-bottom:0;}I am sure you might be 
referring this https://ora2pg.darold.net/documentation.html 

You need to create ora2pg conf file with TYPE COPY, and ALLOW  and remaining other attributes like 
ORACLE_DSNORACLE_PWDORACLE_USER
similarly PG_DSNPG_USERPG_PWD etc etc.
Cheersjc
From: Pepe TD Vo 
Sent: 31 March 2020 16:51
To: Pgsql-admin ; Pgsql-general 
; PgAdmin Support List 
; JC JC 
Subject: Re: migrate off oracle data to postgres I have tables ddl data 
definitions already.  Only need to move the data over off Oracle to Postgres
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success

On Tuesday, March 31, 2020, 11:46:58 AM EDT, JC JC 
 wrote:

In my view ora2pg can do most of the stuff you requested.ora2pg can be used to 
generate the DDL from oracle to postgres based on attributes set in ora2pg conf 
file (for example TYPE TABLE, will allow getting oracle DDL in an output file, 
TYPE COPY will bring the data). No doubt you need to set remaining attributes 
based on the requirement.
Export to CSV is not required, as mentioned TYPE COPY will do a load of data if 
you like it to do Direct Data Streaming.As of my limited experience I never 
encountered any issue on the limit on data being transferred, but definitely 
the load time vary depending on the environment.
Regardsjc



From: Pepe TD Vo 
Sent: 31 March 2020 15:15
To: Pgsql-admin ; Pgsql-general 
; PgAdmin Support List 

Subject: migrate off oracle data to postgres Hello,
I have a huge data on Oracle, would you please suggest how to migrate all data 
off Oracle to Postgres?  Do I need to export into csv and upload to postgres?  
Ora2pg is limited data from oracle to postgresql right?

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success  

create batch script to import into postgres tables

2020-06-16 Thread Pepe TD Vo
good morning experts,
I nêd to set up a batch script to import multi csv files to import them to 
Postgres tables.  Each csv files will be named table1_todaydate.csv, 
table2_todaydate.csv, etc... tablen_todaydate.csv.  Each csv file will import 
to its table and how do I execute the script to called psql from AWS?  Do I 
need to create each batch file for import each table?
all export file is store in c:\export\files\
thank you.
Bach-Nga





Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love, and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success



Re: create batch script to import into postgres tables

2020-06-16 Thread Pepe TD Vo
I can run \copy in Linux with individual csv file into the table fine and run 
import using pgadmin into AWS instance.  I am trying to run \copy all csv files 
import into its own table in Linux and in AWS instance. If all csv files into 
one table is fine but each csv for each table.  Should I create one batch job 
for each imported table?  If each batch file import csv to its table would be 
fine via \copy table_name(col1, col2, ... coln) from '/path/tablename.csv' 
delimiter ',' csv header;  right?
Also, the problem is I can't pull/execute psql from window client to pull the 
psql in aws instance and don't know how to create the batch script for this 
run.  I tried simple \copy pull from c:\tes.csv and psql is unknown.


Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love, and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success

 

On Tuesday, June 16, 2020, 10:39:45 AM EDT, Adrian Klaver 
 wrote:  
 
 On 6/16/20 7:30 AM, Pepe TD Vo wrote:
Please post to list also.
Ccing list.

> using psql in AWS instance
> also psql in Linux.  I can run psql in linux if create a batch file, but 
> don't know how to pull psql in aws instance if the batch script run in 
> Window client.  Also, I need help to pull each csv import to its own 
> table.  Should I create each batch script for each import table?

So the AWS instance and Linux instance are different?

To me the simplest solution would be to push the CSV files to the AWS 
instance and work from there. The files will need to be run through a 
Postgres command to be imported into a table.

Are you familiar with 
COPY(https://www.postgresql.org/docs/12/sql-copy.html) or 
\copy(https://www.postgresql.org/docs/12/app-psql.html)?


> 
> **
> *Bach-Nga
> 
> *No one in this world is pure and perfect.  If you avoid people for 
> their mistakes you will be alone. So judge less, love, and forgive 
> more.EmojiEmojiEmoji
> To call him a dog hardly seems to do him justice though in as much as he 
> had four legs, a tail, and barked, I admit he was, to all outward 
> appearances. But to those who knew him well, he was a perfect gentleman 
> (Hermione Gingold)
> 
> **Live simply **Love generously **Care deeply **Speak kindly.
> *** Genuinely rich *** Faithful talent *** Sharing success
> 
> 
> 
> 
> On Tuesday, June 16, 2020, 10:25:03 AM EDT, Adrian Klaver 
>  wrote:
> 
> 
> On 6/16/20 7:20 AM, Pepe TD Vo wrote:
>  > good morning experts,
>  >
>  > I nêd to set up a batch script to import multi csv files to import them
>  > to Postgres tables.  Each csv files will be named table1_todaydate.csv,
>  > table2_todaydate.csv, etc... tablen_todaydate.csv.  Each csv file will
>  > import to its table and how do I execute the script to called psql from
>  > AWS?  Do I need to create each batch file for import each table?
> 
> You have psql installed on your local(Windows?) machine?
> 
> Or are you using psql in your AWS instance?
> 
>  >
>  > all export file is store in c:\export\files\
>  >
>  > thank you.
>  >
>  > Bach-Nga
>  >
>  >
>  >
>  >
>  >
>  >
>  > **
>  > *Bach-Nga
>  >
>  > *No one in this world is pure and perfect.  If you avoid people for
>  > their mistakes you will be alone. So judge less, love, and forgive
>  > more.EmojiEmojiEmoji
> 
>  > To call him a dog hardly seems to do him justice though in as much as he
>  > had four legs, a tail, and barked, I admit he was, to all outward
>  > appearances. But to those who knew him well, he was a perfect gentleman
>  > (Hermione Gingold)
>  >
>  > **Live simply **Love generously **Care deeply **Speak kindly.
>  > *** Genuinely rich *** Faithful talent *** Sharing success
> 
>  >
>  >
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
> 


-- 
Adrian Klaver
adrian.kla...@aklaver.com
  

Re: create batch script to import into postgres tables

2020-06-18 Thread Pepe TD Vo
I have a Postgresql client installed and connected.  how can i create a batch 
script running from the client window?
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love, and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success

 

On Tuesday, June 16, 2020, 11:42:40 AM EDT, Pepe TD Vo  
wrote:  
 
 Yes, I do have putty installed but can't connect to the aws postgres instance. 
 Only work for oracle instance.  Only connect postgres instance using pgadmin.
follow the url and the login prompt for username and hung there.
thank you.
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love, and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success

 

On Tuesday, June 16, 2020, 11:17:21 AM EDT, Christopher Browne 
 wrote:  
 
 
On Tue, 16 Jun 2020 at 10:59, Pepe TD Vo  wrote:

I can run \copy in Linux with individual csv file into the table fine and run 
import using pgadmin into AWS instance.  I am trying to run \copy all csv files 
import into its own table in Linux and in AWS instance. If all csv files into 
one table is fine but each csv for each table.  Should I create one batch job 
for each imported table?  If each batch file import csv to its table would be 
fine via \copy table_name(col1, col2, ... coln) from '/path/tablename.csv' 
delimiter ',' csv header;  right?

There is no single straightforward answer to that.
Supposing I want a batch to either all be processed, or to all not process, 
then I might write a sql file like:
begin;\copy table_1 (c1, c2, c3) from '/path/tabledata1.csv' csv header;\copy 
table_2 (c1, c2, c3) from '/path/tabledata2.csv' csv header;\copy table_3 (c1, 
c2, c3) from '/path/tabledata3.csv' csv header;commit;
But you may be fine with having a separate SQL script for each table.
There will be conditions where one or the other is more appropriate, and that 
will be based on the requirements of the process.


Also, the problem is I can't pull/execute psql from window client to pull the 
psql in aws instance and don't know how to create the batch script for this 
run.  I tried simple \copy pull from c:\tes.csv and psql is unknown.


You cannot run psql without having it installed; there is a Windows installer 
for PostgreSQL, so you could use that to get it installed.
Hopefully there is an installer that will just install PostgreSQL client 
software (like psql, pg_dump, and notably *not* the database server software); 
I don't use WIndows, so I am not too familiar with that.
 -- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"


Re: create batch script to import into postgres tables

2020-06-18 Thread Pepe TD Vo
I get this part that separates SQL script for import each table, 
(import.sql)begin;
\copy table_1 (c1, c2, c3) from '/path/tabledata1.csv' csv header;
commit;
but when open the psql sql shell script it prompts line by line for localhost, 
port, db, user, and password.  If I set up a script and let it run it won't 
connect to the postgresql instance.  I want to know how to execute a batch 
script connect to the database/instance.   In oracle I created a shell script 
with all oracle_sid, oracle_home, and read the function/procedure... for psql, 
especially from window client, I did put psql_home and connect to the instance, 
it failed

c:\Progra~1\PostgreSQL\11\bin\psql -d PSCIDR -h localhost -p 5432 -U postgres 
-i import.sql
even I do a simple count
c:\Progra~1\PostgreSQL\11\bin\psql -d PSCIDR -h localhost -p 5432 -U postgres 
-c "select count(*) from tableA";

none of them is work. Try to learn how to execute its script.
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love, and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success

 

On Thursday, June 18, 2020, 12:08:44 PM EDT, Adrian Klaver 
 wrote:  
 
 On 6/18/20 8:20 AM, Pepe TD Vo wrote:

Please don't top post. The preferred style on this list is inline or 
bottom posting(https://en.wikipedia.org/wiki/Posting_style).

> I have a Postgresql client installed and connected.  how can i create a 
> batch script running from the client window?

Create a file with commands in it like the example from Christopher 
Browne that was posted earlier:

"There is no single straightforward answer to that.


Supposing I want a batch to either all be processed, or to all not process,
then I might write a sql file like:


begin;
\copy table_1 (c1, c2, c3) from '/path/tabledata1.csv' csv header;
\copy table_2 (c1, c2, c3) from '/path/tabledata2.csv' csv header;
\copy table_3 (c1, c2, c3) from '/path/tabledata3.csv' csv header;
commit;


But you may be fine with having a separate SQL script for each table.


There will be conditions where one or the other is more appropriate, and
that will be based on the requirements of the process."

Then point psql at it:

psql -d some_db -h some_host -U some_user -f the_file

Be aware that \copy is all or nothing. If there is a single failure in 
the copying the whole copy will rollback. Given that the one file per 
table might be preferable.

> 
> **
> *Bach-Nga
> 



-- 
Adrian Klaver
adrian.kla...@aklaver.com


  

Re: create batch script to import into postgres tables

2020-06-18 Thread Pepe TD Vo
>>psql -d production  -U postgres -c 'select count(*) from cell_per';
Null display is "NULL".
  count
---
    68
(1 row)

you can do this once you are in psql. But if you are running from shell script, 
it will be an error

>>What error messages do you get?
my shell script is:@echo off C:\Program Files\PostgreSQL\11\bin\psql -U 
PSmasteruser  -d PSCIDR -h hostname.amazonaws.com -p 5432 -c "select count(*) 
from tableA;"
pause
the error I have is 'C:\Program' is not recognized as an internal or external 
command, operable program or batch file.
I even surround the path in quotes because of space.@echo off cmd /c 
""C:\Program Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d PSCIDR -h 
hostname.amazonaws.com -p 5432 -c "select count(*) from tableA;"
pause
error is: 'C:\Program Files\PostgreSQL\11\bin\psql: illegal option -- u
when I put -P Password123 (or fully qualified password=Password123)
it gives me another error "pset: unknow option: Password123psql: could not set 
printing parameter "Password123"
I can connect from psql shell fine when it prompt hostname, username (but 
connect to postgres not to PSmasteruser, PSmasteruser username set up for aws 
maintenance postgres database, someone did, not me and the password is same on 
both).  Once I'm in psql and I can change to PSCIDR instance fine and run 
select count(*) from tableA;


Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love, and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success

 

    On Thursday, June 18, 2020, 03:06:39 PM EDT, Adrian Klaver 
 wrote:  
 
 On 6/18/20 9:40 AM, Pepe TD Vo wrote:
> I get this part that separates SQL script for import each table,
> 
> (import.sql)
> begin;
> \copy table_1 (c1, c2, c3) from '/path/tabledata1.csv' csv header;
> commit;
> 
> but when open the psql sql shell script it prompts line by line for 
> localhost, port, db, user, and password.  If I set up a script and let 
> it run it won't connect to the postgresql instance.  I want to know how 
> to execute a batch script connect to the database/instance.
> In oracle I created a shell script with all oracle_sid, oracle_home, and 
> read the function/procedure... for psql, especially from window client, 
> I did put psql_home and connect to the instance, it failed
> 
> c:\Progra~1\PostgreSQL\11\bin\psql -d PSCIDR -h localhost -p 5432 -U 
> postgres -i import.sql

The above should be -f import.sql. AFAIK there is no -i for psql, so 
that should be failing.

> 
> even I do a simple count
> 
> c:\Progra~1\PostgreSQL\11\bin\psql -d PSCIDR -h localhost -p 5432 -U 
> postgres -c "select count(*) from tableA";

psql -d production  -U postgres -c 'select count(*) from cell_per';
Null display is "NULL".
  count
---
    68
(1 row)

psql -d production  -U postgres -c 'select count(*) from cell_per'
Null display is "NULL".
  count
---
    68

psql -d production  -U postgres -c 'select count(*) from cell_per;'
Null display is "NULL".
  count
---
    68


> 
> none of them is work. Try to learn how to execute its script.

What error messages do you get?

> 
> **
> *Bach-Nga
> 
> *No one in this world is pure and perfect.  If you avoid people for 
> their mistakes you will be alone. So judge less, love, and forgive 
> more.EmojiEmojiEmoji
> To call him a dog hardly seems to do him justice though in as much as he 
> had four legs, a tail, and barked, I admit he was, to all outward 
> appearances. But to those who knew him well, he was a perfect gentleman 
> (Hermione Gingold)
> 
> **Live simply **Love generously **Care deeply **Speak kindly.
> *** Genuinely rich *** Faithful talent *** Sharing success
> 
> 
> 
> 
> On Thursday, June 18, 2020, 12:08:44 PM EDT, Adrian Klaver 
>  wrote:
> 
> 
> On 6/18/20 8:20 AM, Pepe TD Vo wrote:
> 
> Please don't top post. The preferred style on this list is inline or
> bottom posting(https://en.wikipedia.org/wiki/Posting_style).
> 
>  > I have a Postgresql client installed and connected.  how can i create a
>  > batch script running from the client window?
> 
> Create a file with commands in it like the example from Christopher
> Browne that was posted earlier:
> 
> "There is no single straightforward answer to that.
> 
> 
> Supposing I want a batch 

Re: create batch script to import into postgres tables

2020-06-18 Thread Pepe TD Vo
thank you for the link.  I did try it and it's still errorecho 'SELECT count(*) 
FROM tableA;' | C:\Program Files\PostgreSQL\11\bin\psql -U PSmasteruser -d 
PSCIDR -h hostname.amazonaws.com -p 5432
I can run 'psql -u postgres -d PSCIDR -p 5432' (on Linux server, still learning 
how to run it from psql shell in Window) fine from psql prompt.  Just still 
wonder how to connect directly to the instance PSCIDR from scripting in both 
aws and linux.  Otherwise manually run using pgAdmin.
v/r,
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love, and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success

 

On Thursday, June 18, 2020, 07:00:37 PM EDT, Adrian Klaver 
 wrote:  
 
 On 6/18/20 12:54 PM, Pepe TD Vo wrote:
>>>psql -d production  -U postgres -c 'select count(*) from cell_per';
> Null display is "NULL".
>    count
> ---
>      68
> (1 row)
> 
> you can do this once you are in psql. But if you are running from shell 
> script, it will be an error
> 
>  >>What error messages do you get?
> 
> my shell script is:
> @echo off
> C:\Program Files\PostgreSQL\11\bin\psql -U PSmasteruser  -d PSCIDR -h 
> hostname.amazonaws.com -p 5432 -c "select count(*) from tableA;"
> pause
> 
> the error I have is 'C:\Program' is not recognized as an internal or 
> external command, operable program or batch file.
> 
> I even surround the path in quotes because of space.
> @echo off
> cmd /c ""C:\Program Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d 
> PSCIDR -h hostname.amazonaws.com -p 5432 -c "select count(*) from tableA;"
> pause

Not sure how quoting works in Windows shell scripts, but pretty sure the 
above has unbalanced quotes.


> 
> error is: 'C:\Program Files\PostgreSQL\11\bin\psql: illegal option -- u
> 
> when I put -P Password123 (or fully qualified password=Password123)

I would recommend spending some time here:

https://www.postgresql.org/docs/12/app-psql.html

to see what the options are. Hint: -P is not the option for password.

> 
> it gives me another error "pset: unknow option: Password123
> psql: could not set printing parameter "Password123"

It is the option for pset as the error message says.

> 
> I can connect from psql shell fine when it prompt hostname, username 
> (but connect to postgres not to PSmasteruser, PSmasteruser username set 
> up for aws maintenance postgres database, someone did, not me and the 
> password is same on both).  Once I'm in psql and I can change to PSCIDR 
> instance fine and run select count(*) from tableA;
> 
> 
> 
> **
> *Bach-Nga
> 


-- 
Adrian Klaver
adrian.kla...@aklaver.com
  

Re: create batch script to import into postgres tables

2020-06-19 Thread Pepe TD Vo
thank you,   I tried that too, remove the quote around the echo and it prompt 
for password, as I mentioned no matter I put -P mypassword no matter what I 
spell out password=mypassword still argument error
>>echo select count(*) from tableA; | "C:\Program Files\PostgreSQL\11\bin\psql" 
>>-U PSmasteruser -d PSCIDR -h hostname.amazonaws.com -p 5432  

>> echo select count(*) from tableA; | "C:\Program 
>>Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d PSCIDR -h 
>>hostname.amazonaws.com -p 5432 password=mypassword
all usernames are same password.
thank you so much for all input.
v/r,
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love, and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success

 

On Thursday, June 18, 2020, 09:25:41 PM EDT,  wrote:  
 
 
Remove the quotes around echo

echo select count(*) from web_20200619; | "C:\Program 
Files\postgresql\11\bin\psql" -d *** -h *** -U ***

or, store your query into a text file and use 

psql -f query.sql




Sent from my mobile phone

Le 19 juin 2020 à 02:00, Adrian Klaver  a écrit :



On 6/18/20 4:37 PM, Pepe TD Vo wrote:

thank you for the link.  I did try it and it's still error


echo 'SELECT count(*) FROM tableA;' |


C:\Program Files\PostgreSQL\11\bin\psql -U PSmasteruser -d PSCIDR -h 
hostname.amazonaws.com -p 5432



This is getting old. The error is?

I can run 'psql -u postgres -d PSCIDR -p 5432' (on Linux server, still learning 
how to run it from psql shell in Window) fine from psql prompt.  Just still 
wonder how to connect directly to the instance PSCIDR from scripting in both 
aws and linux.  Otherwise manually run using pgAdmin.


v/r,


**


*Bach-Nga



-- 
Adrian Klaver
adrian.kla...@aklaver.com



  

Re: create batch script to import into postgres tables

2020-06-19 Thread Pepe TD Vo
Thank you sir and I am sorry for the typo not having "--" on password.  I did 
spelling out with --password=mypassword
>> echo select count(*) from tableA; | "C:\Program 
>>Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d PSCIDR -h 
>>hostname.amazonaws.com -p 5432 --password=mypassword

even -W for password>> echo select count(*) from tableA; | "C:\Program 
Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d PSCIDR -h 
hostname.amazonaws.com -p 5432 -W=mypassword

none of them work, still prompt me for password to type in.  I will look into 
the pgpassfile which I know it will fail again.

very respectfully,
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love, and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success

 

On Friday, June 19, 2020, 09:19:35 AM EDT, Adrian Klaver 
 wrote:  
 
 On 6/19/20 4:12 AM, Pepe TD Vo wrote:
> thank you,   I tried that too, remove the quote around the echo and it 
> prompt for password, as I mentioned no matter I put -P mypassword no 
> matter what I spell out password=mypassword still argument error

Once again -P has nothing to do with password. Also --password does not 
take an argument, it is meant to be used as is. The purpose is to force 
a password prompt. This is all spelled out here:

https://www.postgresql.org/docs/12/app-psql.html

Also spelled out in above is:

" It is also convenient to have a ~/.pgpass file to avoid regularly 
having to type in passwords. See Section 33.15 for more information."

And Section 33.15:

https://www.postgresql.org/docs/12/libpq-pgpass.html

"The file .pgpass in a user's home directory can contain passwords to be 
used if the connection requires a password (and no password has been 
specified otherwise).  ..."

Read more at link for how to do that.

> 
>  >>echo select count(*) from tableA; | "C:\Program 
> Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d PSCIDR -h 
> hostname.amazonaws.com -p 5432
> 
>  >> echo select count(*) from tableA; | "C:\Program 
> Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d PSCIDR -h 
> hostname.amazonaws.com -p 5432 password=mypassword
> 
> all usernames are same password.
> 
> thank you so much for all input.
> 
> v/r,
> 
> **
> *Bach-Nga

-- 
Adrian Klaver
adrian.kla...@aklaver.com


  

Re: create batch script to import into postgres tables

2020-06-19 Thread Pepe TD Vo
appreciate for clarification, all inputs and teaching me more in PostgreSQL
have a good weekend and happy father's day to all who is Father.
v/r, 
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love, and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success

 

On Friday, June 19, 2020, 10:57:59 AM EDT, Adrian Klaver 
 wrote:  
 
 On 6/19/20 7:52 AM, David G. Johnston wrote:
> On Fri, Jun 19, 2020 at 6:58 AM Adrian Klaver  <mailto:adrian.kla...@aklaver.com>> wrote:
> 
>    On 6/19/20 6:53 AM, Pepe TD Vo wrote:
>      > Thank you sir and I am sorry for the typo not having "--" on
>    password.
>      > I did spelling out with --password=mypassword
> 
>    Please go back and read my post again.
> 
> 
> To be clear, there is no way to supply a password as a command line 
> argument.  It is fundamentally a bad idea and we don't even make it an 
> option.

Actually that is not entirely true, see my follow up post.

> 
> You need to decide on one of the actual ways of supplying a password, or 
> choose an alternative authentication method like peer.
> 
> David J.
> 


-- 
Adrian Klaver
adrian.kla...@aklaver.com


  

Re: create batch script to import into postgres tables

2020-06-19 Thread Pepe TD Vo
thank you for all the information but I have no problem connecting to the 
database using pgAdmin and/or directly psql from the postgres database.
I need to set up a batch/cron job to run in Linux/AWS to ingest the data.  
Therefore pgadmin GUI is not an option. I used pgAdmin to create tables, 
triggers, function triggers and manual importing/exporting.  You can't schedule 
a time to run importing from  csv file(s) using pgAdmin as same OEM.  
The select table in this question is just an example for me to create scripting 
to see it connect or not.  No one at work to manually run the script and enter 
the password.  Need to bypass it like Oracle scripting.  Since this is not an 
option to put the password to connect to the database and run the script(s), we 
will find another way to do.
I am thankful for all the input.  
v/r,

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love, and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success

 

On Friday, June 19, 2020, 02:24:05 PM EDT, Adrian Klaver 
 wrote:  
 
 On 6/19/20 8:30 AM, cgerard...@gmail.com wrote:
> There is an alternate solution, which is to launch pgadmin GUI, connect 
> to the database, tick « save password »
> Then psql won’t prompt fir password any more.

Pretty sure that is only within the context of pgAdmin.

> Pay however attention to the security concern.
> 
> Sent from my mobile phone
> 
>> Le 19 juin 2020 à 17:07, Pepe TD Vo  a écrit :
>>
>> 
>> appreciate for clarification, all inputs and teaching me more in 
>> PostgreSQL
>>
>> have a good weekend and happy father's day to all who is Father.
>>
>> v/r,
>>
>> **
>> *Bach-Nga
>>
>> *No one in this world is pure and perfect.  If you avoid people for 
>> their mistakes you will be alone. So judge less, love, and forgive 
>> more.EmojiEmojiEmoji
>> To call him a dog hardly seems to do him justice though in as much as 
>> he had four legs, a tail, and barked, I admit he was, to all outward 
>> appearances. But to those who knew him well, he was a perfect 
>> gentleman (Hermione Gingold)
>>
>> **Live simply **Love generously **Care deeply **Speak kindly.
>> *** Genuinely rich *** Faithful talent *** Sharing success
>>
>>
>>
>>
>> On Friday, June 19, 2020, 10:57:59 AM EDT, Adrian Klaver 
>>  wrote:
>>
>>
>> On 6/19/20 7:52 AM, David G. Johnston wrote:
>> > On Fri, Jun 19, 2020 at 6:58 AM Adrian Klaver 
>> mailto:adrian.kla...@aklaver.com>
>> > <mailto:adrian.kla...@aklaver.com 
>> <mailto:adrian.kla...@aklaver.com>>> wrote:
>> >
>> >    On 6/19/20 6:53 AM, Pepe TD Vo wrote:
>> >      > Thank you sir and I am sorry for the typo not having "--" on
>> >    password.
>> >      > I did spelling out with --password=mypassword
>> >
>> >    Please go back and read my post again.
>> >
>> >
>> > To be clear, there is no way to supply a password as a command line
>> > argument.  It is fundamentally a bad idea and we don't even make it an
>> > option.
>>
>> Actually that is not entirely true, see my follow up post.
>>
>> >
>> > You need to decide on one of the actual ways of supplying a 
>> password, or
>> > choose an alternative authentication method like peer.
>> >
>> > David J.
>>
>> >
>>
>>
>> -- 
>> Adrian Klaver
>> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>
>>


-- 
Adrian Klaver
adrian.kla...@aklaver.com