Tagged Questions
-1
votes
0answers
16 views
How to get a preview for an image using postgresql?
I'm using image tables in my database. I want to create a preview for the fetched image using PostgreSQL query.
What is the syntax for creating a preview?
1
vote
1answer
26 views
Database config php always show connection failed
Here's my config code:
<?php
$host="10.128.0.128";
$user="postgres";
$password="postgres";
$port="5432";
$dbname="items";
$link= pg_connect("host=".$host." port=".$port." dbname=".$dbname." user=...
0
votes
2answers
34 views
Don't have permission to access /phppgadmin/ on this server
I can access my postgres database in phppgadmin with http://localhost/phppgadmin/
but when i try to open my database in phppgadmin with my ip address http://10.128.0.128/phppgadmin/ it can't work :
...
0
votes
0answers
26 views
mybatis collection resultset maaping issue. Getting empty collection resultset
I'm new to mybatis and facing an issue with getting a collection in a resultset. The resultset is defined as below:
<resultMap type="User" id="userMap">
<id column="id" property="id" ...
0
votes
1answer
39 views
postgresql - data divided by months
I have following tables
and
I receive the Count of user_id per month where the registration date and login date should be different. For example :
However, I need to receive the count of unique ...
-1
votes
1answer
15 views
Select only group of string which ends with number series 1,2,3, instead of 01,2,3.
I have a requirement where I need to search for the number in the later part of the string.
Now I can have 3 kind of instances.
1. String ending with number starting from 0 , 1 ,2 ,3... so on
2. ...
-6
votes
1answer
57 views
Postgres equivalent for Oracle's DBA_FREE_SPACE and DBA_DATA_FILES
This is the sql query. I need to convert into postgres query
SELECT a.tablespace_name,
a.maxbytes,
a.mbytes,
(a.maxbytes - a.mbytes),
ROUND(((a.maxbytes - a.mbytes) / a....
1
vote
2answers
42 views
Syntax Error creating function in SQL for Postgres 9 database
I am trying to create the following function in a postgres_9 database:
CREATE OR REPLACE FUNCTION getUser(IN user_id INT8)
RETURNS TABLE(id INT8, nom TEXT, prenom TEXT)
AS $$
BEGIN
SELECT ...
1
vote
2answers
48 views
Postgres queries for JSON Array
I have a table called cust_data which stores id and JSON object. I want to write postgres select statements to fetch:
select all id's where "gender": "Female" is not present in persons array [this ...
2
votes
1answer
25 views
Update a table from another table
I have got 2 tables "animal_breeds" and "ztmp.ztmp_509810_anims_out". In "animals breed" every animal has key and breed name and percentage. Few animals might have 2 different breeds with different ...
0
votes
1answer
80 views
How to configure PostgreSQL clustering (Active/Active) in Ubuntu 12.04 LTS?
How to configure clustering (active/active) in PostgreSQL 9.1 ? My current configurations are listed below:
RAM : 8Gb,
O.S : Ubuntu 12.04 LTS,
CPU : 4
1
vote
1answer
45 views
postgres trigger to insert value on when condition
I have to write a trigger in postgres
trigger should insert values in rp_resourceUser table.
I have three table rp_user, rp_resourceUser, rp_resourceType they all under public schema
fields in ...
1
vote
0answers
38 views
What is suitable way to avoid shared locks and exclusive locks?
I have sufficiently highload system built with PostgreSql 9.1 and PHP 5.3.
I have to store last login time and ip user each time after user login. When user logins system stores it to Redis and I ...
0
votes
1answer
48 views
Odoo: OSError: [Errno 2] No such file or directory
Trying to re-install Odoo
Did the following steps:
Deleted the previous odoo dir
Deleted previous postgres users and databases, except the user which I was using and that user created databases
Tried ...
0
votes
2answers
70 views
Unable to connect to PostgreSQL through KETTLE-Pentaho
I have pasted below the snippets of Kettle DB Connection Configuration and of PgAdmin PostgreSQL Dashboard.
Please advice what I am doing wrong.
Localhost and Password are right.
Kettle ...
0
votes
2answers
33 views
Stored functions postgresql returning table
A novice when it comes to stored procedures/functions. I have searched Google, Stackoverflow, and Youtube and are finding all sorts of examples that are convoluted, some not in English.
I'm trying ...
1
vote
2answers
55 views
SQL - Get Next and Previous X records
I'm trying to use a query in postgresql that can get the previous and next X number of records based on a starting record ID used for some pagination. Getting only 1 next or previous is not that hard ...
0
votes
2answers
42 views
How to format and insert a character in between a string- Postgresql
I have this table.
member_id | phone_number
-----------+--------------
1 | 09151234567
2 | 09904897851
3 | 09196332111
4 | 09957935412
5 | 09251279633
...
0
votes
1answer
81 views
How to create tables with millions of rows with fast performance in PostgreSQL?
I have data that correspond to 400 millions of rows in a table and it will certainly keep increasing, I would like to know what can I do to have such a table in PostgreSQL in a way that it would still ...
0
votes
1answer
41 views
How postgresql manage columns
I want to know the way how postgresql manage columns of table.
Say for e.g
I have created one table that contains 2 fields, so how postgresql manage these columns, table? In how many tables ...
-1
votes
2answers
80 views
Drop column doesn't remove column references entirely - postgresql
I have a table that contained 1600 columns and would like to add more fields in that but as per the database rule the more fields not allowed to be created because the higher limit reached.
So I ...
0
votes
1answer
57 views
create postgresql trigger or function to delete old data from one table and insert into alias table
In PostgreSQL i want to create function and trigger which can delete old data of before 8 day from A table(which contain many rows) and insert this data into one new alias table. and i want to execute ...
0
votes
0answers
30 views
date trunc 6 months
I would like to show 6 months worth of data. For example from the 1st of September to 1st March inclusive.
The code below starts from the 1st of September but finishes on today
date >= ...
0
votes
0answers
31 views
How do I install pg routing to an existing postgresql 9.1 database
I installed postgresql 9.1.12 using sudo apt-get install postgresql-9.1 postgresql-9.1-contrib postgresql-9.1-postgis and created databse "mydb". Then extended the mydb using
psql -h localhost -d ...
0
votes
1answer
70 views
Is it possible to overwrite database records from dump?
I have a dump of PostgreSQL database, which I created with command:
pg_dump database_name > dumpname.sql
I would like to restore database from this dump but I get manny errors "...already exists"....
1
vote
1answer
17 views
Seding data to display as a columns
In a given table; I am querying to display certain columns as below
select an.animals_key,
an.anim_name,
an.sex,
an.date_of_birth,
tt.trait_key,
...
0
votes
0answers
31 views
Update conditionally one OR two field - Postgresql
There are many solutions how to update with condition. But I have found none with updating one OR two fields. I want to update the stopdate if it is older than today. Otherwise only the startdate.
...
2
votes
2answers
33 views
Split values from an interval and group by isoweek - Postgresql
I want to "distribute" or "split" hours from a single task into iso weeks based on a date interval. But I do not have a clue how to do this in Postgresql.
CREATE TABLE task
(id int4, start date, ...
1
vote
1answer
32 views
list column with count aggregation function
Imagine these tables structure
Organization table
=====================
| id | name |
-----+----------------+
| 1 | Organization 1 |
=====================
Place table
===============...
2
votes
1answer
71 views
how to save time without time zone in postgres . i am using hibernate Spring MVC
ERROR: column "receipt_time" is of type time without time zone but
expression is of type bytea Hint: You will need to rewrite or cast
the expression. Position: 490
private LocalTime ...
-3
votes
1answer
54 views
I have error loading data into PostgreSQL database
follow up to the question, this is the command line i used but it could not recognizecommand line my password
The Image
I have a PostGreSQL script that contains table definition and sample data but ...
0
votes
1answer
27 views
Replace String Contain multiple string in Postgresql
Shipping address to persons contain multiple text inside it , for example in this 4 Rows of consumer address.
| Address Line |
|------------------------------------...
0
votes
0answers
16 views
Read disk .sql file names with PostgreSQL [duplicate]
does anyone know how to read file name from the client machine disk from the postgresql manager?
In sql used the following:
EXECUTE master.dbo.xp_cmdshell 'dir /b "C:\Folder_Scripts\*.sql"
0
votes
4answers
779 views
List grouped with sublists postgresql
This is a problem I do not how to search and even describe. But I will give it a try.
As a start I have 2 tables:
CREATE TABLE vch
(vchid int4, subject text);
INSERT INTO vch
(vchid, ...
0
votes
1answer
110 views
How to fix input is out of range error in Postgres 9.1
In Postgres 9.1 code below produces error
ERROR: input is out of range
CONTEXT: SQL function "gc_dist1" statement 1
How to fix it in 9.1 ?
create or replace function gc_dist1(_lat1 float8, _lon1 ...
1
vote
1answer
17 views
How to list records of a Table with a counter of how many are used through a relationship
I have these 2 tables:
create_table "instruments", force: :cascade do |t|
t.string "name"
...
create_table "user_instruments", force: :cascade do |t|
t.integer "user_id"
t....
0
votes
0answers
18 views
Fetch record based on string that is present in a field
I'm using the following query to fetch records from database. The row that exists in database and the column question contains: "How are we going to do solve this. Is it complex?"
I'm using the ...
0
votes
1answer
71 views
Import xml data in postgresql
i try insert to postgresql table data from XML
This is sample XML:
<?xml version="1.0" encoding="UTF-8"?>
<ActualStatuses>
<ActualStatus ACTSTATID="0" NAME="Not actual" />
<...
0
votes
1answer
34 views
Slow update command
I ran the following commands in posgresql 9.6:
./bin/createdb testSpatial
./bin/psql -d testSpatial -c "CREATE EXTENSION postgis;"
create table test(name character varying(250), lat_long character ...
1
vote
1answer
27 views
Incorrect results returned by postgres
I ran the following commands in posgresql 9.6:
./bin/createdb testSpatial
./bin/psql -d testSpatial -c "CREATE EXTENSION postgis;"
create table test(name character varying(250), lat_long character ...
0
votes
0answers
50 views
Postgres Recovery Failure
What I am trying to accomplish is a recovery using a continuous archive backup.
I am running a vm of CentOS 6.8 and Postgres 9.1 Postgres 9.1 is the same as the DB that I am pulling from.
I ...
0
votes
0answers
28 views
Excecuting multiple slash commands on postgres prompt
cd C:\Program Files (x86)\pgAdmin III\1.20
psql -U postgres -p portNumber -h remoteServerip dbName \\copy remotetable("id", "Name") TO 'D:\localdesktopfolder.dump'
i am able to switch to remote ...
0
votes
1answer
84 views
grafana data source option not visible
I started to work on grafana and i am not able to find the data source option on my drop down menu of grafana. Can somebody tell me where i am wrong. I followed the documentation of installation ...
-1
votes
2answers
22 views
Creation of a procedure [POSTGRES]
How to create a procedure that asks for a user name and return the following select:
select application_name, client_hostname
from pg_stat_activity
where usename=p_nameuser;
Any ideas? Thank you.
1
vote
1answer
11 views
Mismatch of values when using xml input in postgre sql
I want to create a xml format so that i can use it in the input of a stored procedure,but the result came from the select query is not the output i am expecting.Could you please help me with this?
...
0
votes
0answers
52 views
SQLAlchemy table relation does not exit
I am trying to query my Postgres database. Ultimately, I am trying to conver the following query into sqlalchemy:
SELECT DISTINCT id
FROM (
SELECT id, transcript_info->'words' AS info
FROM ...
0
votes
1answer
31 views
Search specific data [POSTGRES]
How to display a user's login time, the name of the machine with which it is connected, the operating system and the program from which it was opened.
I found this query to select active connections, ...
0
votes
1answer
33 views
How to remove f1 from returned column when using row_to_json in postgresql?
I am using row_to_json to transform the columns into json.
this is my query:
Select row_to_json(row(func1('cust_1')));
note func1 is an sql function that takes one varchar and returns 3 floats
...
1
vote
1answer
153 views
postgres count tables in db
I want to get the count of tables inside the 'hgp17290_data' db in pgadmin.
I've been messing about with POSTGRES SQL and I can get the size of a db, like so:
select pg_database_size('hgp17290_data');...
0
votes
0answers
18 views
optimize migration of table from csv to pg with self join update
I have a CSV of size 1.5GB containing around 11000000 records and I am populating that data to Postgres table
What I have tried so far is:
set local work_mem = '4000MB';
copy truck_dispatch_logs(
...
