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

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 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
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,

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,

   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
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.

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
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
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
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.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
CompTIA Network+
LVL 12
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

I have been given a postgreSQL database export (txt file).  

It contains the create table structures, insert statements etc for the whole database.

Does anyone know of a tool that will import the file directly into an MSSQL database?    Importing it into a local postgress and then into SQL is painfully slow and I need to run this several times over the next few weeks.
0
I have a c# application and I am trying to write to a table in my postgresql database
through a stored procedure  (Function)

I am at the  point where i need to execute the stored procedure having obtained
the values i need to write from a  windows form UI

The problem now is that when i hit the line that should execute the procedure
i get the error:

System.Data.OleDb.OleDbException
  HResult=0x80040E21
  Message=No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21).
  Source=System.Data

With no error message available as displayed  how can i resolve this issue ?

Attached is a word document  showing the error screen shot, my stored procedure  and the stack trace

I will be grateful for any help to resolve this issue

O.A. Oluwole
ERROR-0x80040E21---29-09-2018.docx
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
I have a c#  application which needs to write into sqlserver  and postgresql  databases.

I  use Dapper  ORM

I have been able to specify my connection string   for sqlserver  and it works

Now i am trying to specify  the equivalent  for  PostgreSQL  and I am getting the

Error   " OLE DB Provider  not Specified in the Connection String "

I have tried the following 3  providers

providerName="Npgsql2" />
providerName="Npgsql" />
providerName="SQLOLEDB" />

with same error message

Whats should be my provider string  for a c# application  connection to a

PostgreSQL database  using Dapper

Please Note that  I have Dapper  and   Npgsql installed in my application using Nuget

Attached please find error screen using the debugger and my connection string PostgreSQL

I will be grateful for an assistance

O.A.  Oluwole
OLE-DB-PROVIDER--24-09-2018.docx
0
I'm using a jump box for my developers to access PostgreSQL database.  The leadership has limited access of 2 users to each Jump box.  It is very difficult to scale that model when all the developers need is secure access (this is on the Azure cloud by the way).  I was thinking about opening up port 5433 as recommended by this article to install pgAdmin.  Does anyone have any pros or cons to this course of action?
0
I have a C# application that needs to connect to sqlserver and postgresql

I have been able to connect it to sqlserver and it works and the connection string is shown below

<add name="Tournaments" connectionString="Server=CHRISTINESPC;Database=Tournaments;Trusted_Connection=True;" providerName="System.Data.SqlClient"/>

I have tried a number of options for postgresql without success

Can anybody kindly help to specify what  the postgresql  connection string equivalent is

Notes:

CHRISTINESPC is my servername as displayed in sqlserver  management studio thou I am open to using an IP address
Tournaments my Database name in both sqlserver and postgresql databases

Thanks

Oluwole
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
>