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

   We are on Postgres 9.6.
  I have a table called tbl_client. This has many fields and one of the fields is called end_dt.
My requirement is to insert a row into another table called tbl_audit, for any changes in tbl_client.
So, i created a trigger to capture the same.

Now, the requirement is that i don't insert a row if the change is only on the end_Dt.

Can someone give me an idea how this can be added to the condition?
Keep up with what's happening at Experts Exchange!
LVL 12
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Hi Experts,

       I am working on an web application (wagtail cms) like django-cms, which is in aws ubuntu.  Its backend is postgres, with elastic search few modules reactjs and nodejs.
The application is deployed in docker containers.  uwsgi is the upstream server for nginx.

      I get lots of errors with npm build, i am not able to run the application.  sometimes ports are not accessible. postgres is using 5432, elasticsearch is 9200. react is rendering on 9009. I get lots of dependencies error.    At what situation I have to use dockers?  sometimes I get errors in nginx configuration. could please tell what is good practice for npm build, run deploy.  

    What is the best way to architect the development and test environment?

    Will I be able access the postgresql, elastic search and nginx in linux ubuntu on aws with cms source code and contents on windows local pc?   or having the copy of postgresql, elastic search, cms contents and nginx all on a windows pc as development environment environment.   Elastic search has huge data.  and how to deploy the application on dockers in linux environment.

    What is the best way to have a development environment?  what is best test environment

   could you please tell me on how elastic search data is stored on postgres and what is network host setting in elastic search how it accessed from outside application.

    Could you please throw light on how to create development and test environment.

with …
Hi Experts,

How to connect to postgres, elastic search server in linux, ubuntu on AWS to  with Dango-cms or Wagtail cms from dev machine on windows

Please tell me how elastic search details are stored in database and how to connect to do a elastic search which is in postgres database.

All indexes of elastic search is stored in postgres sql.  How will i connect to postgres from windows dev machine.

And How can I access the postgres database in ubuntu aws to development machine have django-cms or wagtail cms on windows.


database connection string is created with dj_database_url  the connection string is DATABASE=postgres://user:password@server/database.

Please help me,

I get the following error when I build the wagatail cms - based on python
python makemigrations

throws the below error.

django.db.utils.OperationalError: could not connect to server: Connection timed out (0x0000274C/10060)
        Is the server running on host "" and accepting
        TCP/IP connections on port 5432?

I connect string sample postgres sample is postgres://test1:test2@

When I go inside the aws machine through putty (ssh) 54.*.*.*:22


telnet 5432

It is connection is success full.

but not able to connect from outside aws machine from a windows pc (development machine).

With Many Thanks,
Bharath AK
I want to perform a text search on my Postgress table and I want the search to be case -Insensitive.

I have seen some examples of case-insensitive searches using the UPPER or LOWER command, but this would consume a lot of resources.
Doesn't Postgress have option for the SELECT cmd to ignore text case ?

Thank you.
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)

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.
Postgress SQL via VBA.

What should my connection sting look like ?

I have:
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection

    Dim strConn As String
    'strConn = "Driver={SQL Server};Server=; Database=; UID=; PWD="
    'strConn = "Driver={PostgreSQL Unicode(x64)};Server=" & srv & "; Database=" & sDB & "; UID=" & sID & "; PWD=" & sPW
    'strConn = "Driver={PostgreSQL35W};Server=" & srv & "; Database=" & sDB & "; UID=" & sID & "; PWD=" & sPW
    strConn = "Driver={PostgreSQL};Server=" & srv & "; Database=" & sDB & "; UID=" & sID & "; PWD=" & sPW

    cn.Open strConn

Open in new window

But all those options always produce the same error:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

System DNSUser DNS
Hi Experts, I am desperate :(
I am looking for a postgres statetement, to show 2 Queries within 1 table.
I meen, I want to split  1 period from 2017 - 2018into two periods 2017 in one column and 2018 in a second column.

My following statement does not work

  SUM("RechPos1"."NettoGP") AS field_1,
  SUM(public."RechPos"."NettoGP") AS field_2,
  public."RechDat" "RechDat1"
  INNER JOIN public."RechPos" "RechPos1" ON ("RechDat1"."RECHNR" = "RechPos1"."RECHNR")
  INNER JOIN public."RechDat" ON ("RechDat1"."RECHNR" = public."RechDat"."RECHNR")
  INNER JOIN public."RechPos" ON (public."RechDat"."RECHNR" = public."RechPos"."RECHNR")
  public."RechDat"."RECHDATUM" BETWEEN '2017-02-01' AND '2017-02-20' OR
  "RechDat1"."RECHDATUM" BETWEEN '2018-02-01' AND '2018-02-20'

Where is my mistake?
Thanks in advance,
Mongo vs. Postgres DB.  Can you anyone pointed pros and cons?  And from the personal experience advice?
How to use ora2pg in windows environment for oracle to postgreSQL schema migration?
I created a sql statement in Microsoft sql server as
Update [dbo].[custeqip]
Set [yearofmfg] = convert(varchar(4), cast(‘01/01/‘ +right ( cast([yearofmfg] as int )’2) as datatime )’120) where [ yearofmfg] <> 0
It worked fine in ms sql server
I was trying to use the same statement in psql and it’s giving me an error like
The second parameter yearofmfg for convert is invalid
Can some one help me with this
Free Tool: IP Lookup
LVL 12
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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


Hope i am able to convey my requirement.
I have the following script to backup a PostgreSQL database which works fine on the old server. I upgraded to a new Windows 2012 R2 server and now it does not work as before. It works but it prompts me for the password when I execute the batch file

Any idea why it would not work on the new server?

@echo off
setlocal enabledelayedexpansion
REM The next line sets the following DayTime variables: DT_Day, DT_DayOfWeek, DT_Hour, DT_Minute, DT_Month, DT_Quarter, DT_Second, DT_WeekInMonth, DT_Year
for /f "delims=" %%a in ('wmic.exe Path Win32_LocalTime GET * /value') do (for /f "delims=" %%b in ("%%a") do set DT_%%b)
for %%a in (DT_Month DT_Day DT_Hour DT_Minute DT_Second) do (if !%%a! LSS 10 set %%a=0!%%a!)
set Timestamp=%DT_Year%%DT_Month%%DT_Day%_%DT_Hour%%DT_Minute%%DT_Second%
echo Timestamp: %Timestamp%
set PGPassFile=%APPDATA%\postgresql\pgpass.conf
REM xTuple/PostgreSQL Information
SET PGUSER=postgres
SET PGPASS=xxxxxxxx
SET PGHOST=localhost
SET PGBIN="C:\Program Files\PostgreSQL\9.5\bin"
SET BACKUPDES="C:\file_path\Backups"
REM formats can be custom/plain/tar
SET GLOBALS=globals-%Timestamp%.sql
for %%a in (%PGPASS%) do (>"%PGPassFile%" echo %PG_HOST%:%PG_PORT%:%PRODDB%:%PGUSER%:%%~a)
@ECHO Backing up globals to %GLOBALS%...
%pgbin%\pg_dumpall -U %PGUSER% -p %PGPORT% -g > 

Open in new window


For security reasons, users must query a replica database that is updated by an intermediate replica.
We must ensure performance and that requests do not impact replication. How to proceed ?

Thank you

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.
Hi All,

   I have a requirement to encrypt a UDF, so the code cannot be seen by anyone, but can execute it.
Is there a possiblity? Please advise.
Hello Experts,
I am trying to install MYSQL server on a virtual server that already has POSTGRESQL

and the process fails at START SERVER as shown in the image...

Please your advice
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:

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!
Hello all,
    I am looking for a way to have a linux user created on a sFTP gateway automatically with the credentials that were used when a user was created in a database. We have a web application in which we onboard a user and create their account. We would then have to go and manually create a linux user on the sFTP gateway, as it uses standard linux users as the credentials to login to FTP.

Is there a script (perhaps bash or python?) that can either use some sort of trigger to know when a DB user is made, then create a user on the FTP server. If it can use the same credentials that would be ideal, but if not, it can create a random password and then we can have it email it to the user perhaps. If there is nothing made already, if someone could point me in the right direction as to the best way to go about this, or what language would be ideal, please do.

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
Free Tool: ZipGrep
LVL 12
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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

Given my table has the following example
how do I run an update command to set the year column

CREATE TABLE my_table(
 string TEXT,
 year character_ varying(2) 

INSERT INTO my_table (string) VALUES ('30NOV17foobar');

Open in new window

This is what I've got

UPDATE my_table SET year = (SELECT SUBSTRING(string FROM 6 FOR 2) AS year FROM my_table)

Open in new window

I get error "more than one row returned by a subquery used as expression"

baring typos on here the sub query returns the year

Ideally the year column should be type INTEGER but that involves CASTing from text to INTEGER

  I use PostgreSQL and I have a table, which has the following data (only two rows below, for an example):
   ID                        Start_Dt                              End_Dt
    1                          05/10/2017                      10/20/2017
    2                          07/14/2017                      NULL

Open in new window

I want another table based on this, with the data as following:

   ID                        Start_Dt                              End_Dt
    1                          05/10/2017                      05/31/2017
    1                          06/01/2017                      06/30/2017
    1                          07/01/2017                      07/31/2017
    1                          08/01/2017                      08/31/2017
    1                          09/01/2017                      09/30/2017
    1                          10/01/2017                      10/20/2017

    2                          07/14/2017                      07/31/2017
    2                          08/01/2017                      08/31/2017
    2                          09/01/2017                      09/30/2017
    2                          10/01/2017                      10/31/2017
    2                           11/01/2017                      11/26/2017

Open in new window

Can someone kindly help?
  I have the below Stored Procedure in Postgres.
I have an error log table. When someone passes the V_START_DT to be greater than V_END_DT, i want to insert a custom SQLState and SQLERRM into the error log table.
What i've coded, isn't working. Can someone kindly point me to the solution?

                                           	  ,V_NAME VARCHAR(50)
                                           	  ,V_START_DT DATE
                                           	  ,V_END_DT DATE


SQLSTATE := '-1';
SQLERRM := 'Start Date Cannot be greater than End date';


Open in new window

I'm trying to do something super simple...or seems it should be. I have a script that truncates and updates a creates unique combinations across three other tables for a dimension build. I was hoping to create a function/procedure that I could call from an application in a single line, rather than trying to store the sql in my application.

We are using postgresql and sqlworkbench/j. My code to create the table includes a view creation, distinct, union, and in insert into using select.

I was trying:

    RETURNS void AS $$

    $$ LANGUAGE plpgsql

I get the error that "Create Function is not supported".

I'm admittedly a sql rookie and everything I have tried has failed, giving me one error after another. Is there a simpler way to do this? Nothing changes in my create parameters passed in, nada. It's simply that the underlying transactional data is changing and we're only keeping valid combos, so we trunc and reload the table.

Thanks in advance!
As DBA's point of view
I am working as a MS SQL DBA, I want to know,
What is the Difference between MS SQL Server and 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