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

Hello! I'm trying to make my first web application of a hotel(this is an education project not a payed one) and I have a lot of issues...The most important one is that if a user at the registration process selects that he wants to reserve 4 rooms he needs to make the checkin process 4 times, once for each room. I have succesfully managed to register the user and login, but the problem is with showing the check-in form for a number of times... I managed to show it once, but the second time it doesn't work, it shows me the details for the same room, not for a different one, or it makes an infinite loop...what can I do? Here it's the PHP code:
<?php
	ob_start();
	 $success = false;
	// Errors reporting, used if needed
	error_reporting(E_ALL);
	ini_set('display_errors', 'on');

	// General configuration like base, used if needed
	include_once ('include/config.inc.php');

	// Mail functions
	include_once ('include/mail.functions.php');



	// DBConn
	include_once ('class/DB/DBConn.includeall.php');
	$db = new DBConn(NULL);

	// Includere clasa login
	require_once ('class/class_login.php');

	// Set up current language
	$lang = "ro";
	$_SESSION[PRE.'lang'] = $lang;
	$row='';
	$access = 0;
	$user = (isset($_POST['nume']) && !empty($_POST['nume'])) ? $_POST['nume'] : "" ;
	$email = (isset($_POST['email']) && !empty($_POST['email'])) ? $_POST['email'] : "" ;
	$telefon = (isset($_POST['telefon']) && !empty($_POST['telefon'])) ? $_POST['telefon'] : "" ;
	$cnp = 

Open in new window

0
Cloud Class® Course: Microsoft Office 2010
LVL 12
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Hello! I'm a newbie in programming and my first project it's an checkin system for a hotel. The system it's looking like this: first of all, the user will enter his name, check-in, check-out date,  the numbers of rooms that he wants to rent, number of people that will stay in a room, and the email address(all this done in the register.php page). After he'll enter this details, an email will be sent to him where he'll have a reservation number and the details about his reservation. After this, he'll go on an page(login.php) where he'll enter this reservation code and start the check-in process for each room, for example if he choosed 2 rooms, he'll make first the checkin for the first room then for the second(this should be done in the checkin.php page).If the checkin process was never made, it will start, otherwise an message will be shown that will say that the code was used or it's incorrect, it depends. What I want help it's with the login process and the starting of the checkin, I managed to send that email, generate that code but I can't login and start the checkin... I want to use only PostgreSQL, not MYSQL. Here it's the login.php:
<?php

	ob_start();
	 $error = false;
	// Errors reporting, used if needed
	error_reporting(E_ALL);
	ini_set('display_errors', 'on');

	// General configuration like base, used if needed
	include_once ('include/config.inc.php');

	// Mail functions
	include_once ('include/mail.functions.php');

	// Start session if needed
	

Open in new window

0
Hi
I'm trying to import some data into my_table using something  like this

create table my_table(
id SERIAL PRIMARY KEY,
title text,
my_date date,
-- plus other columns of types text, date and integer  
another_date date,
etc
);
COPY my_table (title,my_date,....)
FROM 'path/to/input.csv' WITH DELIMITER '|' CSV HEADER;   

Open in new window


the COPY is failing with

ERROR: date/time out of range: "10/07/2018"
SQL state: 2208
Hint: Perhaps you need a different "datestyle" setting.
Context: COPY my_table line 2 , column another_date:   "10/07/2018"

Open in new window

Is there a way of converting the date to "2018-07-10" on the way in?  I can't guarantee each date is in this format but from what  I've seen they are
if not can I set the dates to accept a broader format import the data then change it back


Opening it in openoffice calc and  setting the format of the column(s)   is first a bad idea, this could introduce errors,  and second some of the cells don't convert (I Tried a copy)


BTW:
I'm using PostgreSQL 8.4
0
Hi,
Any advice, to following issue, when accessing PostgreSQL pgAdmin?
0
Hi
I need to run the following update query on a PostgreSQL / PostGIS database

UPDATE geo_table
SET geom =ST_ConvexHull(
     ST_GeomFromText('MULTIPOINT(co_ord_string)', 4326)
);
WHERE 
Id =1234;

Open in new window


co_ord_string is a column name if I manually copy the row value into above the query runs otherwise I get

parse error – invalid geometry HINT: "MULTIPOINT(" <-- parse error at position 11 within geometry


I'm pretty sure this is because of the single quotes around  'MULTIPOINT(co_ord_string)' interprets co_ord_string as the literal string

The value of co_ord_string is a series of long lat points separated by a comma. These don’t create perfect polygons as they may contain internal points but using ST_ConvexHull a polygon is created


Could someone help with the syntax
0
Hi,

I've a table on AWS Redshift with couple of columns in JSON format.
So i'm using JSON_EXTRACT_PATH_TEXT Function to query the data in the JSON format cloumn.

For example ,

SELECT user,json_extract_path_text(payload,'code') as payload_code FROM activity_log.lx4_logs
where event_reference LIKE 'My Dashboard%'; .

So the output look like following with two coloumns

User       | Code
user1      | ["13877512003","13877642003","10078402003","13901222003","10118722003"]
user2     |["105700520150300750","106468920140600750","112279220150300750","104693120060101500","122805820150600750"]
user3    |NULL
user4    |["10112472015"]

Is there a possibility to get the output, into rows like below without any quotation, square brackets etc please ?

User       | Code
user1      |13877512003
user1      |13877642003
user1      |10078402003
user1      |13901222003
user1      |10118722003
user2     |105700520150300750
user2  |106468920140600750
user2   |112279220150300750
user2  |104693120060101500
user2  |122805820150600750
user3    |NULL
user4    |10112472015

Thanks
0
Hi,

  We are on Postgres 9.6 Linux database.
Have a requirement to create a function and pass  cc_case_ID as the parameter.
requirement then, is to to delete all the records from all the tables in a schema, where the cc_case_ID is equal to the parameter value passed.

Can someone kindly help with the solution?
0
I ran the following tutorial on my lapp stack:
https://docs.bitnami.com/google/how-to/generate-install-lets-encrypt-ssl/

In the tutorial I stopped my whole stack, chmod it's directory and chown it to root.

When I restarted my stack then I got the following error:

pg_ctl.bin: another server might be running; trying to start server anyway
waiting for server to start.... stopped waiting
pg_ctl.bin: could not start server
Examine the log output

Open in new window


I ended up undoing steps in Let's Encrypt tutorial; I chmod 666 for my postgresql directory and chown to user postgres (instead of root).
I found a comment online about deleting postmaster.pid to restart postgresql.
I did not delete but I did rename it.
When I restarted Postgresql then a new postmaster.pid was created and the server is up again.

In further review online, I may have made a major mistake by switching out the postmaster.id .
Am I ok with the new created postmaster.id or should I use the original that was renamed?
And if I should go with the old one then how do I get postgresql safely get it started again?
0
How to convert existing Sql server database to postgre database?
0
Hi,

I'm trying to connect my PostgreSQL to Skyvia, a cloud data integration tool. When attempting to do so, I encounter the error message of:

No connection could be made because the target machine actively refused it 127.0.0.1:5432

I have amended my postgres file to listen_Address = '*' as well as amend the pg_hba file to include the Skyvia ip address for access, as recommended, I've also tried setting the configuration to

host    all             all             0.0.0.0/0              md5

I've also set up an inbound rule on my firewall to allow connections to the port 5432, but still no luck.

Any ideas of where I'm going wrong would be greatly appreciated!!
0
Cloud Class® Course: Certified Penetration Testing
LVL 12
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Hi

I have 2 PostrgreSQL/ PostGIS databases I need to update a table in 1 DB with data in a table  in the second

geo_table in DB1

id,country_code,country_name,long,lat,geom  # point data

Open in new window


boundary.table in DB2

id,country_code,country_name,geom  # polygons

Open in new window



If both tables were in the same database I could run a select query something like (UNTESTED)

UPDATE geo.table
set g.country_code = b.country_code,g.country_name = b.country_name
FROM
geo_table g, boundary b
INNER JOIN on ST_intersect(geo_table.geom, b.geom) 

Open in new window



How can I update columns country_code,country_name in the geo_table with corresponding values in the boundary table where the 2 geometries intersect

If necessary I could copy the boundary table into DB1 but this would mean maintaining duplicate data
0
I have a Windows server where the CPU utilization is constantly at 50% which is mostly made up of the PostgreSQL server service. Even if I reboot the server as soon as it is up the CPU usage is at 50% +

It does not matter whether people are logged in and using the Application that is using the PostgreSQL server. Also, the application using the PostgreSQL server makes very light use of it

Here is a screenshot and one as well below from an CPU monitor external to the server. The CPU monitor is from the last 3 hours which was night time with no one on the server or using the application

0
Hi,

 I have a trigger where there is an insert, update and a delete, conditionally.
 I want to let the trigger do its work, but if there is any error, while doing any transactions, i would like to ignore it.
I tried inserting the exception when others, at the end of the function, but that is not ignoring the results.

Can someone kindly suggest how to add that exception logic in the attached sql.
fn_trg_attribute_audit.sql
0
I have the Postgres SQL below in an API function. I am passing the $id and $ic variables. The $ic variable can be an array of values or a single value.
    WITH cte as (
        SELECT partnerslistori as a FROM sales.members WHERE id = '".$id."'
    )
    SELECT 
        u.id as actualid,
    	(SELECT m.company || ' (' || m.id ||')' FROM support.members m WHERE m.id = u.id) AS actualcompany,
    	u.itemname,
    	DATE_PART('day', CURRENT_TIMESTAMP - u.datein::timestamp) AS daysinstock, 
    	TRIM(u.grade)::character varying as condition, 
    	u.vstockno AS stock, 
    	u.hol AS ic, 
    	CASE WHEN u.rprice > 0 THEN 
    		u.rprice 
    	ELSE 
    		NULL 
    	END AS price, 
    	u.quantity, 
    	u.location
    FROM public.net u 
    WHERE u.holl in (".$ic.")
      AND visibledate <= now() 
      AND u.id = any(regexp_split_to_array('".$id.",'||(select a from cte), ',')); 

Open in new window


Now, I need to "aggregate" the results for a single id and contactenate the vstockno values and sum the quantity values into a single record.

For instance, if I return CSV this using the sql above:
"G06","WAP (G06)","CALIPER",370,"C","CAE17366","536-01036",1.00,1,"CY31B05"
"G06","WAP (G06)","CALIPER",770,"C","CAD16821","536-01036",1.00,1,"CALIPERCO"
"G21","WNB (G21)","CALIPER",257,"C","NBI17724","536-01036",1.00,1,"R3415"
"G21","WNB (G21)","CALIPER",128,"C","NBA18095","536-01036",1.00,1,"R2804"
"K17","GAAAP (K17)","CALIPER",270,"C","7I1581","536-01036",,1,"B5-A04"
"K17","GAAAP (K17)","CALIPER",267,"C","7I1594","536-01036",,1,"B4-B04"
"K17","GAAAP (K17)","CALIPER",78,"C","8C1210","536-01036",,1,"[UNKNOWN]"
"N40","WAC (N40)","CALIPER",147,"C","MNA18002","536-01036",1.00,1,"[UNKNOWN]"
"N49","YUPIP (N49)","CALIPER",77,"K","DS180429","536-01036",6.00,1,"UPULL-E"
"V02","CBAP (V02)","CALIPER",1408,"A","CKA1301","536-01036",40.00,1,"[UNKNOWN]"
"V02","CBAP (V02)","CALIPER",350,"A","CPA1257","536-01036",12.00,1,"BIN.360.A"
"M16","BAP (M16)","CALIPER",196,"A","8A067","536-01036",,1,"LOT-23"
"M23","BA (M23)","CALIPER",115,"A","8A065","536-01036",15.00,1,"36AU"
"M21","BUPI (M21)","CALIPER",115,"A","8A065","536-01036",15.00,1,"36AU"
"M22","BSS(M22)","CALIPER",115,"A","8A065","536-01036",15.00,1,"36AU"

Open in new window

I want it to return this instead; the stock and location columns are concatenated and the quantity is summed when there is more than one result for a particular id:
"G06","WAP (G06)","CALIPER",370,"C","CAE17366, CAD16821","536-01036",1.00,2,"CY31B05, CALIPERCO"
"G21","WNB (G21)","CALIPER",257,"C","NBI17724, NBA18095","536-01036",1.00,2,"R3415, R2804"
"K17","GAAAP (K17)","CALIPER",270,"C","7I1581, 7I1594, 8C1210","536-01036",,3,"B5-A04, B4-B04, [UNKNOWN]"
"N40","WAC (N40)","CALIPER",147,"C","MNA18002","536-01036",1.00,1,"[UNKNOWN]"
"N49","YUPIP (N49)","CALIPER",77,"K","DS180429","536-01036",6.00,1,"UPULL-E"
"V02","CBAP (V02)","CALIPER",1408,"A","CKA1301, CPA1257","536-01036",40.00,2,"[UNKNOWN], BIN.360.A"
"M16","BAP (M16)","CALIPER",196,"A","8A067","536-01036",,1,"LOT-23"
"M23","BA (M23)","CALIPER",115,"A","8A065","536-01036",15.00,1,"36AU"
"M21","BUPI (M21)","CALIPER",115,"A","8A065","536-01036",15.00,1,"36AU"
"M22","BSS(M22)","CALIPER",115,"A","8A065","536-01036",15.00,1,"36AU"

Open in new window


Can this be done in a single SQL statement?
0
My shop currently uses both Oracle 11g Single-Instance, and Oracle 12c RAC databases.  A contingent of managers, co-workers and peers are very interested in pursuing a PostGress solution.  Can you point out some of the pros/cons of both database vendors and their solutions?  By the way my application has requirements for 7/24, high availability, load balancing, and the requirement to perform Point-In-Time (PITR) when and if required.  Also database needs to be hosted in an Linux environment.
0
Hi,
Im currently using psql but for the purpose of unit testing Im using HSQLDB to run my queries. All the queries works fine in psql but I get invalid datetime format exception in hsql.
Below is my query,
INSERT INTO microbatch_redrive
SELECT mii.*,(to_char(CURRENT_TIMESTAMP ,'yyyy-mm-ddThh:mi:ss.msZ')) FROM
(SELECT * FROM
  ( SELECT mi.*,me.extractid FROM
    (SELECT * FROM microbatch_info WHERE
        ((numattempts<= ${maxNumOfAttempts})
        AND (starttime>= '${starttime}') 
        AND (starttime<= '${endtime}')))
    AS mi LEFT OUTER JOIN microbatch_extract_info AS me ON mi.microbatchid = me.microbatchid) 
    AS mime WHERE((mime.raverunstatus='SUCCEEDED' AND mime.extractid is null)
    OR (mime.starttime < to_char(CURRENT_TIMESTAMP,'yyyy-mm-ddThh:mi:ss.msZ') AND mime.raverunstatus='NOT_STARTED')
    OR (mime.raverunstatus = 'FAILED')
    OR (mime.raverunstatus='STARTED' AND mime.starttime< (to_char(CURRENT_TIMESTAMP - INTERVAL '1' hour * ${extractTimeIntervalInHour},'yyyy-mm-ddThh:mi:ss.msZ')))
    )
) AS mii LEFT OUTER JOIN
(SELECT microbatchid FROM microbatch_redrive WHERE raverunstatus='REDRIVE_SUCCEEDED') AS mr ON mii.microbatchid=mr.microbatchid WHERE mr.microbatchid IS NULL;

Open in new window


ERROR:
 Testcase: getFailedMicrobatchesToRedrive(com.amazon.eradar.scheduledjobs.utils.RedriveFailedMicrobatchUtilTest):    Caused an ERROR
    [junit] data exception: invalid datetime format: Thh:mi:ss.msZ
    [junit] java.sql.SQLDataException: data exception: …
0
Hi, i have an application running in a debian server, developed in delphi 7 for 32 bits, with postgresql as database, port 5433.
To get an easy way for updating the app, all the clients (windows platform, 32, 64 bits, xp, 7, and 10), reference the executable in the server, but execute it in a local folder, where an ini file configures the link to the database.
In a lot of customers (not all with a debian server, instead, there are windows servers and ubuntu server), and in this customer, this scheme works fine.
In one PC, with Win10, i have this strange problem:
1) if i use a link, that reference the exe in the server and execute the app, it doesn't work. I get an error (internal to the app), as if i can't reach postgresql server.
2) if i copy the exe, and execute in any folder, executing in the same folder as 1), the app works fine.
It seems to be something of the firewall... i disabled it.
It seems to be something of the antivirus... i disabled it (AVG).
pgAdmin, conects to the server.
I simply can't see any other posible solution...
Can anybody help?
Thanks!
0
when I insert in table A  then it automatic copy record that just create in view table B  in postgresql
0
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

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
>