PostgreSQL

2K

Solutions

3

Articles & Videos

1K

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.

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

Sign up to Post

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
Free Tool: IP Lookup
LVL 8
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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
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
Upcoming Webinar: Percona XtraDB Cluster 6/21 10am
LVL 2
Upcoming Webinar: Percona XtraDB Cluster 6/21 10am

Join Percona’s MySQL Practice Manager Kenny Gryp and QA Engineer, Ramesh Sivaraman as they present Percona XtraDB Cluster, Galera Cluster, MySQL Group Replication on Wednesday, June 21, 2017 at 10:00 am PDT / 1:00 pm EDT (UTC-7).

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
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
Free Tool: Path Explorer
LVL 8
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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
Do we have any techniques or tools to convert ER to Activity diagram.
If so, how?
0
I had this question after viewing Backup and Restore VCenter Linux Appliance methods and scenario best practice ?.

Hi All,

Since VMware forcing us to migrate from Windows VCenter server into the cryptic and hard to manage Linux appliance, I wonder what sort of Proactive and Reactive steps that you implement daily or monthly to ensure that the Vcenter can work normally without any issue ?

Normally, VeeamONE or any other Windows & SQL Server DB monitoring tool is able to send email alert to me when the server having problem or reaching certain amount of threshold.

Any help would be greatly appreciated.

Thanks.
0
$dro ='DROP TABLE IF EXISTS  pong8_nonduplicate_maintablebase';
sc_exec_sql($dro);


$sql ='
CREATE table pong8_nonduplicate_maintablebase as

SELECT DISTINCT
public.pong8_maintablebase."filname",
public.pong8_maintablebase."timestamp8",
public.pong8_maintablebase."Last",
public.pong8_maintablebase."TradePrice",
public.pong8_maintablebase."TradeVolume",
public.pong8_maintablebase."TradeFlags"
FROM
public.pong8_maintablebase' ;

sc_exec_sql($sql);
	
	
echo " maintable ready " ;

Open in new window

0
What I am attempting to do is create a function of the query below, where instead of using + 45, the function iterates through all values from + 35, 36, 37...45 and returns the MAX value from this defined range (35-45).

Notice 45 appears twice in this query. This would be the input variable.

SELECT qqyy, q1.cobrand_id, q1.a/q2.d AS sum
FROM (
    WITH q(qqyy, firstday, lastday) AS (
        SELECT '17_Q4', DATE '2016-10-29', DATE '2016-10-29' + 45
    )
    SELECT q.qqyy, cobrand_id, sum(calc) AS a
    FROM temp_08.jwn_calc s
    JOIN q ON s.optimized_transaction_date > q.firstday
              AND s.optimized_transaction_date <= q.lastday
    GROUP BY q.qqyy, cobrand_id ) q1
INNER JOIN (
    SELECT cobrand_id AS c,
           sum(CASE WHEN optimized_transaction_date > '2015-10-31'
                         AND optimized_transaction_date <= '2015-10-31' + 45
                    THEN Calc END) / 
           sum(CASE WHEN optimized_transaction_date > '2015-10-31'
                         AND optimized_transaction_date <= '2015-10-31' + 91
                    THEN Calc END) AS d
    FROM temp_08.jwn_calc
    GROUP BY cobrand_id) q2 ON q1.cobrand_id = q2.c;

Open in new window


Conceptually, what I am doing is looking at what % of the fiscal quarter was complete 45 days into last year, and using that to extrapolate/divide to derive a full value of the current quarter.
0

PostgreSQL

2K

Solutions

3

Articles & Videos

1K

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.

Top Experts In
PostgreSQL
<
Monthly
>