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

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
Cloud Class® Course: CompTIA Healthcare IT Tech
LVL 12
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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

0
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

0
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
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
I ran the following tutorial on my lapp stack:
https://docs.bitnami.com/google/how-to/generate-install-lets-encrypt-ssl/

In the tutorial I stopped my whole stack, chmod it's directory and chown it to root.

When I restarted my stack then I got the following error:

pg_ctl.bin: another server might be running; trying to start server anyway
waiting for server to start.... stopped waiting
pg_ctl.bin: could not start server
Examine the log output

Open in new window


I ended up undoing steps in Let's Encrypt tutorial; I chmod 666 for my postgresql directory and chown to user postgres (instead of root).
I found a comment online about deleting postmaster.pid to restart postgresql.
I did not delete but I did rename it.
When I restarted Postgresql then a new postmaster.pid was created and the server is up again.

In further review online, I may have made a major mistake by switching out the postmaster.id .
Am I ok with the new created postmaster.id or should I use the original that was renamed?
And if I should go with the old one then how do I get postgresql safely get it started again?
0
How to convert existing Sql server database to postgre database?
0
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!!
0
I have a Windows server where the CPU utilization is constantly at 50% which is mostly made up of the PostgreSQL server service. Even if I reboot the server as soon as it is up the CPU usage is at 50% +

It does not matter whether people are logged in and using the Application that is using the PostgreSQL server. Also, the application using the PostgreSQL server makes very light use of it

Here is a screenshot and one as well below from an CPU monitor external to the server. The CPU monitor is from the last 3 hours which was night time with no one on the server or using the application

0
Hi,

 I have a trigger where there is an insert, update and a delete, conditionally.
 I want to let the trigger do its work, but if there is any error, while doing any transactions, i would like to ignore it.
I tried inserting the exception when others, at the end of the function, but that is not ignoring the results.

Can someone kindly suggest how to add that exception logic in the attached sql.
fn_trg_attribute_audit.sql
0
Cloud Class® Course: Microsoft Azure 2017
LVL 12
Cloud Class® Course: 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,
Im currently using psql but for the purpose of unit testing Im using HSQLDB to run my queries. All the queries works fine in psql but I get invalid datetime format exception in hsql.
Below is my query,
INSERT INTO microbatch_redrive
SELECT mii.*,(to_char(CURRENT_TIMESTAMP ,'yyyy-mm-ddThh:mi:ss.msZ')) FROM
(SELECT * FROM
  ( SELECT mi.*,me.extractid FROM
    (SELECT * FROM microbatch_info WHERE
        ((numattempts<= ${maxNumOfAttempts})
        AND (starttime>= '${starttime}') 
        AND (starttime<= '${endtime}')))
    AS mi LEFT OUTER JOIN microbatch_extract_info AS me ON mi.microbatchid = me.microbatchid) 
    AS mime WHERE((mime.raverunstatus='SUCCEEDED' AND mime.extractid is null)
    OR (mime.starttime < to_char(CURRENT_TIMESTAMP,'yyyy-mm-ddThh:mi:ss.msZ') AND mime.raverunstatus='NOT_STARTED')
    OR (mime.raverunstatus = 'FAILED')
    OR (mime.raverunstatus='STARTED' AND mime.starttime< (to_char(CURRENT_TIMESTAMP - INTERVAL '1' hour * ${extractTimeIntervalInHour},'yyyy-mm-ddThh:mi:ss.msZ')))
    )
) AS mii LEFT OUTER JOIN
(SELECT microbatchid FROM microbatch_redrive WHERE raverunstatus='REDRIVE_SUCCEEDED') AS mr ON mii.microbatchid=mr.microbatchid WHERE mr.microbatchid IS NULL;

Open in new window


ERROR:
 Testcase: getFailedMicrobatchesToRedrive(com.amazon.eradar.scheduledjobs.utils.RedriveFailedMicrobatchUtilTest):    Caused an ERROR
    [junit] data exception: invalid datetime format: Thh:mi:ss.msZ
    [junit] java.sql.SQLDataException: data exception: …
0
I want to install Postgres 10 on Rhel7 into a specific directory where not into anything on the root file system so how would I install everything for postgres 10 into the following directory:
/appsdb/postgres/10/
0
When I run the vaccum command, it freezes the memory thereby resulting in slow performance. How can I prevent vaccum from becoming a memory hog?
0
Hi Everyone,

I am facing with a deadlock issue in Postgres when concurrent processes (created by stress tests from JMeter) from my Java web app update 30 rows in a Postgres table.

Caused by: org.postgresql.util.PSQLException: ERROR: deadlock detected
  Detail: Process 6497 waits for ShareLock on transaction 1369735; blocked by process 6673.
Process 6673 waits for ShareLock on transaction 1370426; blocked by process 6497.
  Hint: See server log for query details.
  Where: while rechecking updated tuple (3,42) in relation "relationship_name"

Can you please give some advice and ideas to resolve this? Thank you very much.
0
How to perform a bulk read/write query from/to postgres where data is contained within multiple separate worker processes and jobs?

My architecture is quite simple and has hit a limitation.

I currently have 2 worker processes (each with a connection pool of 25) which are responsible for managing enqueued jobs. I enqueue by pushing data to redis, and they pull work from there. For the sake of argument, let's say all each job does is:

def perform
  read from db (select from table_1 where id = 123)
  write to db (insert into table_2 etc etc)
end

Open in new window


I currently enqueue 500 of these jobs every minute. I cannot group these jobs together as they are responsible for very specific operations based on data found in rows in my db, and have different retry limits, etc,

So, I enqueue 500 jobs and my two workers pull these jobs and go to work.. each job performing a single read query, and a few seconds performing a write query.

My question is, how might I go about merging the data generated from my jobs I want to insert into my `table_2` in order to build up a bulk query?  I cannot see anyway of doing this without pushing the data into an intermediary store which I then use to build a query and perform.
0
Need help in post gres sql Function...kindly note i need a post gres function created

I fetch data from a table A

select emp no, timestamp, deptno, account from table A, table B
where account_id = input_acct_id,
             sor_id = input_sor_id,
            table A.account_id = tableB.account_id
             time_stamp  > input_time_stamp

I need to send only 40 rows to output. I need to another output variable(More_flag = "Y") if the number of records is more than 40 else i need to send More_flag = "N". This is for pagenation.

I also need to send the timestamp of the 40th record as an output variable as the application send it back to the stored procedure to fetch records after that timestamp.

I had created this stored procedures by creating temp tables for storing the 40th record but it is taking too much time to load and many temp tables are getting created when this stored procedure is invoked there by increasing the cpu utilization.

I need some logic to retreive the records and send the more flag and the 40th record timestamp.


My final output record must contain

(
input_acct_id,
input_sor_id,
input_time_stamp,
sqlcode,
more_flag,
output_time_stamp_40
)

Hope i am able to convey my requirement.
0
Dear all
I have a server where i'm running mysql and postgresql databases based on CentOS 6.8 x86_64. the problem is that the /var directory (where those db are running) is mounted on a 50Gb partition which already full (95%). So, i'm looking for a way to move the content of this partition to a new bigger one where i can remount /var directory. There are 350Gb free remaining on my HDD and it's a RAID1 disk.
Disk-utility-1.jpg
disk-space.jpg
0
Hi there,

I have this KB article from Atlassian, and they show a specific PostgreSQL query, but I need to run it against Microsoft SQL server. Anybody able to assist?

The article is: https://confluence.atlassian.com/confkb/some-attachments-or-links-are-no-longer-accessible-after-server-migration-214862724.html

The query itself that is shown is:

insert into contentmigration
select bodycontentid, substring(body from '\\|http://oldServerName/download/attachments/.*/') from bodycontent;


Thanks for any suggestions!
0
We have a .NET application that we have developed which mostly runs calculation queries and inserts data to a few tables based on the calculation queries. We want to add a progress bar. How could this be accomplished? The longest part is the functions it runs for the calculation where the data is coming from large tables. Inserting the data is very quick after the calculation. The database is a PostgreSQL
0
Cloud Class® Course: Amazon Web Services - Basic
LVL 12
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Hi,
I have installed postgresql  version 9.6.
But when i give "sudo service postgresql start" command am getting the below error
* Restarting PostgreSQL 9.6 database server                                                                                                                          
* Failed to issue method call: Unit postgresql@9.6-main.service failed to load: No such file or directory. See system logs and 'systemctl status postgresql@9.6-main.service' for details.

Please give me the solution
0
I am trying to convert a SQL Server stored procedure to Post gres procedure but stuck up in the XML portion.  Need help in converting this XML piece of code to post gres code.

EXEC sp_xml_preparedocument @idoc OUTPUT, @ROLES;
                  UPDATE SSO_XREF_USER_ROLE SET ACTV_IND=1 WHERE
                  (CONVERT(VARCHAR,SSO_USER_ID)+CONVERT(VARCHAR,SSO_ROLE_ID)) IN
                  (SELECT CONVERT(VARCHAR,X.SSO_USER_ID)+CONVERT(VARCHAR,X.SSO_ROLE_ID) FROM
                        (SELECT XUR.SSO_USER_ID,SR.SSO_ROLE_ID FROM SSO_XREF_USER_ROLE XUR
                              JOIN SSO_ROLE SR ON XUR.SSO_ROLE_ID=SR.SSO_ROLE_ID WHERE
                              SR.SSO_ROLE_NM IN
                              (SELECT RoleName FROM
                                    OPENXML(@idoc, '/DocumentElement/UpdateRoles', 2)
                                          WITH (RoleName  varchar(100),
                                                      RoleStatus  numeric(1,0))
                                                      WHERE RoleStatus=1))AS X)

Not sure what is the equivalent of sp_xml_preparedocument in Postgres procedure
0
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
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

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
>