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

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

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.