[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

I have a c# application and need to run a stored procedure in local  postgresql database

Postgre 9.5.1 is installed on my PC  and

I have tried the stored procedure .  It works ( Its a simple select statement)

Below  are the following:

1. My connection  string
2.  Segment that  generated the error
3. Details of the error
4. Screen Print of where error occured using the Visual Studio Debugger

1. Connection String

<add name="Tournaments" connectionString="Server=(localhost:5432);Database=Tournaments;Integrated Security=true;Provider=PostgreSQL;" providerName="System.Data.OleDb.OleDbConnection"/>

2. Error on Output =  Line

public List<AbseCodeModel> getabsecode_all()
        {
            List<AbseCodeModel> output;
            using (IDbConnection connection = new System.Data.OleDb.OleDbConnection(GlobalConfig.CnnString(db)))
            {
                output = connection.Query<AbseCodeModel>("spgetabsecode_all").ToList();
            }
            return output;
        }


3. Detailed Error

System.Data.OleDb.OleDbException
  HResult=0x80040E21
  Message=Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
  Source=System.Data
  StackTrace:
   at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection …
0
Rowby Goren Makes an Impact on Screen and Online
LVL 12
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

I use Dapper in my C# application

My connection strings and the error displayed during run time are in the attached notepad document.

I am connecting to local postgresql installed on my PC

Why is providerName="Npgsql" not seen as the providername.

or is there a conflict some where ?

How can I resolve this error

Thanks

Olukay
C-connection121118.txt
0
Create a continuous test for PostgreSQL from the server to the client computer. We have an ERP using PostgreSQL database and we have random disconnects from the database/server. I know it is either network or server related but need to figure out how I could create a continuous test of some sort.

Any idea how I could troubleshoot this?
0
Hi,

 We are on Postgresql 9.6
I have a table with fields like this:

   ID                 Status                    Start_Dt                     End_Dt
    1                    AP                        01/01/2018                01/31/2018
    2                    AP                        02/01/2018                02/28/2018
    3                    AP                        03/01/2018                03/31/2018
    4                    AP                        04/01/2018                 04/30/2018
    5                    DE                        05/01/2018                05/31/2018
    6                    AP                         06/01/2018               06/30/2018
     7                   AP                         07/01/2018                07/31/2018
    8                    CS                          08/01/2018               08/31/2018

I need the result like the following:

     Status                        Start_Dt                     End_Dt
        AP                         01/01/2018            04/30/2018
        DE                         05/01/2018            05/31/2018
        AP                          06/01/2018            07/31/2018
        CS                          08/01/2018            08/31/2018

Basically, need to stitch up the dates based on the change in  the Status.

Can someone please help with the solution?
0
PostgreSQL world :

With pgbadger (postgres tool), some results speak about SESSIONS, others about CONNECTIONS.

With PostgreSQL, what is the difference between a session and a connection ?

Your site contains the answer for Oracle but not for Postgres...

Thanks a lot.

Gery
0
Hi,

Can you please help me to write SQL script in PostgreSQL for source data profiling. I can't use any data profiling tools, so I need to write SQL scripts. These are some of the things I want the result:

TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
ColumnDataLength
DataType
MinDataLength
MaxDataLength
AvgDataLength
MinDate
MaxDate
NoDistinct
NoNulls
NoZeroLength
PercentageNulls
PercentageZeroLength
NoDateWithHourminuteSecond
NoDateWithSecond
NoIsNumeric
NoIsDate
NoAtLimit
IsFK
DataTypeComments

I have attached screenshots which is similar to what I am looking for. It is not important to have all results in one script. It can be different scripts but I want column_names in result set. Thank you.17375.jpg17377.jpg
0
I have ubuntu 16.04 server with postgres version 9.3.17 . how to update 9.3.17 to 9.3.24?
i know it's old but for master/slave version i need to have a same version as master.
0
I am trying to retrieve the average value from a PostgreSQL database using a Sequelize ORM with React on the front end and running Node.js server.  My 'GET' request on the Node side retrieves all of the data from the PostgreSQL database so I know everything is wired up with React using the following code:

module.exports = function (db, app, bodyParser) {


    app.use(bodyParser.json());
    app.use(bodyParser.urlencoded({ extended: false }));

    app.get('/survey_init', function(req, res, next) {
        db.answers.findAll({})
        .then(function (data) {

            res.send(data);
              })
          .catch(function (err) {
            return next(err);
          })
        });
        
    }

Open in new window


However, when I attempt to run an average on a specific column called 'conf_one' in my PostgreSQL database I get an error.  I will list the modified code (modified from code above) that is throwing the error here:

module.exports = function (db, app, bodyParser) {


    app.use(bodyParser.json());
    app.use(bodyParser.urlencoded({ extended: false }));

    app.get('/survey_init', function(req, res, next) {
        db.answers.findAll({
          attributes: ['conf_one', [models.Sequelize.fn('AVG', models.Sequelize.col('conf_one')), 'conf_one_avg']]
        })
        .then(function (data) {

            res.send(data);
              })
          .catch(function (err) {
            return next(err);
          })
        });
        
    }

Open in new window



The error that I get with the code is the following error and I do not get the result of an average calculation.


Open in new window

0
This is a read only sql query being written in a SQL editor
The ultimate goal here is to print all 5 columns if the table does exist OR to print the year and 4 NULL columns if the table does not exist.

The tables I need to check if exists are sba_2017_math, sba_2018_math and sba_2019_math
I wrote this basic select statement to return t or f if the table exists or not.
Can I put that t/f into a variable?  I need to do that for all 3 years separately.
SELECT EXISTS (
				SELECT 1
				FROM   information_schema.tables 
				WHERE  table_schema = 'state_data_ct'
				AND    table_name = 'sba_2019_math'
			  )

Open in new window


Then can I use that variable to somehow run the following query?

select 
'2017' as year,
case when @exists2017 = 't' then column1 else null end as column1,
case when @exists2017 = 't' then column2 else null end as column2,
case when @exists2017 = 't' then column3 else null end as column3,
case when @exists2017 = 't' then column4 else null end as column4
from state_data_ct.sba_2017_math
UNION ALL
select 
'2018' as year,
case when @exists2018= 't' then column1 else null end as column1,
case when @exists2018 = 't' then column2 else null end as column2,
case when @exists2018 = 't' then column3 else null end as column3,
case when @exists2018 = 't' then column4 else null end as column4
from state_data_ct.sba_2018_math
UNION ALL
select 
'2019' as year,
case when @exists2019= 't' then column1 else null end as column1,
case when @exists2019 = 't' then 

Open in new window

0
I am trying to generate layer for Postgres database using ADO.net, Entity Framework and Postgres Database.
After I select Postgres provider and establishes the connection with the database, the wizard "To choose database object and setting" (Table, view, stored proc, function) doesn't appear and model doesn't get created.
Hence I am unable to select tables and generate layers.
 
Followings are the version used in my project
npgsql (Installed in Visual Studio from NuGet) - 4.0.2
EntityFramework6.npgsql (Installed in Visual Studio from NuGet) - 3.1.1
pgAdmin4
PostgreSQL 9.6
I have included provider name for npgsql in webconfig and appconfig, but still unable to generate layer.
0
OWASP: Forgery and Phishing
LVL 12
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

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
Become a CompTIA Certified Healthcare IT Tech
LVL 12
Become a CompTIA Certified 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.

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

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
>