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
0answers
3 views
Rails: How to order by different association over same table
Given the following code.
how can I order by carrier name?
how can I order by company_friend name?
class Appointment
belongs_to :company_friendship
has_one :company_friend, through: :...
0
votes
0answers
4 views
Postgres - bind results of equal type by year
Please excuse my not very propper way of asking this as i am new to postgres...
Having the following two tables:
CREATE TABLE pub (
id int
, time timestamp
);
id time
1 ...
0
votes
0answers
7 views
Error when trying to make 2 database calls
Thanks in advance for tolerating with my noobishness in nodejs.
I am making an api using swagger and node js using a postgres DB.
I have loaded data into the db and am calling it from the api ...
0
votes
0answers
3 views
In Postgres, is it possible to filter out rows with an array column whose value is a subset of values contained in other rows using sql
I want to shorten this result to only return rows that have a superset of all the rows and order of values matters
for example:
{3,1,5} is not a 'superset' {3,5}
I use the word 'superset' here ...
0
votes
1answer
11 views
Postgresql limit column to 3 per value
SELECT u.id,
'Shift' AS which,
se.created_at
FROM users AS u
JOIN schedule_elements AS se ON se.owner_id = u.id
UNION ALL
(SELECT u.id,
...
0
votes
0answers
6 views
PostgreSQL - Assign integer value to string in case statement
I need to select one and only 1 row of data based on an ID in the data I have. I thought I had solved this (For details, see my original question and my solution, here: PostgreSQL - Select only 1 row ...
1
vote
1answer
11 views
How does Postgresql round half-microseconds in timestamps?
I was under the impression that PostgreSQL rounded half-microseconds in timestamps to the nearest even microsecond. E.g.:
> select '2000-01-01T00:00:00.0000585Z'::timestamptz;
...
0
votes
1answer
20 views
JOIN three tables resulting in duplicate records
The following query is almost correct, except that it results in duplicate rows from the list_taxonomies table. I only need unique rows from the list_taxonomies table. I've struggled with a few ...
0
votes
0answers
12 views
Why does my postgresql custom type constructor produce ERROR: type is only a shell?
I created a timerange type with the below. At least I thought I did, my local version might have been created through a pg_restore operation.
CREATE TYPE timerange AS RANGE (
subtype = TIME
);
...
0
votes
0answers
7 views
pyodbc Cursor.Execute freezes when using prepared statements
I am using Python 2.7 and pyodbc to talk to a Postgresql database. The execute statement freezes and does not return when I use prepared statement. If I use straight sql then it works fine. Any ideas? ...
0
votes
0answers
8 views
How to join two tables in django to make GeoJson
In Django, I have two models:
class Ssm_El_Ml_Pl_Blocks(models.Model):
lease_id = models.CharField(max_length=150, primary_key=True)
license = models.CharField(max_length=50, blank=True, null=...
0
votes
1answer
10 views
What does it mean when psql has blank database access privileges?
When I run \l+ in psql to get a list of all databases and their access privileges, I see that some of the databases have blank access privileges. What does it mean when the Access privileges column is ...
0
votes
2answers
14 views
Can I index into an array literal in a SELECT statement?
I am reading from a PostgreSQL database (that I do not control) that includes an integer column that acts like an enum, but the enum values are not in the database.
This is not my actual data, but ...
0
votes
0answers
10 views
How do I access the additional columns in models that inherit from a parent-type when querying Bookshelf.js
In my project, I have a parent table 'Tasks' with fields like 'owner_id' and 'title'. I also have two specific variations of Tasks called 'TaskFind' and 'TaskFix'. These both extend the 'Tasks' table ...
0
votes
1answer
16 views
How to do WHERE <before> an aggregate function (Postgres)
It's hard to explain from the title, but this is my SQL:
SELECT
SUM("payments"."amount"),
"invoices"."property_id"
FROM "payments"
JOIN "invoices"
ON "payments"."invoice_id" = "invoices"."id"
...
-1
votes
2answers
15 views
How do i upload image to database from djang form?
I have tried this but did not work.
class Agent(models.Model):
first_name = models.CharField(max_length=50, blank=False)
last_name = models.CharField(max_length=50, blank=False)
...
0
votes
0answers
16 views
How to stop postgres cron jobs ? is there even a way to stop it?
Suppose if I run the following cron job in my DB:
SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
will that ever stop running ? How can I ...
0
votes
1answer
11 views
sort rows by split of dot-separated-string values
I have an index column, string type, where the data is in format like "1.5.4.10.7".
I need to make a query returns the values sorted as if the column were an array of integers derived by splitting ...
0
votes
2answers
24 views
PostgreSQL - Grouping specific columns in SQL
The similar problem might have been raised here before, but it's very hard to find the answer i'm looking for.
Let's say I have a table like this:
isbn | Name 1 | Name 2 ...
0
votes
0answers
15 views
Achieving multi-tenancy with Spring and Quartz
I am trying to implement multitenant schema solution in our application and ran into problems with Quartz jobs.
I thought I could simply change the table prefix and add a schema name like this:
s[...
0
votes
0answers
9 views
Node.js, loopback, Heroku and H12 error
we have the following constellation: node.js (4TLS) application with loopback for most parts of the API with an Angular front-end. We're using a Postgres DB with 1 follower in the backend (size just ...
1
vote
0answers
18 views
Mathematical calculation in label - postgresql
I'm not even sure if this is possible however I'm been looking around online for a while now and have not seen any syntax that provides the solution. I'd like to include a mathematical calculation in ...
0
votes
2answers
17 views
Group by YYYYMM from YYYYMMDD int column - Postgresql
I have date in yyyymmdd format in an int column and I would like to group by month i.e. yyyymm.I've tried the below two versions
select to_char(to_timestamp(create_dt),'YYYYMM'),count(*) from ...
0
votes
0answers
16 views
PostgreSQL CONTAINS operator (@>) in CakePHP?
Wondering if CakePHP supports the CONTAINS operator
The query I have in Postgres is something like:
SELECT * FROM accounts WHERE name @> '{matthew}'::text[]
Where accounts.name is populated ...
0
votes
0answers
17 views
Atomically retrieve batch of rows from Postgresql database
I have a Python script that will retrieve a batch of rows from a table in a remote Postgresql database, do some processing, and then store the result back to the database. I will have this script ...
0
votes
0answers
7 views
Multiple pids for one transaction and pg_terminate_backend
We are using pgpool with one master and one slave. When we execute a transaction with selects and updates some selects go to the slave and some to the master and all the updates/inserts go to the ...
0
votes
0answers
17 views
Postgres BRIN pages_per_range calculation
this is a follow-up question to my previous post regarding BRIN indexes in Postgres 9.5 Postgres choosing BTREE instead of BRIN index.
Is there an intelligent way to calculate what the:
...
0
votes
0answers
15 views
error while creating Postgres extension www_fdw
I want to use www_fdw extension of PostgreSQL in order to read data from web services. I used this command to create extension:
CREATE EXTENSION IF NOT EXISTS www_fdw CASCADE
but it gives me ...
0
votes
0answers
13 views
Setting the schema name in postgres using R
I am using R to connect to the postgres db. Connection is done using below function.
dbConnect(m, dbname=dbname, host=host, port=port, user=user,password=password)
m is the driver(postgres).
but it ...
0
votes
2answers
24 views
Select min and max values while grouped by a third column
I have a table with campaign data and need to get a list of 'spend_perc' min and max values while grouping by the client_id AND timing of these campaigns.
sample data being:
camp_id | client_id | ...
1
vote
1answer
18 views
Postgres CREATE EXTENSION All Databases
I've got postgres installed in multiple environments. In each of those environments, I have 2+ databases.
If I have superuser database rights, is there a way to install the CITEXT extension for all ...
0
votes
0answers
22 views
Store multiple files in one BLOB
I would like to store multiple files concatenated in a single BLOB in my postgresql database.
I wonder if it is possible to use the lo-funcs to perform that process or do I have to store each file ...
0
votes
2answers
41 views
Create table from another table
I have two tables (A,B) inside my database.
Now I will create a new Table C which is from the columns identical to the table B.I want to write all the data from table B which geometry is inside table ...
1
vote
3answers
28 views
Is the column order predictable when using *?
If I run
SELECT *
FROM my_table;
is the column order predictable?
What are the underlying design aspects of postgres that determine this order?
0
votes
1answer
23 views
Group PostgreSQL data to have no more than N values in output
I need to load chart data fast. The data resides in a date-Y table:
CREATE TABLE (
id serial primary key,
date timestamp not null,
y1 double precision,
y2 double precision,
y3 ...
0
votes
0answers
12 views
Modify a subdomain at rack level in rails application
I want to catch a request at rack-level in rails application and modify its subdomain. e.g, If the request is xyzdevelopment.example.com or xyzbeta.example.com its should be convert into xyz.example....
0
votes
1answer
7 views
Custom guc variables not set properly
I am trying to set a string variable say 'bdr_node_name' in the terminal using set command.
bdrdemo=# set bdr.node_name = 'node1';
SET
bdrdemo=# show bdr.node_name ;
bdr.node_name
---------------
...
2
votes
1answer
26 views
Encoding a Parameter List for a Hasql query
I'm trying to get Hasql to encode a list for a "select ... where in" query. It typechecks if I use contramany from contravariant-extras, but I get a syntax error at runtime.
import qualified Database....
0
votes
1answer
22 views
Elixir Ecto: How to write a migration with belongs_to and has_many?
I have two models, Personand Pet, and I want a Personto be able to have many pets, but a Petto belong to only one person:
defmodule MyApp.Person do
use MyApp.Web, :model
alias MyApp.Pet
...
2
votes
3answers
29 views
PostgreSQL previous and next group value
The problem is the following:
Suppose, I have a table of such view (it is a sub-sample of the table I'm working with):
| col1 | col2 |
|------|------|
| 1 | a2 |
| 1 | b2 |
| 2 | c2 |
...
0
votes
0answers
27 views
Knex with PostgreSQL select query extremely perfomance degradation on multiple parallel requests
In brief
I'm developing a game(of dream) and my backend stack is Node.js and PostgreSQL(9.6) with Knex. I hold all players data here and I need to request it frequently.
One of the requests need ...
0
votes
0answers
11 views
Slow django model instance creation with Docker
I have django application with some model. I have manage.py command that creates n models and saves it to db. It runs with decent speed on my host machine.
But if I run it in docker it runs very slow,...
0
votes
1answer
12 views
Remote connect to Heroku pg database error: could not translate host name to address
I am trying to connect to my Heroku hosted postgres database but unfortunately it is returning a "failed to translate host name to address" error. I directly take the DATABASE_URL from heroku as shown ...
0
votes
1answer
22 views
Fastest way to iterate over a 4-Dimensional array?
I have some variables which I will need to insert into a database (PostGres).
The variables are 4-D and each has the following attributes: time, level, latitude, longitude.
For example:
print(...
0
votes
2answers
22 views
Postgres extract value from jsonb array
I have a jsonb field with an array like this one below:
[
{
"type":"discount",
"title":"Discount 10%"
},
{
"file":"...
1
vote
1answer
30 views
“Big” data in a JSONB column
I have a table with a metadata column (JSONB). Sometimes I run queries on this column. Example:
select * from "a" where metadata->'b'->'c' is not null
This column has always just small JSON ...
-1
votes
3answers
25 views
How to get the count of each unique item in a many to many relationship?
I have an ingredients table containing unique ingredients:
ID INGREDIENT
1 Sugar
2 Egg
3 Vanilla
I have a recipe table containing various recipes:
ID RECIPE
1 Eggs Florentine
2 ...
1
vote
1answer
34 views
How to compare dates one with time zone and one without time zone
I have to compare the two dates to get the last 10 min records from database,I'm using postgresql,
I have write this query
select *
FROM x_table
WHERE x_time >= (NOW()::TIMESTAMP WITHOUT TIME ...
2
votes
1answer
22 views
postgresql remove stale lock
After a system crash my Postgresql database does have a lock on a row.
The pg_locks table contains a lot of rows without a pid. i.e.
select locktype,database,relation,virtualtransaction, pid,mode,...
-1
votes
2answers
53 views
Using age and gender to search for ID numbers in a table
In an interface where user records in a table can be searched.
The operator must be able to search by the age and/or the gender of the user(the record). However this must be done by using the ID ...
