We help IT Professionals succeed at work.

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.

Dear Experts
We have decided to develop customized application using .NET core frame work with Visual Studio using C# and for reports and analytics we are going to use  PowerBI report server (on-premise).Please help me understand pros and cons on deciding the database that is should we have to go for  MS SQL or PostgreSQL
0
Dear Experts
Based on the business processes we have identified solution development to be done in .NET Core framework with  Visual Studio using C# and MSSQL we have decided power BI for reporting and analytics( on-premise power BI report server) but customer have asked if possible to consider PostgreSQL, please help to understand on following
1.Application developing using .NET Core framework with Visual Studio using C# instead of MSSQL for database going for PostgreSQL will there be any complications, limitations and some thing that we have to comprise.
2.If we chose PostgreSQL as database server and front end application .NET Core framework with Visual Studio using C# and develop application but using power BI for reporting and analytics is there going to be any challenges, does power BI has connector to interface with PostgreSQL please let us know ,
Please help on above 1 and 2
0
Hi All,

   We are using Postgresql 9.6
I'm trying to truncate a table "A" and it complains of a dependency "B"/ Once "B" is truncated, i tried to truncate "A" and it complained of another table " C". Tried to truncate "C" and it complained of a dependency "D".

I want to know the list of tables to truncate, before truncating "A" so i can plan a right level of backing up of the tables.
Can someone please help?
0
Hi,

  We are on Postgres 9.6 version.
I have a table determine_json, that has a column doc_col. Its a Jsonb object and the construct is as below.
 I need to count the number of docId nodes (3 in this example) and display.
Can someone help achieve this?

[
   {
      "docId":"5e319a9146e0fb00c2e1f829",
      "docDesc":"Application Signature Page",
      "docName":"G.Test1.pdf",
      "createdBy":"abc@gmail.com",
      "createdDt":"2020-01-29",
      "updatedBy":"abc@gmail.com",
      "updatedDt":"2020-01-29",
      "memberName":"Mary Ringold",
      "docCategory":"Application",
      "individualId":"915044989",
      "documentTypeCd":"AASP",
      "individualName":"Mary Ringold",
      "docSelectedFlag":null
   },
   {
      "docId":"5e319aa946e0fb00c2253576",
      "docDesc":"Bank Statement",
      "docName":"G.CTest2.pdf",
      "createdBy":"abc@gmail.com",
      "createdDt":"2020-01-29",
      "updatedBy":"abc@gmail.com",
      "updatedDt":"2020-01-29",
      "memberName":"Mary Ringold",
      "docCategory":"Assets",
      "individualId":"915044989",
      "documentTypeCd":"ABS",
      "individualName":"Mary Ringold",
      "docSelectedFlag":null
   },
   {
      "docId":"5e319ab646e0fb00c2e1f82f",
      "docDesc":"Health Insurance Premiums",
      "docName":"G.Test3.pdf",
      "createdBy":"abc@gmail.com",
      "createdDt":"2020-01-29",
      "updatedBy":"abc@gmail.com",
      "updatedDt":"2020-01-29",
      "memberName":"Mary Ringold",
      

Open in new window

0
I have below 3 sql but I need 1 sql that can show all 3 result as 1 output

Sql : select current_database();
Output : dbname

Sql : select inet_server_addr();
Output : 101.99.234.20

Sql : select user;
Output : userA

Above is 3 different output but I need 1 sql that can give 1 output like below.

Output
Current_database,ip,user
Dbname, 101.99.234.20, userA

Is it possible to join these sql.
0
Hi,

   I have a table like this.
PM_ID	      IND_ID	PGM_ID	 Start_Dt	        End_Dt
1	              200	        300	  5/1/2019	      8/4/2019
2	              200	        300	 7/20/2019	     12/31/2019
3	              200	        300	 1/1/2020	     12/31/9999

Open in new window


I need to identify the overlapping date ranges for the combination of IND_ID and PGM_ID and display as follows.
Can someone help with this?
IND_ID	PGM_ID	 Start_Dt	        End_Dt          Overlap_Start_Dt      Overlap_End_Dt
200	  300	  5/1/2019	      8/4/2019          07/20/2019                12/31/2019

Open in new window

0
Hi! I'm looking at a crime dataset and I want to create a query that finds the block of each district (there are 22 total) with the highest number of gun-related crimes. I think I have to create a subquery but not really sure how to do this. I tried creating an alias with mycount but that also didn't work.

My most successful attempt is:
gun='%GUN%'
try:
    cursor.execute("SELECT block, count(block) AS MYCOUNT FROM crimes WHERE DESCRIPTION::text LIKE %s GROUP BY block ORDER BY count(block) DESC",[gun])
    rows4=cursor.fetchall()
except:
    db_connection.rollback()
Annotation-2020-02-16-095100.pngand it creates the attached file. I want to select only the 22 blocks that have the most gun-related crimes in their corresponding district.
0
Hello,

I'm working in RedShift (PostGreSQL) and need to append distinct values for a field in one table to the end of another table.

Table1 has 2 fields;
    T1.ID,
    T1.Date

Table 2 has 3 fields:
    T2.ID,
    T2.Date,
    T2.Items

T1.ID + T1.Date represent a single row in T1.

In Table2, T2.ID, and T2.Date appear in multiple rows given the number of distinct values in T2.Items.  

The task is this.  Joining T1 and T2 on T1.ID = T2.ID and T1.Date = T2.Date append the distinct values in T2.Items.  
And to make it even more fun, the max number of distinct values in T2.Items for each ID+Date is not known.

Any ideas on a fairly graceful way to do this?

TIA!
0
Hi,
What is wrong below? Is it the place to run query?
8a.png
0
I have a connection string in the appconfig file of my c# application  (see below) that writes to a postgresql database.

AppConfig

[]<connectionStrings>
    <add name="Tournaments" connectionString="Host = 138.197.165.89; Database = tournaments; Port = 5432; Username = postgres" />
  </connectionStrings>[/code]

In the method below that goes into my Database  I have had to repeat the string again  (see below)

[] public class PgrConnector : IDataConnection
    {
        private const string db = "tournaments";
        private const string pgrstring = "Host = 138.197.165.89; Database = tournaments; Port = 5432; Username = postgres";
     
        public void ClosePayrollTestRun(MonthPayModel model)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection(pgrstring))
            {

                using (var command = new NpgsqlCommand("public.sppayrollproccesor_closepayrolltestrun", conn))
                {
                    conn.Open();
                    command.CommandType = CommandType.StoredProcedure;

                    //foreach (DataRow row in dataSource.)
                    //{
                    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, …
0
Hi

I'm using npgsql for first time with postgres 11 db

My code is as follows

    cmd = new NpgsqlCommand("INSERT INTO \"tracking_dataa\"(imei_number, gps_datetime, event_id, speed, latitude, longitude, heading, raw_data)	" +
                                                                          "VALUES(@ImeiNumber, @GPSDateTime, @EventId, @Speed, @Latitude, @Longitude, @Heading, @RawData);", con, transactSql)
                                {
                                    CommandType = CommandType.Text
                                };
                                cmd.Parameters.AddWithValue("@ImeiNumber", item.ImeiNumber);
                                cmd.Parameters.AddWithValue("@GPSDateTime", item.GPSDateTime);
                                cmd.Parameters.AddWithValue("@EventId", item.EventId);
                                cmd.Parameters.AddWithValue("@Speed", item.Speed);
                                cmd.Parameters.AddWithValue("@Latitude", item.Latitude);
                                cmd.Parameters.AddWithValue("@Longitude", item.Longitude);
                                cmd.Parameters.AddWithValue("@Heading", item.Heading);
                                cmd.Parameters.AddWithValue("@RawData", item.RawData);
                            
                                cmd.ExecuteNonQuery();

Open in new window


However, I get the error
{"42P01: relation \"tracking_dataa\" does not exist"}

Open in new window


i've tried the following

INSERT INTO \"tracking_dataa\"(imei_number, gps_datetime, event_id, speed, latitude, longitude, heading, raw_data

INSERT INTO tracking_dataa (imei_number, gps_datetime, event_id, speed, latitude, longitude, heading, raw_data

INSERT INTO \"public\".\"tracking_dataa\"(imei_number, gps_datetime, event_id, speed, latitude, longitude, heading, raw_data

INSERT INTO public.tracking_dataa (imei_number, gps_datetime, event_id, speed, latitude, longitude, heading, raw_data

Open in new window


Nothing seems to work, the same error exists

When I browse my DB in Navicat, the table is under the public schema and I can connect fine

What am i doing wrong?
0
Looking for learnings/advice of Oracle to Postgres conversion (SELECT queries in particular).

I'm aware of a few key differences (e.g. no "dual", no "sysdate", no "connect by ...") but I'm hoping someone has, or knows of, a good fact sheet of differences.

If it helps: Oracle 11 to "recent Postgres" (not sure of version as yet)
0
I'm currently looking at massive scaling challenges

I need a database system that supports the following

here are my requirements
1) High insert rates for timeseries data (60,000 records per second - could be batch inserted)
2) NewSql style database (not NoSql)
3) One main table takes the LOG data, the rest are things like users etc that I need to join to the LOG table, therefore i need full SQL support
4) be able to scale horizontally and also put different months data on different servers so I dont run out of diskspace (currently its all on one large disk)
5) where possible just throw new hardware at the database and it work
6) support OLAP and OLTP work loads - i.e. high insert rates, and reporting (ideally under one system)
7) Open source / free

I've looked at:
Postgres & Citus
MemSql
Vitess

I'm wondering if anyone has any input or experience that could help recommending a good option or considerations
0
We are currently using Sql Server as our database

We are now planning on moving to PostgreSql 12

Most things look ok, but we mainly use stored procedures in sql server to return data
I cannot find out how to migrate these from Sql Server to postgres
Does anyone have any ideas if this is possible or what the impact will be?
0
Hello experts,

The following procedure allows me to export various tables related to a ssms database.

Sub SQLQueryOut2(wsName As String, strSQL, strInitialRange, strServer)

Dim objRS
Dim SQL

Set wb = ActiveWorkbook
Set DestSh = Nothing
On Error Resume Next
Set DestSh = Sheets(wsName)
Set wsConfig = Worksheets("Config")
On Error GoTo 0

    If DestSh Is Nothing Then
        Set DestSh = wb.Sheets.Add(After:=Sheets(wb.Sheets.Count))
        DestSh.Name = wsName
        wsConfig.Select
    Else
        Sheets(wsName).Cells.ClearContents
        wsConfig.Select
    End If
    
Set objRS = CreateObject("ADODB.Recordset")


' ==> to modify
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Driver={SQL Server};" & _
"Server=" & strServer & ";" & _
"UID=user;" & _
"PWD=password;" & _
"Database=database;"


SQL = strSQL
objRS.Open SQL, objConn

On Error Resume Next
Set rs = objConn.Execute(SQL)
On Error GoTo 0
If rs Is Nothing Then
MsgBox "SQL query reported at row " & rw & "  is not properly set up unable to transfer  data."
Exit Sub
End If

 For Idx = 1 To rs.Fields.Count
        Sheets(wsName).Range(strInitialRange).Offset(0, Idx - 1) = rs.Fields(Idx - 1).Name
    Next

    Sheets(wsName).Range(strInitialRange).Offset(1).CopyFromRecordset rs

Set objRS = Nothing
Set objConn = Nothing

End Sub

Sub SQLQueryoutConfigSheet()

    Dim wsConfig As Worksheet, wsResult As Worksheet
    Set wb = ActiveWorkbook
    Set wsConfig = Worksheets("Config")
    

Open in new window

0
Hi, I am new to postgreSQL.  I have a question about how to connect to postgreSQL db in java ?
0
Because of some stupid mistake(s) I somehow crashed my Pentaho BI server.
The problem was not noticed immediately, and so the oldest usable backup was more recent than the mistakes.
I somehow succeeded in restoring the login form display (good for morale!) but now none of the previously recorded users seems to wotk, most notably the admin user.

Where are supposed to be stored the users and roles?

Background:
Pentaho 7.0 (I prefer not to update until the users problem is solved)
using Postgres 9.4
0
I am trying to read  a date  field into my model
I have a postgresql database and i am doing the read from
a c# method  (see the code below)

[]{
                        LoansDetailModel model = new LoansDetailModel();

                        if ((string)reader["ent_code"] != null)
                        {
                            //model.Id = (int)reader["id"]; //id is the column name in the database
                            model.staff_no = (string)reader["staff_no"]; //absecode is the column name in the database
                            model.ent_code = (string)reader["ent_code"]; //absedesc is the column name in the database
                            model.loanid = Convert.ToInt32(reader["loanid"]);
                            //model.date_taken = (DateTime)reader["date_taken"];
                            model.date_taken = DateTime.ParseExact("date_taken", "dd-MM-yyyy", System.Globalization.CultureInfo.InvariantCulture);
                            model.amt_taken = (decimal)(reader["amt_taken"]);
                          //  model.ded_date = Convert.ToDateTime(reader["ded_date"]);
                            model.date_taken = DateTime.ParseExact("ded_date", "dd-MM-yyyy", System.Globalization.CultureInfo.InvariantCulture);
                            model.amt_left = (decimal)reader["amt_left"];
                            model.amt_paid = (decimal)reader["amt_paid"];
                            model.amtpay_todate = …
0
I have a table in my postgresql database  with the name proportions  (see table data below)

Table with Data in postgresql database
I need to read every record of this table and use the values to update another table.

When I had data in a grid   and needed to read and save all the data i had  a statement of the type

GlobalConfig.Connection.CreateJobHistoryForm((DataTable)dgvDetailsTable.DataSource); (where dgvDetailsTable is my Grid name)

Now that i want to read directly from  a database table and NOT a grid what would the equivalent of the line above.

so that in my method reading the data i would be able to read with (row in Rows)

I did something like GlobalConfig.Proportions()   and that did not give me the ability to use (row in rows)

Do i need to convert a regular database table to a DATATABLE  before i can loop through in my method
and in that case  how would i do that?

or is there a way to read table data  directly

Thanks

O.A. Oluwole
0
Please help us with Actian PSQL Control Center.

We are trying to rebuild a databasefile.

Errormessage:
rbldcli -c -s -f95 -bD:\Amanda\Program\Amanda7 D:\Amanda\Program\Amanda7\Data\AC.BTR
REBUILD-20: The utility is processing D:\Amanda\Program\Amanda7\Data\AC.BTR.
REBUILD-27: The utility could not create the temporary file in the output directory during conversion.

The ownership of directories is System.
0
I need  to run about  30 payroll scripts (doing  Inserts  , Update Delete) into a postgresql database from my c# application.

The whole scripts must either succeed or  fail with a roll back.

The script must run from more than 3  places in the application

First i decided that  to have scripts run from different places in my application i need to have the script  called from a library
(Is this assumption correct ?)

So I created a Payrollprocessor in my Library

Under my  Run Payroll Button I have the rough structure  of what i need to do as shown below:

Handling Transaction Under a Click Button Event
with  the code also shown  below:

 private void button2_Click(object sender, EventArgs e)
        {
            
            try
            {
                BEGIN TRANSACTION;
                PayrollProcessor processor = new PayrollProcessor();
                //script01 = processor.writetolivefile;
                //script02 = processor.ClearParollTable;
                END TRANSACTION;
            }
            catch (Exception)
            {
                transaction.Rollback();
                throw ex;
                
            }
        }

Open in new window


The issues i need to resolve are:

1. Since i have about 30 script with each one accessing the database for inserts, updates and delete how do i
ensure that commits  are only done once, particularly since each visit  would execute a stored procedure.
How do i stop one stored procedure from committing until all …
0
I have a   script that tries to write to a postgresql database with the script below:

Script that does Insert into a postgresql database
If you observe the line  commented out  . I was able to insert into the database using the query analyser

I reviewed  the log after  the error and the application request an explicit type CAST

See  error below

2019-08-20 17:55:37 PDT ERROR:  function public.sppayrates_updateinsert(curgrdx => character varying, enttypex => character varying, entcodex => character varying, gradeamtx => numeric, filterfieldx => character varying, companycodex => character varying, validrecordynx => character varying, payrateidx => integer) does not exist at character 15
2019-08-20 17:55:37 PDT HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2019-08-20 17:55:37 PDT STATEMENT:  SELECT * FROM public.sppayrates_updateinsert("curgrdx" := $1,"enttypex" := $2,"entcodex" := $3,"gradeamtx" := $4,"filterfieldx" := $5,"companycodex" := $6,"validrecordynx" := $7,"payrateidx" := $8)
2019-08-20 18:00:54 PDT ERROR:  function public.sppayrates_updateinsert(unknown, unknown, unknown, integer, unknown, unknown, unknown) does not exist at character 8
2019-08-20 18:00:54 PDT HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2019-08-20 18:00:54 PDT STATEMENT:  SELECT public.sppayrates_updateinsert(
	    'TR3C',
	    'A',
	    'BASIC',
	    50000,
	    'P',
	    'NNPC',
	    'Y'
	);

Open in new window


How do i do an explicit cast type

I have  text , decimal and integer  columns .

I will like to know how to do an explicit cast  each type of data

Thanks

Olukay
0
I am able to save a Date field in to the database  in the format  "dd-mm-yyyy"
with the statement below:


startdate.Value = DateTime.ParseExact(GridVariables.GridColumn03, "dd-MM-yyyy", System.Globalization.CultureInfo.InvariantCulture);
enddate.Value = DateTime.ParseExact(GridVariables.GridColumn04, "dd-MM-yyyy", System.Globalization.CultureInfo.InvariantCulture);

However  when trying to get the same data  from the database  it comes back in the format  "yyyy-mm-dd" using the 2 lines below

model.Startdate = (DateTime)reader["Startdate"];  
model.Enddate = (DateTime)reader["Enddate"];

I believe i need a similar clause  with a cultureInfo   statement

Somehow  I have not been able to get the syntax right  particularly with the reader

Can someone please help me to state the equivalent for the read or get operation so that
the output  in my List and DataGrid  are in the format  "dd-mm-yyyy"

Thanks

Olukay
0
I am trying to write data from a grid  to a postgresql database .

The datagrid with the data is shown below:

Data in Grid ready for writing to the database
I got an error  saying that a field is not recognised as valid date field
But i know the field is declared  as a date field. See the error screen below

Error screen  with content of the Date fields
Note that the string GridColumn03  and gridColumn04    are showing as string and
i tried the Convert  statement

My full  Method is shown below

public void CreateJobHistoryForm(DataTable dataSource)
        {
            // New datatable implementation  24-05-2019
            //Place  to process tuples into the database
            //  Save Button Click
            using (NpgsqlConnection conn = new NpgsqlConnection(pgrstring))
            {
                // public.speducation_insert
                using (var command = new NpgsqlCommand("public.spjobhistory_writemasterdetail", conn))
                //using (var command = new NpgsqlCommand("public.speducation_insert", conn))
                {

                    conn.Open();
                    command.CommandType = CommandType.StoredProcedure;

                    //command.Parameters.Add(value: new NpgsqlParameter("staffnox", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = GridVariables.GridColumn01 });
                    //command.Parameters.Add(new NpgsqlParameter("companycodex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, 

Open in new window

0
I have  a stored procedure in my postgresql database

The procedure compiles without  any error  (see  screen)

Stored Procedure Creation Page Without any Error
When i tried to excute I got an error of a field lastnumber as not existing even though it exists in the table (see error screen)


Error page when i tried executing Stored Procedure
I then  tried to replicate the stored procedure as just an ordinary sql statement and it executed properly  giving the desired result (see  screen)

Update Worked when i just executed the sql statement separately
Why am i having the error that a field existing in the database is being reported as not existing

I also attach the table with it Fields and Data  (see attached screen)

Table with Data
I will be grateful  for any advise on how to resolve this issue.

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