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

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 …

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?

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 = …

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')
)

Select all 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

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

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

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.

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?

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'

Select all 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

Select all Open in new window

and this data in u.unet:

"id",  "pt" , "price"
"M34", "107",  100.00
"M34", "107", 2315.00

Select all Open in new window

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.

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?

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?