Skip to content
Fix Code Error

Run a PostgreSQL .sql file using command line arguments

March 13, 2021 by Code Error
Posted By: Anonymous

I have some .sql files with thousands of INSERT statements in them and need to run these inserts on my PostgreSQL database in order to add them to a table. The files are that large that it is impossible to open them and copy the INSERT statements into an editor window and run them there. I found on the Internet that you can use the following by navigating to the bin folder of your PostgreSQL install:

psql -d myDataBase -a -f myInsertFile

In my case:

psql -d HIGHWAYS -a -f CLUSTER_1000M.sql

I am then asked for a password for my user, but I cannot enter anything and when I hit enter I get this error:

psql: FATAL: password authentication failed for user “myUsername”

Why won’t it let me enter a password. Is there a way round this as it is critical that I can run these scripts?

I got around this issue by adding a new entry in my pg_hba.conf file with the following structure:

# IPv6 local connections:
host    myDbName    myUserName ::1/128    trust

The pg_hba.conf file can usually be found in the ‘data’ folder of your PostgreSQL install.

Solution

You have four choices to supply a password:

  1. Set the PGPASSWORD environment variable. For details see the manual:
    http://www.postgresql.org/docs/current/static/libpq-envars.html
  2. Use a .pgpass file to store the password. For details see the manual:
    http://www.postgresql.org/docs/current/static/libpq-pgpass.html
  3. Use “trust authentication” for that specific user: http://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-TRUST
  4. Since PostgreSQL 9.1 you can also use a connection string:
    https://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-CONNSTRING
Answered By: Anonymous

Related Articles

  • How do I include certain conditions in SQL Count
  • Improve INSERT-per-second performance of SQLite
  • Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  • Homebrew install specific version of formula?
  • What are the new features in C++17?
  • What is your most productive shortcut with Vim?
  • The definitive guide to form-based website authentication
  • What are the undocumented features and limitations…
  • How do I login and authenticate to Postgresql after…
  • Ukkonen's suffix tree algorithm in plain English
  • How to Update Database from Assets Folder in App
  • How do you list volumes in docker containers?
  • Postgres could not connect to server
  • data.table vs dplyr: can one do something well the…
  • Secure hash and salt for PHP passwords
  • postgresql port confusion 5433 or 5432?
  • SQL Server: Database stuck in "Restoring" state
  • Postgres password authentication fails
  • How to UPSERT (MERGE, INSERT ... ON DUPLICATE…
  • psql: command not found Mac
  • How to create a temporary table in SSIS control flow…
  • python 3.2 UnicodeEncodeError: 'charmap' codec can't…
  • Save PL/pgSQL output from PostgreSQL to a CSV file
  • Completely uninstall PostgreSQL 9.0.4 from Mac OSX Lion?
  • SQL query return data from multiple tables
  • Format of the initialization string does not conform…
  • psql: FATAL: database "" does not exist
  • How can I find the product GUID of an installed MSI setup?
  • Can not connect to local PostgreSQL
  • What is the copy-and-swap idiom?
  • Why does `which` print out a script?
  • How to thoroughly purge and reinstall postgresql on ubuntu?
  • How do I install Java on Mac OSX allowing version switching?
  • How to retrieve data from sqlite database in android…
  • PostgreSQL psql terminal command
  • SQL Network Interfaces, error: 50 - Local Database…
  • configure: error: C compiler cannot create executables
  • What does do?
  • How to configure postgresql for the first time?
  • Docker compose fails to start a service with an…
  • Database development mistakes made by application developers
  • Difference between variable declaration syntaxes in…
  • PostgreSQL Crosstab Query
  • PostgreSQL: Resetting password of PostgreSQL on Ubuntu
  • How to speed up insertion performance in PostgreSQL
  • How to resolve…
  • Checking letter case (Upper/Lower) within a string in Java
  • Logging best practices
  • Psql could not connect to server: No such file or…
  • CKEditor setData not clearing or changing text in rte
  • How to prevent scrolling the whole page?
  • Ubuntu apt-get unable to fetch packages
  • How can I start PostgreSQL server on Mac OS X?
  • How to encrypt/decrypt data in php?
  • How do I execute multiple SQL Statements in Access'…
  • Postgresql: password authentication failed for user…
  • How do I count unique visitors to my site?
  • How to paste yanked text into the Vim command line
  • Text editor to open big (giant, huge, large) text files
  • How to handle Vue 2 memory usage for large data (~50…
  • AppCompat v7 r21 returning error in values.xml?
  • How do I style a dropdown with only CSS?
  • How to install Intellij IDEA on Ubuntu?
  • javascript .replace and .trim not working in vuejs
  • Import SQL dump into PostgreSQL database
  • Excel VBA Password via Hex Editor
  • psql: FATAL: role "postgres" does not exist
  • When trying to insert a table into a quill editor I…
  • How to install the Raspberry Pi cross compiler on my…
  • Postgresql: Scripting psql execution with password
  • Sort table rows In Bootstrap
  • java.sql.SQLException: - ORA-01000: maximum open…
  • Adding a guideline to the editor in Visual Studio
  • Why cat does not work with parameter -0 in xargs?
  • I want to create a SQLite database like in the…
  • Pandas how to split vlaues of every column based on colon
  • How to use pipenv on mac?
  • How can I use/create dynamic template to compile…
  • How to avoid "module not found" error while calling…
  • Python PWManager problem with TypeError: object of…
  • How do you clear the SQL Server transaction log?
  • T-SQL How to create tables dynamically in stored procedures?
  • Using psql to connect to PostgreSQL in SSL mode
  • why does $http baddata occur. i need to authenticate user
  • VueJS and tinyMCE, custom directives
  • SQL Insert Query Using C#
  • Backup database in Laravel 8
  • How to run mysql command on bash?
  • Start redis-server with config file
  • Getting Monaco to work with Vuejs and electron
  • How to use the encrypt password for login php
  • When should I use a table variable vs temporary…
  • What's the difference between eval, exec, and compile?
  • Running multi-stacker docker built docker image…
  • Getting Chrome to prompt to save password when using…
  • How to create a new database after initally…
  • How to make onClick card to move to another page in react
  • Can't install via pip because of egg_info error
  • jQuery.on() Delegation: Slightly complex selector…
  • DJango doesn't execute request.method == "post" with…

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Post navigation

Previous Post:

Trim string in JavaScript?

Next Post:

PostgreSQL: Which version of PostgreSQL am I running?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

.net ajax android angular arrays aurelia backbone.js bash c++ css dataframe ember-data ember.js excel git html ios java javascript jquery json laravel linux list mysql next.js node.js pandas php polymer polymer-1.0 python python-3.x r reactjs regex sql sql-server string svelte typescript vue-component vue.js vuejs2 vuetify.js

  • you shouldn’t need to use z-index
  • No column in target database, but getting “The schema update is terminating because data loss might occur”
  • Angular – expected call-signature: ‘changePassword’ to have a typedeftslint(typedef)
  • trying to implement NativeAdFactory imports deprecated method by default in flutter java project
  • What should I use to get an attribute out of my foreign table in Laravel?
© 2022 Fix Code Error