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.
0
votes
0answers
17 views
how to find if unique key constraint exist on one column or more than one column
I am working on a perl script, where i need to run update queries. But I need to check if the update sql command does not violate unique key constraint.
So if a table tb(C1,C2,C3).
and my update ...
0
votes
0answers
4 views
how to use OpenJPA to persist data to postgresql database using schema?
I have a rather simple OpenJPA application inserting data into a posgresql database. Now, the DB designer has decided to split the data into separate tables using schema;
CREATE TABLE Data ...
0
votes
1answer
6 views
Cayenne, Postgres: primary key generation
I'm using Cayenne 3.2M1 and Postgres 9.0.1 to create a database. Right now I'm having problems with the primary key generation of Cayenne since I have tables with more than one primary key and as far ...
1
vote
3answers
21 views
Return multiple values and populate column in array like manner
I am using Postgres 9.3 on MacOSX.
I am wondering how I can return multiple values (depending on certain criterion) and use them to populate a column in a list/array like manner?
--DUMMY DATA
...
1
vote
1answer
16 views
Error when trying to return from plpgsql function using RETURNING with update/insert
I am able to create the following function in my Postgresql 9.3 database:
create or replace function upsert_expense(
p_id integer,
p_amount integer,
p_payer_id integer,
...
1
vote
2answers
17 views
Joined elements should *all* match some criteria
I have a setup like:
conversations(id)
notifications(id, conversation_id, user_id)
users(id)
If a conversation concerns someone, the user is linked to the conversation through one notification.
...
0
votes
1answer
13 views
How to query a PostgreSQL database column whether an item apperas more than once
I want to know, how to get a value that is represented more than once in query.
The query:
SELECT
partnumber,
bez_gem
FROM accumulation, municipality
WHERE ...
0
votes
0answers
7 views
DJango creating a model of existing table with bigserial
I'm using DJango 1.6 with python 2.7, I have an existing postgresql+postgis database
the tables declare a column of type bigserial as their primary key
for example:
CREATE TABLE MMCompany
(
...
0
votes
1answer
16 views
default postgres user and password
I'm just new to postgresql db management.
I've been playing around with a db that I didn't create.
Trying to understand the different roles that have been created.
Does the fact that I can log in ...
-1
votes
1answer
17 views
Why doesn't Django/PostgreSQL reuse primary key values after objects with that primary key have been deleted? [duplicate]
I'm trying to figure out why Django (or PostgreSQL, not to sure at which level it is) doesn't reuse primary keys from objects that have been deleted. When objects are created, they all have an id ...
0
votes
1answer
17 views
Postgresql get first and last day of all iso week in a given year
select week_num, week_start, week_end,to_char(week_start,'dd Dy Mon yyyy'), to_char(week_end,'dd Dy Mon yyyy') from(
WITH RECURSIVE t(n) AS (
select (date_trunc('week',(date_trunc('week',(2016 || ...
1
vote
0answers
11 views
Postgresql — All else equal, is querying for (small) integer or float values faster than querying for (small) string values?
I'm about to mark maybe 100,000 records retroactively/posthoc-wise with category-indicating string or integer values. There are more to come. The categories to be marked by this column reflect a ...
-1
votes
3answers
40 views
Use PHP Array Values in SQL Queries
I have a PHP array of values and I want to use the values to retrieve only certain data from an PostgreSQL database.
The array contains the id numbers to the books in the table.
Here is my code:
...
0
votes
0answers
17 views
How big can Django primary keys get?
Is there a maximum value as to how high pk values for a model can get? For example, for something like an activity feed model, the pk's can get really large.
Ex, "Kyle liked this post", "Alex ...
0
votes
0answers
8 views
Node.js Async | insert into postgresql database results from api
I am quite newbie with node.js. What i am trying to achieve is the following:
Connect to my postgresql database and get info of a place (id, coordinates).
call a weather api and get the info of that ...
0
votes
0answers
17 views
Questions about a rather specific query on Postgresql
SELECT
Account_Invoice.amount_untaxed
,right(Res_Partner.vat, length(Res_Partner.vat) - 2) AS RFC
FROM Account_Invoice
INNER JOIN Res_Partner ON Account_Invoice.partner_id = Res_Partner.id
...
0
votes
1answer
15 views
DJango 1.6 model.BinaryField causes TypeError
I've created a class model using DJango 1.6 with python 2.7 as follows:
class Company(models.Model):
Name = models.CharField(max_length=150)
PhoneNumber = ...
0
votes
1answer
21 views
Any way I can read a SQL item ID, and add a line with a new value to the same item ID?
If any of you guys know DSpace and the DB behind it, you know you can change almost any of the dc.properties there.
All the documents that are already on DSpace were submitted missing the dc.rights ...
0
votes
2answers
28 views
Add Check Constraint not working SQL
I need to add a constraint to a table such that column ab is always greater than column h. I have tried
ALTER TABLE batting
ADD constraint possibleHits check (ab>h);
But that returns
ERROR: ...
1
vote
2answers
18 views
what value to set in postgresql.conf to enable use of “localhost” and “127.0.0.1” and ip address? [on hold]
In order to be able to connect to my postgresql database from another machine, I had to configure my postgresql.conf file like so:
...
0
votes
1answer
14 views
Enable remote access to Postgre 9.2 in ubuntu?
I can't access remotely my postgre database. My fellow is making a QT project that access the database from my web-server. It's very close to the problem from this guy: Is the server running on host ...
0
votes
0answers
7 views
Hibernate Spatial - 'Invalid endian flag value encountered' Exception
I'm trying to run a simple query in Hibernate Spatial 4.0 on PostgreSQL 9.3. I have a number of objects in a table with latitude/longitude values, and I'm trying to query objects that fall within a ...
0
votes
0answers
10 views
Cannot get rid of following error “pg_dump: aborting because of server version mismatch” on Lion OSX
I am trying to download the dump of a database on Heroku in the following way:
pg_dump dc6psqngs8h580 -h url_address -U user_name> db.sql
but I am getting this error all the time:
pg_dump: ...
0
votes
2answers
26 views
PHP Cart Contents Print Out
I have a simple shop displaying data from PostgreSQL database:
$database = pg_connect("host= port= ");
$query = 'SELECT * FROM table1 ';
$query .= ' ORDER BY Price';
$result = pg_query($database, ...
1
vote
0answers
13 views
django-hstore compatibility issue with Django 1.6
django-hstore seems to be not compatible with Django 1.6. I have this error on runserver. With Django 1.5.5 everything was correct. Any idea what could be the problem?
Note that I have changed the ...
1
vote
1answer
26 views
Constraints w/ Recursive Postgres Query
I'm looking to skip a certain city as I traverse my data. Currently, this query works to find all available flights from SLC to LA, including trips with layovers. You'll see this in the picture below. ...
0
votes
0answers
13 views
How to fetch Heroku database and import it into a project on Amazon EC2?
On Heroku is PostgreSQL database, on EC2 MySQL.
I downloaded database from Heroku through the addon called pgbackups, but when I open the downloaded file, the output is like
5047 444d 5001 0c00 0408 ...
0
votes
0answers
5 views
Cloudera Manager installation issue in RHEL 5.7
We are trying to install CDH 4.4 on RHEL 5.7. But the installation was aborted because of Postgres DB installation issues.
Logs from ...
0
votes
1answer
16 views
Postgresql - change foreign key to another column
I have two tables:
create table test1 (
oldPrimaryKey varchar(100) primary key,
someText varchar(100)
)
create table test2 (
someText varchar(100),
oldPrimaryKey varchar(100) references ...
0
votes
1answer
21 views
Strange behaviour in postgresql c custom function
After I spent a lot of hours to develop my first simple c custom function below I discover a strange behaviour, could someone suggests why?
If I remove this line the function is normally compiled but ...
0
votes
1answer
25 views
pg_query expects Parameter 1 to be Resource, Boolean given… why?
I must have read every single PostGreSQL and PHP tutorial on the internet trying to find an answer for this problem. Everytime I try to connect to the db it connects successfully, but instead of the ...
0
votes
0answers
13 views
Represent postgres INET type in slick case class / table definition
Are there any possibilities of using the postgres INET type in a slick table definition?
I'm aware of https://github.com/tminglei/slick-pg but it doesn't support the INET type.
0
votes
0answers
46 views
How can I assign a certain php variable into a checkbox value?
I'm trying to make the value attribute of a checkbox a certain value from my SQL database. It needs to print out the list of almost all values, which it currently does. I need to attach be able to add ...
1
vote
2answers
21 views
PostgreSQL - Storing multiple versions of a service description
I am maintaining a web application which allows a user to upload web service descriptions. The service table to store those descriptions looks roughly like this:
CREATE TABLE service(
id integer ...
-3
votes
1answer
19 views
Change some strings with CASE statement on postgresql [on hold]
I want to replace some string values with another values in one column.
I'm using this statement:
UPDATE table1
CASE
WHEN (column1 LIKE '%A%') THEN replace(column1, 'A', 'B')
WHEN (column1 ...
0
votes
1answer
15 views
Postgres on the command line with external editor not running query
When I run \e on the command line while logged into the psql command line tool, my default editor, sublime text, duitifully opens. However, when I type in a query like this:
create table tutorials (
...
0
votes
0answers
18 views
Facing Difficulties in arranging complex entity update in PostgreSQL
I have an invoice entity with up to 100 items described by the 3 following tables:
CREATE TABLE inventory (
id SERIAL PRIMARY KEY,
name CITEXT NOT NULL UNIQUE CONSTRAINT ...
0
votes
0answers
19 views
PostgreSQL: Alternative to UPDATE (As COPY to INSERT)
I realize update operation speed in PostgreSQL doesn't meet my expectation especially when I update so many row at the same time, said 10K rows data. Is there any fast alternative to UPDATE? as using ...
0
votes
0answers
18 views
Parse postgres object literals
I'm working on a java/postgres project, and one of the db tables contains a fairly complex composite type. The project uses Ebean for O/R mapping, and I'm trying to map an ebean class to the table in ...
0
votes
1answer
14 views
Import updated version of database in Postgres
So I have this PostgreSQL database which I dumped and copied to another server. On that new server I updated rows and also added tables.
Now I would like to "sync" the new one over the old so that ...
0
votes
0answers
20 views
postgres inverse of json_each
I don't have json text in a field. I have a row with columns "text","format","keyname","..." and I want to export it to json in the format '{"value of keyname column": {"text":"value of ...
0
votes
0answers
31 views
counting entries, pooling over columns and returning boolean values
(Feel free to modify the title accordingly)
Hi, I am using Postgres 9.3 on MacOSX. I am also new to any kind of SQL so bare with me. I am trying to create a query function (code and example below), ...
0
votes
0answers
20 views
Postgres won't start with symbolic link to postgresql.conf
I would like to have a symbolic link from /etc/postgresql/9.3/main/postgresql.conf to /path/to/config/postgresql.conf. However when I do this, it seems that postgres will fail to start on reboot.
If ...
1
vote
1answer
22 views
Cannot generate a lot of unique strings
I've got a problem while trying to update a column with a unique string in PostgreSQL. I have a table with about 30kk records in it. What I want is to set a unique sha1 value to a specific column ...
0
votes
0answers
18 views
Ranking based on continuous date - postgresql
Bangalore BLR - Bagmane Tech Park 2013-10-11 Data Centre 0
Bangalore BLR - Bagmane Tech Park 2013-10-11 BMS 0
Bangalore BLR -Bagmane Tech Park 2013-10-12 BMS 0
Bangalore ...
0
votes
1answer
22 views
Is there a simple command to pack all database ids to have no gaps
I have my Postgre database which I used hibernate sequence to generate ids. But the sequence was common for all tables. But now my plan is to have a separate sequence for each table. Bow the what I ...
2
votes
3answers
29 views
How to parse postgresql wal log file to sql
The PostgreSQL database server stores "change data" in WAL log file, and I wanted to parse the archive log file to sql like mysqlbinlog parse binlog file to sql, That I can find the application ...
0
votes
1answer
13 views
PostgreSQL populate_record and hstore
I am running Postgres 9.2.4 with hstore extension (on Windows)
Given a Table:
CREATE Table tmpM (
id bigserial NOT NULL,
EventId bigint NOT NULL,
LoginId bigint NOT NULL,
CONSTRAINT tmpM_key ...
-1
votes
0answers
23 views
Reverse engineering radio buttons with multiple options and its sub-values
I need to reverse engineer below field in Rails. I've tried various methods like saving into another string column then reference this from types table with belongs_to relationship. This doesn't seem ...
-2
votes
1answer
19 views
Postgis functionalities in Microsoft SQL Server
I'm using Postgresql 8.4, Postgis 1.5 for My GIS works. Planning to change my Database to Microsoft SQL Server 2012.
Can we use all the Postgis functionalities in Microsoft SQL Server 2012. Please ...
Community Bulletin
Related Tags
sql × 5855ruby-on-rails × 2125
database × 1882
php × 1108
java × 1060
mysql × 1006
python × 999
django × 847
heroku × 797plpgsql × 785
ruby-on-rails-3 × 687
ruby × 593
postgresql-9.1 × 575
hibernate × 523
postgis × 494
activerecord × 478
jdbc × 417
performance × 383
postgresql-9.2 × 335
database-design × 324
triggers × 315
stored-procedures × 273
c# × 271
psycopg2 × 271
arrays × 256
