[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x

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

For a database hosted elsewhere we have asked for a dump to use locally with GIS.
The file we have from the dump has the following in it and fails on the import via SQL query.

COPY catch_methods (name) FROM stdin;
Set Line
Set net
Other
\.

Open in new window



The error that comes up is:
Invalid command \.. Try \? for help

What am I missing in regards to this, there are multiple large entries with the above just being a short one.
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!

Hi,

We are migrating from oracle to postgresql. And we faced problems while rewriting oracle procedures to postgresql functions.

We get all the data required for the endpoint from single procedure to reduce round trips to database.

for example if we want show user posts

create or replace procedure user_posts(userId IN NUMBER, noOfPosts OUT NUMBER, posts OUT sys_refcursor, postComments OUT sys_refcursor)

here we are getting noOfPosts, posts and postComments resultset from single procedure.

Searched for the equivalent procedure in postgresql but not found.

While searching for the answer I found returns table. But using returns table I am not able to achieve this. I am able to get only one of these three out parameters.

Because of this constraint now we are using three queries for noOfPosts, posts and postComments.

How can we achieve same with postgresql.

Thanks.
0
Hi,
   we have postgresql function which accepts tags_type composite type as a input parameter.  
How can we pass the values to the function using jpa.

create type tags_type as(tag text, tag_url text)


CREATE OR REPLACE FUNCTION public.create_article(title text, tags tags_type[])
 RETURNS integer
AS $function$
DECLARE
	id int2;
	t tags_type;
begin
	
	insert into articles(a_title)
	values(title);

	id := currval('article_id_seq');

	FOREACH t in array tags
	loop
		insert into atricle_tags(at_id, at_tag, at_url_name)
		values(id, t.tag, t.tag_url);
	end loop;

	return id;
end;
$function$
LANGUAGE plpgsql

Open in new window

0
Hi,

 I am trying to get refcursor from postgresql procedure using spring data jpa. I am getting below error.

org.springframework.orm.jpa.JpaSystemException: No Dialect mapping for JDBC type: 1111; nested exception is org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111

Here data got inserted in database but not able to get the data from.

CREATE OR REPLACE FUNCTION public.register(email text, password text, name text)
 RETURNS refcursor

AS $function$
DECLARE
	userInfo REFCURSOR;
	userId int4;
begin
	insert into "users"(u_email, u_password, u_name)
	values(email, password, name);
	
	userId := currval('user_u_user_id_seq');

	open userInfo FOR SELECT * FROM users WHERE u_user_id = userId;

	return userInfo;
end;
$function$
 LANGUAGE plpgsql

Open in new window



@Override
public User register(User user) {


StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery(REGISTER_PROCEDURE)
        .registerStoredProcedureParameter(1, String.class, ParameterMode.IN).setParameter(1, user.getEmail())
        .registerStoredProcedureParameter(2, String.class, ParameterMode.IN).setParameter(2, user.getPassword())
        .registerStoredProcedureParameter(3, String.class, ParameterMode.IN).setParameter(3, user.getName());

storedProcedureQuery.execute();
List<User> list = storedProcedureQuery.getResultList();


return list.get(0);
}

Open in new window


@Entity
@Table(name = "users")
public class User implements Serializable{

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "u_user_id")
    private long userId;

    @Column(name = "u_email")
    private String email;

    @Column(name = "u_password")
    private String password;

    @Column(name = "u_name")
    private String name;


}

Open in new window

0
this query work in postgresql and console SQL of sc, but no work in SQL of grid...why?

      SELECT
          cpccod,
          lpad(cpcdes,cast(60+(4*cpcnvl) as int), ' '),
          cpccla,
          cpcslm
      FROM
          "public".sccmpc
           where trim(ccodemp)='1' and cempre='E02'
           order by cpccla
0
I want to convert my postgress 7.4 Sql_ascii database to postgress 9.4 utf8 database..... how can I do it?..... please help
0
Greetings,

I have a 700gb postgres database which has a standby readonly replica and I wish to migrate and upgrade to a new server with the latest postgres version so what would be the ideal migration path for this situation? it's in a Linux environment.
0
Simply put.. I connected to a free micro instance of a postgres DB I created on AWS RDS.

It worked fine. I set up a database and a couple tables on it.

Now that I'm trying to connect to it from my app.. it's just timing out.

AND.. I can no longer connect to it from PSequel or.. anything. It just keeps timing out.

I'm not even sure what more detailed questions to ask or what to direction to poke in on this one.
0
Hi, I write after sending my code to the attention. So ... I have to convert an Oracle Pl-Sql application into a similar PostgreSQL application. In addition to the ect table view tables, I need to convert Procedures and Functions.
I find myself having to convert an Oracle Pl-Sql procedure with 6 input parameters (the search filter) and 2 output parameters (one numeric for cpunt of extracted recs, the other Type Cursor for the processed cursor result ).
Well in Postgres does not exist the Type Cursor, so I wondered how to overcome the problem having a minimum target: the structure of the procedure / function should remain unaltered in the input and output parameters.
This is because the java application must be able to call the procedure / function both in oracle and postgres without modifying the call itself.
It's possible?
So far I have worked with the refcursor solution and with the table solution (as data type of output).
With the first one they could not get the result of the cursor but only the error "unnamed_portal_1" while with the TABLE solution I finally found the solution but it obliges me to have to modify the similar running procedure in Oracle.
Attach the test environment and code tested in the various versions.
0
The same slightly changed function returns only the count. I have difficulty with the cursor result sets.
If I use the function3 using refcursor, I return to "unnamed_portal_1", who finds the solution to extract the second cursor? The 2 OUT variables must remain since the java application must invoke the Oracle function and the one converted to Postgres without changing the call
function2.sql
function3.sql
0
Ask an Anonymous Question!
LVL 10
Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

I find this function, but I can not return any data.
Testing the individual queries, there are 5 recs that should be extracted.
Where am I wrong?
The function MUST be with the INPUT parameters that are highlighted and with the two OUT parameters.
This is because of the conversion of a similar Oracle function, a function that must be invoked by the same application by either pointing to Oracle or pointing to Postgres
function1.sql
0
CREATE OR REPLACE FUNCTION pck_gestione_tracce_sistema.p_get_rotta_traccia_sistema6(
    IN in_id_traccia_sistema numeric,
    IN in_id_versione numeric,
    IN in_limite_inferiore numeric,
    IN in_limite_superiore numeric,
    IN id_limite_inferiore_data date,
    IN id_limite_superiore_data date,
    IN in_id_viaggio numeric,
    OUT on_out_total_count numeric,
    OUT oc_out_recordset character varying)
  RETURNS record AS
$BODY$
DECLARE
   LD_LIMITE_INFERIORE_DATA Date ;
   LD_LIMITE_SUPERIORE_DATA Date ;
   LK_PACKAGE_NAME varchar(1000);
   LK_FUNCTION_NAME varchar(1000);
   LN_DEBUG_INFO Integer;
 
   ref refcursor;
   ref1 refcursor;

Begin  

   
   LN_DEBUG_INFO := 1;
   LK_PACKAGE_NAME := 'PCK_GESTIONE_DATI';
   LK_FUNCTION_NAME := 'P_GET_ROTTA_TRACCIA_SISTEMA';
   LD_LIMITE_INFERIORE_DATA  := TO_DATE(TO_CHAR(ID_LIMITE_INFERIORE_DATA,'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS');
   LD_LIMITE_SUPERIORE_DATA  := TO_DATE(TO_CHAR(ID_LIMITE_SUPERIORE_DATA,'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS');

    If (LN_DEBUG_INFO = 1) Then
      PERFORM nereus.p_logging(IN_TIPO        => 'I'::character varying,
                IN_CODICE      => 'INFO'::character varying,
                IN_DESCRIZIONE => 'Input call'::character varying,
                IN_MODULO      => LK_PACKAGE_NAME::character varying, --LK_PACKAGE_NAME,
                IN_FUNZIONE    => LK_FUNCTION_NAME::character varying, --LK_FUNCTION_NAME,
               …
0
CREATE OR REPLACE FUNCTION pck_gestione_tracce_sistema.p_get_rotta_traccia_sistema10(
    IN in_id_traccia_sistema numeric,
    IN in_id_versione numeric,
    IN in_limite_inferiore numeric,
    IN in_limite_superiore numeric,
    IN id_limite_inferiore_data date,
    IN id_limite_superiore_data date,
    IN in_id_viaggio numeric,
    OUT on_out_total_count numeric,
    OUT oc_out_recordset character varying)
  RETURNS SETOF record AS
$BODY$
DECLARE
   LD_LIMITE_INFERIORE_DATA Date ;
   LD_LIMITE_SUPERIORE_DATA Date ;
   LK_PACKAGE_NAME varchar(1000);
   LK_FUNCTION_NAME varchar(1000);
   LN_DEBUG_INFO Integer;
 
   ---ref refcursor;
   ---ref1 refcursor;
   counter record;
   setdati record;

Begin  

   
   LN_DEBUG_INFO := 1;
   LK_PACKAGE_NAME := 'PCK_GESTIONE_DATI';
   LK_FUNCTION_NAME := 'P_GET_ROTTA_TRACCIA_SISTEMA';
   LD_LIMITE_INFERIORE_DATA  := TO_DATE(TO_CHAR(ID_LIMITE_INFERIORE_DATA,'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS');
   LD_LIMITE_SUPERIORE_DATA  := TO_DATE(TO_CHAR(ID_LIMITE_SUPERIORE_DATA,'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS');

    If (LN_DEBUG_INFO = 1) Then
      PERFORM nereus.p_logging(IN_TIPO        => 'I'::character varying,
                IN_CODICE      => 'INFO'::character varying,
                IN_DESCRIZIONE => 'Input call'::character varying,
                IN_MODULO      => LK_PACKAGE_NAME::character varying, --LK_PACKAGE_NAME,
                IN_FUNZIONE    => …
0
Today we have 1 odoo instance with one front and 1 postgres db, how to scale it as we want to use it with 50k+ users ? :)
0
I have a server which runs on PostgreSQL, Django on apache and gets hang up every couple of days. I understood that it would require PostgreSQL optimization. But there are so many parameters not sure where to start. As of now, the server hanging up. Please let me know what kind of information you need so that I can provide you and you can better assist me.

Also I have iptables firewall setup not sure where exactly is the problem. But the website hangs up every 1 one day and when I restart the apache it works for one day.
0
Hello Experts,

Currently the Atlassian applications along with the Postgres databases reside on 1 VM.

I am looking for any recommendations for splitting up the applications/databases and platform options to integrate the Atlassian applications on (VM, Docker or AWS).

We have 100 users split between the US and UK.
0
Hello, I am using Ubuntu 16.04 and Python psycop2 to insert about 2.1 million rows (5 columns) with a loop reading a 120GB binary file into a Postgresql Table.   I have a I7-7600K CPU with 16GB Ram.   Around 280,000 my system runs out of memory and my computer will lock up or the Python IDE will crash.   Could someone tell how to fix this problem?   I tried to turn off auto-commit and commit every 1000 inserts.   Any help would be greatly appreciated.    Thank you.

Below is a sample of my code

for entry in entries:
   #retrieve data here in the loop

        try:
            query = "INSERT INTO registryTable (pkt_num, timestamp, name, address, gender) VALUES (%s, %s, %s, %s, %s);"
            data = pkt_num, timestamp, name, address, gender
            cur.execute(query, data)
        except psycopg2.DatabaseError, e:
            print(e)
        i += 1
        if i % 1000 == 0:
            conn.commit()

David
0
Hello,

i have this SQL that i am getting a string out of range exception

SELECT month(from_unixtime(wo.createdtime/1000)) "Month",
week(from_unixtime(wo.createdtime/1000)) "Month/Week",
count(wo.workorderid) "Number of opened request",
count(case when std.ispending='0' THEN 1 ELSE NULL END) "Number of closed request" 
FROM WorkOrder wo 
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID 
WHERE (wo.ISPARENT='1') and wo.CREATEDTIME >= <from_thisyear> 
AND wo.CREATEDTIME <= <to_thisyear> group by month(from_unixtime(wo.createdtime/1000)),week(from_unixtime(wo.createdtime/1000)) 
order by 1

Open in new window



Error
java.lang.StringIndexOutOfBoundsException: String index out of range: -1
	at java.lang.String.substring(Unknown Source)
	at com.adventnet.servicedesk.reports.utils.ReportUtil.parseDateColumns(ReportUtil.java:2422)
	at com.adventnet.servicedesk.reports.utils.CoreReportDesigner.generateSQLReport(CoreReportDesigner.java:8151)
	at com.adventnet.servicedesk.reports.utils.CoreReportDesigner.generateSQLReport(CoreReportDesigner.java:8020)
	at com.adventnet.servicedesk.reports.action.ReportRequestHandler.runSQLQueryReport(ReportRequestHandler.java:1684)
	at com.adventnet.servicedesk.reports.action.ReportRequestHandler.handleRequest(ReportRequestHandler.java:2002)
	at com.adventnet.servicedesk.reports.action.CustomReportHandler.handleRequest(CustomReportHandler.java:195)
	at 

Open in new window

0
We are setting an SFTP server with ProFTPD with PostgreSQL back end.

The user can authenticate and upload and download fine.  However, we are running into some sort of timeout issue while transferring upload/download.  Our SFTP server is anticipating large file transfer gigabyte.  

Checking on the /var/log/message we see that while the client is upload/download, there will be an SSH2 session closed and opened in a specific time around 45 seconds on one server and 30 seconds on another VM that we are testing on.  It also follow by a signal 11 on ProFPTD.

Mar 27 22:52:38 ftsftpsvr02 proftpd[21446]:  - SSH2 session opened.
Mar 27 22:52:49 ftsftpsvr02 proftpd[21449]:  - SSH2 session opened.
Mar 27 22:53:27 ftsftpsvr02 proftpd[21449]:  - ProFTPD terminating (signal 11)
Mar 27 22:53:27 ftsftpsvr02 proftpd[21449]:  - SSH2 session closed.
Mar 27 22:53:31 ftsftpsvr02 proftpd[21453]: - SSH2 session opened.
Mar 27 22:53:39 ftsftpsvr02 proftpd[21446]:  - SSH2 session closed.
Mar 27 22:54:10 ftsftpsvr02 proftpd[21453]:  - ProFTPD terminating (signal 11)
Mar 27 22:54:10 ftsftpsvr02 proftpd[21453]:  - SSH2 session closed.
Mar 27 22:54:13 ftsftpsvr02 proftpd[21458]:  - SSH2 session opened.
Mar 27 22:55:42 ftsftpsvr02 proftpd[21458]:  - SSH2 session closed.

We check both proftd.conf and sshd_config and there isn't any tiemout specified.
0
Tech or Treat! - Giveaway
LVL 10
Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Greetings,

I have been tasks to upgrade Postgres cluster (replication with read only standby node) from 9.0 to 9.4, 9.5 or 9.6. What's the best option to upgrade and what advantages in the various versions you gain versus the current version? This is in a Linux environment. Please advise.
0
I am running Postgres 8.4 on CentOS 6.3.  I have configured the pg_hba.conf file to allow remote connections.  Everything works well except about once a week the pg_hba.conf file is getting reset to a default file -- my changes are completely lost.  When this happens we lose connectivity, of course.  I've made a backup of the file so restoring and restarting the Postgres service corrects the problem, but I can't figure why the file is getting reset.

This is a brand new server.  I am the only admin.  I've configured everything on the server and have reviewed all Cron jobs to ensure nothing is running that could possibly be resetting the file.  Any ideas?
0
Hello Experts,

I would like to use spark streaming to identify in real time if a record already exists in the database.

The data that we receive contains the customerId and I would like to be able to determine if the customerId that we are receiving is already stored in a specific table in our database. If not I would go ahead and store the record, if yes I won't store it again.

The customerId is a value generated by an external application
The data is received from Kafka 24/7.
0
I'm stuck (novice says with a bit of gifted code)

Error : ERROR:  syntax error at or near "1"
LINE 5:        INNER JOIN  LATERAL ( SELECT TOP 1
                                                ^

UPDATE ACC
SET ACC.ACC_EC = SITESmin.ACC_EC, 
    ACC.ACC_NC = SITESmin.ACC_NC
FROM    ACC
       INNER JOIN LATERAL ( SELECT TOP 1
                                *
                      FROM      SITES
                      ORDER BY  ( acc_ec - site_etg ) * ( acc_ec - site_etg ) + (acc_ncb - site_ntg ) * ( acc_ncb - site_ntg )
                    ) SITESmin;

Open in new window



MACOSX 10.12.3 postgres (PostgreSQL) 9.4.6

I thought lateral was ok according to : https://blog.heapanalytics.com/postgresqls-powerful-new-join-type-lateral/

Is it LIMIT and not TOP ?
0
I am having trouble changing the structure and data of certain columns that are currently in postgres as text (and that contain blanks) into either integer or double precision. And a simple ALTER ... USING... statement won't work because there are blanks that haven't been changed to NULL.

Current sample data
to

NeedsToLookLike.PNG
To look like this
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
>