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
Become a Microsoft Certified Solutions Expert
LVL 12
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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
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 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
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
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
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
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
Microsoft Azure 2017
LVL 12
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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

  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
Hi

I have 2 PostrgreSQL/ PostGIS databases I need to update a table in 1 DB with data in a table  in the second

geo_table in DB1

id,country_code,country_name,long,lat,geom  # point data

Open in new window


boundary.table in DB2

id,country_code,country_name,geom  # polygons

Open in new window



If both tables were in the same database I could run a select query something like (UNTESTED)

UPDATE geo.table
set g.country_code = b.country_code,g.country_name = b.country_name
FROM
geo_table g, boundary b
INNER JOIN on ST_intersect(geo_table.geom, b.geom) 

Open in new window



How can I update columns country_code,country_name in the geo_table with corresponding values in the boundary table where the 2 geometries intersect

If necessary I could copy the boundary table into DB1 but this would mean maintaining duplicate data
0
I have the Postgres SQL below in an API function. I am passing the $id and $ic variables. The $ic variable can be an array of values or a single value.
    WITH cte as (
        SELECT partnerslistori as a FROM sales.members WHERE id = '".$id."'
    )
    SELECT 
        u.id as actualid,
    	(SELECT m.company || ' (' || m.id ||')' FROM support.members m WHERE m.id = u.id) AS actualcompany,
    	u.itemname,
    	DATE_PART('day', CURRENT_TIMESTAMP - u.datein::timestamp) AS daysinstock, 
    	TRIM(u.grade)::character varying as condition, 
    	u.vstockno AS stock, 
    	u.hol AS ic, 
    	CASE WHEN u.rprice > 0 THEN 
    		u.rprice 
    	ELSE 
    		NULL 
    	END AS price, 
    	u.quantity, 
    	u.location
    FROM public.net u 
    WHERE u.holl in (".$ic.")
      AND visibledate <= now() 
      AND u.id = any(regexp_split_to_array('".$id.",'||(select a from cte), ',')); 

Open in new window


Now, I need to "aggregate" the results for a single id and contactenate the vstockno values and sum the quantity values into a single record.

For instance, if I return CSV this using the sql above:
"G06","WAP (G06)","CALIPER",370,"C","CAE17366","536-01036",1.00,1,"CY31B05"
"G06","WAP (G06)","CALIPER",770,"C","CAD16821","536-01036",1.00,1,"CALIPERCO"
"G21","WNB (G21)","CALIPER",257,"C","NBI17724","536-01036",1.00,1,"R3415"
"G21","WNB (G21)","CALIPER",128,"C","NBA18095","536-01036",1.00,1,"R2804"
"K17","GAAAP (K17)","CALIPER",270,"C","7I1581","536-01036",,1,"B5-A04"
"K17","GAAAP (K17)","CALIPER",267,"C","7I1594","536-01036",,1,"B4-B04"
"K17","GAAAP (K17)","CALIPER",78,"C","8C1210","536-01036",,1,"[UNKNOWN]"
"N40","WAC (N40)","CALIPER",147,"C","MNA18002","536-01036",1.00,1,"[UNKNOWN]"
"N49","YUPIP (N49)","CALIPER",77,"K","DS180429","536-01036",6.00,1,"UPULL-E"
"V02","CBAP (V02)","CALIPER",1408,"A","CKA1301","536-01036",40.00,1,"[UNKNOWN]"
"V02","CBAP (V02)","CALIPER",350,"A","CPA1257","536-01036",12.00,1,"BIN.360.A"
"M16","BAP (M16)","CALIPER",196,"A","8A067","536-01036",,1,"LOT-23"
"M23","BA (M23)","CALIPER",115,"A","8A065","536-01036",15.00,1,"36AU"
"M21","BUPI (M21)","CALIPER",115,"A","8A065","536-01036",15.00,1,"36AU"
"M22","BSS(M22)","CALIPER",115,"A","8A065","536-01036",15.00,1,"36AU"

Open in new window

I want it to return this instead; the stock and location columns are concatenated and the quantity is summed when there is more than one result for a particular id:
"G06","WAP (G06)","CALIPER",370,"C","CAE17366, CAD16821","536-01036",1.00,2,"CY31B05, CALIPERCO"
"G21","WNB (G21)","CALIPER",257,"C","NBI17724, NBA18095","536-01036",1.00,2,"R3415, R2804"
"K17","GAAAP (K17)","CALIPER",270,"C","7I1581, 7I1594, 8C1210","536-01036",,3,"B5-A04, B4-B04, [UNKNOWN]"
"N40","WAC (N40)","CALIPER",147,"C","MNA18002","536-01036",1.00,1,"[UNKNOWN]"
"N49","YUPIP (N49)","CALIPER",77,"K","DS180429","536-01036",6.00,1,"UPULL-E"
"V02","CBAP (V02)","CALIPER",1408,"A","CKA1301, CPA1257","536-01036",40.00,2,"[UNKNOWN], BIN.360.A"
"M16","BAP (M16)","CALIPER",196,"A","8A067","536-01036",,1,"LOT-23"
"M23","BA (M23)","CALIPER",115,"A","8A065","536-01036",15.00,1,"36AU"
"M21","BUPI (M21)","CALIPER",115,"A","8A065","536-01036",15.00,1,"36AU"
"M22","BSS(M22)","CALIPER",115,"A","8A065","536-01036",15.00,1,"36AU"

Open in new window


Can this be done in a single SQL statement?
0
My shop currently uses both Oracle 11g Single-Instance, and Oracle 12c RAC databases.  A contingent of managers, co-workers and peers are very interested in pursuing a PostGress solution.  Can you point out some of the pros/cons of both database vendors and their solutions?  By the way my application has requirements for 7/24, high availability, load balancing, and the requirement to perform Point-In-Time (PITR) when and if required.  Also database needs to be hosted in an Linux environment.
0
Hi, i have an application running in a debian server, developed in delphi 7 for 32 bits, with postgresql as database, port 5433.
To get an easy way for updating the app, all the clients (windows platform, 32, 64 bits, xp, 7, and 10), reference the executable in the server, but execute it in a local folder, where an ini file configures the link to the database.
In a lot of customers (not all with a debian server, instead, there are windows servers and ubuntu server), and in this customer, this scheme works fine.
In one PC, with Win10, i have this strange problem:
1) if i use a link, that reference the exe in the server and execute the app, it doesn't work. I get an error (internal to the app), as if i can't reach postgresql server.
2) if i copy the exe, and execute in any folder, executing in the same folder as 1), the app works fine.
It seems to be something of the firewall... i disabled it.
It seems to be something of the antivirus... i disabled it (AVG).
pgAdmin, conects to the server.
I simply can't see any other posible solution...
Can anybody help?
Thanks!
0
when I insert in table A  then it automatic copy record that just create in view table B  in postgresql
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 am attempting to use an HTML form contained in a modal to transmit a PDF and an image to a PostgreSQL database. I don't have the database set up yet, but I've already run into an issue. My file structure has a folder for 'models', 'routes', 'views', and a main 'app.js' file. I've included the app file below as well as the files containing the issue. The form is in the 'btb.html' file (contained within the views folder) and begins on line 211 (there is a second form below it, but I haven't begun to use that one yet). The form should be posting to the URL of the 'btbRoute.js' file (contained within the routes folder) and the "router.post" there should handle the response and create an entry in the database. I've also included two files from the 'models' folder-- the 'order.js' file that defines the schema model for my database entry and the 'index.js' file that will eventually connect all my models together.

I'll eventually want to render the information that I'm inputting elsewhere on the website, so if anything that I'm doing would prevent that I would need to find a different solution (I have no idea if that's even a thing, but just in case).

Thanks in advance.
index.js
order.js
btbRoute.js
btb.html
app.js
0
Postgres allows you to create a table using inheritance. We have a design where we have 1400 tables that inherit from one main table. These tables are for each of our vendor's inventory.

When I want to query stock for a vendor, I just query the main table. When running `Explain`, the explanation says that it is going through all 1400 indexes and quite a few of the inherited tables. This causes the query to run very slowly. If I query only the vendor's stock table, I cut the query time to less than 50% of the time by querying the main table.

We have a join on another table that pulls identifiers for the vendor's partner vendors and we also want to query their stock. Example:
(This query runs in about 2 seconds)
SELECT 
	(select m2.company from sup.members m2 where m2.id = u.id) as company,  
    u.id,
	u.item, 
	DATE_PART('day', CURRENT_TIMESTAMP - u.datein::timestamp) AS daysinstock, 
	u.grade as condition, 
	u.stockno AS stocknumber, 
	u.ic, 
	CASE WHEN u.rprice > 0 THEN 
		u.rprice 
	ELSE 
		NULL 
	END AS price, 
	u.qty
FROM pub.net u 
LEFT JOIN sup.members m1 
    ON m1.id = u.id OR u.id = any(regexp_split_to_array(m1.partnerslist,',')) 
WHERE u.ic in ('01036') -- part to query
  AND m1.id = 'N40'     -- vendor to query

Open in new window


Now, this refactor makes the code run in about 800ms.
WITH cte as (
    SELECT partnerslist as a FROM sup.members WHERE id = 'N40' 
) 
SELECT 
    (select m2.company from sup.members m2 where m2.id = u.id) as company,  
    u.id,
	u.item, 
	DATE_PART('day', CURRENT_TIMESTAMP - u.datein::timestamp) AS daysinstock, 
	u.grade as condition, 
	u.stockno AS stocknumber, 
	u.ic, 
	CASE WHEN u.rprice > 0 THEN 
		u.rprice 
	ELSE 
		NULL 
	END AS price, 
	u.qty
FROM pub.net u 
WHERE u.ic in ('01036')                                                    -- part to query
  AND u.id = any(regexp_split_to_array('N40,'||(select a from cte), ','))  -- vendor to query

Open in new window


However, is there away to modify them to get the data from ONLY the specific inherited tables?

The n40_stock table has stock for the vendor with id = N40 and N40's partner vendors (partnerslist) are G01, G06, G21, K17, N49, V02, M16  so I would also want
to query the g01_stock, g06_stock, g21_stock, k17_stock, n49_stock, v02_stock, and m16_stock tables.
0
Hi,

  We work on Postgresql 9.6, installed on Linux.  
We get SQL code requests coming from different teams, such as DDLs, DMLs, TCLs etc.
We have various environments such as DEV01, DEv02, ST01, ST02 and UAT.

My task is to deploy the code snippets coming from individual code requests, into all the above environments.
Currently i'm copying and pasting these scripts manually into each of the aboce environments.

Is there a way to automate this effort so that, i can change the db in a file and quickly execute the code?
0
Hi

I have a PostGreSQL/PostGIS database
Something like this

ID, Group_ code , point_number, date, geom
1, 1234, 1 , 2018-05-10, geom
2, 1234, 2 , 2018-05-10, geom
3, 1234, 3 , 2018-05-10, geom
4, 1234, 4 , 2018-05-10, geom
5, 1234, 5 , 2018-05-10, geom
6, 1235, 1 , 2017-05-10, geom
7, 1235, 2 , 2017-05-10, geom
8, 1235, 3 , 2017-05-10, geom
9, 1235, 4 , 2017-05-10, geom
10, 1234, 1 , 2017-05-10, geom  // WRONG

Open in new window


I can have multiple Group_ code  the same but the should have the same date

I need to find all rows where the same Group_ code has 2 or more dates
0
I want to convert column type from  to integer

ALTER TABLE billdetail ALTER COLUMN  masterid TYPE integer;

Open in new window


[Err] ERROR:  column "masterid" cannot be cast automatically to type integer
HINT:  You might need to specify "USING masterid::integer".

when I try
 ALTER TABLE billdetail USING masterid::integer; 

Open in new window


[Err] ERROR:  syntax error at or near "USING"
LINE 1: ALTER TABLE billdetail USING masterid::integer;
0
PostgreSQL  Vacuum:

•SELECT version();   --PostgreSQL 9.4.9
•Currently we have vacuum enabled with conservative setting of %20.

1.   The language on the support sites talks about the tables of the Postgress DB being held in individual files ?
 If we were to issue a 'vacuum full' command, would the recovery process re-write each table 1 at a time and 0 out the previous table ?
 How much extra space do we need to have available to issue 'vacuum full' and not run out of disk space ? (At least as much as the largest table ? or the whole D.B. ?)

2.  Can we call 'Vacuum Full' and specify a particular table, we only that table get locked ?

3.    Can 'vacuum full' be cancelled once called if it is taking too long and we need to get production back up and running on that table ?
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
>