Tagged Questions
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 ...
0
votes
1answer
9 views
How to loop through array in postgresql?
In this postgressql function i created a array by spliting a string. Now i want loop on this array and do some processing on it.
Function:
CREATE OR REPLACE FUNCTION getAllFoo() RETURNS character ...
0
votes
0answers
5 views
AWS Elastic Beanstalk in a VPC with Amazon RDS
I want to run a simple Ruby Rack app (a REST API) that takes Internet requests (from iPhone clients), talks to a PostgreSQL database, and responds with JSON.
How exactly should I set this up on ...
0
votes
0answers
7 views
Import Sequelize models back into database
I lost a Postgres installation. I thought it was no big deal because I had a directory full of Sequelize models. I can't find anything on google about how to get those models back into a new instance ...
0
votes
0answers
17 views
psql: could not connect to server on Linux
I installed PostgreSQL on Linux, how is indicated in Debian Wiki
After installation I run the following commands:
# su - postgres
$ psql
And I got the following error:
psql: could not connect ...
0
votes
1answer
11 views
Issue with JPA configuration
I'm having an issue with my project configuration. I'm using hybernate + postgres (Running in eclipse + jetty)
My colleagues can run the project without problems, but I'm unable to and we can't find ...
0
votes
1answer
10 views
Eliminate duplicate entries in Hibernate tables of one-to-many relationship reference
I am currently working with a program that receives three number values (companyName, groupName, hostName) each time you run the program. Each time I received the three values, I grouped them into one ...
2
votes
1answer
12 views
PostgreSQL AFTER INSERT trigger, and referencing the inserted row
I have an INSERT trigger in PostgreSQL that I'm trying to have join the inserted row on another table, and then insert the result in a third table. Let's call the original table, that the INSERT ...
0
votes
1answer
19 views
SELECT rows by to many relationship ids
I have a pretty basic table setup:
CREATE TABLE rooms (id UUID PRIMARY KEY, room_type VARCHAR(255));
CREATE TABLE subscriptions (room_id UUID, user_id UUID);
CREATE TABLE users (id UUID PRIMARY KEY, ...
0
votes
1answer
8 views
Query rows for matching JSONB column where key ends with a name and key value is a specific value
Given the following rows with a jsonb column details. How do I write a query so that records where the key name ends with _col with value B are selected. So records with ids 1, 2.
id | details
1 | ...
0
votes
0answers
7 views
Run sequelize model hooks when seeding
I'm currently trying to seed a postgresql database with sequelize, I have hooks declared on my model that work just fine when creating separate records (e.g. tests)
Does the data in my seed file need ...
1
vote
0answers
12 views
How to fill list properties of objects from SQL while maintaining small result sets?
Problem description
For a (PDF-)report in a Java application I query various data from a PostgreSQL 9.6 database. The data passed to the report also contains lists which in turn contain other lists. ...
0
votes
2answers
13 views
Populate column with query results in Postgresql
Table columns structured like:
longitude, latitude, gid, Hash
-78.885636, 36.854, 1, empty
Using PostgreSQL 9.4 and trying to update column Hash with results of a geohash function:
SELECT ...
0
votes
1answer
15 views
Springboot(new version) getting javax.sql.DataSource' that could not be found
I am trying to connect my springboot application to postgres database, but i am getting an error.
pom.xml
<parent>
<groupId>org.springframework.boot</groupId>
<...
0
votes
1answer
6 views
How to implement a purge os soft-deleted records in Postgres?
In my Postgres 9.4 database, I have the following trigger / function, which implements a "soft-delete" functionality:
ALTER TABLE my_schema.my_table
ADD COLUMN delete_ind integer
CREATE OR REPLACE ...
0
votes
0answers
14 views
Psycopg2 - Reason: Image not found in Django project running PostgresApp
I have a Django project running a PostgreSQL database. I run the postgresapp for this. I installed psycopg2 using pip and ran the command:
python manage.py migrate
I got the following error:
...
0
votes
1answer
11 views
How to Check if a Foreign Key Exists on a Specific Table in PostgreSQL
I have a foreign key named user__fk__store_id that was supposed to be created on the user table.
However, I made a mistake and instead have it created on another table I have named client.
My ...
-2
votes
0answers
16 views
What constraints cannot be captured in a standard ER diagram. Any examples please?
Trying to design the Mondial database that covers key information about countries and geographic features for global statistics (Countries, Economy, Politics, Population...). I am having hard time ...
0
votes
0answers
8 views
osm2pgrouting - Parsing data not well-formed (invalid token)
I need import open street maps data into my PostGIS database over osm2pgrouting tool. But when I run command, tables was created but without any data. Can you tell me what am I doing wrong? I have ...
1
vote
1answer
12 views
Coalesce and Concat in the same statement, Postgres
I am trying to concat an individual's first and last name together but coalesce a team name when there is a null value. Unfortunately my syntax is returning a SPACE, so coalesce does not recognize it ...
0
votes
0answers
15 views
UPDATE a non primary key column - duplicate key value violates unique constraint
I need some help. I want to update a column value for all records. This column is new (created with alter table) and has no constraint. But, when executing:
UPDATE mytable SET active = false
The ...
0
votes
1answer
30 views
How this function can return a boolean in SQL with extract
I create a function to return true if I can't insert a note for a student if he is minor.
CREATE OR REPLACE FUNCTION mineur (note REAL, numeutu INT)
RETURNS BOOLEAN AS $$
SELECT $1 IS NULL OR
SELECT ...
0
votes
1answer
8 views
Windows 7 - Error using PHP + ODBC for PostgreSQL
I'm trying to connect PHP to a PostgreSQL database via ODBC.
I'm doing the same for MySQL databases and everything works OK.
I'm using the following code:
$connect = odbc_connect('PT_blala','','');
$...
1
vote
1answer
24 views
Splitting table PK values into roughly same-size ranges
I have a table in Postgres with about half a million rows and an integer primary key.
I'd like to split its entire PK space into N ranges of approximately same size for independent processing. How do ...
0
votes
3answers
46 views
SQL - Return a title with the highest number
I want to return from my database the title with the most eps.
With the following code I do get all the titles back.
SELECT titel, MAX(aantalafleveringen) FROM imdb.tvserie GROUP BY titel;
Hope that ...
0
votes
0answers
13 views
Set Hosting on postgrSQL 9.6 for windows 32 bit
From many days i'm trying to set the hosting on my postgreSQL. I try many solution but no one works. The version of my postgres is 9.6 for windows 32 bit with pgAdmin 4.
I've set in my pg_hba.conf ...
0
votes
2answers
17 views
How to use update from select in postgreSQL?
update users
set users.przetwarzanie = historia_panel.zmiana_z
from historia_panel
where
users.user_number like historia_panel.user_number and
historia_panel.zmienna_zmieniana like '%...
0
votes
1answer
16 views
join on mutated column postgresql
I have two tables with different date formats, and I want to mutate on of the columns to equalise the formats, is there any way to join on a mutated a column ?
The query :
join table_b on table_b....
0
votes
1answer
13 views
How to insert a value to postgres database column which contains double quotes and triple quotes
I want to insert a query string into a Postgres database column in the following format
{"enrolled_time":'''SELECT DISTINCT enrolled_time AT TIME ZONE %s FROM alluser'''}
I try this:
UPDATE reports ...
0
votes
0answers
16 views
insert or update if exist postgres 9.4 with jdbc template [duplicate]
I already know that PostgreSQL 9.5 and newer support INSERT ... ON CONFLICT UPDATE, i.e. upsert. Although, I can't afford to upgrade version I have.
So i try this in pgAdmin:
do $$
begin
insert ...
-1
votes
1answer
10 views
What locks are added when sql is executed in postgresql?
When I perform insert, delete, and update operations，What locks are added in the table?
i want to konw select insert delete update vacuum create index.
0
votes
3answers
21 views
Updating column values from one table to another
I have three tables
X [id,event_time]
Y [x_id,z_id] -- here x_id is id from X and z_id is id from Z
Z [id,event_time]
Now I want to update the values of X[event_time] with Z[event_time]. Is there ...
1
vote
3answers
28 views
Get rows that no foreign keys point to
I have two tables
CREATE TABLE public.city_url
(
id bigint NOT NULL DEFAULT nextval('city_url_id_seq'::regclass),
url text,
city text,
state text,
country text,
common_name text,
...
1
vote
2answers
32 views
PostgreSQL find duplicate field
I have table like:
ID PERSON UPDATE
1 132 1.1.2015
1 132 2.1.2015
3 152 4.5.2013
9 152 1.2.2013
I need to count persons which have different id's which is greater than 1.
0
votes
1answer
15 views
PostgreSQL: control reached end of function without RETURN
I'm trying to create a function which returns either X rows or 0 rows.
This is my code:
...
IF var != '' THEN
RETURN var;
ELSE
RETURN NULL;
END IF;
END;
$$
LANGUAGE '...
0
votes
1answer
19 views
Populating column values from another table in postgreSQL
I have 3 tables with the column as per below :
1) map(creativeid (integer), campaignid integer))
2) campaigns(id (integer), last_edited (date))
3) creatives(id(integer), last_edited (date))
map ...
1
vote
1answer
17 views
PostgreSQL BETWEEN selects record when not fulfilled
Why does this query returns a record?:
db2=> select * FROM series WHERE start <= '882001010000' AND "end" >= '882001010000' ORDER BY timestamp DESC LIMIT 1;
id | timestamp | ...
0
votes
2answers
22 views
PostgreSQL JSONB result to MyBatis HashMap
I have a super simple table test e.g.
create table test (
id serial primary key,
status varchar (10)
);
insert into test (status)
values ('ready'), ('ready'),
('steady'), ...
0
votes
2answers
32 views
Speeding up multiple left outer join SQL query
I would like some help speeding up my SQL query with multiple joins. What I am trying to retrieve is equivalent to all user fields with their latest corresponding page, item and club. The below ...
1
vote
1answer
24 views
Postgres 9.6 - Updating text stored inside JSONB
I have a database that contains lots of blobs of JSON that reference lots of offsite Images.
The table:
CREATE TABLE vendors (
id uuid NOT NULL,
name character varying(255) NOT NULL,
...
0
votes
0answers
17 views
Postgres Listener - Pull last inserted/deleted row
I'm trying to create a database listener (postgres) in Java. Every time a user inserts or deletes an entry of a table I want to receive the value that was inserted or deleted, respectively.
For that ...
0
votes
1answer
28 views
sqlalchemy core with labels and joins postgres
Using sqlalchemy expression language, I wish to select ids from 2 table. Hence I need to use labels. How to resolve this problem. I need ids and all other attribute values from the related tables. ...
0
votes
0answers
16 views
PostgreSQL equivalent of Oracle LOG ERRORS clause in MERGE statement
I am trying to covert the below oracle MERGE statement to equivalent in PostgreSQL.
Oracle query:
MERGE INTO dest a
USING source b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET a.code ...
0
votes
0answers
15 views
“Unknown” datatype when using Psycopg2 and unnest
I'm trying to bulk load the data from the UK Companies House csv files into PostgreSQL using python.
I'm converting each row of data into a list of dicts and then using an unnest statement to unpack ...
0
votes
1answer
20 views
'module' object is not callable with sqlalchemy
I'm totally new using sqlalchemy and postgresql. I read this tutorial to build the following piece of code :
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy import engine
def ...
0
votes
0answers
12 views
Hibernate/SQL check references in foreign keys
I have some specific databases that are using partition system from EDB postgres database. Database guys at the moment don't know how to set up foreign keys at database level. Is there any way to turn ...
1
vote
0answers
29 views
Getting users recursively in Postgresql
I have an ordinary user table:
Users: ID-NAME
Then I have a Connections-table:
Connections: ID-MASTER_ID-SLAVE_ID
Here are some users:
INSERT INTO Users VALUES(1, 'Mark');
INSERT INTO Users ...
1
vote
1answer
19 views
How to get data out of a postgres bytea column into a python variable using sqlalchemy?
I am working with the script below.
If I change the script so I avoid the bytea datatype, I can easily copy data from my postgres table into a python variable.
But if the data is in a bytea postgres ...
1
vote
1answer
17 views
How to execute an update statement with where clause having select statement
I have a table (student) with the following schema in postgres 9.5:
id | marks | division | class
1 90 A 1
2 90 ...
0
votes
1answer
9 views
Connection refused when i create a new Serveur in PostreSQL
When i try the add a new serveur to my PostgreSQL on the pc in my university, i get an error :
could not connect to server : Connection refused the server running on host "localhost" (::1) and ...
0
votes
3answers
18 views
Any handy tool to access mysql in GUI
I would like to find a handy/free GUI tool which can let me manage/view Mysql and other databases(possibly). Any suggestions? Prefer windows platform tools, but Macosx is also OK.
