How Do You Get a Web Page to Invoke a Postgres Command (e.g., for a RESTful API)?

0 Comments

Problem scenario
You want a REST call to invoke some SQL code on a Postgres database.  How do you do this?

Solution
Install Apache web server, PHP, and the PHP-pgsql package on your Postgresql database server.  This example assumes you have PHP, Apache web server, Postgres, and the php-pgsql package installed on your server.  Assuming the web server is running and the default directory for web pages is still /var/www/html/ do the following.

…

Continue reading “How Do You Get a Web Page to Invoke a Postgres Command (e.g., for a RESTful API)?”

How Do You Ingest (or Import) a CSV File into a Postgresql Database Table?

0 Comments

Problem scenario
You want to extract data from a .csv file.  In other words you want to  transform data from a flat file and import it into a database.  Is there an example of how to create a table, format a .csv file, and run a command to import the .csv file into the Postgresql database?

Solution
1.  Here is how to create a table to target for the import or ETL (Extract,

…

Continue reading “How Do You Ingest (or Import) a CSV File into a Postgresql Database Table?”

How Do You Create Two Tables with a Shared Column between Them (e.g., Have a Foreign Key Constraint)?

0 Comments

Problem scenario
You want to create two tables in a Postgresql database.  You want the tables to share a column to relate to one another.  You want to implement a foreign key constraint to achieve this goal.  Is there an example of SQL statements to do this?

Solution
Run these two SQL commands that start with the words “CREATE TABLE”:

CREATE TABLE table1 (id integer,

…

Continue reading “How Do You Create Two Tables with a Shared Column between Them (e.g., Have a Foreign Key Constraint)?”

How Do You Troubleshoot a Syntax Error When Trying to Create a Table with a Primary Key in SQL?

0 Comments

Problem scenario
You are trying to create a table using SQL.  You use the keywords “CONSTRAINT” and “PRIMARY KEY.”  For example, your query (or SQL DML command) looks like this:

create table contint ( specialcode char(6) CONSTRAINT PRIMARY KEY, title varchar(40)) ;

When you run the SQL command you get the message ‘…syntax error at or near “PRIMARY” LINE…’  What should you do to fix this?

…

Continue reading “How Do You Troubleshoot a Syntax Error When Trying to Create a Table with a Primary Key in SQL?”

How Do You Deploy LAPP with Frontend User Authentication?

0 Comments

Problem scenario
You want a web page that asks for a username and password (e.g., to log in).  The users will not know if the authentication is done via Apache or Postgres.  You happen want the authentication to be challenged against a Postgres database.  This way if you have backend access to the Postgres database, you can use “CREATE ROLE” or “CREATE ROLE jane PASSWORD ‘veryfun’ LOGIN” to create users.  

…

Continue reading “How Do You Deploy LAPP with Frontend User Authentication?”

How Do You Create a Table in a Specific Database Using a SQL Front-End GUI Application?

0 Comments

Problem scenario
You are using a SQL front end to query a Postgres database.  (To view directions for installing a free front-end GUI application, click here.) You have run various commands to prove the connection to the database server works.  

Running this query

select version();

shows

“PostgreSQL 9.5.6 on x86_64-pc-linux-gnu, compiled by …”

You have used CREATE DATABASE successfully. 

…

Continue reading “How Do You Create a Table in a Specific Database Using a SQL Front-End GUI Application?”

How Do You Troubleshoot the Postgres Message “ERROR: permission denied to create database”?

0 Comments

Problem scenario
From a SQL front end you are connected to a Postgres database as user jdoe.  You try to create a database with the “create database verycool” SQL command.  You get this message: “ERROR: permission denied to create database.”

You try to enter the database from the back end, but you get ‘psql: FATAL: role “root” does not exist.’   Or you may even get the error “psql: FATAL: 

…

Continue reading “How Do You Troubleshoot the Postgres Message “ERROR: permission denied to create database”?”

How Do You Integrate PHP with Postgresql?

0 Comments

Problem scenario
You want your PHP files (either a frontend web page or a purely backend script) to integrate with Postgres.  You want .php files to log into a Postgres database to run SQL commands.  How do you do this?

Solution
Install php5-pgsql or php7.0-pgsql.  It depends on your version of PHP.  Use this command to find out what version of PHP you have:  php –version

Install this module: 

…

Continue reading “How Do You Integrate PHP with Postgresql?”

How Do You Troubleshoot This Error with a PostgreSQL Front End Application “Connection refused … Is the server running on host “x.x.x.x” and accepting TCP/IP connections on port 5432″?

0 Comments

Problem scenario
With a SQL front end (e.g., pgAdmin or HeidiSQL) you get this error “could not connect to server: Connection refused 0x0000274D/10061) Is the server running on host “x.x.x.x” and accepting TCP/IP connections on port 5432?

You double checked you are trying to connect to the correct IP address (x.x.x.x).  You know port 5432 is not blocked.  You know the postgres service is listening on port 5432 on the destination server.

…

Continue reading “How Do You Troubleshoot This Error with a PostgreSQL Front End Application “Connection refused … Is the server running on host “x.x.x.x” and accepting TCP/IP connections on port 5432″?”

How Do You Connect to a Specific Postgres Database without a SQL Front End?

0 Comments

Problem scenario
From a Linux command prompt (e.g., Linux terminal) you want to connect to a specific Postgres database.  How do you create a table or issue SQL commands to a specific Postgres database from the back-end (the Postgres server itself)?

Solution
1.  First enter the Postgres command prompt by running these two commands:

sudo -i -u postgres
psql

2.

…

Continue reading “How Do You Connect to a Specific Postgres Database without a SQL Front End?”