Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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,

For security reasons, users must query a replica database that is updated by an intermediate replica.
We must ensure performance and that requests do not impact replication. How to proceed ?

Thank you

Regards
0
Get your problem seen by more experts
LVL 11
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Dear all
I have a server where i'm running mysql and postgresql databases based on CentOS 6.8 x86_64. the problem is that the /var directory (where those db are running) is mounted on a 50Gb partition which already full (95%). So, i'm looking for a way to move the content of this partition to a new bigger one where i can remount /var directory. There are 350Gb free remaining on my HDD and it's a RAID1 disk.
Disk-utility-1.jpg
disk-space.jpg
0
Hi All,

   I have a requirement to encrypt a UDF, so the code cannot be seen by anyone, but can execute it.
Is there a possiblity? Please advise.
0
Hello Experts,
I am trying to install MYSQL server on a virtual server that already has POSTGRESQL

and the process fails at START SERVER as shown in the image...

Please your advice
aa.png
0
Hi there,

I have this KB article from Atlassian, and they show a specific PostgreSQL query, but I need to run it against Microsoft SQL server. Anybody able to assist?

The article is: https://confluence.atlassian.com/confkb/some-attachments-or-links-are-no-longer-accessible-after-server-migration-214862724.html

The query itself that is shown is:

insert into contentmigration
select bodycontentid, substring(body from '\\|http://oldServerName/download/attachments/.*/') from bodycontent;


Thanks for any suggestions!
0
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
We have a .NET application that we have developed which mostly runs calculation queries and inserts data to a few tables based on the calculation queries. We want to add a progress bar. How could this be accomplished? The longest part is the functions it runs for the calculation where the data is coming from large tables. Inserting the data is very quick after the calculation. The database is a PostgreSQL
0
Hi,
I have installed postgresql  version 9.6.
But when i give "sudo service postgresql start" command am getting the below error
* Restarting PostgreSQL 9.6 database server                                                                                                                          
* Failed to issue method call: Unit postgresql@9.6-main.service failed to load: No such file or directory. See system logs and 'systemctl status postgresql@9.6-main.service' for details.

Please give me the solution
0
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
[Webinar On Demand] Database Backup and Recovery
LVL 11
[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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
I am trying to convert a SQL Server stored procedure to Post gres procedure but stuck up in the XML portion.  Need help in converting this XML piece of code to post gres code.

EXEC sp_xml_preparedocument @idoc OUTPUT, @ROLES;
                  UPDATE SSO_XREF_USER_ROLE SET ACTV_IND=1 WHERE
                  (CONVERT(VARCHAR,SSO_USER_ID)+CONVERT(VARCHAR,SSO_ROLE_ID)) IN
                  (SELECT CONVERT(VARCHAR,X.SSO_USER_ID)+CONVERT(VARCHAR,X.SSO_ROLE_ID) FROM
                        (SELECT XUR.SSO_USER_ID,SR.SSO_ROLE_ID FROM SSO_XREF_USER_ROLE XUR
                              JOIN SSO_ROLE SR ON XUR.SSO_ROLE_ID=SR.SSO_ROLE_ID WHERE
                              SR.SSO_ROLE_NM IN
                              (SELECT RoleName FROM
                                    OPENXML(@idoc, '/DocumentElement/UpdateRoles', 2)
                                          WITH (RoleName  varchar(100),
                                                      RoleStatus  numeric(1,0))
                                                      WHERE RoleStatus=1))AS X)

Not sure what is the equivalent of sp_xml_preparedocument in Postgres procedure
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
For a database hosted elsewhere we have asked for a dump to use locally with GIS.
The file we have from the dump has the following in it and fails on the import via SQL query.

COPY catch_methods (name) FROM stdin;
Set Line
Set net
Other
\.

Open in new window



The error that comes up is:
Invalid command \.. Try \? for help

What am I missing in regards to this, there are multiple large entries with the above just being a short one.
0
Hi,

We are migrating from oracle to postgresql. And we faced problems while rewriting oracle procedures to postgresql functions.

We get all the data required for the endpoint from single procedure to reduce round trips to database.

for example if we want show user posts

create or replace procedure user_posts(userId IN NUMBER, noOfPosts OUT NUMBER, posts OUT sys_refcursor, postComments OUT sys_refcursor)

here we are getting noOfPosts, posts and postComments resultset from single procedure.

Searched for the equivalent procedure in postgresql but not found.

While searching for the answer I found returns table. But using returns table I am not able to achieve this. I am able to get only one of these three out parameters.

Because of this constraint now we are using three queries for noOfPosts, posts and postComments.

How can we achieve same with postgresql.

Thanks.
0
Receive 1:1 tech help
LVL 11
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Hi,
   we have postgresql function which accepts tags_type composite type as a input parameter.  
How can we pass the values to the function using jpa.

create type tags_type as(tag text, tag_url text)


CREATE OR REPLACE FUNCTION public.create_article(title text, tags tags_type[])
 RETURNS integer
AS $function$
DECLARE
	id int2;
	t tags_type;
begin
	
	insert into articles(a_title)
	values(title);

	id := currval('article_id_seq');

	FOREACH t in array tags
	loop
		insert into atricle_tags(at_id, at_tag, at_url_name)
		values(id, t.tag, t.tag_url);
	end loop;

	return id;
end;
$function$
LANGUAGE plpgsql

Open in new window

0
Hi,

 I am trying to get refcursor from postgresql procedure using spring data jpa. I am getting below error.

org.springframework.orm.jpa.JpaSystemException: No Dialect mapping for JDBC type: 1111; nested exception is org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111

Here data got inserted in database but not able to get the data from.

CREATE OR REPLACE FUNCTION public.register(email text, password text, name text)
 RETURNS refcursor

AS $function$
DECLARE
	userInfo REFCURSOR;
	userId int4;
begin
	insert into "users"(u_email, u_password, u_name)
	values(email, password, name);
	
	userId := currval('user_u_user_id_seq');

	open userInfo FOR SELECT * FROM users WHERE u_user_id = userId;

	return userInfo;
end;
$function$
 LANGUAGE plpgsql

Open in new window



@Override
public User register(User user) {


StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery(REGISTER_PROCEDURE)
        .registerStoredProcedureParameter(1, String.class, ParameterMode.IN).setParameter(1, user.getEmail())
        .registerStoredProcedureParameter(2, String.class, ParameterMode.IN).setParameter(2, user.getPassword())
        .registerStoredProcedureParameter(3, String.class, ParameterMode.IN).setParameter(3, user.getName());

storedProcedureQuery.execute();
List<User> list = storedProcedureQuery.getResultList();


return list.get(0);
}

Open in new window


@Entity
@Table(name = "users")
public class User implements Serializable{

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "u_user_id")
    private long userId;

    @Column(name = "u_email")
    private String email;

    @Column(name = "u_password")
    private String password;

    @Column(name = "u_name")
    private String name;


}

Open in new window

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

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
>