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 have a cloud database
The database  is postgresql and the operating system is Linux Unbuntu 16.04
The server has email configured

Each time i log in I get the attached message which suggest to me
that the system checks for mail and cant  find any mail . Thats why
it reports no mail in the message

My requirement is that the loggin name of the user logging in should be sent to
my email address  techsupport@datatronicsnig.com

What modification do i need to do in my login script to achieve this

How can i achieve  this functionality  for every login

O.A. Oluwole
login-message.PNG
0
Windows Server 2016: All you need to know
LVL 1
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Hello Experts,

I have a webpage and I need to get the data from a postgres db.  I will probably hire someone to actually do this, but I'd like to understand the process prior.  I think that I have to use node.js to get the data from the database.  Does that sound right?  Or is there a better way to get at the data?
0
I have a PosgreSQL database where I have an array field with 4 values.  This query will extract one value at the time
select public.addresses.address[1] from public.addresses

Open in new window


I'm linking the table to an Access database and the array I have above does not work there. How can I do this in Access?

Here is are my results from PostgreSQL and this is what I want to do in Access
Image-39.jpg
Image-38.jpg
0
The query is as below.
The attached file shows the existing worksheet with the columns that are output from this query.
The attached file also shows the desired worksheet, with some columns removed and the postcode column included twice, the extra postcode column to be included as the first column.
CREATE OR REPLACE VIEW ADDRESS_VIEW

AS

SELECT
AD.ADDRESS_DETAIL_PID as ADDRESS_DETAIL_PID,
AD.STREET_LOCALITY_PID as STREET_LOCALITY_PID,
AD.LOCALITY_PID as LOCALITY_PID,
AD.BUILDING_NAME as BUILDING_NAME,

AD.LOT_NUMBER_PREFIX as LOT_NUMBER_PREFIX,
AD.LOT_NUMBER as LOT_NUMBER,
AD.LOT_NUMBER_SUFFIX as LOT_NUMBER_SUFFIX,

FTA.NAME as FLAT_TYPE,
AD.FLAT_NUMBER_PREFIX as FLAT_NUMBER_PREFIX,
AD.FLAT_NUMBER as FLAT_NUMBER,
AD.FLAT_NUMBER_SUFFIX as FLAT_NUMBER_SUFFIX,

LTA.NAME as LEVEL_TYPE,
AD.LEVEL_NUMBER_PREFIX as LEVEL_NUMBER_PREFIX,
AD.LEVEL_NUMBER as LEVEL_NUMBER,
AD.LEVEL_NUMBER_SUFFIX as LEVEL_NUMBER_SUFFIX,

AD.NUMBER_FIRST_PREFIX as NUMBER_FIRST_PREFIX,
AD.NUMBER_FIRST as NUMBER_FIRST,
AD.NUMBER_FIRST_SUFFIX as NUMBER_FIRST_SUFFIX,
AD.NUMBER_LAST_PREFIX as NUMBER_LAST_PREFIX,
AD.NUMBER_LAST as NUMBER_LAST,
AD.NUMBER_LAST_SUFFIX as NUMBER_LAST_SUFFIX,

SL.STREET_NAME as STREET_NAME,
SL.STREET_CLASS_CODE as STREET_CLASS_CODE,
SCA.NAME as STREET_CLASS_TYPE,
SL.STREET_TYPE_CODE as STREET_TYPE_CODE,
SL.STREET_SUFFIX_CODE as STREET_SUFFIX_CODE,
SSA.NAME as STREET_SUFFIX_TYPE,

L.LOCALITY_NAME as LOCALITY_NAME,

ST.STATE_ABBREVIATION as …
0
Hi Team,

I am having a Python script which insert data into PostgreSQL from XML file. However, we have updated the python version to 3.6.1 it does not add any data into PostgreSQL database and does not throw any error message on command line screen.
Can you please suggest what's goes wrong in the below code...
#/usr/bin/python3.6.1

# import modules
import sys
import psycopg2
import datetime


#import cursors
now = datetime.datetime.now()
# current data and time
dt = now.strftime("%Y%m%dT%H%M%S")

# xml tree access
#from xml.etree import ElementTree
import xml.etree.ElementTree as ET

# incremental variable
x = 0
with open(sys.argv[1], 'rt',encoding="utf8") as f:
    	#tree = ElementTree.parse(f)
		tree = ET.parse(f)
# connection to postgreSQL database
try:
    conn=psycopg2.connect("dbname='####' user='postgres' password='XXXX'")
except:
    print ("Hey I am unable to connect to the database.")
cur = conn.cursor()
# access the xml tree element nodes
try:
	for node in tree.findall('.//tu'):
		src = node.find('tuv[1]/seg')
		tgt = node.find('tuv[2]/seg')
		
		source = src.text.encode("utf-8")
		target = tgt.text.encode("utf-8")
		
		#query to database table
		query = "INSERT INTO MT_DB (SRC_LOCALE,SRC_DATA,TGT_LOCALE,TGT_DATA,DOMAIN,DATETIME,TYPE) VALUES (%s, %s, %s, %s, %s, %s, %s)"
		data = (sys.argv[2],source,sys.argv[3],target,sys.argv[4],str(dt),sys.argv[5])

		#for locale swap with data
		#data = 

Open in new window

0
Hi,

   I have a PostgreSQL backup file.It is possible to convert mdf/ldf file.
0
I am trying to create a query returns some aggregates in Group By for a Select statement that also includes text field that is not distinct or included in an aggregate function.

While I can manually build the results in an Excel worksheet, my hope is to make a single query that can be executed on a schedule.

The target results look like this:

Target data set
Basically, the desire is to get a report of who order what, how much and the average size. Both Sales Reps and Customer Service Reps (CSRs) can take orders in the given territories. There is a one-to-many ratio between Sales Reps and CSRs. The blank items on the left mean that the only the CSR entered order for the territory for this time frame.

I have referenced portions in the data set and included the SQL below.

The code for the queries are almost identical with the exception of a couple lines.  I have highlighted these.

Query 1
And here is the other one.

Query 2
Since this would be a monthly report that is run overnight, it is not necessarily have to to run fast or efficient. So subqueries and looping would be fine. As indicated in the title, the db here is Postgres 9.4, but I will try to apply the concepts for product accordingly,.

Can anyone provide some hints / guidance on how to accomplish?

Thanks much in advance.
0
ALTER TABLE hotel
    ADD CONSTRAINT h_hotelname references location_hotel(m_hotelname)

Open in new window


[Err] ERROR:  syntax error at or near "references"
LINE 2:     ADD CONSTRAINT h_hotelname references location_hotel(m_h...

How can I fix it
Capture.PNG
0
PostgreSQL 8.1.8 with function SELECT TRANSLATE($1, 'áéíóúàèìòùãõâêîôôäëïöüçÁÉÍÓÚÀÈÌÒÙÃÕÂÊÎÔÛÄËÏÖÜÇ', 'aeiouaeiouaoaeiooaeioucAEIOUAEIOUAOAEIOOAEIOUC') and table with over a million records takes over 20 seconds for an ilike search on text field. What is my best option or solution to this problem besides upgrading PostgreSQL. Select command is in PHP file.
0
Hi, I am using JDBC to connect to a Postgres DB.  I am calling a method that connects to the DB twice.  It connects once to do a select statement and another time to do an insert statement.  I am checking to see if the a session_id exists and if it doesn't I am adding the session_id into the DB.   It does the initial select fine, but when I try to connect the second time to do the insert I am getting the error in the title above. Here is what my code looks like.  Can anyone help me out with resolving this?

private void initialInsert(String sessionId) {

		try {
			Class.forName("org.postgresql.Driver");
		} catch (ClassNotFoundException e) {
			log.info("Class Not Found: " + e.getMessage());
		}
		log.info("Entering the initial insert");
		String sessionIdFromDB = "";
		String url = "jdbc:postgresql://10.233.52.71:5432/CustomersLikeYou";
		Properties props = new Properties();
		props.setProperty("user", "itrd");
		props.setProperty("password", "itrd");
		Connection conn = null;
		ResultSet rs = null;
		try {
			log.info("Entering select");
			conn = DriverManager.getConnection(url, props);
			Statement statement = conn.createStatement();
			rs = statement.executeQuery(
					"select session_id from public.individual_customer where session_id = '" + sessionId + "'");
			log.info("Succeful select");
			while (rs.next()) {
				if (StringUtils.isNotEmpty(rs.getString("session_id"))) {
					sessionIdFromDB = rs.getString("session_id");
				}
			}

		} catch (SQLException 

Open in new window

0
Get MongoDB database support online, now!
LVL 2
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Hi, I have been getting an error in my java web application "No suitable driver found for jdbc:postgresql://...."

This is a maven java web application.  The jar is included as a dependency in the pom file and when the WAR is built out the jar can be found in webapps/MyApplication/WEB-INF/lib.  

I also tried placing the jar in /usr/share/tomcat/lib.  I am still recieving the same error.  I have tried this on a local DB with postgres and the driver works fine.  Can anyone help me out?
0
What is the equivalent package for oracle  dbms_application_info..set_module in postgreSQL
0
Hi,

   how to take PostgreSQL backup in local driver and restore also.please tell me easiest way of backup & restore.
0
Hi,

  Now i am using Postgre sql in one machine.more than 3 person want to access this postgresql database.So each & everytime login that postgresql machine.So can you please suggest application using connect postgresql db access remote like SQL Express database connect instance remote using SQL management studio.
0
Hi,

I found this command for solving the lock file "postmaster.pid" already exists in postgresql
You could stop your server doing :

pg_ctl -D /usr/local/var/postgres stop

Where did the command should be run?
I was tried to execute using run & command prompt not working.I have a error message please find the attachment file.
PostgreIssue.png
0
I am using Navicat and I (finally) have Postgresql running.

I accept defaults as
Record delimiter LF
Text qualifier " (there aren't any - maybe there should be)

Error Message: ERROR:  invalid input syntax for type timestamp: "01:28:00"
LINE 1: ...','180480','','','1','2','1','1','1987-01-05','2','01:28:00'...
                                                             ^

[Msg] [Imp] Processed: 49, Added: 0, Updated: 0, Deleted: 0, Error: 1
[Msg] [Imp] Finished - Unsuccessfully

Open in new window


So I make both date and time field varchar and try again:

Error Message: ERROR:  malformed array literal: "5/1/1987"
LINE 1: ...1AD00002','529950','180480','','','1','2','1','1','5/1/1987'...
                                                             ^
DETAIL:  Array value must start with "{" or dimension information.

[Msg] [Imp] Processed: 49, Added: 0, Updated: 0, Deleted: 0, Error: 1
[Msg] [Imp] Finished - Unsuccessfully

Open in new window


and the query is I think: (not written by me I hasten to add, by Navicat)

[Msg] [Imp] Import start
[Msg] [Imp] Import type - CSV
[Msg] [Imp] Import from - /Users/ADM/Sync/6-Process01/accXtract.csv
[Msg] [Imp] Create table [accXtract]
[Msg] [Imp] Import table [accXtract]
[Err] [Imp] Cannot insert rows 1 - 49:
Query: insert into "public"."accXtract" ("Accident_Index","Location_Easting_OSGR","Location_Northing_OSGR","Longitude","Latitude","Police_Force","Accident_Severity","Number_of_Vehicles","Number_of_Casualties","Date","Day_of_Week","Time","Local_Authority_District","Local_Authority_Highway","1st_Road_Class","1st_Road_Number","Road_Type","Speed_limit","Junction_Detail","Junction_Control","2nd_Road_Class","2nd_Road_Number","Pedestrian_Crossing-Human_Control","Pedestrian_Crossing-Physical_Facilities","Light_Conditions","Weather_Conditions","Road_Surface_Conditions","Special_Conditions_at_Site","Carriageway_Hazards","Urban_or_Rural_Area","Did_Police_Officer_Attend_Scene_of_Accident","LSOA_of_Accident_Location","ACCREF","ACCMTH","A8M","ACCDAY","A8H","ACCYR","MONTH","Location_5Easting_OSGR","Location_5Northing_OSGR") values 

Open in new window

0
Hi,

   I have installed Postgresql 9.6.1 in my desktop machine.If i open the pgadmin it show error"Failed to connect to the pgAdmin application server. Click here to try again."
    After click retry more than 20 times.it will connected after that start pgadmin occur the same issue.please advice to me.what should i do?
0
Hello World.
After searching I am surprised there doesn't seem to be any discussion about this.
I have navicat with two days remaining on trial. I have no idea if it suits me or not, being a novice. I am using Postgres because it seems to come with mac and I like stuff that is built in.
0
SQL Novice here. Mac 10.12.13 I have Postgres installed as the one that comes with mac and Navicat on 14 day trial. Not worked out how to use either., but these are not my questions.

This is, can SQL do Pythagoras and having done that select the nearest location from table 2 to each record in table 1?

The Pythagoras calculation is like this:

Table1 coordinatesE1(a), coordinatesN1(b) (there are 6 million of these)
Table2 coordinatesE2(c), coordinatesN2(d)  (there are 3,335 of these)

We need to know the nearest of each of the locations in table2 to each location in table1.

The calculation of each distance is the square root of:

((a-c)) x (a-c) + ((b-d) x (b-d))

For selection purposes it MAY be acceptable (I do not yet see why not) to omit the square root calc because the least number result is the least number whether the square or the root of it, indeed the square allows for greater differentiation between possible duplicates (as far as I know there are none). Also not least because a square root cannot be calculated it takes longer.

I imagine the above is straightforward, what is not (for me) is having worked out the results for table1 record 1  for every record in table2, how then to select the nearest (lowest/min) results. A sub query I imagine, but only because I have read about them on my road to here.

The original legacy program takes three weeks to do this, so time is not a factor. Frequency of running is likely to be "rare", but I am rather …
0
Visualize your virtual and backup environments
LVL 1
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Continued from here because the initial question has a solution and the discussion has continued.

@tel2: the number of blocks is the United Kingdom OSGB map system, depending on how many digits you take depends the number of squares.

Say we have allocated a site to a location, there may well be other sites within 1km of that location, so when someone checks they will find apparently unallocated sites regarding that location. For this reason generating multiple sites for each location may become relevant. Originally I allowed for this by generating every permutation, but there are too many without some limit to it.

I am wondering if MS Query which is included with Excel, may offer a solution. Always assuming a suitable query can be fashioned.
If we know the expected results will fit in Excel and not break it. I have considered Powerpivot, but rejected it because it is not ubiquitous whereas MS Query is (aside from OBDC driver needed for Mac that comes with Windows.).
We know that VBA breaks Excel.

I'd love to see an SQL Query that will do this.

mac osx 10.12.3
PytagorasArrays500K08EEExtract.xlsx
0
I had this question after viewing Installing Navicat postgreSQL on a local Mac.

Anyone know what the answers might have been? He doesn't say...

Is there a built in thing to be able to do stuff?

mac osx 10.12.3 yosemite
postgres just installed.
navicat just installed.
lost.
0
I run
pg_dump test_database -U test_user -h localhost > backup

Open in new window

and get
pg_dump: [archiver (db)] connection to database "test_database" failed: FATAL:  Ident authentication failed for user "test_user"

Open in new window


It DOES NOT ask me to enter password

If I run
pg_dump test_database -U test_user -W -h localhost > test_database.bck

Open in new window


I get password prompt, but after entering password same error.

How can I authenticate to make pg_dump?
0
Simple question and I still can not find exact answer anywhere on the web.

So I have Jira system. It's not big (20 users). It uses PostgreSQL database. Can I use pg_dump while the system is online? I do not care that system can produce some errors during dump. I care that the database will not become corrupted because of the dump. How does pg_dump work? Does it unload database from memory during the dump? Any prooflinks will be greatly appreciated.
0
Hello,

I got a Database link from Oracle 10gR2 to Postgre 9.5 and i'm having an issue when trying to run a few commands.

I executed a few queries from pl/sql and it works sometimes but sometimes it doesn't.

I noticed that it works with numeric fields , but it doesn't work with string fields.

Here are the results of my tests.

insert into "schema_test"."search"@postgres ("user_id", "request_pk") values ('user_1', 123456);  
--Result: runs ok

select * from "schema_test"."search"@postgres where "request_pk" = 123456;    
--Result: runs ok - request_pk field is  numeric

select * from "schema_test"."search"@postgres where "user_id" = 'user_1';  
--Result: Runs indefinitively and never returns - user_id field is a varchar

delete from"schema_test"."search"@postgres where "request_pk" = 123456;      
--Result: runs ok - request_pk field is  numeric

delete from "schema_test"."search"@postgres where "user_id" = '20171009338401';  
--Result: error  ORA-02070: database postgres does not support some function in this context  
--user_id field is a varchar

Would you guys have any idea of what could be going on?

Thansk in advance
0
I have a VB .Net application which talks to a backend PostgreSQL database.
I'm using NpgSQL to run queries against that database.

In order to get better performance for bulk inserts into the database, I'm using the PostgreSQLCopyHelper (http://bytefish.de/blog/postgresql_bulk_insert/)

I have used it successfully in other C#/.Net applications.
I have however run into issues when I integrated into my current VB.Net application.

This roughly what my code currently looks like:

 
  Dim copyHelper As PostgreSQLCopyHelper(Of FooClass)
    Dim entities As IEnumerable(Of FooClass)
    
     Dim connection As NpgsqlConnection
    
    connection = New NpgsqlConnection(connectionString)
    connection.Open()

    copyHelper.SaveAll(connection, entities)

Open in new window

   

I get the following exception when I attempt to do a SaveAll():

HResult -> -2146233079
Message      -> "Can't close writer, a row is still in progress, end it first"      

With the following stacktrace:

   at Npgsql.NpgsqlBinaryImporter.Close()
   at Npgsql.NpgsqlBinaryImporter.Dispose()
   at PostgreSQLCopyHelper.PostgreSQLCopyHelper`1.SaveAll(NpgsqlConnection connection, IEnumerable`1 entities)


How do I go about debugging this?


Thanks in advance.
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
>