PostgreSQL

PostgreSQL is an object-relational database management system with an emphasis on extensibility and standards-compliance. As a database server, its primary function is to store data securely, supporting best practices, and to allow for retrieval at the request of other software applications. PostgreSQL implements the majority of the SQL2011 standard, is ACID-compliant and transactional using multiversion concurrency control (MVCC), and handles complex SQL queries using many indexing methods that are not available in other databases. It is cross-platform and runs on many operating systems including Linux, FreeBSD, OS X, Solaris, and Microsoft Windows.

Share tech news, updates, or what's on your mind.

Sign up to Post

I'm using a jump box for my developers to access PostgreSQL database.  The leadership has limited access of 2 users to each Jump box.  It is very difficult to scale that model when all the developers need is secure access (this is on the Azure cloud by the way).  I was thinking about opening up port 5433 as recommended by this article to install pgAdmin.  Does anyone have any pros or cons to this course of action?
0
Learn Ruby Fundamentals
LVL 12
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

I have a C# application that needs to connect to sqlserver and postgresql

I have been able to connect it to sqlserver and it works and the connection string is shown below

<add name="Tournaments" connectionString="Server=CHRISTINESPC;Database=Tournaments;Trusted_Connection=True;" providerName="System.Data.SqlClient"/>

I have tried a number of options for postgresql without success

Can anybody kindly help to specify what  the postgresql  connection string equivalent is

Notes:

CHRISTINESPC is my servername as displayed in sqlserver  management studio thou I am open to using an IP address
Tournaments my Database name in both sqlserver and postgresql databases

Thanks

Oluwole
0
PostgreSQL world :

With pgbadger (postgres tool), some results speak about SESSIONS, others about CONNECTIONS.

With PostgreSQL, what is the difference between a session and a connection ?

Your site contains the answer for Oracle but not for Postgres...

Thanks a lot.

Gery
0
Hi,

Can you please help me to write SQL script in PostgreSQL for source data profiling. I can't use any data profiling tools, so I need to write SQL scripts. These are some of the things I want the result:

TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
ColumnDataLength
DataType
MinDataLength
MaxDataLength
AvgDataLength
MinDate
MaxDate
NoDistinct
NoNulls
NoZeroLength
PercentageNulls
PercentageZeroLength
NoDateWithHourminuteSecond
NoDateWithSecond
NoIsNumeric
NoIsDate
NoAtLimit
IsFK
DataTypeComments

I have attached screenshots which is similar to what I am looking for. It is not important to have all results in one script. It can be different scripts but I want column_names in result set. Thank you.17375.jpg17377.jpg
0
I'm trying to build a PostgreSQL/PostGIS database using Entire country files dataset but I'm getting missing data error

I'm wondering if the copy command is affected by diacritics or I've not set the database up properly
Created a new database with UTF8 encoding

I built the table schema based on the given format (but using type text for NM_MODIFY_DATE not varchar,  having these as dates didn't make a diferance)
PostgreSQL docs state fields can be NULL by default  so

create table my_table(
id SERIAL PRIMARY KEY,
test1 text;  -- can be null
test2 text NOT NULL;  -- Has to have a value
}

Open in new window


In my  theory every column apart from id doesn't have to contain a value
 
I used large text viewer to open the uncompressed countries.txt file and copied the top 5 rows into a test file

Using   PostgreSQL Copy this test file imported correctly so I know my schema is correct
copy my_table(List of columns ) from '\\Path\\To\\TestFile.txt' WITH delimiter E'\t' csv HEADER;

Open in new window



When tried to ingest the larger  countries.txt (2.9GB) file I get an error "missing data" for column xyz at line 12345 (Last column with  data in that row, NM_MODIFY_DATE)

Using large text viewer again I located this line with proceeding and following lines into my test file and tried the copy again but get the same error

I opened the test file in …
0
Hi Experts,

     docker container is not picking the recent changes from the source code.     it makes me to build the docker image every time to see the latest changes on the source code.

Please find below the contents of the Dockerfile

FROM ubuntu:16.04

MAINTAINER *****

RUN apt-get update -y
RUN apt-get install -y software-properties-common python-software-properties curl
RUN add-apt-repository -y ppa:fkrull/deadsnakes

RUN apt-get update -y && apt-get install -y curl
RUN apt-get update -y && apt-get install -y \
	git \
	python3.6 \
	python3.6-dev \
	nginx \
	sqlite3 \
	nodejs \
	build-essential \
	libmagickwand-dev \
	cron \
	nginx

RUN rm -f /usr/bin/python3
RUN ln -s /usr/bin/python3.6 /usr/bin/python3
RUN curl https://bootstrap.pypa.io/get-pip.py | python3

WORKDIR /home/trove
COPY . .

COPY build/docker/uwsgi_params .
COPY build/docker/uwsgi.ini .
RUN pip3 install --no-cache-dir uwsgi
RUN pip3 install --no-cache-dir -r requirements.txt

COPY build/docker/start /usr/bin/
COPY build/docker/crontab /etc/cron.d/harvest-cron
RUN chmod 0644 /etc/cron.d/harvest-cron
RUN touch /var/log/harvest.log

RUN echo "daemon off;" >> /etc/nginx/nginx.conf
COPY build/docker/nginx-app.conf /etc/nginx/sites-available/default
COPY build/docker/start /usr/bin/
RUN mkdir /var/log/harvest/
RUN python3 manage.py collectstatic --noinput

WORKDIR /home/trove/
RUN chmod 755 /home/trove
RUN chown -R www-data:www-data /home/trove

EXPOSE 80
CMD ["start"]

Open in new window


please find below the contents of the crontab

SHELL=/bin/bash
* * * * * root ( source /tmp/environment.sh && /usr/bin/python3 /home/trove/run.py $(cat /tmp/method) ) >> /dev/null 2>/var/log/harvest/ts_errors.log

Open in new window

0
I have ubuntu 16.04 server with postgres version 9.3.17 . how to update 9.3.17 to 9.3.24?
i know it's old but for master/slave version i need to have a same version as master.
0
I am planning to design an Java application accessing "Postgresql" database in Windows server. This Java application is expected to be used by 50-100 users simultaneously.

1. Would like to have prerequisites about System requirements for setting up "postgresql" database and Java in windows server?

2. While implementing in production environments, whether any licensing is needed for "postgresql" database?
0
I am trying to retrieve the average value from a PostgreSQL database using a Sequelize ORM with React on the front end and running Node.js server.  My 'GET' request on the Node side retrieves all of the data from the PostgreSQL database so I know everything is wired up with React using the following code:

module.exports = function (db, app, bodyParser) {


    app.use(bodyParser.json());
    app.use(bodyParser.urlencoded({ extended: false }));

    app.get('/survey_init', function(req, res, next) {
        db.answers.findAll({})
        .then(function (data) {

            res.send(data);
              })
          .catch(function (err) {
            return next(err);
          })
        });
        
    }

Open in new window


However, when I attempt to run an average on a specific column called 'conf_one' in my PostgreSQL database I get an error.  I will list the modified code (modified from code above) that is throwing the error here:

module.exports = function (db, app, bodyParser) {


    app.use(bodyParser.json());
    app.use(bodyParser.urlencoded({ extended: false }));

    app.get('/survey_init', function(req, res, next) {
        db.answers.findAll({
          attributes: ['conf_one', [models.Sequelize.fn('AVG', models.Sequelize.col('conf_one')), 'conf_one_avg']]
        })
        .then(function (data) {

            res.send(data);
              })
          .catch(function (err) {
            return next(err);
          })
        });
        
    }

Open in new window



The error that I get with the code is the following error and I do not get the result of an average calculation.


Open in new window

0
Hi Experts,

I want to restore postgres database.

I had taken backup with pg_dump.  the command which I used to take backup is

pg_dump -U postgres -h 127.0.0.1 -p 5432 ts > ts.tar
password:

it created the file ts.tar

Steps I had tried to restore is

root@ip-10-252-14-11:/home/ubuntu/workarea/sourcecode/database_backup# pg_restore --host localhost --port 5432 --username "postgres" --dbname "ts" --role "ts" --no-password --verbose "./ts.tar"
pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

root@ip-10-252-14-11:/home/ubuntu/workarea/sourcecode/database_backup# pg_restore --host localhost --port 5432 --username "postgres" --dbname "ts" --role "ts" --no-password < "./ts.tar"
pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

root@ip-10-252-14-11:/home/ubuntu/workarea/sourcecode/database_backup# pg_restore --host 127.0.0.1 --port 5432 --username "postgres" --dbname "ts" --role "ts" --no-password < "./ts.tar"
pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

root@ip-10-252-14-11:/home/ubuntu/workarea/sourcecode/database_backup# pg_restore --host 127.0.0.1 --port 5432 --username "postgres" --dbname "ts" --role "ts"  < "./ts.tar"
pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

root@ip-10-252-14-11:/home/ubuntu/workarea/sourcecode/database_backup# pg_restore --host 127.0.0.1 --port 5432 --username "ts" --dbname "ts" 

Open in new window

0
CompTIA Network+
LVL 12
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

I have a set of stored procedures i need to create in PostgreSQL

My Database name is TOURNAMENTS

Where within  PGAdmin  do i create  these stored procedure

 

i have attached the pgAdmin screen
Stored-Procedure-Creation-18-08-2018.PNG
0
Hi,
   We are on Postgres 9.6
I have a table Customer that has these fields:

ID          Rel_Id
10            12, 23, 34

Then there is this Relation table, that has this structure

Rel_id           Rel_name
12                  Mike
23                  Dave
34                  Jim

I want to show the output as:
ID           Rel_ID                       Rel_Name
10       12,23,34                      Mike, Dave, Jim

Can someone please help?
0
This is a read only sql query being written in a SQL editor
The ultimate goal here is to print all 5 columns if the table does exist OR to print the year and 4 NULL columns if the table does not exist.

The tables I need to check if exists are sba_2017_math, sba_2018_math and sba_2019_math
I wrote this basic select statement to return t or f if the table exists or not.
Can I put that t/f into a variable?  I need to do that for all 3 years separately.
SELECT EXISTS (
				SELECT 1
				FROM   information_schema.tables 
				WHERE  table_schema = 'state_data_ct'
				AND    table_name = 'sba_2019_math'
			  )

Open in new window


Then can I use that variable to somehow run the following query?

select 
'2017' as year,
case when @exists2017 = 't' then column1 else null end as column1,
case when @exists2017 = 't' then column2 else null end as column2,
case when @exists2017 = 't' then column3 else null end as column3,
case when @exists2017 = 't' then column4 else null end as column4
from state_data_ct.sba_2017_math
UNION ALL
select 
'2018' as year,
case when @exists2018= 't' then column1 else null end as column1,
case when @exists2018 = 't' then column2 else null end as column2,
case when @exists2018 = 't' then column3 else null end as column3,
case when @exists2018 = 't' then column4 else null end as column4
from state_data_ct.sba_2018_math
UNION ALL
select 
'2019' as year,
case when @exists2019= 't' then column1 else null end as column1,
case when @exists2019 = 't' then 

Open in new window

0
I have a website where the number of visitors is increasing and it is facing a poor response time. I am not a server specialist and for this reason I want to expose my issue here to receive some feedback. The website has the following characteristics:

* Developed with ASP.NET Web Forms (I will be implementing a new version with ASP.NET MVC soon).
* It uses a PostgreSQL 9.1 database.
* It is 100% dymanic which means that every page is formed at run-time getting information from the DB.
* Hosted in a cloud server with 2 Cores and 4 GB of RAM.
* Windows Server 2012 in drive C:
* PostgreSQL DB in another "hard disk" as drive D:
* The average number of daily visitors is 2,500

Watching the Task Manager I can notice that there are many PostgreSQL processes which consume CPU 100%. Another process that is shown in the "IIS Worker Process" but I have not seen it as critical as the PostgreSQL process. So it seems that the main issue here has to do with the database access. When CPU usage is high the website response is extremely low and also it is very difficult to establish a remote desktop connection.

I have thought about upgrading the cloud server to, for example, 4 cores and 8 GB of RAM, but firstly I want to get feedback from experts here so I can take the best approach. I am not a server expert and, for this reason, I do not want to take steps blindly.

Respectfully,
Jorge Maldonado
0
I am trying to generate layer for Postgres database using ADO.net, Entity Framework and Postgres Database.
After I select Postgres provider and establishes the connection with the database, the wizard "To choose database object and setting" (Table, view, stored proc, function) doesn't appear and model doesn't get created.
Hence I am unable to select tables and generate layers.
 
Followings are the version used in my project
npgsql (Installed in Visual Studio from NuGet) - 4.0.2
EntityFramework6.npgsql (Installed in Visual Studio from NuGet) - 3.1.1
pgAdmin4
PostgreSQL 9.6
I have included provider name for npgsql in webconfig and appconfig, but still unable to generate layer.
0
is it possible to create master/slave between postgresql version 9.5 (as master) and 10(as slave) on ubuntu 16.04 and 18.04?
i running master/slave between two 9.5 but i can't start postgres 10 as slave.
the error massage is connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
and it's because /var lib/postgres/10/main folder is on version 9.5 or .. .
0
Hello! I'm trying to make my first web application of a hotel(this is an education project not a payed one) and I have a lot of issues...The most important one is that if a user at the registration process selects that he wants to reserve 4 rooms he needs to make the checkin process 4 times, once for each room. I have succesfully managed to register the user and login, but the problem is with showing the check-in form for a number of times... I managed to show it once, but the second time it doesn't work, it shows me the details for the same room, not for a different one, or it makes an infinite loop...what can I do? Here it's the PHP code:
<?php
	ob_start();
	 $success = false;
	// Errors reporting, used if needed
	error_reporting(E_ALL);
	ini_set('display_errors', 'on');

	// General configuration like base, used if needed
	include_once ('include/config.inc.php');

	// Mail functions
	include_once ('include/mail.functions.php');



	// DBConn
	include_once ('class/DB/DBConn.includeall.php');
	$db = new DBConn(NULL);

	// Includere clasa login
	require_once ('class/class_login.php');

	// Set up current language
	$lang = "ro";
	$_SESSION[PRE.'lang'] = $lang;
	$row='';
	$access = 0;
	$user = (isset($_POST['nume']) && !empty($_POST['nume'])) ? $_POST['nume'] : "" ;
	$email = (isset($_POST['email']) && !empty($_POST['email'])) ? $_POST['email'] : "" ;
	$telefon = (isset($_POST['telefon']) && !empty($_POST['telefon'])) ? $_POST['telefon'] : "" ;
	$cnp = 

Open in new window

0
Hello! I'm a newbie in programming and my first project it's an checkin system for a hotel. The system it's looking like this: first of all, the user will enter his name, check-in, check-out date,  the numbers of rooms that he wants to rent, number of people that will stay in a room, and the email address(all this done in the register.php page). After he'll enter this details, an email will be sent to him where he'll have a reservation number and the details about his reservation. After this, he'll go on an page(login.php) where he'll enter this reservation code and start the check-in process for each room, for example if he choosed 2 rooms, he'll make first the checkin for the first room then for the second(this should be done in the checkin.php page).If the checkin process was never made, it will start, otherwise an message will be shown that will say that the code was used or it's incorrect, it depends. What I want help it's with the login process and the starting of the checkin, I managed to send that email, generate that code but I can't login and start the checkin... I want to use only PostgreSQL, not MYSQL. Here it's the login.php:
<?php

	ob_start();
	 $error = false;
	// Errors reporting, used if needed
	error_reporting(E_ALL);
	ini_set('display_errors', 'on');

	// General configuration like base, used if needed
	include_once ('include/config.inc.php');

	// Mail functions
	include_once ('include/mail.functions.php');

	// Start session if needed
	

Open in new window

0
Hi
I'm trying to import some data into my_table using something  like this

create table my_table(
id SERIAL PRIMARY KEY,
title text,
my_date date,
-- plus other columns of types text, date and integer  
another_date date,
etc
);
COPY my_table (title,my_date,....)
FROM 'path/to/input.csv' WITH DELIMITER '|' CSV HEADER;   

Open in new window


the COPY is failing with

ERROR: date/time out of range: "10/07/2018"
SQL state: 2208
Hint: Perhaps you need a different "datestyle" setting.
Context: COPY my_table line 2 , column another_date:   "10/07/2018"

Open in new window

Is there a way of converting the date to "2018-07-10" on the way in?  I can't guarantee each date is in this format but from what  I've seen they are
if not can I set the dates to accept a broader format import the data then change it back


Opening it in openoffice calc and  setting the format of the column(s)   is first a bad idea, this could introduce errors,  and second some of the cells don't convert (I Tried a copy)


BTW:
I'm using PostgreSQL 8.4
0
CompTIA Security+
LVL 12
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Hi,
Any advice, to following issue, when accessing PostgreSQL pgAdmin?
0
Hi
I need to run the following update query on a PostgreSQL / PostGIS database

UPDATE geo_table
SET geom =ST_ConvexHull(
     ST_GeomFromText('MULTIPOINT(co_ord_string)', 4326)
);
WHERE 
Id =1234;

Open in new window


co_ord_string is a column name if I manually copy the row value into above the query runs otherwise I get

parse error – invalid geometry HINT: "MULTIPOINT(" <-- parse error at position 11 within geometry


I'm pretty sure this is because of the single quotes around  'MULTIPOINT(co_ord_string)' interprets co_ord_string as the literal string

The value of co_ord_string is a series of long lat points separated by a comma. These don’t create perfect polygons as they may contain internal points but using ST_ConvexHull a polygon is created


Could someone help with the syntax
0
Hi,

I've a table on AWS Redshift with couple of columns in JSON format.
So i'm using JSON_EXTRACT_PATH_TEXT Function to query the data in the JSON format cloumn.

For example ,

SELECT user,json_extract_path_text(payload,'code') as payload_code FROM activity_log.lx4_logs
where event_reference LIKE 'My Dashboard%'; .

So the output look like following with two coloumns

User       | Code
user1      | ["13877512003","13877642003","10078402003","13901222003","10118722003"]
user2     |["105700520150300750","106468920140600750","112279220150300750","104693120060101500","122805820150600750"]
user3    |NULL
user4    |["10112472015"]

Is there a possibility to get the output, into rows like below without any quotation, square brackets etc please ?

User       | Code
user1      |13877512003
user1      |13877642003
user1      |10078402003
user1      |13901222003
user1      |10118722003
user2     |105700520150300750
user2  |106468920140600750
user2   |112279220150300750
user2  |104693120060101500
user2  |122805820150600750
user3    |NULL
user4    |10112472015

Thanks
0
Hi,

  We are on Postgres 9.6 Linux database.
Have a requirement to create a function and pass  cc_case_ID as the parameter.
requirement then, is to to delete all the records from all the tables in a schema, where the cc_case_ID is equal to the parameter value passed.

Can someone kindly help with the solution?
0
How to convert existing Sql server database to postgre database?
0
Hi,

I'm trying to connect my PostgreSQL to Skyvia, a cloud data integration tool. When attempting to do so, I encounter the error message of:

No connection could be made because the target machine actively refused it 127.0.0.1:5432

I have amended my postgres file to listen_Address = '*' as well as amend the pg_hba file to include the Skyvia ip address for access, as recommended, I've also tried setting the configuration to

host    all             all             0.0.0.0/0              md5

I've also set up an inbound rule on my firewall to allow connections to the port 5432, but still no luck.

Any ideas of where I'm going wrong would be greatly appreciated!!
0

PostgreSQL

PostgreSQL is an object-relational database management system with an emphasis on extensibility and standards-compliance. As a database server, its primary function is to store data securely, supporting best practices, and to allow for retrieval at the request of other software applications. PostgreSQL implements the majority of the SQL2011 standard, is ACID-compliant and transactional using multiversion concurrency control (MVCC), and handles complex SQL queries using many indexing methods that are not available in other databases. It is cross-platform and runs on many operating systems including Linux, FreeBSD, OS X, Solaris, and Microsoft Windows.

Top Experts In
PostgreSQL
<
Monthly
>