Tagged Questions
-1
votes
3answers
19 views
How to write pgsql update query with string aggrigate?
I have update query that will manually change the field value as a unique string, the table already have a lost of data and the id as unique Pkey. So i nee the names should look like
mayname-id-1,
...
0
votes
1answer
28 views
is it possible to connect two dbms MySQLand postgres SQL in codeigniter?
I am developing like fair size project which is in developing stage using MySQL as a primary database. Now, client has some data, which is in postgres SQL, which can not be converted into MySQL. So, ...
0
votes
2answers
29 views
postgresql create function with multiple IF ELSE condition
I am creating a function in postgresql which will do something like following:
CREATE OR REPLACE FUNCTION check_label_id_exist(_labelid integer, OUT result text) AS
$BODY$
DECLARE
BEGIN
SELECT ...
1
vote
1answer
35 views
Handle and display large data set in web browser
I am still a noob in web app development and sorry if this question might seem obvious for you guys.
Currently I am developing a web application for my University using Python and Django. And one ...
1
vote
2answers
21 views
How to get the trigger(s) associated with a view or a table in PostgreSQL
I have one requirement that I have to get the list of triggers associated to the given table/view.
Can anyone help me to find the triggers for a table in PostgreSQL?
0
votes
0answers
17 views
How to create a table, load data, and then add indexes using SQLAlchemy
I have a python file which uses SQLAlchemy to define all the tables in a given database, including all the applicable indexes and foreign key constraints.
The file looks something like this:
Base = ...
1
vote
1answer
29 views
RDBMS or NoSQL for complex schedule data with loops and ordered actions?
I'm trying to figure out how best to store (and model) the scheduling component of an automation system. I need to store schedules on which things will be executed (lights, pumps, etc.). I'm not ...
mongodb
1
vote
3answers
52 views
SQL : Create a full record from 2 tables
I've got a DB structure as is (simplified to maximum for understanding concern):
Table "entry" ("id" integer primary key)
Table "fields" ("name" varchar primary key, and others)
Table "entry_fields" ...
0
votes
2answers
37 views
Select records based on a non unique column
My table looks like the following:
I want to be able to select a number of records based on the auditidentity column.
I tried using LIMIT but this only limits the number of rows returned but each ...
1
vote
0answers
13 views
Postgres Migration Issue 9.0.0 to 9.3, collation error
I was trying to migrate from PostgreSQL data from PostgreSQL 9.0.0 to 9.3. I am having an issue related to the collation.
ERROR: could not determine which collation to use for string comparison
...
0
votes
0answers
26 views
Rails Query for models that contain both/multiple associations
I have model Book and Books has_many Authors. I would like to find all the books that have associations with two different authors. The relationship is HABTM.
To find the number of books with one ...
1
vote
1answer
25 views
Manually locking of DB necessary even if I use Hibernate/Postgresql/JDBC?
Sorry to ask this in case it has been answered before, but I heard (from a potential other noob) that Hibernate has/had some kind of connection pool manager that also handles locking of the database. ...
2
votes
7answers
99 views
Postrges vs oracle doing 1 million sqrts am I doing it wrong?
We am trying to get an idea of the raw performance of Oracle vs PostgreSQL. We have extensive oracle experience but are new to PostgreSQL. We are going to run lots of queries with our data, etc. But ...
-1
votes
1answer
12 views
Creating an instance of a user defined data type in a stored procedure
I have a postgres function where it returns a data type, a data type that was user-defined, I am modifying the function by removing a function call, that returned that user-defined object. I now need ...
0
votes
0answers
26 views
Simplest way to merge two postgres databases into one?
Currently I have two postgres 9 databases for two different datasources.
Database 1 is called musicbrainz, the main user is musicbrainz and tables are created in schema musicbrainz
Database 2 is ...
0
votes
1answer
43 views
Python: unable connect with database
Python 3.4 with psycopg2
I used this guide to set up a basic psycopg2 connection like so:
#!/usr/bin/python
import psycopg2
import sys
import pprint
def main():
conn_string = "dbname='CIBTST' ...
0
votes
0answers
20 views
Actual rows value in explain analyze output changed by Sort Node
When I run this query in PostgreSQL9.3.4 on TPC-H database
explain analyze select s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment from part,supplier,partsupp,nation,region where ...
0
votes
1answer
34 views
how to calculate balances in an accounting software using postgres window function
I'ved got a problem same as this but I am using Postgres.
Calculate balance with mysql
have a table which contains the following data:
ID In Out
1 100.00 0.00
2 10.00 ...
0
votes
0answers
43 views
Adding Postgresql to rails app
I am running Mac OSX 10.9.4, rails 4
I am trying to add Postgresql to a rails app to deploy to heroku. However, I cannot seem to bundle install it. And when i tried:
rails new myapp -d postgresql
...
0
votes
0answers
21 views
Dinstinct is not resulting the proper result [duplicate]
I am new to Postgresql Database...i don't know what is problem but below query with Distinct results non-distinct results. please help on this issue.
SELECT
Distinct(LEAST(name_1, ...
2
votes
3answers
71 views
What does PRIMARY KEY actually signify, and does my table need one?
I have a PostgreSQL 9.3 database with a users table that stores usernames in their case-preserved format. All queries will be case insensitive, so I should have an index that supports that. ...
0
votes
1answer
17 views
PostgreSQL replace table between databases
On a website I have some scripts which work on a temporary database.
Before the scripts are starting I drop and recreate the temporary database from the production database.
On the end of the process ...
0
votes
0answers
12 views
Postgres 9.1.13 Dump differs from Template
I have a PostgreSQL database that I want to make a backup of. I use pg_dump to dump it. I went ahead and created another brand new database and executed the dump on that new database. My assumption ...
0
votes
1answer
30 views
Pulling down from github, unable to work locally with postgreSQL
I cloned a repository in a RoR app using postgres but I'm unable to work locally. When I run the server I get this error:
could not connect to server: No such file or directory
Is the server running ...
0
votes
1answer
25 views
PostgreSQL why/when should I use ECPG
I've decided to use postgreSQL as database for a game project(C++).
At the moment I'm using Oracle and it's Pro*C precompiler at work and heard that postgreSQL also has something similar called ECPG.
...
0
votes
0answers
15 views
User privilege for index creation / drop in PostgreSQL
I know that there is GRANT privilege in PostgreSQL which grants permission to a particular user to assign privilege on various operations .Is there a way to GRANT CREATE/DROP privilege on indexes ...
0
votes
0answers
17 views
how improve the select query by avoiding/correcting the inner select(s) in Postgresql
following is my query
SELECT
gtab04.Productid,gtab04.Product,gtab04.SaleUnit
,gtab04.Packing,gtab04.ConvFact,gtab04.PTR,gtab04.MRP,
gtab04.PRate,gtab04.PTR1,gtab04.PTR2,gtab04.Location,0::integer ...
-1
votes
1answer
23 views
PostgreSQL database interaction in C# [closed]
I'm working on a project in which I have a PostgreSQL database containing various tables and what not. I have a c# application that needs to be able to enter new data/change existing data/ or retrieve ...
0
votes
1answer
26 views
How to optimize query postgres
I am running the following query:
SELECT fat.*
FROM Table1 fat
LEFT JOIN modo_captura mc ON mc.id = fat.modo_captura_id
INNER JOIN loja lj ON lj.id = fat.loja_id
INNER ...
0
votes
1answer
27 views
Warning: Data in the database will be overwritten and will not be recoverable
I want to unload my application on ROR on Heroku. I unload my application on Heroku, but I have no database. What I should do that my database unloaded on Heroku. I received many errors. One of this ...
0
votes
1answer
72 views
+50
Can not import csv to postgres (unexpected character found at location 4194303)
When I try to import a csv to my Redshift database, I get this error
Missing newline: Unexpected character 0x75 found at location 4194303
Everything seems to be fine ...
1
vote
2answers
38 views
Foreign key of serial type - ensure always populated manually
I have two tables: countries and regions.
CREATE TABLE Countries(
id SERIAL,
name VARCHAR(40) NOT NULL,
PRIMARY KEY(id)
)
CREATE TABLE Regions(
id SERIAL,
...
0
votes
1answer
26 views
Prevent dropping a persistent connection
We are using Talend and rest web services to store data on Heroku. After some idle time our persistent connection gets dropped. I have search postgresql documentation but I cannot find any parameter ...
0
votes
1answer
35 views
Slow Simultaneous Writes to Same Table in PostgreSQL Database
I suspect this question may be better suited for the Database Administrators site, so LMK if it is and I'll move it. :)
I'm something of a database/Postgres beginner here so help me out. I have a ...
1
vote
1answer
28 views
How to hide/lock linked PostgreSQL tables when MS Access is used as a front end and PostgreSQL as backend?
I have MS Access as a front end and PostgreSQL as back end for my database. So I set up the database in PostgreSQL and linked the tables to MS Access using the ODBC drivers. Everything works great, I ...
0
votes
0answers
21 views
Looking for a little direction - Attempting to update database once a day automatically
I have created a webservice that does the required actions to update said database. It downloads an excel file, converts it to a CSV, and then uses said CSV to update the Postgres database. The ...
0
votes
0answers
23 views
Selecting views in PostgreSQL/PostGIS
I am trying retrieve all tables and views created by a user using the following query:
SELECT table_name FROM information_schema.tables WHERE table_schema='public'
and table_catalog='testdb' and ...
0
votes
2answers
45 views
LEFT OUTER JOIN conditions for two queries?
I have the following two queries to find pageviews and orders for each week starting from Jan 2014.
select productid,
EXTRACT (week from dt)as WEEK,
count(productid)as PageViews
from PageView
where ...
0
votes
1answer
33 views
PostgreSQL: Error: column of relation does not exist
UPDATE master as m
SET m.husband = p.id
From per as p
where m.drid = p.drid AND p.address > 80000 AND p.address <= 154969
I have a table called per which has column called id. Basically, I ...
0
votes
1answer
17 views
Repeated Values
So I am trying to fetch pageviews and Orders from two tables Transaction and PagViews . When I run this query :
EXPLAIN
SELECT
pv.productid,
EXTRACT(week from pv.dt) as WEEK,
...
0
votes
1answer
64 views
Count the total number of records in the database but return only X records
We are shifiting one of our applications from PostGreSQL to MySQL. We have the following query in PostGres -
SELECT
idrte
, left_name
, gacodemun
, clsrte
, speed
, ...
1
vote
2answers
11 views
how can I set template0 for my new postgresql db on pgadmin
I want to create a db in postgre sql and I want to set its template property to template0 in pgAdmin III, how can I do that?
0
votes
1answer
16 views
postgresql import backup into a new db
I am trying to restore my .sql backup of postgresql. I read its documents but I want to have clear simple command for importing my whole db (schema and data) into a new postgresql db.
1
vote
1answer
25 views
postgresql where does go the output of pg_dump
I am trying to backup a db of postgresql and I want to use pg_dump command.
I tried :
psql -U postgres
postgres-# pg_dump test > backup.sql
But I don't know where the output file goes.
Any help ...
2
votes
3answers
104 views
Grant privileges for a particular database in PostgreSQL
I'm moving from MySQL to PostgreSQL and have hit a wall with user privileges. I am used to assigning a user all privileges to all tables of a database with the following command:
# MySQL
grant all ...
5
votes
1answer
47 views
What is the most efficient way to extract a date from a timestamp in PostgreSQL?
Having queries of the forms
select * from foo
where created_on::date = '2014/1/1'
or
select * from foo
where date_trunc('day', created_on) = '2014/1/1'
or
select * from foo
where ...
1
vote
2answers
26 views
Databases: Insert from multipler servers
Rally DB newbie question:
I am trying to insert user records to a DB. The id type could be an autoincrementing serial, or an INT.
How do I insert a record with an ID that is unique, and I can get ...
0
votes
1answer
22 views
How can I export data from one schema to insert on another schema with the same structure
I want to move all my data on my 'test1' schema on all table where id > 5000
So, I did export 'test1' schema and create new temp schema name 'test1_new' then I do this on all table
INSERT INTO ...
0
votes
2answers
17 views
PostgreSQL how often does a value changes
I have a user table where I save the last connection oft every user. Is there a possibility to see how often the value changes? I can't add a new column for that because old programms use this table ...
0
votes
0answers
13 views
Syncing One Online and Multiple Offline Versions of the Same Schema DB in Rails
SETUP
I have three instances running of my app deployed in three separate geographical locations running locally (since Internet connections are not reliable).
I have one master instance of the app ...
