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 ...
3
votes
2answers
14 views
Dealing with a poorly designed “variable column” table in PostgresSQL
I am dealing with a poorly designed table, somewhat like this
create table (
entity_key integer,
tag1 varchar(10),
tag2 varchar(10),
tag3 varchar(10),
...
...
0
votes
0answers
10 views
Error loading psycopg2 module: dlopen with virtualenv django python 2.7
Guys I stuck this problem for couple weeks already. I have tried almost every answer I could find in stack overflow.
The error is below:
(venv1) Caimings-MacBook-Pro:yang NLStom$ python manage.py ...
0
votes
0answers
4 views
distance between geometries in multigeometry
I have a table with a number of multiline geometries. For each of these I would like to calculate the maximum distance between the lines which make up that multiline geometry (and add that number to a ...
0
votes
0answers
5 views
How to Return a Generated Series as Array in an SQLAlchemy Hybrid Property Expression
I have an SQLAlchemy model providing an age as a range type. Excluding unrelated fields, it looks like this:
class Occasion(Base):
#: The expected age of participants
age = Column(
...
0
votes
0answers
9 views
PostgreSQL RANK() function over aggregated column
I'm constructing quite complex query, where I try to load users with their aggregated points altogether with their rank. I found the RANK() function that could help me to achieve this but can't get it ...
0
votes
1answer
11 views
Make sure to get different results for two workers on a PostgresqlDB
I have a PostgresDB and in one table I have Server-IPs, now I want to take a bunch of workers to go and check the servers in my DBs. But I want to make sure that no two workers get the same Server-IP, ...
0
votes
1answer
24 views
Group and sum rows in a table with historical data
I have a table with data entry regarding the activity of an RFID reader. It has data being inserted every second. I want to write a query to sum the ping_count of the rows with the same item_id & ...
1
vote
0answers
31 views
Sort results returned from a query, like a nested query
I have a query, that I use to find the latest rankings of keyword a huge table. I use the following query to achieve that.
@results = KeywordRanking.includes(:business_keyword, :keyword)
...
0
votes
0answers
21 views
PostgreSQL - Duplicate table in parts, empty other fields, rename table
I'm very new to PostgreSQL or SQL in general, so I don't know whether my question might be simple and stupid – sorry in advance.
My situation is the following:
I've got a table with 57 columns and ...
-1
votes
2answers
14 views
Best ways to apply Joins inside a Update Query in Postgreql (performance wise) [on hold]
I Want to update a table column after checking multiple conditions on multiple tables
0
votes
0answers
9 views
how do i perform full text search in postgressql? [on hold]
i am a beginner in postgressql. I am looking to perform a full text search in postgressql. can anyone provide me with a good example on how to begin ?
0
votes
0answers
12 views
Creating a Void Function in PostgreSQL
I am getting an error on this create function code in Postgresql. The error says it is happening around Line 2 at DELETE, but it happens at WITH if I remove that line so I think it is a problem with ...
0
votes
0answers
8 views
SQLAlchemy not finding Postgres table connected with postgres_fdw
Please excuse any terminology typos, don't have a lot of experience with databases other than SQLite. I'm trying to replicate what I would do in SQLite where I could ATTACH a database to a second ...
-3
votes
0answers
19 views
space betaween colunm name (postgres) Php How can i correct this sql statement
SELECT
"Timestamp" as Timestampone,
"Last" as Last,
"Trade Price" as Trade_Price
code above does not work when I change to
SELECT
".Timestamp." as Timestampone,
".Last." as Last,
".Trade Price." ...
-1
votes
1answer
8 views
PostgreSQL - How to describe table with column key in postgreSQL? [duplicate]
How to describe table with column key in postgreSQL?
Because in postgreSQL not show with the key so need your help
I want desc table like MySQL but need in postgreSQL
1
vote
2answers
17 views
Postgres SQL query doesn't identify the column
I'm having a huge problem with my query, for some reason I just can't get one of the WHERE clauses to work.
This is my SQL:
SELECT COUNT(*) FROM "diets" JOIN "meals" on "idDiet" = "dietId"
WHERE ...
2
votes
1answer
22 views
Postgres insert on conflict update using other table
I having syntax error for the following sql which spent an hour but cant find any answer form postgres documentation.
CREATE TABLE transaction (userid SMALLINT, point INT, timestamp TIMESTAMPTZ);
...
0
votes
0answers
3 views
specify quoting char when using of PyGreSql's cursor.copy_from
If I use Postgresql's copy command, i can specify the quoting character. While using the PyGresql's cursor.copy_from method, there is no parameter for specifying the quoting character, and actually ...
0
votes
0answers
9 views
Do you think it is right approach? MPTT model in Django -> Tree structure in Redis
I am using Django framework. I wanted to make menu dynamically. I used django-mptt module and made tree structure with it. I store menu structure with tree structure, then template loads its data from ...
0
votes
1answer
17 views
Turn a field of comma-separated zip codes into a list of cities
I have two tables:
people, with columns user_id (unique) and zipcodes (comma-separated value; multiple zipcodes possible in a single value);
location, with columns zipcode, city, and state (each ...
0
votes
0answers
24 views
Postgresql Cursor in Ruby on Rails
How do I view the contents of a returns Postgresql cursor?
I have a custom function in Postgresql that returns a cursor. In Rails the returned values looks like an array that contains:
#<Order:...
0
votes
0answers
7 views
October CMS custom database types
I'm using October cms with a PostgreSQL database. I want to install PostGIS and include the geometry database type for use in a plugin, can anyone help?
I can install PostGIS and make queries using ...
0
votes
1answer
12 views
Forcing a DROP in Postgresql
How can I force DROP a TEMP TABLE? I have a web app that is using temp tables, but I keep running into this error:
PG::ObjectInUse: ERROR: cannot DROP TABLE "t" because it is being used by active ...
0
votes
1answer
10 views
postgres odbc connection fails
I am trying to set up a new postgres odbc connection for an application.
This is done on 32-bit Windows 7 system. I installed latest postgresql 9.6 & psqlodbc 0905, and there were no errors. When ...
0
votes
0answers
21 views
Postgres queries equivalence
I would like to know if these two queries produce the same result:
update A.cred
set user_name = concat(CURRENT_TIMESTAMP::text)
from A.temp_table2 tt
WHERE email NOT IN (select email from A....
0
votes
0answers
12 views
Rails / Postgres database query to dynamically retrieve correct record?
I need help troubleshooting a database query to generate correct results. This is a fitness app developed with Rails 4.2.6, Ruby 2.2.4, and a Postgres 1.9.0 database. When a user saves new body ...
0
votes
1answer
17 views
Procedure INSERT with CASE returning number of inserts
I have 2 tables in my PostgreSQL database:
1) Plan
ID (INT)
m1 (NUMERIC)
m2 (NUMERIC)
m3 (NUMERIC)
m4 (NUMERIC)
m5 (NUMERIC)
m6 (NUMERIC)
m7 (NUMERIC)
m8 (NUMERIC)
m9 (NUMERIC)
m10 (NUMERIC)
m11 (...
1
vote
0answers
26 views
Error on Migration when adding SQL functions in Rails
I have the following 2 files:
20161009180554_add_process_functionst_to_db.rb
class AddProcessFunctionstToDb < ActiveRecord::Migration
def up
funcs = File.read(Rails.root.join('db', 'migrate'...
0
votes
0answers
23 views
How to handle an ALTER TABLE RENAME?
Asuming I have a table myTable that stores:
id | tableName
---------------------
1 | a
2 | b
... | ...
I have event triggers for CREATE TABLE / DROP TABLE events that ...
0
votes
1answer
19 views
How to get a json object as column in postgresql?
I have these table on mu PostgreSQL 9.05:
Table: core
Fields: name, description, data
data field is a json field, with (for example): {"id": "100", "tax": "4,5"}
Always is one json per data.
My ...
-2
votes
0answers
9 views
android application that uses same database of odoo openERP
how to make android application uses the same database of odoo (PostgreSQL)
to (add,update,delete,read) records from PostgreSQL DB
android
0
votes
1answer
21 views
Reusing joins between different parts of a query
I have the following tables:
CREATE TABLE sequence (
id serial PRIMARY KEY
-- ...other sequence data
)
CREATE TABLE sound (
id serial PRIMARY KEY
-- ...other sound data
)
CREATE TABLE layer ...
1
vote
0answers
40 views
Convert SQL QUERY to Django
I have the next query in Postgres:
SELECT a.id, a.name, a.code,
(SELECT coalesce((SELECT MAX(value) FROM financials WHERE client_key=a.id AND year=2016), 0) AS ca_vlr_n)
FROM clients a
I want to ...
1
vote
1answer
10 views
Fetch values after @ in Postgresql
I have a PostgreSQL table customer in which I have two fields : Email and Name. I want to fetch the values after the @ and need the count of these. Please find the image below :
0
votes
1answer
24 views
How to host Node.Js server and PostgreSQL database from my computer?
I want to host my own server and database on my computer, I don't want to pay monthly for services.
I developed a node.js app and it's using a postgresql database. I have a domain with an angular app ...
0
votes
2answers
68 views
Get records created after a particular time of day
Say I have an Event model with a date_time field representing the date time the event is held, and I want to see all Events that are held, say, 'after 10pm', or 'before 7am' across multiple dates. How ...
0
votes
0answers
15 views
Get data by chunks with Prepared statement [duplicate]
I have PostgreSQL db and I need to use prepared statement in java to get the data. I read that the results set load all the results to the memory, how can I load the results by chunks while using ...
0
votes
2answers
17 views
Optimization of PostgreSQL query with filtering by counts
Is there a way to optimize such a query in PostgreSQL 9.5, 9.6?
SELECT x FROM (
SELECT x,count(x) cnt FROM very_big_table WHERE (conditions) GROUP BY x
) sub
WHERE cnt > 10
Indexes to all ...
0
votes
0answers
8 views
Hibernate Embedded & Child, column not found
I have the following hibernate definitions:
@Embeddable
public abstract class AbstractTimestampEntity {
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "_VERSION", nullable = false)
...
0
votes
1answer
21 views
Why MySQL InnoDB can handle concurrent updates and PostgreSQL can't?
Let's imagine you have a table with this definition:
CREATE TABLE public.positions
(
id serial,
latitude numeric(18,12),
longitude numeric(18,12),
updated_at timestamp without time zone
)
...
0
votes
3answers
33 views
Rails - Business opening hours schema
I have a Venue model that has_many and accept_nested_attributes_for working_hours.
The Working Hour model:
create_table "working_hours", force: :cascade do |t|
t.integer "day"
t.time "...
0
votes
0answers
19 views
Using PostgreSQL or PostgreSQL + MongoDB?
I'm currently planning a social-media application - especially the backend.
Basically I have all the social aspects for which I want to use SQL (PostgreSQL I guess) but I also have geolocations ...
0
votes
1answer
16 views
rails dynamic where sql query
I have an object with a bunch of attributes that represent searchable model attributes, and I would like to dynamically create an sql query using only the attributes that are set. I created the method ...
1
vote
0answers
12 views
Ruby Postgresql PGconn.connect takes up to 60 seconds to initialize
Trying to do some rails dev under windows and I'm finding that the initial connection to postgres is taking up to 60 seconds. I dug into the activerecord postgresql adapter and added some profiling in ...
2
votes
1answer
29 views
Get created table name
I'm trying to create a event trigger, executed whenever a table is created.
When this happens I would like to insert into a table ( which has 2 columns id and tablename ) the name of the table created....
1
vote
1answer
21 views
SQL with table as becomes ambiguous
Perhaps I'm approaching this all wrong, in which case feel free to point out a better way to solve the overall question, which "How do I use an intermediate table for future queries?"
Let's say I've ...
0
votes
0answers
15 views
PostgreSQL on pythonanywhere.com
Please i used the django-girls django tutorial created my first django project but used postgreSQL database instead and i pushed it to my repository on github. I tried to host it on pythonanywhere.com ...
0
votes
0answers
18 views
Using Laravel, copy database table from one database server database to another database server
Server 'a' has a postgres database 'sourcedb' with a table 'products'. I am creating a delivery system in laravel on a different server 'b' with its own database 'destinationdb' using mysql (MariaDb)....
0
votes
1answer
18 views
Rails inheritance_column migration
I'm changing the inheritance_column value of a base model, which is extended using STI, in an existing app. How can I write a migration in order to make the existing columns conform with the new ...
0
votes
1answer
22 views
PostgreSQL: Aggregate daily sum over list of durations
Current situation
I have a table that tracks usage of a certain resource. It looks like this:
started | timestamp with time zone | not null
last_ping | timestamp with time zone |
stopped | ...
