PostgreSQL

Solutions

Content

Contributors

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.

PostgreSQL: Copy from File missing data error

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 …

17 Comments

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

…

3 Comments

Question by

Jasaida

2018-08-29

postgres update.

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.

1 Comment

LVL 26

Question by

Sekar Chinnakannu

2018-08-24Solved

PostGRESQL Implementation Plan....

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?

3 Comments

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

…

1 Comment

LVL 1

Question by

Bharath A.K

2018-08-19Solved

How the restore postgres database on linux?

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

…

6 Comments

CompTIA Network+

LVL 12

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

PostgreSQL

3 Comments

Question by

pvsbandi

2018-08-10SolvedPrivate

Decode comma separated IDs to names

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?

PostgreSQL

3 Comments

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

…

4 Comments

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

6 Comments

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 Comments

Question by

Jasaida

2018-07-20Solved

master/slave on postgresql

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 .. .

PostgreSQL

1 Comment

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

…

1 Comment

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

…

9 Comments

LVL 1

Question by

trevor1940

2018-07-10Solved

PostgreSQL: Copy from File date format error

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

PostgreSQL

9 Comments

CompTIA Security+

LVL 12

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.

LVL 12

Question by

HuaMin Chen

2018-07-09Solved

Problem to access pgAdmin

Hi,
Any advice, to following issue, when accessing PostgreSQL pgAdmin?

7 Comments

LVL 1

Question by

trevor1940

2018-07-05Solved

PostgreSQL / PostGIS Update Syntax

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

5 Comments

LVL 2

Question by

Clement P

2018-07-05

JSON_EXTRACT_PATH_TEXT Function

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

1 Comment

Question by

pvsbandi

2018-06-29SolvedPrivate

Delete rows dynamically

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?

9 Comments

How to convert existing Sql server database to postgre database?

1 Comment

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!!

1 Comment

First
Prev
1
2
3
4
5
6
7
Next
Last

PostgreSQL

Solutions

Content

Contributors

PostgreSQL

Related Topics

PostgreSQL

Related Topics