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 postgresql stored procedure  doing an insert

I want to introduce  an  If/Else  clause into the script so that

I can use the else part to do an update

Where would the if /else clause be in the procedure  below:

CREATE OR REPLACE FUNCTION public.spjobhistory_insert(
    IN staffnox text,
    IN companynamex text,
    IN startdatex date,
    IN enddatex date,
    IN yearsexpx integer,
    IN designationidx text,
    IN companycodex text,
    IN empidx integer,
    OUT empjobidx integer)
  RETURNS integer AS
$BODY$
INSERT INTO jobhistory(staff_no,companyname,startdate,enddate,yearsexp,designationId,companycode,empId)
VALUES                     (staffnox,companynamex,startdatex,enddatex,yearsexpx,designationidx,companycodex,empidx) RETURNING empjobid
$BODY$
  LANGUAGE sql VOLATILE
  COST 100;
ALTER FUNCTION public.spjobhistory_insert(text, text, date, date, integer, text, text, integer)
  OWNER TO postgres;

I have tried different places for the if and else  and i get an error each time.

Can i confirm first that it is possible and if so where  do i place it  after which i will  introduce

the update script

Thanks

Olukay
0
Bootstrap 4: Exploring New Features
LVL 13
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

We wanted to return table (with data)  through a function in Postgres , we have been  selecting data from table through return query and  in function return signature as set of Type variable combination but it is time consuming when we return large volume of data . Could you please advise how to return table directly.
0
PostgreSQL syntax problem.

Query below will run but result is: ERROR:  out of memory for query result

SELECT
AD.ADDRESS_DETAIL_PID as ADDRESS_DETAIL_PID,
AD.STREET_LOCALITY_PID as STREET_LOCALITY_PID,
AD.LOCALITY_PID as LOCALITY_PID,
AD.BUILDING_NAME as BUILDING_NAME,

AD.LOT_NUMBER_PREFIX as LOT_NUMBER_PREFIX,
AD.LOT_NUMBER as LOT_NUMBER,
AD.LOT_NUMBER_SUFFIX as LOT_NUMBER_SUFFIX,

FTA.NAME as FLAT_TYPE,
AD.FLAT_NUMBER_PREFIX as FLAT_NUMBER_PREFIX,
AD.FLAT_NUMBER as FLAT_NUMBER,
AD.FLAT_NUMBER_SUFFIX as FLAT_NUMBER_SUFFIX,

LTA.NAME as LEVEL_TYPE,
AD.LEVEL_NUMBER_PREFIX as LEVEL_NUMBER_PREFIX,
AD.LEVEL_NUMBER as LEVEL_NUMBER,
AD.LEVEL_NUMBER_SUFFIX as LEVEL_NUMBER_SUFFIX,

AD.NUMBER_FIRST_PREFIX as NUMBER_FIRST_PREFIX,
AD.NUMBER_FIRST as NUMBER_FIRST,
AD.NUMBER_FIRST_SUFFIX as NUMBER_FIRST_SUFFIX,
AD.NUMBER_LAST_PREFIX as NUMBER_LAST_PREFIX,
AD.NUMBER_LAST as NUMBER_LAST,
AD.NUMBER_LAST_SUFFIX as NUMBER_LAST_SUFFIX,

SL.STREET_NAME as STREET_NAME,
SL.STREET_CLASS_CODE as STREET_CLASS_CODE,
SCA.NAME as STREET_CLASS_TYPE,
SL.STREET_TYPE_CODE as STREET_TYPE_CODE,
SL.STREET_SUFFIX_CODE as STREET_SUFFIX_CODE,
SSA.NAME as STREET_SUFFIX_TYPE,

L.LOCALITY_NAME as LOCALITY_NAME,

ST.STATE_ABBREVIATION as STATE_ABBREVIATION,

AD.POSTCODE as POSTCODE,

ADG.LATITUDE as LATITUDE,
ADG.LONGITUDE as LONGITUDE,
GTA.NAME as GEOCODE_TYPE,

AD.CONFIDENCE as CONFIDENCE,

AD.ALIAS_PRINCIPAL as ALIAS_PRINCIPAL,
AD.PRIMARY_SECONDARY as …
0
PostgreSQL syntax problem.

Query below will run but result is: ERROR:  out of memory for query result

SELECT
AD.ADDRESS_DETAIL_PID as ADDRESS_DETAIL_PID,
AD.STREET_LOCALITY_PID as STREET_LOCALITY_PID,
AD.LOCALITY_PID as LOCALITY_PID,
AD.BUILDING_NAME as BUILDING_NAME

When  the query is amended to:

set FETCH_COUNT=1000
SELECT
AD.ADDRESS_DETAIL_PID as ADDRESS_DETAIL_PID,
AD.STREET_LOCALITY_PID as STREET_LOCALITY_PID,
AD.LOCALITY_PID as LOCALITY_PID,
AD.BUILDING_NAME as BUILDING_NAME

 the result is:
ERROR:  syntax error at or near "SELECT"
LINE 3: SELECT
        ^
SQL state: 42601
Character: 23

I am seeking help with the syntax.
0
Query problem in postgreSQL

There are 33 tables and there is a query that is meant to set the delimiter as '|'

COPY address_alias_type_aut
C:\GNAF\Authority Code\Authority_Code_ADDRESS_ALIAS_TYPE_AUT_psv.csv' DELIMITER '|' CSV HEADER;
 
But it will not run:
ERROR:  syntax error at or near "C"
LINE 3: C:\GNAF\Authority Code\Authority_Code_ADDRESS_ALIAS_TYPE_AUT...
        ^
SQL state: 42601
Character: 30

I've doublechecked the folders and file names in C: drive and they appear to be correct (files uploaded here)
The full tables setup query and the full delimiter query are attached.

Help will be much appreciated.
create-tables.txt set-delimiter.txt Authority-Code.rar
0
Hi Guys,

A quick one for you - In PG sql I want to select the current date minus a number of days as a variable at TIME ZONE 'US/Pacific'.

For instance

 select (now() -  cast( :p as integer) AT TIME ZONE 'US/Pacific');

( :p is a number i.e 2 to be passed when running a query ).

I want the above select to return the current date/time AT TIME ZONE 'US/Pacific' minus a number of days .

How can I achive it please ?
0
Hi,  Please find attached reference code in detail, it is regarding Postgres store procedure/function and temp tables on Greenplum database.
I am looking for a  meaningful solution or work around solution, please don't provide time wasting ambiguous answers.
expert_exchange_question.txt
0
Hi,
I have postgresql databases.
I want to perform the following query while connected to a particular database
select * from kyc_profile kp, rbl_kyc_profile rkp where kp.userid={{userid}} and rkp.userid={{userid}}

where kyc_profile table is in Database - zetauser on the host H
and rbl_kyc_profile table is in Database - app_portal on the same host H
the username and password to connect to database are different.
I have connection to both the Database active from Dbeaver.
Its postgresql database.

How can i acheive this ?
Thanks
0
Hi Experts,

I get the following error for python application.

django.db.utils.OperationalError: FATAL:  remaining connection slots are reserved for non-replication superuser connections

Open in new window


I use dj_database_url.py for configuring database.   I am using Postgres as a database

I had set connection_max_age to 0 in the config. dj_database_url.py

Still, I get this error.

I had checked the entire source code by using grep there is no connection in the source code.

I getting this error from inside docker container.  the database is outside the docker container.

the connection string I use to connect is DATABASE=postgres://test:*****@172.17.0.1/test where 172.17.0.1 is docker gateway.

max_connection =100 in postgresql.conf which located inside /etc/postgresql/9.5/main/

Any help is greatly appreciated.
0
Looking for a 64 bit driver for PostgreSQL 64bit, I have been using the 32bit driver for more than a year now w/o issue.
Support updated my version of office, and now all my VBA code needs to be 64bit compatible.

I can find a few links to a 64bit driver by 'Devart', the description states that its only free for a few months, then a fee.
Did find some links to other versions, but Cisco security flagged the site as being reported as dangerous and/or infected. so I didn't try to get by the security.

Any help is appreciated.
Even if there was some magic command to allow office 64 to use 32 bit drivers ?

thanks
0
CompTIA Security+
LVL 13
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

C#  Stored Procedure Execution Script  - Writing into a postgresql database

Please  see the script below:

public List<MonthlyVariationsModel> GetStaffVariations ()
{
    List<MonthlyVariationsModel> results = new List<MonthlyVariationsModel> ();

    // Trying to see how to make the connection string DRY  Oluwole 29-11-2018
    //using (var conn = new NpgsqlConnection(GlobalConfig.CnnString(db))

    using (var conn = new NpgsqlConnection (pgrstring))
    {
        using (var command = new NpgsqlCommand ("public.spgetstaffvariation_thisperiod", conn))
        {
            conn.Open ();

            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new NpgsqlParameter ("companycodex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = LoginDetails.staticcompany });
            command.Parameters.Add(new NpgsqlParameter ("mnthyearx", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = "Mnthyear" });
            command.Parameters.Add(new NpgsqlParameter ("staffnox", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.InputOutput, Value = "Staff_no" });
            command.Parameters.Add(new NpgsqlParameter ("entcodex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Output, Value = "Ent_code" });
            command.Parameters.Add(new NpgsqlParameter ("gradeamtx", NpgsqlTypes.NpgsqlDbType.Numeric) { Direction = 

Open in new window

0
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
1.png2.           Stored Procedure scripts for the inserts
2.png3.     .InvalidCastException: 'Can't write CLR type System.Int32 with handler type TextHandler'
        Error Screen during the execution of the script
3.png4.      SCHEMA of the Table  into which I have already inserted a record to test the storedprocedure
4.pngWhat could i possibly doing wrong and how can i resolve the issue

Thanks

Oluwole
Error-Inserting-into-a-postgre-tabl.docx
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 have C# Application writing into a postgresql database and basically  what i want to do during login is to

1. Login with a valid UserName and Password  (2 parameters)

2. Get the Database  to return 3 values (parameters)  after  successful  validation of username and password
    ie  ID (serial No)  of Person  ,  the  COMPANY   and  ROLE

3. I want the returned values placed on the login screen  ( for my verification )

I have achieved  1 and 2 above

and for the sample data that i tested  the values  returned from the database  are
ID = 6    COMPANY = company03  and  ROLE = GEN  (see debugger screen in word file)

Getting the 3 values above from the Data Access Layer to the User Interface layer (Login form) is where i have a challenge

I attach  a detailed word file showing  the following

1. DataAccess  Layer  Script showing the 2 input and 3 output parameters. It is ok and working

2. Refactored Class in the Data Access layer

3. Script Under Login Button

4. Login Screen Showing 3 Values to be Returned from Database

5. Debugger  Values Showing the 3 variables selected from the database and values returned

6. Debugger Values on Loginscreen showing var03 = null  even though it is selected  as "GEN" from Database

The Major Issues I want addressed  are:

1. The refactored class  only has variable var03 specified  I want the correct syntax  that will accomodate var01, var02 and var03

2.  From the debugged login screen my var03 is …
0
Hi,

We are considering to use PostgreSQL as a open source DB as well as MySQL but from your point of view, what great is PostgreSQL?

we are currently using MS SQL on Azure and some MySQL/MariaDB, why PostgreSQL is good ? why still need it ?

usually how other companies will implement PostgreSQL together with other DB?
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
I have a c# application writing into a postgresql database through a stored procedure.

I have 2 input parameters  and 2 output parameters

I need to be able  to get the 4 (2 input and 2 output)  into some screen variables

The  scrip that runs the stored procedure is shown below;

        public void SelectUserProfile(UserProfileModel model)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection(pgrstring))
            {

                using (var command = new NpgsqlCommand("public.spuserprofile_select", conn))
                {
                    conn.Open();
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new NpgsqlParameter("usercodex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = model.usercode });
                    command.Parameters.Add(new NpgsqlParameter("passwordx", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = model.userpwd });
                    command.Parameters.Add(new NpgsqlParameter("groupcodex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Output, Value = model.groupcode });
                    command.Parameters.Add(new NpgsqlParameter("companycodex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Output, Value = model.companycode });

                    string var01 = model.companycode;
                    string var02 = …
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
I have a C# application  and i need to save data  from a
windows form screen to a postgresql database

The connection string and the stored procedure  (spabsecode_Insert)  for inserting into the database  
have been tested  and are working fine.

Below  is my Save Method

public void CreateAbseCode(AbseCodeModel model)
        {
            NpgsqlConnection conn = new NpgsqlConnection("Host = 127.0.0.1; Database = Tournaments; Port = 5432; Username = postgres; Password = manager");
            conn.Open();
            {
                NpgsqlCommand cmd = new NpgsqlCommand("spabsecode_Insert", conn);

                var p = new DynamicParameters();

                cmd.Parameters.Add(new NpgsqlParameter("absecode", model.Absecode));
                cmd.Parameters.Add(new NpgsqlParameter("absedesc", model.Absedesc));

                cmd.ExecuteNonQuery();

                model.Id = p.Get<int>("Id");

            }
        }

When i click  the save button and in the debugger  i get the line  cmd.ExecuteQuery() and then run into a syntax error .



I attached 2 screen shots

1. A screen of the record I am trying to save

2. A screen of the debugger screen showing that execution stopped  on cmd.ExecuteQuery  line

I will be grateful to have an expert look at the syntax of this line and the next line which is trying

to retrieve the model  ID  (An auto increament number)

Thanks

Olukay
Save-Screen021218.PNG
SaveDebugged021218.PNG
0
Python 3 Fundamentals
LVL 13
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

I have a stored procedure ( spgetabsecode_all )  in postgresql  that returns values when executed  
from the query  analyser

When I execute same from a  C#  application  it returns no values

What is wrong with my output statement  and why is the output reporting   zero records

Please note that  I have confirmed separately  that my connection string
to postgresql  using Npgsql  provider  is correct.


see the 2 screens attached

1.  The select executed locally from query analyser  with the result

2. error screen from the debugger within visual studio
storedprocedure261118.PNG
debugscreen261118.PNG
0
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
What is the location of  the binary and library postgresql directories installed on docker linux suse 12 sp2 ?
We have this postgres DB installation working ok.
I have access to the database using pgadmin and dbeaver.
But we don't know who did this installation and I need to know the location of the binary and library postgresql directories in order to run pg_ctl and psql.
0
Hi All,
   
 Postgresql 9.6 and we have two tables.
First table layout is like below.
   TB_ID       Col_id     Col_nm                       Col_Desc
      1               1           fin_cd1                         finance Code 1
      1               2           fin_cd2                         finance Code 2
      1               3           fin_cd3                         finance Code 3

Open in new window

  TB_ID                 Code_Value
      1                       FA|BR|CM
  

Open in new window


I need to display the result as follows:
    TB_ID                fin_cd1            fin_cd2                  fin_cd3
       1                        FA                     BR                         CM

Open in new window

           

First table is joined to the second, on the TB_ID, and the number of rows in  the first table is equal to the number of pipe delimited values in the second table.

Please help
0
Hi,

  We are on Postgresql 9.6.1.
I have a table which has an ID attribute, start_Dt and end_Dt.
Need to find the first_start_dt where there is a chain of the prior end_dt beging the same as the current start_Dt.

I have the example data and the desired output below.
Can someone kindly help?

WITH AA
AS
(SELECT ID,START_DT,END_DT
  FROM 
  (
  VALUES
     (10,'2018-01-01','2018-01-31')
    ,(10,'2018-02-04','2018-03-28')
    ,(10,'2018-03-28','2018-05-20'
    ,(10,'2018-05-20',NULL)
    ,(20,'2018-07-01','2018-08-10')
    ,(20,'2018-08-01','2018-09-10')
  ) tmp(ID,START_DT,END_DT)
)

SELECT ID,start_dt,end_Dt
 FROM AA

Open in new window


Desired result:
 
 id        start_dt               end_dt
 10     2018-01-01        2018-01-31
 10     2018-02-04           null
 20     2018-07-01         2018-09-10
0
I need modify this query to honor the minmk and maxmk, if mt = 'P' and either minmk and maxmk exist.
Not sure how to do that...

Currently, the query returns 125.00 for the first record and 2893.75. It needs to return 140.00 and 2815.00

Note: There are only 'P' (percentage) and 'F' (flat) markup types.

SELECT
    -- when the markup is a 'P' (percentage) multiply     
	CASE WHEN (SELECT c0.mt 
               FROM p.markups c0 
               WHERE c0.id = 'M07' AND 
                     c0.pid = 'M34' AND 
                     c0.pt = '107') = 'P' THEN
		TO_CHAR(u.price + (u.price * (SELECT c1.mk 
		                                FROM p.markups c1 
		                                WHERE c1.id = 'M07' AND 
		                                      c1.pid = 'M34' AND 
		                                      c1.pt = '107')) , 'FM999999999.00')
	ELSE
	-- when the markup is a 'F" (flat), so add it
		TO_CHAR(u.price + (SELECT c2.mk 
		                    FROM p.markups c2 
		                    WHERE c2.id = 'M07' AND 
		                          c2.pid = 'M34' AND 
		                          c2.pt = '107') , 'FM999999999.00')
		                          
	END as mkprice	
FROM u.unet u
WHERE u.id = 'M34' AND 
      u.pt = '107'

Open in new window

If I have this data in `p.markups`:
"id" , "pid", "pt" , "mt", "mk", "minmk", "maxmk", "exc"
"M07", "M34", "107", "P" , 0.25, 40     , 500    , 0

Open in new window

and this data in u.unet:
"id",  "pt" , "price"
"M34", "107",  100.00
"M34", "107", 2315.00

Open in new window

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
>