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

Hi, I am new to postgreSQL.  I have a question about how to connect to postgreSQL db in java ?
0
Rowby Goren Makes an Impact on Screen and Online
LVL 13
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

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

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

0
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!
0
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?
0
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.
0
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?
0
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:
 2019-06-02_19h45_40.png
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.
0
How to declare exception variables in Postgresql functions and raise these user defined exceptions on various conditions ? Please advise with some examples.
0
I have a single record in a Grid that i wish to save to the database
This is a c#  application writing to a postgresql database.

See  record as in the attached screen

Single Record in Grid
Once i click the Save button and go to the method that creates the DataTable  
my records  gets duplicated before i move into the the method that writes into the database

See the duplicated records below

Record Duplicated in Method Where Datatable is Constituted
I attach the code under my SaveButton  and GetResultsTable Methods

         
 if (SaveButton.Text == "Save" && StaffNoValue.Text.Length != 0)
            {
                if (Validateform)
                {
                    var Companylist = from row in dgvDetailsTable.Rows.Cast<DataGridViewRow>()

                                      select (
                                             Convert.ToString(row.Cells["dgvtxtCompName"].Value),
                                             Convert.ToString(row.Cells["dgvstartdate"].Value),
                                             Convert.ToString(row.Cells["dgvenddate"].Value),
                                             Convert.ToString(row.Cells["dgvtxtyearsexp"].Value),
                                             Convert.ToString(row.Cells["dgvtxtPosition"].Value));

                    GridVariables.GridColumn01 = StaffNoValue.Text;                 // Staff No Corresponding to IdValue.Text
                    GridVariables.GridColumn07 = LoginDetails.staticcompany;        // 

Open in new window

0
I have a c# application writing  Grid data  into a postgresql database
The grid data is shown below being created as a datatable

Grid Data Showing 2 records constituted into a DataTable
After  validation i have called   the statement below which should move the Data into a method for writing

GlobalConfig.Connection.CreateEducationForm((DataTable)dgvDetailsTable.DataSource);

The full script of the method called is shown below:

public void CreateEducationForm(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, Value = GridVariables.GridColumn07 });
                    

Open in new window

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 sonar 7.7-community docker on postgres 11.0.3
this my compose file:

------------------
version: "2"
services:
  db:
    image: postgres:11.3
    user: "${UID}:${GID}"
    restart: unless-stopped
    container_name: sonar-postgresql
    ports:
     - 5430:5432
    environment:
      POSTGRES_DB: sonar
      POSTGRES_USER: sonar
      POSTGRES_PASSWORD: Sonar
    volumes:
      - /containers/postgres/sonar/sonar_data:/var/lib/postgresql/data
    restart: always
    ulimits:
      nproc: 65535
      nofile:
        soft: 32000
        hard: 40000
  sonarqube:
    image: pf-sonar:1.0
    restart: unless-stopped
    container_name: PF-sonar
    ports:
      - 9100:9000
      - 9092:9092
    volumes:
      - /containers/sonar/sonarqube_conf:/opt/sonarqube/conf
      - /containers/sonar/sonarqube_data:/opt/sonarqube/data
      - /containers/sonar/sonarqube_extensions:/opt/sonarqube/extensions
      - /containers/sonar/sonarqube_plugins:/opt/sonarqube/lib/bundled-plugins
    environment:
      - sonar.jdbc.url=jdbc:postgresql://db:5432/sonar
      - DB_TYPE=postgresql
      - DB_USER=sonar
      - DB_PASSWORD=Sonar
#    restart: always
    ulimits:
      nproc: 65535
      nofile:
        soft: 32000
        hard: 40000
----------------

it try to use port 9000 and then try port 9100  i have error

2019.05.22 18:12:20 ERROR web[][o.s.s.a.EmbeddedTomcat] Fail to start web server
PF-sonar     | …
0
How to connect ManageEngine ServiceDesk PLus database to Access?
I tried creating an ODBC but I get an error
EE1.JPGAppreciate if anyone can help me connect/link SDP tables
0
I am executing a query in PyCharm connected to a PostgreSQL database. Here is the code in question:

sql_query = '''INSERT INTO "daily_performance_metrics"."employee_transactions" 
("WarehouseName", "Date", "EmployeeName", "00:00", "00:15", "00:30", "00:45", "01:00", "01:15", "01:30", "01:45", "02:00", "02:15", "02:30", "02:45", "03:00", "03:15", "03:30", "03:45", "04:00", "04:15", "04:30", "04:45", "05:00", "05:15", "05:30", "05:45", "06:00", "06:15", "06:30", "06:45", "07:00", "07:15", "07:30", "07:45", "08:00", "08:15", "08:30", "08:45", "09:00", "09:15", "09:30", "09:45", "10:00", "10:15", "10:30", "10:45", "11:00", "11:15", "11:30", "11:45", "12:00", "12:15", "12:30", "12:45", "13:00", "13:15", "13:30", "13:45", "14:00", "14:15", "14:30", "14:45", "15:00", "15:15", "15:30", "15:45", "16:00", "16:15", "16:30", "16:45", "17:00", "17:15", "17:30", "17:45", "18:00", "18:15", "18:30", "18:45", "19:00", "19:15", "19:30", "19:45", "20:00", "20:15", "20:30", "20:45", "21:00", "21:15", "21:30", "21:45", "22:00", "22:15", "22:30", "22:45", "23:00", "23:15", "23:30", "23:45", "ReceiveRate", "PutAwayRate", "PickRate", "PackRate", "ShipRate", "receiveptp", "putawayptp", "pickptp", "packptp","TOT") 
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 

Open in new window

0
Hello experts,
I have the following procedure which allows me to perform queries.
I was wondering how should I adapat the connection string.
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")


Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Driver={SQL Server};" & _
"Server=" & strServer & ";" & _
"UID=user;" & _
"PWD=pwd;" & _
"Database=db;"


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 have a PostgreSQL table with empty cells or cells containing '' two single quotes

so when i do

SELECT DISTINCT col_a,col_b,col_c FROM my_table

Open in new window


I get multiple rows because col_b has rows with nothing and rows with ''

FYI:
It was ingested from different sources 1 had col_z the other didn't

Any suggestions how to clear the cells with ''
0
how to move postgres (with all databases) from ubuntu to docker container(using docker compose)?
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
>