Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to insert from linux to postgreSQL

How to insert from linux to postgreSQL

From
"Campbell, Lance"
Date:

PostgreSQL 10.x

 

Use case:

I want to read a log file on my linux server and write each line out to a row in a postgreSQL table.  When I do inserts however I want to make sure I am using prepared statements to avoid SQL injections.  I know how to do this is Java but I have not found an example using a linux bash scripts. 

 

Does anyone have an example they could point me to?

 

Thanks,

 

Lance

 

Re: How to insert from linux to postgreSQL

From
Ron
Date:
On 4/8/19 2:03 PM, Campbell, Lance wrote:

PostgreSQL 10.x

 

Use case:

I want to read a log file on my linux server and write each line out to a row in a postgreSQL table.


Presumably some manipulations need to be performed so that you can't just do COPY FROM STDIN?

When I do inserts however I want to make sure I am using prepared statements to avoid SQL injections.  I know how to do this is Java but I have not found an example using a linux bash scripts. 

 

Does anyone have an example they could point me to?


I think the problem is that you need an open connection to use prepared statements, and you can't do that in bash.  What about using Python?  (After all, that's a scripting language, too.)


--
Angular momentum makes the world go 'round.

Re: How to insert from linux to postgreSQL

From
Scott Whitney
Date:
Is the log file "ready" for INSERT, line by line? If not, you need to preprocess it. The 2 solutions I would use are psycopg for Python or sed + INSERT depending.
From: Ron <ronljohnsonjr@gmail.com>
Sent: Monday, April 8, 2019 2:18:18 PM
To: pgsql-admin@lists.postgresql.org
Subject: Re: How to insert from linux to postgreSQL
 
On 4/8/19 2:03 PM, Campbell, Lance wrote:

PostgreSQL 10.x

 

Use case:

I want to read a log file on my linux server and write each line out to a row in a postgreSQL table.


Presumably some manipulations need to be performed so that you can't just do COPY FROM STDIN?

When I do inserts however I want to make sure I am using prepared statements to avoid SQL injections.  I know how to do this is Java but I have not found an example using a linux bash scripts. 

 

Does anyone have an example they could point me to?


I think the problem is that you need an open connection to use prepared statements, and you can't do that in bash.  What about using Python?  (After all, that's a scripting language, too.)


--
Angular momentum makes the world go 'round.


 

Scott Whitney
Director of IT

scott@journyx.com | +1 (800) 755-9878

7600 Burnet Road, Ste. 300
Austin, TX 78757

Visit us at www.journyx.com



To unsubscribe from Journyx promotional emails, click here or visit http://journyx.com/communication-preferences.

Re: How to insert from linux to postgreSQL

From
Rui DeSousa
Date:
I would use psql \copy command within a shell script or just not use it.  

Personally, I would choose Perl or GoLang if it requires parsing the data before inserting.  



On Apr 8, 2019, at 3:03 PM, Campbell, Lance <lance@illinois.edu> wrote:

PostgreSQL 10.x
 
Use case:
I want to read a log file on my linux server and write each line out to a row in a postgreSQL table.  When I do inserts however I want to make sure I am using prepared statements to avoid SQL injections.  I know how to do this is Java but I have not found an example using a linux bash scripts. 
 
Does anyone have an example they could point me to?
 
Thanks,
 
Lance

Re: How to insert from linux to postgreSQL

From
"Peter M. Groen"
Date:
Or just use PERL as it was designed to (pre)process large amounts of
texts... psql support is pretty great....


-- 
Open Systems Development B.V.
Peter M. Groen
Rooseveltstraat 18P
2321 BM  Leiden
Telef  : 
Mobile : +31 6 811 0 3537
Email  : pgroen@osdev.nl


On Mon, 8 Apr 2019 19:20:03 +0000, Scott Whitney wrote :
>Is the log file "ready" for INSERT, line by line? If not, you need to
>preprocess it. The 2 solutions I would use are psycopg for Python or
>sed + INSERT depending. ________________________________ From: Ron
><ronljohnsonjr@gmail.com> Sent: Monday, April 8, 2019 2:18:18 PM
>To: pgsql-admin@lists.postgresql.org
>Subject: Re: How to insert from linux to postgreSQL
>
>On 4/8/19 2:03 PM, Campbell, Lance wrote:
>PostgreSQL 10.x
>
>Use case:
>I want to read a log file on my linux server and write each line out
>to a row in a postgreSQL table.
>
>Presumably some manipulations need to be performed so that you can't
>just do COPY FROM STDIN?
>
>When I do inserts however I want to make sure I am using prepared
>statements to avoid SQL injections.  I know how to do this is Java but
>I have not found an example using a linux bash scripts.
>
>Does anyone have an example they could point me to?
>
>I think the problem is that you need an open connection to use
>prepared statements, and you can't do that in bash.  What about using
>Python?  (After all, that's a scripting language, too.)
>
>
>--
>Angular momentum makes the world go 'round.
>
>
>
>[http://journyx.com/emailImages/ScottWhitney.png]
>
>[http://journyx.com/emailImages/linkedin.png]<https://www.linkedin.com/company/journyx-inc/>
>[http://journyx.com/emailImages/twitter.png]
><https://www.twitter.com/journyxinc>
>        [http://journyx.com/emailImages/bluebar.jpg]
>
>Scott Whitney
>Director of IT
>
>[http://journyx.com/emailImages/journyx.jpg]
>
>scott@journyx.com<mailto:scott@journyx.com> | +1 (800) 755-9878
>
>7600 Burnet Road, Ste. 300
>Austin, TX 78757
>
>Visit us at www.journyx.com<https://www.journyx.com/>
>
>
>
>To unsubscribe from Journyx promotional emails, click here
><http://journyx.com/communication-preferences> or visit
>http://journyx.com/communication-preferences.

Re: How to insert from linux to postgreSQL

From
Frank Alberto Rodriguez
Date:
Then why not using java? Just exec the .jar with the terminal params and do de job in java, or read a configuration file.
Or you could use PL/java.

Greetings


On Mon, 2019-04-08 at 19:03 +0000, Campbell, Lance wrote:

PostgreSQL 10.x

 

Use case:

I want to read a log file on my linux server and write each line out to a row in a postgreSQL table.  When I do inserts however I want to make sure I am using prepared statements to avoid SQL injections.  I know how to do this is Java but I have not found an example using a linux bash scripts. 

 

Does anyone have an example they could point me to?

 

Thanks,

 

Lance