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 PostgreSQL version when asking questions. Questions concerning administration or advanced features are best ...
0
votes
0answers
3 views
Database URL trouble with rails app in heroku
I am trying to run a pull request from Heroku using the command:
heroku pg:pull HEROKU_POSTGRESQL_quotesapp mylocaldb --app quotesappp
and I get this error:
Unknown database: ...
0
votes
0answers
3 views
Typescript definitions from postgresql query result
Suppose you have a complex query returning hierarchical JSON. How to generate Typescript definitions of the query result?
0
votes
0answers
2 views
CKAN: Error 500
Trying to install CKAN on Ubuntu 14.04 and decided to follow this method: http://docs.ckan.org/en/latest/maintaining/installing/install-from-package.html. Everything has installed correctly, except I ...
1
vote
2answers
38 views
Create 10 different random numbers
I want generate some random data. ~50k rows
Every row have ID autonumeric and 10 numeric values from 1 to 90.
+----+------+------+------+------+------+------+------+------+------+------+
| id | nr1 ...
0
votes
0answers
4 views
Creating a calendar like table using crosstab in postgres
I'm going through the Seven Databases in Seven Weeks book, and am sort of stuck on an extra work question.
The question is:
Build a pivot table that displays every day in a single month, where
...
0
votes
1answer
9 views
PostgreSQL ambiguous error
WITH novos_valores (cd_cidade, nm_cidade, uf) AS (values (12000, 'FAKE CITY', 'RS')),
upsert AS ( UPDATE cidade cid
SET nm_cidade = nv.nm_cidade, uf = nv.uf
...
0
votes
0answers
3 views
pg_upgrade on Windows cannot write to log file pg_upgrade_internal.log
I'm trying to run pg_upgrade on Windows 2008R2, but I'm getting the error:
cannot write to log file pg_upgrade_internal.log Failure, exiting
I saw a similar question for Linux at 23216734 which ...
2
votes
1answer
20 views
PostgreSQL sum quantity of children items
I have a subscription service that delivers many items.
Subscribers add items to a delivery by creating a row in delivery_items.
Until recently subscribers could only add 1 of each item to a delivery. ...
0
votes
0answers
9 views
SQLAlchemy cascading polymorphic column updates
I have a parent Employee table and a child Engineer table. From a client perspective I only want to interact with the Employee model. This is easily implemented for READ and DELETE, but issues arise ...
0
votes
2answers
12 views
Postgis - How to detect an instersection of the bigger area instersected
I have two maps loaded into the database, one map has the geometry of the states, and the other one has the geometry of the urban areas.
I want to make and intersection to make a relation between the ...
0
votes
1answer
20 views
sql yielding double values in double select query
I have the following sql query:
SELECT
a.url,
b.lpvs,
a.volume,
a.sales_usd,
a.sales_eur,
a.sales_gbp,
a.sales_jpy
FROM (
SELECT
referer_trackings.cookie_first_url AS url,
count(purchases.id) AS ...
0
votes
1answer
5 views
No entries in pg_hba.conf
Ok first of all i'm french so sorry for the possible incomprehension or bad translate of error messages.
I have a Raspberry, with a Tomcat server on. Also, i use Postgresql too.
On my website, The ...
1
vote
0answers
36 views
Why does adding one smallint column to result slow query by factor of 60?
Here I have what seems to me to be a routine table and a table creation script that I have put in /tmp/try.sql. I am running postgres under OSX 10.10.2.
Here is the table:
cow_dev=# \d carc
...
0
votes
0answers
11 views
Postgres restore from WAL files without having a basebackup using pg_basebackup
I have the following situation.
Have a master/replica setup.
Somehow the database was dropped accidently by a user and a new database with the same name was created by django. in this case, will ...
0
votes
1answer
19 views
Selecting ranges of dates without overlapping
I have the following relational schema:
dates(date: date, code: char)
code can be ST,MN,MX,ED.
An example:
╔════════════╦══════╗
║ date ║ code ║
╠════════════╬══════╣
║ 2001-10-01 ║ ST ║
║ ...
0
votes
0answers
8 views
Heroku - RDS - NodeJS - Postgres - How to prevent “terminating connection due to administrator command” error?
I can see this error showing up in Heroku logs. Whenever this happens my app crashes and gets restarted.
"terminating connection due to administrator command"
This is an exception thrown by the PG ...
1
vote
1answer
14 views
How to run a postgressql function
I am struggling to execute a postgressql function. I am trying to read the documentation on the side but still no use. I am using Toad Extension for eclipse to develop/run the function
So far, this ...
0
votes
0answers
6 views
Ansible: specify SSL certificate when connecting to Redshift Host
I'm currently trying to setup some playbooks that automate the creation of some testing databases on a redshift cluster.
I noticed there are no specific ansible modules for Redshift but reckon that ...
0
votes
1answer
12 views
ActiveJDBC: save() generates insert only for auto-generated primary key?
I recently started using ActiveJDBC. I have the following table (postgresql)
CREATE TABLE users (
id uuid PRIMARY KEY,
email text UNIQUE NOT NULL,
...
1
vote
1answer
19 views
Django: how to use two different databases with Wagtail CMS
Inside a Django project, I have one app, otherapp, which hits a Postgres database on a remote server that contains scraped data. I have a second app, content, which hits a different Postgres database ...
0
votes
0answers
16 views
How to recover data from WAL file in postgresql?
In my production server I had thousands of user data but accidentally that data got deleted somehow. Now I need to restore the data.
How to recover data from WAL file in postgresql?
0
votes
1answer
26 views
Postgres hierarchical (jsonb) CTE unnecessarily slow
I have a JsonB column in my table which holds hierarchical information.
MyTable (id uuid, indexes jsonb, content bytea)
Now if I create a CTE say
WITH RECURSIVE hierarchy(pid, id, content) AS (
...
1
vote
1answer
13 views
Database column encryption postgres
How to encrypt column in postgres database using pgcrypto addon ?
I am using postgres 9.3 and i need to encrypt one of my column , does postgres also support Aes encryption or by any mean i can ...
-1
votes
0answers
23 views
Optimize PARTITION query
I have this query that takes forever to finish and still I have no results.
When I remove partition it works fine, but with it, it just keeps running with no end...
SELECT store_displayname AS ...
1
vote
1answer
25 views
PostgreSQL - text Array contains value similar to
I'm trying to get rows where a column of type text[] contains a value similar to some user input.
What I've thought and done so far is to use the 'ANY' and 'LIKE' operator like this:
select * from ...
0
votes
2answers
24 views
Configuring Django with Nginx, uWSGI and Postgres on Docker
I'm trying to setup a Django app on Docker with Nginx, uWSGI and Postgres. I found this great guide on setting up Compose for Django and Postgres: https://docs.docker.com/v1.5/compose/django/
...
1
vote
0answers
15 views
Django Heroku Postgres - ProgrammingError: relation does not exist
I'm using Django 1.7, PostgresApp (PostgreSQL 9.4), Python 2.7.
I have followed this tutorial for setting up and creating the app: https://docs.djangoproject.com/en/1.9/intro/tutorial02/
In the ...
1
vote
0answers
16 views
Postgres syntax error on Create constraint trigger
I need a trigger to cause a reindex (index is on an immutable function)
I define the function as
CREATE FUNCTION "TEST".triggerFuncTest()
RETURNS TRIGGER
AS $$
BEGIN
REINDEX INDEX "TEST"."MyIndex";
...
0
votes
0answers
23 views
Postgres not leveraging index (jsonb) in recursive CTE [duplicate]
I have a JsonB field in my table which holds hierarchical information.
MyTable( id uuid, indexes jsonb, content bytea)
Now if I create a CTE say
WITH RECURSIVE hierarchy(pid, id, content) AS (
...
0
votes
0answers
6 views
How to execute Postgresql scripts via Wix Installer
We have a requirement where we would like to execute a postgresql sql scripts of creating and installing the database on the machine.
We are using Bootstrapper in our project where we are able to ...
0
votes
1answer
37 views
0
votes
0answers
8 views
pgAdmin: Jobs option is invisible
I am using PostgreSQL vesrion 9.3 for Operating system window 7.
I am getting problem to display the option jobs in postgresql PgAdmin III.
Here are the pictures what I have tried:
After setting ...
2
votes
0answers
24 views
PostgreSQL - use csv data without importing to db
I have a csv file which contains some data I want to use in a psql query. I am currently able to import the data from the csv into a table in my db, but I want to make the query without saving the ...
0
votes
0answers
12 views
Login as any pgsql user in peer authentication
Is there any method to make root is able to connect to database as any users in peer authentication?
Peer authentication can verify the users from domain socket. However, the database user name has ...
1
vote
1answer
59 views
Join query returns duplicate rows
purchase_request_master
prm_voucher_no| project_id| status_id| request_date
17 46 3 11-6-2016 0:00
18 46 ...
1
vote
2answers
13 views
Rails + PostgreSQL: grouping together with a sum
I have a table with this structure:
id | name | batch_id | used
----------------------------------
1 | voucher 1 | 1 | 1
2 | voucher 2 | 1 | 0
3 | voucher 3 | 1 | 1
4 | ...
2
votes
1answer
34 views
pg_search exact match of search terms
When trying to find "Harrison Ford" in a document, pg_search will return any text that contains 'Harrison' and 'Ford', for example:
pg_search_scope :search_by_full_name, :against => ...
2
votes
3answers
42 views
SQL query to group by age range from date created
I want to get statistics with sql query. My table is like this:
ID MATERIAL CREATEDATE DEPARTMENT
1 M1 10.10.1980 D1
2 M2 11.02.1970 D2
2 M3 18.04.1971 D3
.....................
...
0
votes
1answer
19 views
Postgres SQL dropping table drops all dependent views?
In postgres when I drop a table it is dropping all views that depend upon it. Is there a way to persist the views so that they dont get dropped
Note: the table will be regenerated on daily basis with ...
0
votes
1answer
28 views
Converting new lines to <br />
I wanted to figure out a good way to replace new line characters to br's, the only way I could make it work is with "3x-replace" solution, which works (and I guess it put into function is not that ...
0
votes
0answers
5 views
How to edit PostgreSQL configuration files from cPanel?
I am trying to connect to a remote PostgreSQL database via VBA. I read here about the Database server configuration to make this possible but i cannot find the configuration files in cPanel file ...
-2
votes
2answers
45 views
Pair up rows in Sql
I have a Player table with 3 columns id, name, wins (number of win matches). To make a Swiss pairing where two players having near wins would play together in next round, I sort this table order by ...
0
votes
1answer
22 views
How to join these Two Tables in PostgreSQL?
How to Join These two Tables I'm not getting any Idea
CREATE TABLE entity_social_media (
entity_social_id integer NOT NULL,
entity_id integer NOT NULL,
entity_type character varying(50) NOT NULL,
...
0
votes
1answer
14 views
:tsearch english vs simple dictionary performance
I'm following a tutorial on pg_search and the author is specifying dictionary: "english" when the default is "simple", but the author doesn't mention why.
class Product < ActiveRecord::Base
...
0
votes
0answers
23 views
MySQL key in Postgres
We are migrating our MySQL database to Postgres and the following line is giving us trouble.
CREATE TABLE IF NOT EXISTS room_categories (
id SERIAL,
room_category VARCHAR(30) NOT NULL,
...
0
votes
0answers
30 views
Custom functions in SQL
how would I implement custom functions in SQL?
Let's say I have a list of values and I want to transform those in a way where SQL doesn't supply a default function. E.g. look at every field's string, ...
0
votes
1answer
4 views
ERROR: data type inet has no default operator class for access method “gist”
Using PostgreSQL version 9.4.5, I have a table that contains an INET column.
In that column I have a gist index
CREATE TABLE ipv4_prefix (
id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
...
0
votes
1answer
8 views
Array of type enum coming back as string from Postgres
Given a custom enum:
CREATE TYPE vehicle AS ENUM ('car', 'truck', 'bicycle');
And a table like so:
CREATE TABLE vehicle_events (
timestamp timestamptz NOT NULL DEFAULT current_timestamp,
labels ...
0
votes
2answers
38 views
Database stores historical price changes, how to calculate price at specific point in time?
I am working with historical price data of many different products.
I have a table products that lists all products with their name, description etc., identifying them with an uuid. Then there is ...
0
votes
1answer
18 views
How to import OSM polygons data from PostGIS to R?
I am new to the world of spatial analysis using R. Using this link I have downloaded OSM data in .osm.pbf format. Then I used osm2pgsql tool to get data in PostgreSQL (PostGIS extension). Now I have ...
