Tagged Questions
PostgreSQL is an open-source, object-relational database management system (ORDBMS) available for many platforms including Linux, UNIX, MS Windows and Mac OS X. Please mention your PostgreSQL version when asking questions. Greenplum Database, Amazon Redshift, ParAccel, Postgres-XC, ...
0
votes
0answers
2 views
Docker - Tomcat and PostgreSQL containers in same host - No Route to host
I have containerized a web application running on Tomcat and started the container using the command,
docker run -d -p 8080:8080 tveuser/tve-repository:tve-services
I am also running a PostgreSQL ...
2
votes
2answers
45 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
0answers
3 views
schemaName on cakephp 2.5.3
I'm using postgreSQL 9.3 and only on 2.5.3 version cakePHP I have some issues.
Cake assumes the schemaName equal my DATABASE_CONFIG name property, and my only schemaName is public.
To "solve" that ...
1
vote
3answers
18 views
When / how are default value expression functions bound with regard to search_path?
For testing purposes, I provide my own implementation of the now() function which is public.now(). Using search_path to override the default pg_catalog.now() with my own version mostly works, but I ...
0
votes
0answers
8 views
Setting 4 MSB to 0 in a 64 bit integer without using binary operators
I receive data from file in string format, which I upload to a staging table and then convert to appropriate data type before uploading it to final table.
One of the field that I am getting represent ...
0
votes
0answers
5 views
Kill all database connections on heroku when max connections have been reached
My instance has a maximum number of connections of 16. As soon as I realized that I had 19 connections, I hurried to kill one, which turns out to open a standard connection with the same priority and ...
0
votes
0answers
5 views
Order based on existence of tag, acts-as-taggable-on gem
I'd like to order a query based on the tags associated with a product. If the tag exists it should be placed at the bottom of the query. I am using the acts-as-taggable-on gem to manage tags for the ...
0
votes
0answers
9 views
HTML input, Postgresql output using Javascript
I am trying to make a script within my HTML code. This script should connect to my database, store two variables ID and PID from a form in my HTML code. Once having these vales I will use them in a ...
0
votes
2answers
28 views
Multiple queries in database depending on parameter without loops
I have a PostgreSQL database and a Perl script like this
sub reassign_minions {
my $self = shift;
my @users = $self->get_userids_of_minions();
my $sth = ...
0
votes
1answer
18 views
PostgreSQL: How to insert new records store their primary keys then use them for another insert?
If I want to save the primary key (mytable_id) from a single insert I've done the following:
CREATE OR REPLACE FUNCTION myfunct(ownerid text) RETURNS void AS
$$
DECLARE myvar INTEGER;
BEGIN
...
0
votes
2answers
19 views
Combining multiple SELECT INTO results into one variable
The method I am using may not be efficient (or possible), if so please let me know.
I am trying to use SELECT INTO to select two values and then attach them to a variable (v1) which will be returned ...
2
votes
3answers
31 views
Is the assign operation atomic in PostgreSQL?
I would like to know if the assign operation is atomic in PostgreSQL. I have a table with counter that will be incremented from multiple threads like this:
UPDATE SomeTable SET Counter = Counter + 1 ...
0
votes
0answers
16 views
what should be put into the option default for the type array
I've started working with the type array of doctrine, but I got a problem with it when I tried to define a default value. I've put array(un, deux, trois) but this created it as it is in my postgresql ...
-3
votes
0answers
26 views
i need help in installing the pg gem?
Christopher-MacBook-Pro:`rails_projects jtorkornoo$ gem install pg -- --with-pg-config=/Applications/Postgres.app/Contents/Versions/9.3/bin/pg_config`
Building native extensions with: `'--with-pg- ...
0
votes
4answers
24 views
SQL return a Distinct column and the first date of the distinct column
I have a database that contains multiple url and a date (created_at) associate with each of these url.
I would like to have something like:
Select DISTINCT url, "the first date of this url"
from ...
0
votes
0answers
17 views
Could not create database for connection named “ged” could not find driver
I'm trying to use postgresql with symfony2.3. I've installed symfony using composer. When I try to create a database using the following command line : php app/console doctrine:database:create I get ...
0
votes
0answers
7 views
Strange behavior creating a new Database with Npgsql and Entity Framework
I'm trying to create a new database with npgsql and EF 6. This is my code:
using System.Data.Common;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.Migrations;
using ...
0
votes
4answers
30 views
Using HAVING clause to get the maximun date
What can I do to get a record with the maximum date using HAVING clause without repeat the query (With postgreSQL):
This is the way repeating the query:
SELECT *
FROM table
WHERE field_1 = '1' AND ...
0
votes
0answers
8 views
syntax for string array migration
It seems postgres arrays are supported knex #226. Is there syntax for a migration beyond raw sql? The schema building docs don't mention it.
Presumably using raw sql using the postgres array docs ...
-1
votes
0answers
16 views
REPOST: How to get numbers to not convert to decimals when converting xls to csv using python?
I am trying to insert excel data into a table (postgres) using a python script. I am running into an issue though where all the numbers get converted to decimal (ie 8 -> 8.0). I realized this is ...
0
votes
1answer
21 views
QT - Postgresql - Wait for insert to finish before starting another insert
I have a program on QT which first inserts values to table with an auto increase column "Table A". Then it joins the values of Table A with Table B to insert into Table C (I need to use the recently ...
1
vote
1answer
18 views
update postgresql with syntax errors
I have one table called test, which has 4 columns:
id INT
v_out INT
v_in INT
label CHARACTER
I'm trying to update the table with the following query:
String sql = "
update
test
set
...
0
votes
2answers
23 views
Trying to find the last character in a non-standard length string
I'm a relatively new coder and I've been struggling with the following problem for a few days. I am trying to separate the characters after the last period in an email address so I can group results ...
0
votes
1answer
12 views
HAProxy splitting read/write postgresql
I use postgresql as database. I have a master/slave with streaming replication. I want to use HAProxy for load balancing. I want to send the writes to the master, and the reads to the slave. Can I do ...
0
votes
1answer
14 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
1answer
13 views
Start postgres server downloaded from ruby gem on ubuntu
When I run rake:db migrate from my rails app, I get this output:
rake aborted!
PG::ConnectionBad: could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) ...
1
vote
1answer
20 views
PostgreSQL window function with LIMIT
The query below groups the results from first into 4 equally spaced date bins and aggregates an average for the_value in each bin.
WITH first as(
SELECT
extract(EPOCH FROM foo.t_date) as ...
0
votes
0answers
8 views
How can I revert the concatenation of paper_trail versions on 2 separate objects?
Background: We have a system for approving employee hours. One object, UnapprovedEmployeeAssignment (UEA), gets created when someone submits hours for the payroll admin to approve. Upon approval, the ...
0
votes
0answers
10 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 ...
2
votes
1answer
32 views
Is PostgreSQL Stored Procedure thread safe?
I have a PostgreSQL stored procedure which is accessed concurrently by java clients in multiple JVMs. (i.e. same Java client running in multiple JVMs).
Below is my stored proc.
CREATE OR REPLACE ...
0
votes
0answers
7 views
Postgresql: Find values in JSON array by wildcard and comparison operators with index
I have a table with JSON array data I'd like to search.
CREATE TABLE data (id SERIAL, json JSON);
INSERT INTO data (id, json)
VALUES (1, '[{"name": "Value A", "value": 10}]');
INSERT INTO data ...
0
votes
1answer
24 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
27 views
update table with 4 columns specified, but only 2 columns are available
I have one table called test, which has 4 columns:
id INT
v_out INT
v_in INT
label CHARACTER
I'm trying to update the table with the following query:
String sql = "
update
test
...
0
votes
1answer
19 views
NOCYCLE in Postgres
I have a Oracle query with a NOCYCLE clause which I have to translate to Postgres:
SELECT FG_ID,CONNECT_BY_ROOT FG_ID as Parent_ID
FROM FG t
START WITH t.Parent_filter_group_id is null
CONNECT BY ...
0
votes
1answer
5 views
Insert unbounded range into TSRANGE Postgresql
Im looking to insert an unbounded range into Postgresql TSRANGE type, according to the documentation, you set the unbounded side to null.
So I have
INSERT INTO MyTable(Id, DateRange)
...
0
votes
1answer
25 views
psql: FATAL: too many connections for role
I tried connecting to the database server using the command:
psql -h host_ip -d db_name -U user_name --password
It displays the following line and refuses to connect.
psql: FATAL: too many ...
0
votes
1answer
17 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
12 views
Installation issues of hhvm-pgsql on centos 6.5 (64 bit)
When I try to install hhvm-pgsql on centos 6.5(64 bit) server the following errors occur.
$ yum install hhvm-pgsql
...
Resolving Dependencies
--> Running transaction check
---> Package ...
0
votes
1answer
25 views
postgres function not excuting properly: syntax error
CREATE OR REPLACE FUNCTION deleteWeekAggTables (tablename TEXT, Duration TEXT) RETURNS INTEGER as $$
DECLARE
startWeek INTEGER;
endWeek INTEGER;
BEGIN
startWeek= EXTRACT(YEAR FROM ...
0
votes
0answers
13 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 ...
-1
votes
0answers
24 views
Changing data in the database related on time
I would like to discuss an approach how to implement following workflow in the database.(Mysql or Postgresql.. )
I am running some simulation, which is a couple of objects changing their values in ...
1
vote
1answer
12 views
Import excel sheet to Postgresql database
Is there any tool/ query based mechanism for importing excel sheet data (xls/csv) to postgresql table?
0
votes
1answer
22 views
Rust cargo cannot find postgres package
I have this in Cargo.toml
[dependencies.postgres]
git = "https://github.com/sfackler/rust-postgres.git"
This resulted in the following output, when running cargo build:
$ cargo build -u
...
0
votes
2answers
29 views
Using 'case when' and null time stamp in select statement
I'm using a case when block in postgresql to determine how results will be displayed. The block is as follows:
(case
when four.status = 'active' and (four.expiration > (current_date + ...
-4
votes
0answers
27 views
On Demannd PostgreSql 1:M Database Synchronsation for Selected Tables [on hold]
Requirement is like I need to perform database sync on Demand or Event base or Real time for Selected tables of Admin Database to Client Databases.
So I need an any open source php tool or library ...
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
vote
0answers
17 views
Enabling PostgreSQL with custom PHP on Windows Azure
I have deployed a PHP web app connects to a PostgreSQL on Azure Website. Azure Website doesn't support to customize system level extensions to be enabled using .user.ini given within the wwwroot ...
0
votes
1answer
6 views
How to batch load data with psql with different encodings?
I have a UTF8 database and a UTF8 script to fill tables with data. However I want to run this script with psql -d instance -U user -f fillTables.sql. As my system has a Windows CP1252 encoding it ...
0
votes
0answers
17 views
Adding Postgres Objects to Template1
I'm trying to add some demo data to template1 that will be automatically added to all future User Trial Databases.
My question is: How do you run SQL statements from pgAdmin III on template1?
My ...
0
votes
1answer
8 views
Postgres. role “root” does not exist. When trying to pg:pull database from Heroku
Im new to Postgres and to Heroku. I am trying to pull the database from Heroku but I'm missing something simple. I did:
heroku pg:pull HEROKU_POSTGRESQL_IVORY_URL localdb
And I got the error:
...
