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 there any way to create master slave for postgresql when slave is on docker installation ?
0
Starting with Angular 5
LVL 13
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

In Postgres how to track DML changes on a table like insert/update /delete for each row through RULE efficiently . we have tried like below but it seems on the same table if we have multiple rules somehow it is not working.  We have to use RULE only, triggers are not supported in our database. we use Greenplum.
please  suggest with the updated scripts with any other better solutions.

--Update rule
need to log updated records along with other columns which did not change

DROP RULE IF EXISTS vc_rule_upd ON CORE.VC_TEST_RULE CASCADE;

CREATE OR REPLACE RULE vc_rule_upd AS ON UPDATE TO public.VC_TEST_RULE
    WHERE NEW.AUDIT_TO_TS <> OLD.AUDIT_TO_TS
    DO INSERT INTO public.vc_rule_log_table VALUES (
                                    OLD.customer_id,
                                                      OLD.customer_name,
                                                      OLD.sal,
                                                      NEW.audit_to_ts,
                                                      OLD.audit_user_id,
                                                      OLD.job_id,
                                    current_user,
                                    current_timestamp
                                );
      
                                                
--Insert/update rule

if inserting row is new row we need we need log the new row,
if that row already existed then update ( not sure how to add insert/update on the same rule)                                                  

DROP RULE IF EXISTS vc_rule_ins ON public.VC_TEST_RULE CASCADE;

CREATE OR REPLACE RULE vc_rule_ins AS ON INSERT TO public.VC_TEST_RULE
      WHERE NEW.customer_id <> OLD.customer_id
       DO INSERT INTO …
0
Hi,

  We are on Postgresql 9.7
There is a table "Table-1" with the following fields.
ID                Name                DOB               SSN                   Start_dt            End_dt
1                   Kevin               2/3/2001      xxx-xx-2354        1/1/2018         12/31/9999

Open in new window

Whenever there is an update, let's say, to the DOB, the current row gets end dated and a new row gets inserted, like below:
ID                Name                DOB               SSN                   Start_dt            End_dt
1                   Kevin               2/3/2001      xxx-xx-2354        1/1/2018         3/17/2019
2                   Kevin               2/3/2000      xxx-xx-2354        3/17/2019         12/31/9999

Open in new window


My question is related to a trigger to capture the old and new values of the DOB , which is a mix of UPDATE and INSERT.
First off, how can we sense if it is a change to the existing entry or a new insert, in this CDC Type-2 scenario?

Please help
Thank you!
0
I need someone who understands shell scripting in a PostgresSQL environment to look at this.
I am trying to get the Pgbackrest  (ver. 2.10) application to provide notifications by Email when it encounters errors.
The error reports (either from a logfile or the command pgbackrest info) should be included in the notification.
The email are to be sent from a RHEL server.
CRON tasks currently used sends empty notification emails even when there are no errors.
0
Steps to install PostgreSQL 6.9 on a Oracle Virtual Box on WIndows 10 computer.
0
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
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,

   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,

   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
Learn Ruby Fundamentals
LVL 13
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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
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
Exploring SQL Server 2016: Fundamentals
LVL 13
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

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

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
>