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 am attempting to use an HTML form contained in a modal to transmit a PDF and an image to a PostgreSQL database. I don't have the database set up yet, but I've already run into an issue. My file structure has a folder for 'models', 'routes', 'views', and a main 'app.js' file. I've included the app file below as well as the files containing the issue. The form is in the 'btb.html' file (contained within the views folder) and begins on line 211 (there is a second form below it, but I haven't begun to use that one yet). The form should be posting to the URL of the 'btbRoute.js' file (contained within the routes folder) and the "router.post" there should handle the response and create an entry in the database. I've also included two files from the 'models' folder-- the 'order.js' file that defines the schema model for my database entry and the 'index.js' file that will eventually connect all my models together.

I'll eventually want to render the information that I'm inputting elsewhere on the website, so if anything that I'm doing would prevent that I would need to find a different solution (I have no idea if that's even a thing, but just in case).

Thanks in advance.
index.js
order.js
btbRoute.js
btb.html
app.js
0
Keep up with what's happening at Experts Exchange!
LVL 12
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Postgres allows you to create a table using inheritance. We have a design where we have 1400 tables that inherit from one main table. These tables are for each of our vendor's inventory.

When I want to query stock for a vendor, I just query the main table. When running `Explain`, the explanation says that it is going through all 1400 indexes and quite a few of the inherited tables. This causes the query to run very slowly. If I query only the vendor's stock table, I cut the query time to less than 50% of the time by querying the main table.

We have a join on another table that pulls identifiers for the vendor's partner vendors and we also want to query their stock. Example:
(This query runs in about 2 seconds)
SELECT 
	(select m2.company from sup.members m2 where m2.id = u.id) as company,  
    u.id,
	u.item, 
	DATE_PART('day', CURRENT_TIMESTAMP - u.datein::timestamp) AS daysinstock, 
	u.grade as condition, 
	u.stockno AS stocknumber, 
	u.ic, 
	CASE WHEN u.rprice > 0 THEN 
		u.rprice 
	ELSE 
		NULL 
	END AS price, 
	u.qty
FROM pub.net u 
LEFT JOIN sup.members m1 
    ON m1.id = u.id OR u.id = any(regexp_split_to_array(m1.partnerslist,',')) 
WHERE u.ic in ('01036') -- part to query
  AND m1.id = 'N40'     -- vendor to query

Open in new window


Now, this refactor makes the code run in about 800ms.
WITH cte as (
    SELECT partnerslist as a FROM sup.members WHERE id = 'N40' 
) 
SELECT 
    (select m2.company from sup.members m2 where m2.id = u.id) as company,  
    u.id,
	u.item, 
	DATE_PART('day', CURRENT_TIMESTAMP - u.datein::timestamp) AS daysinstock, 
	u.grade as condition, 
	u.stockno AS stocknumber, 
	u.ic, 
	CASE WHEN u.rprice > 0 THEN 
		u.rprice 
	ELSE 
		NULL 
	END AS price, 
	u.qty
FROM pub.net u 
WHERE u.ic in ('01036')                                                    -- part to query
  AND u.id = any(regexp_split_to_array('N40,'||(select a from cte), ','))  -- vendor to query

Open in new window


However, is there away to modify them to get the data from ONLY the specific inherited tables?

The n40_stock table has stock for the vendor with id = N40 and N40's partner vendors (partnerslist) are G01, G06, G21, K17, N49, V02, M16  so I would also want
to query the g01_stock, g06_stock, g21_stock, k17_stock, n49_stock, v02_stock, and m16_stock tables.
0
Hi,

  We work on Postgresql 9.6, installed on Linux.  
We get SQL code requests coming from different teams, such as DDLs, DMLs, TCLs etc.
We have various environments such as DEV01, DEv02, ST01, ST02 and UAT.

My task is to deploy the code snippets coming from individual code requests, into all the above environments.
Currently i'm copying and pasting these scripts manually into each of the aboce environments.

Is there a way to automate this effort so that, i can change the db in a file and quickly execute the code?
0
Hi

I have a PostGreSQL/PostGIS database
Something like this

ID, Group_ code , point_number, date, geom
1, 1234, 1 , 2018-05-10, geom
2, 1234, 2 , 2018-05-10, geom
3, 1234, 3 , 2018-05-10, geom
4, 1234, 4 , 2018-05-10, geom
5, 1234, 5 , 2018-05-10, geom
6, 1235, 1 , 2017-05-10, geom
7, 1235, 2 , 2017-05-10, geom
8, 1235, 3 , 2017-05-10, geom
9, 1235, 4 , 2017-05-10, geom
10, 1234, 1 , 2017-05-10, geom  // WRONG

Open in new window


I can have multiple Group_ code  the same but the should have the same date

I need to find all rows where the same Group_ code has 2 or more dates
0
I want to convert column type from  to integer

ALTER TABLE billdetail ALTER COLUMN  masterid TYPE integer;

Open in new window


[Err] ERROR:  column "masterid" cannot be cast automatically to type integer
HINT:  You might need to specify "USING masterid::integer".

when I try
 ALTER TABLE billdetail USING masterid::integer; 

Open in new window


[Err] ERROR:  syntax error at or near "USING"
LINE 1: ALTER TABLE billdetail USING masterid::integer;
0
PostgreSQL  Vacuum:

•SELECT version();   --PostgreSQL 9.4.9
•Currently we have vacuum enabled with conservative setting of %20.

1.   The language on the support sites talks about the tables of the Postgress DB being held in individual files ?
 If we were to issue a 'vacuum full' command, would the recovery process re-write each table 1 at a time and 0 out the previous table ?
 How much extra space do we need to have available to issue 'vacuum full' and not run out of disk space ? (At least as much as the largest table ? or the whole D.B. ?)

2.  Can we call 'Vacuum Full' and specify a particular table, we only that table get locked ?

3.    Can 'vacuum full' be cancelled once called if it is taking too long and we need to get production back up and running on that table ?
0
I want to install Postgres 10 on Rhel7 into a specific directory where not into anything on the root file system so how would I install everything for postgres 10 into the following directory:
/appsdb/postgres/10/
0
PostgreSQL vacuum: if our administrator runs vacuum manually or a terabyte database to recover about %10 of the space,  how much of performance hit can we expect to see from the database while the vacuum process is running ?

Will all other processes slow to where the DB is unusable until the vacuum completes ?
No impact at all ?   Just want no surprises !

Thanks
0
Hi Experts

Could you point the vantages (disadvantages) of using PostgreSQL instead of MySQL?

Preferencialy based on your own conclusions.

Thanks in advance!
0
Hi Experts

Could you point a way to migrate a PostgreSQL database to a MySQL database?

I'm currently using Win7 environment.

Thanks in advance!
0
The 14th Annual Expert Award Winners
LVL 7
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Hi Experts

Could you point what's  needed to use PostgreSQL from PHP?

In terms of configurations on PHP.ini f.e. and dll(s)  that must be copied in PHP folders f.e.

Thanks in advance!
0
When I run the vaccum command, it freezes the memory thereby resulting in slow performance. How can I prevent vaccum from becoming a memory hog?
0
i install bacula on ubuntu 16.04 server using postgres database.
bacula-dir and fd and sd is running .
i cannot connect to bconsole.
and in /var/log/bacu.../bacula.log i have error:
bacula-dir: dird.c:972-0 Could not open Catalog "MyCatalog", database "backup".
0
Hi Everyone,

I am facing with a deadlock issue in Postgres when concurrent processes (created by stress tests from JMeter) from my Java web app update 30 rows in a Postgres table.

Caused by: org.postgresql.util.PSQLException: ERROR: deadlock detected
  Detail: Process 6497 waits for ShareLock on transaction 1369735; blocked by process 6673.
Process 6673 waits for ShareLock on transaction 1370426; blocked by process 6497.
  Hint: See server log for query details.
  Where: while rechecking updated tuple (3,42) in relation "relationship_name"

Can you please give some advice and ideas to resolve this? Thank you very much.
0
is there any document for backup and backup plan for postgresql database on ubuntu server 16.04 using pgadmin4.
0
DB = Postgress

I want to select only values that are prefixed with  "d_mc". example of field values are  "d_mc###", "d_mm###", "d_ma###"

How do I do that form an a Select statement ?  All of the following just seem to return everything.

select relname, substring(relname, '\d+$') as my_Number 
     from pg_stat_user_tables
     -- where substring(relname, 'd_mc\d+$')
     -- where relname like "d_mc\D%"
    where relname like 'd_mc[0-9]+'

Open in new window


Thanks
0
Hi,
   We are on Postgres 9.6.
  I have a table called tbl_client. This has many fields and one of the fields is called end_dt.
My requirement is to insert a row into another table called tbl_audit, for any changes in tbl_client.
So, i created a trigger to capture the same.

Now, the requirement is that i don't insert a row if the change is only on the end_Dt.

Can someone give me an idea how this can be added to the condition?
0
Hi Experts,

       I am working on an web application (wagtail cms) like django-cms, which is in aws ubuntu.  Its backend is postgres, with elastic search few modules reactjs and nodejs.
The application is deployed in docker containers.  uwsgi is the upstream server for nginx.

      I get lots of errors with npm build, i am not able to run the application.  sometimes ports are not accessible. postgres is using 5432, elasticsearch is 9200. react is rendering on 9009. I get lots of dependencies error.    At what situation I have to use dockers?  sometimes I get errors in nginx configuration. could please tell what is good practice for npm build, run deploy.  

    What is the best way to architect the development and test environment?

    Will I be able access the postgresql, elastic search and nginx in linux ubuntu on aws with cms source code and contents on windows local pc?   or having the copy of postgresql, elastic search, cms contents and nginx all on a windows pc as development environment environment.   Elastic search has huge data.  and how to deploy the application on dockers in linux environment.

    What is the best way to have a development environment?  what is best test environment

   could you please tell me on how elastic search data is stored on postgres and what is network host setting in elastic search how it accessed from outside application.

    Could you please throw light on how to create development and test environment.

with …
0
Hi Experts,

How to connect to postgres, elastic search server in linux, ubuntu on AWS to  with Dango-cms or Wagtail cms from dev machine on windows

Please tell me how elastic search details are stored in database and how to connect to do a elastic search which is in postgres database.

All indexes of elastic search is stored in postgres sql.  How will i connect to postgres from windows dev machine.

And How can I access the postgres database in ubuntu aws to development machine have django-cms or wagtail cms on windows.

 ES_CONNECTION=172.17.0.1:9200,

database connection string is created with dj_database_url  the connection string is DATABASE=postgres://user:password@server/database.

Please help me,

I get the following error when I build the wagatail cms - based on python
python manage.py makemigrations

throws the below error.

django.db.utils.OperationalError: could not connect to server: Connection timed out (0x0000274C/10060)
        Is the server running on host "172.17.0.1" and accepting
        TCP/IP connections on port 5432?

I connect string sample postgres sample is postgres://test1:test2@172.17.0.1/database

When I go inside the aws machine through putty (ssh) 54.*.*.*:22

then

telnet 172.17.0.1 5432

It is connection is success full.

but not able to connect from outside aws machine from a windows pc (development machine).

With Many Thanks,
Bharath AK
0
Cloud Class® Course: Microsoft Windows 7 Basic
LVL 12
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

I want to perform a text search on my Postgress table and I want the search to be case -Insensitive.

I have seen some examples of case-insensitive searches using the UPPER or LOWER command, but this would consume a lot of resources.
Doesn't Postgress have option for the SELECT cmd to ignore text case ?

Thank you.
0
How to perform a bulk read/write query from/to postgres where data is contained within multiple separate worker processes and jobs?

My architecture is quite simple and has hit a limitation.

I currently have 2 worker processes (each with a connection pool of 25) which are responsible for managing enqueued jobs. I enqueue by pushing data to redis, and they pull work from there. For the sake of argument, let's say all each job does is:

def perform
  read from db (select from table_1 where id = 123)
  write to db (insert into table_2 etc etc)
end

Open in new window


I currently enqueue 500 of these jobs every minute. I cannot group these jobs together as they are responsible for very specific operations based on data found in rows in my db, and have different retry limits, etc,

So, I enqueue 500 jobs and my two workers pull these jobs and go to work.. each job performing a single read query, and a few seconds performing a write query.

My question is, how might I go about merging the data generated from my jobs I want to insert into my `table_2` in order to build up a bulk query?  I cannot see anyway of doing this without pushing the data into an intermediary store which I then use to build a query and perform.
0
Postgress SQL via VBA.

What should my connection sting look like ?

I have:
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection

    Dim strConn As String
    'strConn = "Driver={SQL Server};Server=; Database=; UID=; PWD="
    'strConn = "Driver={PostgreSQL Unicode(x64)};Server=" & srv & "; Database=" & sDB & "; UID=" & sID & "; PWD=" & sPW
    'strConn = "Driver={PostgreSQL35W};Server=" & srv & "; Database=" & sDB & "; UID=" & sID & "; PWD=" & sPW
    strConn = "Driver={PostgreSQL};Server=" & srv & "; Database=" & sDB & "; UID=" & sID & "; PWD=" & sPW

    cn.Open strConn

Open in new window

But all those options always produce the same error:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

System DNSUser DNS
0
Hi Experts, I am desperate :(
I am looking for a postgres statetement, to show 2 Queries within 1 table.
I meen, I want to split  1 period from 2017 - 2018into two periods 2017 in one column and 2018 in a second column.

My following statement does not work

SELECT
  public."RechDat"."RECHDATUM",
  public."RechPos"."ARTNR",
  public."RechPos"."ID",
  "RechPos1"."ARTNR",
  "RechPos1"."ID",
  SUM("RechPos1"."NettoGP") AS field_1,
  SUM(public."RechPos"."NettoGP") AS field_2,
  "RechDat1"."RECHDATUM"
FROM
  public."RechDat" "RechDat1"
  INNER JOIN public."RechPos" "RechPos1" ON ("RechDat1"."RECHNR" = "RechPos1"."RECHNR")
  INNER JOIN public."RechDat" ON ("RechDat1"."RECHNR" = public."RechDat"."RECHNR")
  INNER JOIN public."RechPos" ON (public."RechDat"."RECHNR" = public."RechPos"."RECHNR")
WHERE
  public."RechDat"."RECHDATUM" BETWEEN '2017-02-01' AND '2017-02-20' OR
  "RechDat1"."RECHDATUM" BETWEEN '2018-02-01' AND '2018-02-20'
GROUP BY
  public."RechDat"."RECHDATUM",
  public."RechPos"."ARTNR",
  public."RechPos"."ID",
  "RechPos1"."ARTNR",
  "RechPos1"."ID",
  "RechDat1"."RECHDATUM"


Where is my mistake?
Thanks in advance,
TomBild-076.png
0
Mongo vs. Postgres DB.  Can you anyone pointed pros and cons?  And from the personal experience advice?
0
How to use ora2pg in windows environment for oracle to postgreSQL schema migration?
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.