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 that needs to write into a postgresql table using a stored procedure

I have tried the stored procedure manually to conform it allows the insert operation and its ok

I attached a step by step explanation of what i have done in the attached word file

The following is contained in the attachment

1.      Values in the Model Based on Debugger

2.           Stored Procedure scripts for the inserts

3.     .InvalidCastException: 'Can't write CLR type System.Int32 with handler type TextHandler'
        Error Screen during the execution of the script

4.      SCHEMA of the Table  into which I have already inserted a record to test the storedprocedure

What could i possibly doing wrong and how can i resolve the issue

Thanks

Oluwole
Error-Inserting-into-a-postgre-tabl.docx
0
Introduction to R
LVL 12
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Does anyone know of a good tool to handle postgresql differential BU without rep, currently I am using native Wal procedure, but I am investigating, there are some good Linux tools, but could not find any windows apps, that don't blow my developer one person budget
0
Hi,
I am using postgresql database.
I want to run a query like insert into some table...
But i want to make it run say after 1 second.
Is there any way to do that...like append something to the insert query and make it a bigger query and
when you run that it runs the exact insert query but after 1 second.

Thanks
0
I am running Wal backup, I am looking for the correct string for pg configuration files, that will copy my last successful Wal backup files to another local directory,  

bu wal is running fine  minutely, I now need to have the finalized files pushed for pg to another dir, if I monitor the wal dir, and move some files, that are still active somehow, pg will crash
0
Hi,
I have created the following table-
CREATE TABLE rbl_kyc_profile (
      userid int8 NOT NULL,
      kyctype varchar(50) NOT NULL,
      effectivekycstatus varchar(50) NOT NULL,
      validfrom timestamp NULL,
      validtill timestamp NULL,
      authtype varchar(50) NULL,
      ovdnumber varchar(50) NULL,
      attrs jsonb NULL,
      name varchar(100) NULL
);
Many places i have seen an extra column id in the table.
Do i need to do that in this table ??
what are the pros and cons of it ?

Thanks
0
Hi,

   In DB2, we can declare a temporary table in a Stored Procedure SP1 and use it in another stored procedure SP2, while calling SP1.

Is there a way to do it in Postgresql 9.7?
Please help with an example, if there's a way to it?
Thanks a lot!
0
Hi,
I have to create the following table in postgreSQL :
create table rbl_kyc_profile (
  id int8 not null,
  userid varchar(50) not null,
  kyctype enum ('PAPER','E_KYC','MINIMAL','SHORTFALL')
)

Open in new window


I got an error : SQL Error [42704]: ERROR: type "enum" does not exist
 
I saw on postgreSQL documentation page : https://www.postgresql.org/docs/9.2/datatype-enum.html
That one first needs to create an enum type in postgreSQL.
This is very different from MYSQL where you can just create a column of enum type by specifying the values...
I have the following questions..

1) Since the kycType in my java classes is an enum. And it has a fixed set of values should i create an ENUM in postgre for it or just create a varchar for it ?
2) Because postgreSQL uses enum types in a different way than MySQL does what impact it has if i create the above kycType as an enum type and then use it in the table ?

Thanks
0
Hi,

   We are on Postgresql 9.7
I have two tables.
Table-1 has about 100 codes. As an example,
ID        Code
1           'M'
2           'C'
3           'R'

My table-2 has a code  field, which needs to only allow a subset of the codes from Table-1.
Example: Table-2 can only have values 'M' and 'R'.
Is it possible to create a constraint or something?
  Please advice.
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
JavaScript Best Practices
LVL 12
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

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
Looking for a good sample project which has Akka HTTP, Akka HTTP Swagger, Slick, PostgreSQL
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
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
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.

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

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
>