PostgreSQL Help & Support | Experts Exchange

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

and it creates the attached file. I want to select only the 22 blocks that have the most gun-related crimes in their corresponding district.

8 Comments

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!

6 Comments

LVL 13

Question by

HuaMin Chen

2020-02-06Solved

Issue to run query

Hi,
What is wrong below? Is it the place to run query?

14 Comments

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

14 Comments

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?

3 Comments

LVL 52

Question by

PortletPaul

2019-11-14Solved

Oracle to Postgres Select Query differences

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)

3 Comments

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

4 Comments

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?

3 Comments

LVL 1

Question by

Luis Diaz

2019-10-23SolvedPrivate

Excel VBA: export sql & postgresql tables

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

…

20 Comments

Hi, I am new to postgreSQL. I have a question about how to connect to postgreSQL db in java ?

1 Comment

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

2 Comments

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

4 Comments

I have a table in my postgresql database with the name proportions (see table data below)

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

2 Comments

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.

4 Comments

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 …

9 Comments

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

7 Comments

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

11 Comments

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

…

3 Comments

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)

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

Olukay

2 Comments

I have the following method (below) in my c# application

  public List<PermissionsModel> GetFormPermissions()
        {
            //List<Models.GeneralSetup.PermissionsModel> results = new List<Models.GeneralSetup.PermissionsModel>();
            List<PermissionsModel> results = new List<PermissionsModel>();
            using (var conn = new NpgsqlConnection(pgrstring))
            {
                using (var command = new NpgsqlCommand("public.sppermissions_getforrole", 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("formnamex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = LoginDetails.staticformname });
                    command.Parameters.Add(new NpgsqlParameter("rolex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = LoginDetails.staticrole });
                    command.Parameters.Add(new NpgsqlParameter("formexecutex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Output });
                    command.Parameters.Add(new NpgsqlParameter("addbuttonx", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction =

Open in new window

…

2 Comments

Hi All,

We are on Postgresql 9.7.
We have a set of tables in different schemas, with identical structure.
One is the source schema and the other is the target schema.
Our task is to identify what changed between the source and target for each table, and
* Insert the new entries into the target tables,
* Identify for each table, all the column values which changed between source and target tables and insert into an audit table.
* Update the existing entries in the target tables based on the column values identified from the audit table.

Can someone kindly suggest an easier way of achieving this, than comparing each column manually?
Thanks much!

PostgreSQL

10 Comments

One of our managers wishes to link his pgadmin to one of our MSSQL Database in read only mode. Is this possible and if so how?

3 Comments

LVL 1

Question by

Luis Diaz

2019-06-27SolvedPrivate

PostgreSQL: declare variables

Hello experts,
I was wondering how you declare variables in PostgreSQL:
My attemp is the following

Declare @tablename;
Set @tablename = "tablename";
Select * from db.@tablename

Open in new window

And I have the following error message.
ERROR: syntax error at or near "@"
LINE 1: Declare @tablename;
^

********** Error **********

ERROR: syntax error at or near "@"
SQL state: 42601
Character: 9
Thank you in advance for your help.

5 Comments

Hi,

We are on Postgres 9.7
I need to create a sequence which will be YYYYMM, followed by a number that gets incremented. YYYYMM will be the year and month of the current date.
Can someone help?

7 Comments

LVL 1

Question by

Luis Diaz

2019-06-02SolvedPrivate

Excel VBA: ADODB connection

Hello Experts,
I am trying to set up adobd connection through postresql however I don’t know which arguments should be added based on this screenshot:

Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Driver={PostgreSQL}; Port=5432;" & _
"Server=" & strServer & ";" & _
"UID=;" & _
"PWD=;" & _
"Database=;"

Open in new window

Additionally, I don’t know if I need to add a library or a driver
Thank you in advance for your help.

3 Comments

PostgreSQL

Related Topics

PostgreSQL

Related Topics