Questions tagged [postgresql]

PostgreSQL is an open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact version of Postgres when asking questions. Questions concerning administration or advanced features are best directed to dba.stackexchange.com.

0
votes
0answers
10 views

how to create date

how to create date format yyyy-mm with postgresql11 CREATE TABLE public."ASSOL" ( id integer NOT NULL, "ind" character(50) , "s_R" character(50) , "R" character(50) , "th" ...
0
votes
0answers
9 views

query to check whether IP address falls in range of the startIP and EndIp columns in the table

I need to stop some IP addresses from accessing my app. I need to write a query using postgres to check whether an IP(coming dynamically) falls in any range between startip and endip stored in the db. ...
0
votes
1answer
15 views

Change Position of Existing column in Rails

I have one Column in my Table, i want to change its position from last to second, can anyone please help me to do that. thanks in Advance.i have idea about after: column_name, but its apply while ...
0
votes
2answers
13 views

Get the body of a stored procedure from PostgreSQL using JDBC

I can't get a stored procedure's body from Java code using JDBC. Which methods should I use to solve this? I found the metadata of all stored procedures using this code: ResultSet resultSet1 = ...
0
votes
1answer
12 views

SQLAlchemy - Query database for one column based on min of another column grouped by a third value

I'm dabling with databases and am using SQLAlchemy in python with a postgres backend. I have a schema defined as: from sqlalchemy.dialects.postgresql import UUID, TIMESTAMP from sqlalchemy.ext....
0
votes
1answer
24 views

Native query on Sping Boot: Mixing of ? parameters and other forms like ?1 is not supported

I'm running a native query on spring boot jpa with Postgresql/PostGIS. The query is running fine on postgresql: WITH data AS (SELECT '{ "type": "Point", "coordinates": [102.0, 0.5] }'::jsonb ...
0
votes
0answers
16 views

Postgres sql Inner join and Delete query both are returning different results

I'm using Postgres Inner join query to select some data from a table and after selecting that data I'm trying to delete that data using Delete query. But both of the query are producing different ...
0
votes
0answers
14 views

log4net AdoNetAppender Npgsql throws 42601: syntax error at or near “:”

I got a problem trying to write my log4net logs to Postgres Database. .NET 4.6.2 project, PostgreSQL 11, Npgsql 4.0.7 My config: <log4net debug="true"> <appender name="...
0
votes
0answers
19 views

How do I update postgres table with a running total of amount

I am using Postgres. I can't UPDATE the table with "Cum_sum". In the "cum_sum" column , I want to update the table with a permanent column, "Cum_sum". "Cum_sum" is a running total of Amount. This ...
0
votes
0answers
11 views

How to optimize handling `bytea` in Postgres

I'm working on a Bitcoin Indexer that dumps blockchain data into Postgres. I had a previous DB schema design that used SERIAL IDs, but I'm now playing with db schema in which primary keys/IDs are ...
0
votes
0answers
14 views

Modify my code so that it accomplishes one more function

SELECT p.personname,d.subjectname,d.subjectID,e.grade from exams e INNER JOIN students s on s.studentID = e.studentID INNER JOIN persons p on p.idpers = s.idpers INNER JOIN subject d ON d.subjectID = ...
0
votes
0answers
15 views

How to fix using loopback 4 (translate rest to graphql) : OPTIONS http://localhost:3001/graphql 405 (Method Not Allowed)

I am connecting the postgresql with loopback 4 and call the API on frontend using axios + restful and it was sucessfully done. But when I tried to connect postgresql with loopback 4 and call the API ...
0
votes
3answers
48 views

Is there any way where I can do ALIAS reference other column value?

I am trying to figure out how to do ALIAS which referring the value of other column. I am not sure it is possible, but does it acquire some tricks to make it done? How can I perform query something ...
0
votes
1answer
24 views

Select from column including null values

I'm trying to create SELECT that includes various foreign key to list all the rows it has, but there is two foreign key that can be null. bank_id and bbank_id can be null, so with this example it ...
0
votes
1answer
13 views

Cant save data in multiple tables using cakePHP 3.7

Been trying to save data into 2 other tables when saving Fisica entity to the Database, since I am new to cakephp it seems impossible right now, have seen multiple posts, videos, documentations and ...
0
votes
0answers
15 views

POST Request not allowing database long enough time to generate ID

My post request to my rails-api is not allowing adequate time for my secondary ID to generate. When I use setState in the parent container within the axios.post function (addNewLoad function), my ...
0
votes
1answer
17 views

Find running total as value changes

I would like to get a running total as value changes. my table has sequence of values and they keep changing. I need to give output as how many times the value repeated in a sequence. For ex: lets ...
0
votes
0answers
18 views

How to do build a complex query in postgresql

My table called Approvals has following columns: Id, Approvers, PendingAtLevel Sample value of Approvers looks like this [["ash","bash","rash"],["cat","bat","rat"],["boss"]] which is basically a 2d ...
0
votes
1answer
15 views

Reading UUID from result set in Postgres JDBC

I'm using UUID for my id column, I'm looking for a way to retrieve the data inside my Java application. I can't find a method in ResultSet for getting a UUID. How would I go about getting the UUID?
0
votes
0answers
9 views

Will pgjdbc stringtype set to VARCHAR block the use of an index for a column of type TEXT?

The pgjdbc connection property stringtype can be either VARCHAR or UNDEFINED: Specify the type to use when binding PreparedStatement parameters set via setString(). If stringtype is set to VARCHAR ...
1
vote
1answer
19 views

Postgresql - how to combine these two queries

I try to combine these two queries in one. the result of these queries is the number of accepted / rejected applications for a given operator. I want to get such a result - in three column: number ...
0
votes
0answers
32 views

How to support multiple databases (postgresql) with Sidekiq server for the same project?

I want Sidekiq to support multiple dynamic databases for the same project desc "Start sending the client invitation" task "sidekiq:start", [:database] => :environment do |task, args| database = ...
0
votes
1answer
14 views

Django. Change filed type UUIDField to CharField

I had in user model field: class User(PermissionsMixin): id = models.UUIDField(primary_key=True, unique=True, default=uuid.uuid4, ...
0
votes
1answer
14 views

How do I select a postgres Many-to-One relationship as a single row? [duplicate]

I have a many-to-one relationship between Animals and their attributes. Because different Animals have different attributes, I want to be able to select all animals with their attribute name as a ...
0
votes
1answer
22 views

How to get repetitive records in aggregate field

I have prepared a query: SELECT building_custom."Location Code", building_custom."RUD Site Name", string_agg(concat_ws('-',subnet.range_begin,subnet.range_end),',') FROM building INNER JOIN ...
0
votes
1answer
14 views

Persisting a single, static, large Postgres database beyond removal of the db cluster?

I have an application which, for local development, has multiple Docker containers (organized under Docker Compose). One of those containers is a Postgres 10 instance, based on the official postgres:...
1
vote
2answers
23 views

Postgresql pattern matching

I want to get data in which name and surname start with 'Ib', but only name starts with 'Ib' in the resulting rows. Here is my query: select* from student where concat(name,surname) like '%Ib';
-1
votes
2answers
29 views

What are the countries in which movies have been released all the years (for which we have records)?

I have the following table called Movies MovieID | MovieName | LaunchYear | ProducerID | DirectorID | Country | Budget As it says in the title, I have to select the countries that have had a movie ...
0
votes
1answer
19 views

Column added to table but does not show up

I'm new to Postgres. I created a database and a table, then I added columns to the table: ALTER TABLE NDQ01 ADD COLUMN Date, date To verify, I did: \d+ ndq01 and the output is: Table "public....
0
votes
1answer
16 views

ON CONFLICT DO NOTHING doesn't stop query continuing with next insert statement

I'm using WITH to run two INSERT statements (table A & table B). The first INSERT statement has an ON CONFLICT CLAUSE, if there is a duplication in A.name, DO NOTHING. The second statement relies ...
0
votes
1answer
22 views

Recursive call in stored procedure raises error

I have created a simple tree relation in postgres. A father node can have n-nodes. To get all childs from a father node I want to write a stored procedure that calls itself recursivly. This is the ...
0
votes
1answer
27 views

An easy way to write INSERT INTO table VALUES (value1, value2, …valueN)

I need to insert values from an array using PHP into my database. The array has 40 positions, each position is a value I have to insert into a table of my database. I need a quick way to write it ...
-1
votes
0answers
12 views

Unclear how to use interval function to reference a date in the past

Trying to pull records from before a change in our system to determine if certain wrong smell reasons were more prevalent before or after, using a location to narrow results. I'm trying to select ...
0
votes
0answers
18 views

After restarting httpd, app is not able to establish connection with aws RDs postgress server

I have deployed a Django app in AWS elastic beanstalk. The app is running perfectly. Only I am getting error OperationalError at / could not connect to server: Connection refused Is the server ...
1
vote
1answer
21 views

PostgreSQL param binding conflicts with JSONB operators

I'm updating a JSONB field in my postgreSQL database (using PHP Symfony DBAL) but I'm also using parameter-style data binding to remove the risk of SQL-injection. My query looks like this: UPDATE ...
1
vote
1answer
40 views

Concat function in postgresql

I have the below select statement in hive .It executes perfectely fine. In Hive select COALESCE(product_name,CONCAT(CONCAT(CONCAT(TRIM(product_id),' - '),trim(plan_code)),' - UNKNOWN')) as ...
0
votes
0answers
8 views

Postgres “peer authentication failed for [username]”

I installed Postgres 10 on Ubuntu 18.04 this week and created a database. I assigned my Ubuntu login name as the database role (username). For two days I had no problem issuing the command: sudo ...
0
votes
1answer
20 views

Union from 2 different data sources (same server )

How do I union 2 different data sources? not join- I want to union data ! 1 tablename - from base1 2 tablename - from base2 base1 and base2 from same server (postgrsql) ERROR: cross-database ...
0
votes
2answers
29 views

PostgreSQL: select column and exclude from group

This question has probably been asked in different formats, but I could not find the answer. I have table orders date, quantity_ordered, unit_cost_cents , product_model_number, title I would like ...
0
votes
0answers
7 views

How to unnest/explode/flatten the comma separated value in a column in Amazon Redshift?

I am trying to generate a new row for each value in col2. As the value is in string format, I need to wrap it in double quotes before using any Redshift json function on it. Input:=> col1(int) ...
0
votes
0answers
14 views

How to set schema with node-postgres

I'm trying to query against a schema called DOCUMENT in my postgres db using node-postgres. I can't seem to get a query to run against the specified schema. This query runs fine directly against ...
1
vote
0answers
12 views

What's the difference between loginTimeout, connectTimeout and socketTimeout in pgjdbc

In pgjdbc we have: loginTimeout connectTimeout socketTimeout cancelSignalTimeout But it isn't clear to me what's the difference (when are they applied) between loginTimeout, connectTimeout and ...
1
vote
2answers
29 views

Postgresql ROUND function

I try to round this query but my result has 10 decimal places. I've tried DECIMAL() function, CAST()ROUND() and its still doesn't work. I need 2 decimal places. select type, count(id) filter ( where ...
0
votes
0answers
14 views

How to handle “on conflict do update” cases in Jpa/Hibernate?

I'm trying to persist logging information in an PostGreSQL database using Hibernate (and Spring Data). In this particular case, I need to store simple user log in events. The schema: CREATE TABLE ...
2
votes
1answer
19 views

What are the bennefits of prepareThreshold = 5 in pgjdbc?

The prepareThreshold in pgjdbc have the following definition: Determine the number of PreparedStatement executions required before switching over to use server side prepared statements. The default ...
0
votes
1answer
17 views

Transferring data from MYSQL database to PostgreSQL

I have a MYSQL database(db_mysql) whose data I want to transfer to another PostgreSQL database(db_psql) which is initially empty. I tried using the command pgloader mysql://root@localhost/db_mysql ...
0
votes
1answer
14 views

how to have a trigger work only for certain columns (they are chosen by their id) after updating

I have two tables.Tickets which has the columns: ticketid,startdate , enddate and transactions which has the columns: transactionid, ticketid (fk to tickets), ticketcost.I want to create a trigger on ...
0
votes
1answer
15 views

Ecto query to grab all values that satisfy all values in array_aggregator not just any?

Wonder if someone can help me with an array aggregator issue I’ve got a query that does a join using a joining table then it filters down all values that are inside a given array and filters out ...
0
votes
0answers
11 views

How do I insert NULL with psycopg2 using _conform_ asis and fstring?

I'm new to Python and Psycopg2. I've spent the last few hours googling various pages in this area but cant find anything that matches my exact problem. There are other Stackoverflow questions that are ...
0
votes
0answers
7 views

cascade delete of child in another child in postgres

How to delete rows in table A if deleted rows in table B, which is connected to table A through table C in postgresql? CREATE FUNCTION delete_training() RETURNS trigger AS $$ BEGIN delete ...

http://mssss.yulina-kosm.ru