[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x

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 all,
    I am looking for a way to have a linux user created on a sFTP gateway automatically with the credentials that were used when a user was created in a database. We have a web application in which we onboard a user and create their account. We would then have to go and manually create a linux user on the sFTP gateway, as it uses standard linux users as the credentials to login to FTP.

Is there a script (perhaps bash or python?) that can either use some sort of trigger to know when a DB user is made, then create a user on the FTP server. If it can use the same credentials that would be ideal, but if not, it can create a random password and then we can have it email it to the user perhaps. If there is nothing made already, if someone could point me in the right direction as to the best way to go about this, or what language would be ideal, please do.

Thanks!
0
Visualize your virtual and backup environments
LVL 1
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Hi

Given my table has the following example
how do I run an update command to set the year column

CREATE TABLE my_table(
 id SERIAL PRIMARY KEY,
 string TEXT,
 year character_ varying(2) 
);

INSERT INTO my_table (string) VALUES ('30NOV17foobar');

Open in new window




This is what I've got

UPDATE my_table SET year = (SELECT SUBSTRING(string FROM 6 FOR 2) AS year FROM my_table)

Open in new window


I get error "more than one row returned by a subquery used as expression"

baring typos on here the sub query returns the year

Ideally the year column should be type INTEGER but that involves CASTing from text to INTEGER
0
Hi,

  I use PostgreSQL and I have a table, which has the following data (only two rows below, for an example):
     
   ID                        Start_Dt                              End_Dt
    1                          05/10/2017                      10/20/2017
    2                          07/14/2017                      NULL

Open in new window


I want another table based on this, with the data as following:

   ID                        Start_Dt                              End_Dt
    1                          05/10/2017                      05/31/2017
    1                          06/01/2017                      06/30/2017
    1                          07/01/2017                      07/31/2017
    1                          08/01/2017                      08/31/2017
    1                          09/01/2017                      09/30/2017
    1                          10/01/2017                      10/20/2017

    2                          07/14/2017                      07/31/2017
    2                          08/01/2017                      08/31/2017
    2                          09/01/2017                      09/30/2017
    2                          10/01/2017                      10/31/2017
    2                           11/01/2017                      11/26/2017

Open in new window


Can someone kindly help?
0
Hi,
  I have the below Stored Procedure in Postgres.
I have an error log table. When someone passes the V_START_DT to be greater than V_END_DT, i want to insert a custom SQLState and SQLERRM into the error log table.
What i've coded, isn't working. Can someone kindly point me to the solution?

CREATE OR REPLACE FUNCTION F_ERR_VALIDATION(V_ID INT
                                           	  ,V_NAME VARCHAR(50)
                                           	  ,V_START_DT DATE
                                           	  ,V_END_DT DATE
                                              )
RETURNS VOID
AS
$$

DECLARE LV_ERROR_CODE VARCHAR(20);
		 LV_ERROR_MSG  VARCHAR(200);
 BEGIN       
 INSERT INTO TB_CLIENT
 VALUES (V_ID,V_NAME,V_START_DT,V_END_DT);

EXCEPTION WHEN (V_START_DT > V_END_DT) THEN
SQLSTATE := '-1';
SQLERRM := 'Start Date Cannot be greater than End date';

 INSERT INTO TB_ERROR_LOG
 VALUES ('TB_CLIENT',V_ID,SQLSTATE,SQLERRM);
END IF;
 
 EXCEPTION WHEN OTHERS THEN
 LV_ERROR_CODE = SQLSTATE;
 LV_ERROR_MSG = SQLERRM;
 INSERT INTO TB_ERROR_LOG
 VALUES ('TB_CLIENT',V_ID,LV_ERROR_CODE,LV_ERROR_MSG);
END $$ LANGUAGE PLPGSQL;

Open in new window

0
I'm trying to do something super simple...or seems it should be. I have a script that truncates and updates a table...it creates unique combinations across three other tables for a dimension build. I was hoping to create a function/procedure that I could call from an application in a single line, rather than trying to store the sql in my application.

We are using postgresql and sqlworkbench/j. My code to create the table includes a view creation, distinct, union, and in insert into using select.

I was trying:

    CREATE OR REPLACE FUNCTION update_table()
    RETURNS void AS $$
    BEGIN
      CREATE OR REPLACE VIEW ...

     ...;
    END;
    $$ LANGUAGE plpgsql
    IMMUTABLE;

I get the error that "Create Function is not supported".

I'm admittedly a sql rookie and everything I have tried has failed, giving me one error after another. Is there a simpler way to do this? Nothing changes in my create statement...no parameters passed in, nada. It's simply that the underlying transactional data is changing and we're only keeping valid combos, so we trunc and reload the table.

Thanks in advance!
0
As DBA's point of view
I am working as a MS SQL DBA, I want to know,
What is the Difference between MS SQL Server and PostgreSQL?
0
Hi All,

   I'm new to PostgreSQL.
Have a requirement to write a stored procedure, where the error code and description should be inserted into an error log table and the handler shall continue.
Can someone kindly give me a code example which satisfies my scenario?
Thanks in advance!
0
Need help in post gres sql Function...kindly note i need a post gres function created

Declare
field variable = #
record delimiter =!

I fetch data from a table A

select emp no, deptno, account from table A
where account_id = input_acct_id,
             sor_id = input_sor_id,
             time_stamp  = input_time_stamp

I need to send only 40 rows to output. I need to another output variable(More_flag = "Y") if the number of records is more than 40 else i need to send More_flag = "N". This is for pagenation.

Based on the deptno from table A, i need to fetch the dept desc from table B
 select dept_desc from table B
where deptno = tableA.deptno.

I need to send these variable either as a record set or a string

output_rec =    empno fielddelimiter, deptno fielddelimiter, dept_Desc recorddelimiter

(ie) after every field there must a field delimiter and after every record there must a record delimiter.

My final output record must contain

(
input_acct_id,
input_sor_id,
input_time_stamp,
sqlcode,
more_flag,
output_rec,
)

Hope i am able to convey my requirement.
0
Hello,

I have 2 database in postgres, both DB are related using dblink and I need to detect that when making a change in a structure or view could affects the related DB or its views.

The changes I make and need to control are:
1- When I make a change on the structure in DB 1, for example we add a field, and forget to change it in the view or related query. In this case I need a list of views that have related to be able to update them.

2- When I make a change in views in DB 1, and I do not edit all the related queries. In this case, I need a list of queries where the view is used in order to update it.

3- When I make a change in view in DB 1 used by a dblink view in DB 2 without updated it. In this case I need to be notified that the view or structure used in dblink to be able to update it.

Is there any functionality that I can use to be able to do this type of controls?
0
Hi
I'm trying to build a spatial query to locate all results within a radius of a point
I'm passing 3 Variables   & testing if numeric  

something like this
if(isset($_GET['long'])){
$err=0;
if(is_numeric($_GET['long'])){
   $long =$_GET['long'];
}
else{
  $err++
}
if(is_numeric($_GET['lat'])){
   $lat =$_GET['lat'];
}
else{
  $err++
}
if(is_numeric($_GET['rad'])){
   $rad =$_GET['rad'];
}
else{
  $err++
}

If($err ==0){
 $where =  "ST_DWithin(geography(geom), 'POINT(" . $long . " " . $lat . ")'," . $rad . ")";
$sql =<<<SQL
  SELECT * 
  FROM mytable 
  WHERE $1
SQL;
$result = pg_prepare($dbconn, "my_query", $sql) or die (pg_last_error());
$result = pg_execute($dbconn, "my_query", array($where);
}
}

Open in new window

The above fails no mater how it is written the $sql isn't passed to the database correctly pg_error returns false errors near  'ST_DWithin''
Tried bellow first and various other  computations

$sql = <<<SQL
  SELECT * 
  FROM mytable 
  WHERE 
  ST_DWithin(geography(geom), 'POINT($1 $2)', $3)
SQL; 
$result = pg_prepare($dbconn, "my_query", $sql) or die (pg_last_error());
result = pg_execute($dbconn, "my_query", array($long,$lat,$rad);

Open in new window

however using pg_query like below works

$sql = <<<SQL
  SELECT * 
  FROM mytable 
  WHERE 
  ST_DWithin(geography(geom), 'POINT($long $lat)', $rad)
SQL; 

$result = pg_query($dbconn, $sql) or die (pg_last_error());

Open in new window


Can anyone suggest why this is happening and is pg_query secure enough?

I thought about  adding this to the  input but is_numeric trustworthy enough?

if(is_numeric($_GET['long'])){
    $long =pg_escape_string($_GET['long']);
}

Open in new window



BTW I don't have the required files for PDO & the above has been hand copied from a closed system so may contain typos this question  is aimed at why / how do you pg_prepare spatial queries passing parrameters
1
NFR key for Veeam Backup for Microsoft Office 365
LVL 1
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Hi
I have some point data stored in a postgreSQL/PostGIS Database
I need to do some distance measurements select point within 0.5 km of my point

something like

select * from geo_table where ST_DWithin('POINT(Long Lat)' ::geography,geog,0.5)

Open in new window


the problem is points are stored as Geometry Longitude, Latitude SRD= 4326

  my geo_table has geom column created from  Longitude, Latitude points and a currently empty geog column

Any idea how to  create the geography column either from the existing geography column or from the Longitude, Latitude (Stored digitally not DMS ) columns?

thank you
1
I have created a view in PosgreSQL with the following update rule

CREATE OR REPLACE RULE "_UPDATE" AS
    ON UPDATE TO custom.qty_brk_price DO INSTEAD  UPDATE inventory_price_matrix SET amount = new.amount
   FROM inventory_price_matrix ipm
     JOIN custom.qty_brk_price ON ipm.part_no::text = qty_brk_price.part_no::text
  WHERE inventory_price_matrix.part_no::text = new.part_no::text AND inventory_price_matrix.whse::text = '00'::text AND inventory_price_matrix.promo_code::text = 'BV_QTYBRK'::text AND inventory_price_matrix.amount_type::text = 'P'::text AND inventory_price_matrix.uom_code::text <> ''::text AND qty_brk_price.qty_brk_price = old.qty_brk_price AND inventory_price_matrix.min_qty = old.min_qty;

Open in new window


It works to run the following update query on the view in PostgreSQL but not in Microsoft Access when running the same query

UPDATE custom.qty_brk_price SET amount = 139
WHERE part_no='10006';

Open in new window


Can someone help on why that same query would not work in Access when linking to that view in PostgreSQL?

In Access I get this error
access-error.jpg
0
Hi,
 I am using PostgreSQL database for developers.Now I want to migrate the PostgreSQL database to SQL lite in windows 7.
 what should i do? Please suggest me.
0
Hi
I'm running PHP version 5.3.5 on a standalone (No internet) fedora 12

I need to build a search web page to query a PostgreSQL database I can't use PDO because the driver is missing

pg_connect works so how do I write a select query that can take 1 or more user parameters from a front end form and loop through the results

At this stage I'm just trying to work out how to query the database safely  eg bind the user parameters to prevent SQL Injection  

I get SQL syntax error on the where clause I think the array isn't being passed into $1


syntax error at or near "," line XX:  name like $1 , array("Trevor%")





<?php
error_reporting(E_ALL);

// Connect to a database named "mary"
$dbconn = pg_connect("dbname=mary") or die("Cannot connect ".  pg_last_error()) ;

$name = "Trevor%";  // simulate $_POST 

$SQL = <<<SQL
Select name,age,address from my_table where name like $1" , array("$name"));
SQL;
 // actual query is over multi lines

$results= pg_query($SQL) or die("Cannot connect ".  pg_last_error()) ;

if($results){
// loop through results building HTML
  while($row = pg_fetch_array($result, NULL, PGSQL_ASSOC)){
            echo "<p>Name:" . $row['name'] . "age: " .$row['age'] .  "address:" . $row['address'] . "</p>";
  }
}// end if results
else{
      echo "<p>No Data </p>";
}

Open in new window

0
I have a VB6 running against a cloud database
When I tried pg_dump  on a local postgre database it worked  fine

When i tried it against a cloud  postgreSQL database the connection went through and then i got the error attached

What could be the reason ??

Is there a way to start pg_dump from within a vb6 application without running a batch file
I will appreciate the syntax for this if it exists

Thanks

O.A.  Oluwole
pg_dumperror.PNG
0
Hi Team,

         I am using PostgreSQL using PGadmin 4 for Backup &restore. I can able to take backup easily using backup.And also restore the backup file to new database.

       But I can't able to overwrite database restore.If i try new database restore successfully completed.If i restore backup file to old database i get a error.I was using pg admin and lot of command also for restore.Anyone can you please help me for this issue. I am using windows 7 32 Bit.
0
I have 2 tables in posgreSQL something like this

table_1

id,text1,text2,text_3
1,99,012345
2,999,023456

table_2

id,text3
1,099/12345
2,999/23456 

Open in new window


I need to link the 2 tables together  so on
table_1
if text1 is 2 digits I need to add  a leading zero = temp_text1
and remove the leading zero to text2  = temp_text2
without altering the text in text1 or text2 as  these are linked to another tables

then run an update command

update table_1 set text3 = concatenate(temp_text1, '/', temp_text2)  

Text3 in both table should then be the same format

xxx/xxxxx  or 123/12345
0
I am running a small subnet which  has several servers performing their varied tasks, one of which is a a PostGres SQL box. This small group has a management server which has two nics - one on the primary net and the other on the "internal".

The two servers in question we will call "Jump" and "PG"

Jump has an ip address of 10.10.10.10 for the primary net and an address of 192.168.20.10 for the internal net.
BG has an ip address of 192.168.20.40

My iptables rules are as follows:

    iptables -A INPUT -p tcp -m state --state NEW,ESTABLISHED -m tcp --dport 5432 -j ACCEPT

    iptables -A FORWARD -d 192.168.20.40/32 -p tcp -m tcp --dport 5432 -m comment --comment "Forward inbound PGAdmin traffic to postgres server" -j ACCEPT

     iptables -t nat -A PREROUTING -i eth0 -p tcp --dport 5432 -j DNAT --to-destination 192.168.20.40:543

     iptables -t nat -A POSTROUTING -s 192.168.26.44 -o eth0 -j MASQUERADE

PGAdmin is configured to address the Jump server on port 5432 and when attempting contact, the traffic arrives to the PG server and returns to my workstation but with a hitch.
     Watching tcpdump on the linux boxes and Wireshark on the Windows box, I am able to see the tcp stream initiate at my workstation travel through the jump box and arrive at the PG box.  When the packet has arrived at the PG box, the source is my workstation.
          IP 10.10.10.11.54564 > 192.168.20.40.5432: Flags [S]
     The PG box sends a reply to my workstation:
         …
0
Hi
My VCenter server service on VMware virtual appliance 5.5 keeps stopping. upon investigating it looks like the database has taken all the disk space (99%).

I have followed this article and it only reclaimed a couple of gigs as now showing (97%). see screen print attached.

https://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2056448

Can anyone tell me how I can reclaim more disk space please?

Thanks
disk.PNG
0
NEW Veeam Backup for Microsoft Office 365 1.5
LVL 1
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

I have a cloud database
The database  is postgresql and the operating system is Linux Unbuntu 16.04
The server has email configured

Each time i log in I get the attached message which suggest to me
that the system checks for mail and cant  find any mail . Thats why
it reports no mail in the message

My requirement is that the loggin name of the user logging in should be sent to
my email address  techsupport@datatronicsnig.com

What modification do i need to do in my login script to achieve this

How can i achieve  this functionality  for every login

O.A. Oluwole
login-message.PNG
0
We support a system where custom fields in the system are stored as hstore fields. Like this

select query
select udf_data from inventory

Open in new window


""LocOver"=>"", "freight"=>"0", "3M Codes"=>"", "V_Partno"=>"110SP-60", "brokerage"=>"0", "Dentsply C"=>"""

Here is the data for one record. LocOver, freight, 3M Code, V_Partno, brokerge, Dentsply are the field names in the system

I want to update

I want to update the udf_data column with the values from inventory.part_no column from the udf_data V_Partno

So the update statement should be like this:

update customers
set udf_data = misc1
where udf_data ?| '{cust_type}'

But the hstore has a particular update statement like this:

update customers set udf_data = udf_data || '"cust_type"=>"test"'::hstore

Which does not work. Can anyone help?
0
Hello Experts,

I have a webpage and I need to get the data from a postgres db.  I will probably hire someone to actually do this, but I'd like to understand the process prior.  I think that I have to use node.js to get the data from the database.  Does that sound right?  Or is there a better way to get at the data?
0
I have a PosgreSQL database where I have an array field with 4 values.  This query will extract one value at the time
select public.addresses.address[1] from public.addresses

Open in new window


I'm linking the table to an Access database and the array I have above does not work there. How can I do this in Access?

Here is are my results from PostgreSQL and this is what I want to do in Access
Image-39.jpg
Image-38.jpg
0
The query is as below.
The attached file shows the existing worksheet with the columns that are output from this query.
The attached file also shows the desired worksheet, with some columns removed and the postcode column included twice, the extra postcode column to be included as the first column.
CREATE OR REPLACE VIEW ADDRESS_VIEW

AS

SELECT
AD.ADDRESS_DETAIL_PID as ADDRESS_DETAIL_PID,
AD.STREET_LOCALITY_PID as STREET_LOCALITY_PID,
AD.LOCALITY_PID as LOCALITY_PID,
AD.BUILDING_NAME as BUILDING_NAME,

AD.LOT_NUMBER_PREFIX as LOT_NUMBER_PREFIX,
AD.LOT_NUMBER as LOT_NUMBER,
AD.LOT_NUMBER_SUFFIX as LOT_NUMBER_SUFFIX,

FTA.NAME as FLAT_TYPE,
AD.FLAT_NUMBER_PREFIX as FLAT_NUMBER_PREFIX,
AD.FLAT_NUMBER as FLAT_NUMBER,
AD.FLAT_NUMBER_SUFFIX as FLAT_NUMBER_SUFFIX,

LTA.NAME as LEVEL_TYPE,
AD.LEVEL_NUMBER_PREFIX as LEVEL_NUMBER_PREFIX,
AD.LEVEL_NUMBER as LEVEL_NUMBER,
AD.LEVEL_NUMBER_SUFFIX as LEVEL_NUMBER_SUFFIX,

AD.NUMBER_FIRST_PREFIX as NUMBER_FIRST_PREFIX,
AD.NUMBER_FIRST as NUMBER_FIRST,
AD.NUMBER_FIRST_SUFFIX as NUMBER_FIRST_SUFFIX,
AD.NUMBER_LAST_PREFIX as NUMBER_LAST_PREFIX,
AD.NUMBER_LAST as NUMBER_LAST,
AD.NUMBER_LAST_SUFFIX as NUMBER_LAST_SUFFIX,

SL.STREET_NAME as STREET_NAME,
SL.STREET_CLASS_CODE as STREET_CLASS_CODE,
SCA.NAME as STREET_CLASS_TYPE,
SL.STREET_TYPE_CODE as STREET_TYPE_CODE,
SL.STREET_SUFFIX_CODE as STREET_SUFFIX_CODE,
SSA.NAME as STREET_SUFFIX_TYPE,

L.LOCALITY_NAME as LOCALITY_NAME,

ST.STATE_ABBREVIATION as …
0
Hi Team,

I am having a Python script which insert data into PostgreSQL from XML file. However, we have updated the python version to 3.6.1 it does not add any data into PostgreSQL database and does not throw any error message on command line screen.
Can you please suggest what's goes wrong in the below code...
#/usr/bin/python3.6.1

# import modules
import sys
import psycopg2
import datetime


#import cursors
now = datetime.datetime.now()
# current data and time
dt = now.strftime("%Y%m%dT%H%M%S")

# xml tree access
#from xml.etree import ElementTree
import xml.etree.ElementTree as ET

# incremental variable
x = 0
with open(sys.argv[1], 'rt',encoding="utf8") as f:
    	#tree = ElementTree.parse(f)
		tree = ET.parse(f)
# connection to postgreSQL database
try:
    conn=psycopg2.connect("dbname='####' user='postgres' password='XXXX'")
except:
    print ("Hey I am unable to connect to the database.")
cur = conn.cursor()
# access the xml tree element nodes
try:
	for node in tree.findall('.//tu'):
		src = node.find('tuv[1]/seg')
		tgt = node.find('tuv[2]/seg')
		
		source = src.text.encode("utf-8")
		target = tgt.text.encode("utf-8")
		
		#query to database table
		query = "INSERT INTO MT_DB (SRC_LOCALE,SRC_DATA,TGT_LOCALE,TGT_DATA,DOMAIN,DATETIME,TYPE) VALUES (%s, %s, %s, %s, %s, %s, %s)"
		data = (sys.argv[2],source,sys.argv[3],target,sys.argv[4],str(dt),sys.argv[5])

		#for locale swap with data
		#data = 

Open in new window

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
>