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 PostgreSQL version when asking questions.
-1
votes
0answers
11 views
in postgres select columns with the same prefix
Using the following code i can select a few columns that share the same prefixes (either upreg_srt or downreg_srt) from my table and delete (drop) them:
DO
$do$
DECLARE
_column TEXT;
BEGIN
FOR ...
0
votes
1answer
15 views
why my newly created table is sorted based on the first column?
I have created a new table using the following script (basically loading csv file's data into the table) - note, that my data in the csv file in NOT sorted based on any of the table columns:
CREATE ...
1
vote
0answers
5 views
How can I format an interval type in Jasper
I have a query to returning a column that is postgres "interval" type. How can I format this field to show the duration as HH:mm? I tried a few different types for the Expression Class like ...
5
votes
2answers
37 views
How to represent dates with uncertainty in PostgreSQL
PostgreSQL provides the date format datatype to store dates. The problem with these dates is however they can't - as far as I know - reason about uncertainty.
Sometimes one does not know the full ...
1
vote
1answer
18 views
in postgres how can i delete all columns that share the same prefix
I have been using the following code for dropping all tables that share the same prefix (in this case delete all tables that their name starts with 'supenh_'):
DO
$do$
DECLARE
_tbl text;
BEGIN
FOR ...
0
votes
0answers
8 views
Create and store mappings from user's spreadsheet columns to my database (with different column-order and different text headers)
Design question:
How would you go about creating a system that will deposit data into a database from spreadsheets the user uploads--but every user will have differently ordered columns and ...
1
vote
1answer
14 views
Rails OR query with postgres array
I have a tagging system in rails using postgres' array data type. I'm trying to write a scope that will return any posts that include a tag. So far I have this scope working:
scope :with_tag, ...
0
votes
0answers
22 views
After hitting the first page, PG::UndefinedTable: ERROR: relation “users” does not exist, multiple postgres databases
I have multiple Postgres databases locally and defined:
database.yml
development:
adapter: postgresql
host: localhost
username: postgres
database: i
password: password
port: 5433
...
0
votes
0answers
8 views
tRest Talend with dynamic URL and paramters from DB
I'm trying to use Talend to get some data from a REST web service by a keyword. After this I will store this data on DB. At this point everything works fine, but just for a simple keyword that I used ...
1
vote
0answers
17 views
How to serialize ampresands in the xml document by the use postgres
How to make xpath return the actual data not the escaped one for example:
If I use xpath to xml document that contains <, the result is < but < is desired.
How to get this desired ...
0
votes
1answer
27 views
Cannot connecting heroku postgresql database to localhost
I am trying to work on a web app developed by someone else on Heroku. After pulling the source code, I installed PostgreSQL 9.3 on my computer.
Tried to import the existing database on Heroku but it ...
0
votes
0answers
13 views
where query in daterange in rails with postgresql
How to use where queries with daterange in postgresql with Rails 4.2:
ConsultingLocation Load (0.6ms) SELECT "consulting_locations".* FROM "consulting_locations" WHERE ...
-1
votes
2answers
21 views
PermGen space Memory leak
org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet default threw exception
java.lang.OutOfMemoryError: PermGen space
I am getting this error when ...
0
votes
0answers
27 views
The char column of result has redundant spaces in Postgres
I'm new to PostgreSQL, today I use php PDO to connect to Postgres and user var_dump print the results, it's astonishing that there are many redundant spaces in the char column of result set, my php ...
0
votes
0answers
20 views
Puppet include all repositories before installing package
i'm trying to install postgresql from puppetlabs package, but i need that will use my own package on my own repository.
So i'm able to specify which package should install, which service name will ...
0
votes
0answers
14 views
Rails 4 - postgresql 9.4 jsonb does not exist
I have used rails 4 and postgresql 9.4 in my project.when i was run "rdd && rdc && rdm && rds" and i got this error PG::UndefinedObject: ERROR: type "jsonb" does not exist how ...
0
votes
2answers
27 views
Postgres Enable TCP/IP Connection [on hold]
I have installed postgres-9.4 database server on a CentOS 6.6 system. I am trying to connect to this server from my laptop (connected to the same network. Laptop's ip is 192.168.1.105. I am running ...
0
votes
0answers
10 views
javax.resource.spi.ResourceAdapterInternalException: Unable to obtain physical connection to org.postgresql.ds.PGConnectionPoolDataSource
After I deploy new version on geronimo 2.0 server, a few page of my web app through exception as below:
ERROR outbound.MCFConnectionInterceptor - Error occurred creating ManagedConnection for ...
0
votes
0answers
28 views
Sqlalchemy/Flask/Postgres ProgrammingError: <unprintable ProgrammingError object>
I'm setting up an app in Flask and I just introduced a new table "StudyGroup" to my database schema. I'm still testing so I'm just drop-create-loading the database until everything works. I'm using ...
0
votes
2answers
20 views
How do webmasters migrate their site content when moving to a new platform?
I have a wordpress website with thousands of content submissions, I am now looking to scale the website and to do so would need to move it to a rails platform where I can implement more valuable ...
0
votes
1answer
21 views
psql “more is not recognized” error
I am using the postgresql (9.4) interactive terminal psql on Windows 8.1 (64 bit). After installing and creating a database and table and inserting data into a row I want to view the data.
However, ...
0
votes
0answers
21 views
Why am I getting an uninitialized constant only locally when trying to use two postgres databases?
I have a Rails application and I am trying to have two databases, one for management of live data, and one of that live data. Works fine when I test it live on Heroku, but locally it throws the ...
0
votes
1answer
12 views
Hibernate HQL Join with composite Key
i'm having some problems trying to write a HQL query to
join two entities/tables as below defined
CustomersDAO.java
@Entity
@Table(name="customers")
public class CustomerDAO implements ...
0
votes
0answers
9 views
Changing postgresql 9.3 database location on Amazon ec2 linux
I apologize for the long post. I have a Postgresql 9.3 server running on a Amazon linux AMI. I also have a compressed dump file from another server which I created using pg_dumpall. Now, I want to ...
2
votes
0answers
11 views
SQLAlchemy: order_by(None) for joinedload subclause queries?
We are using SQLAlchemy 0.9.8 on Python 2.7.7 and Postgres 9.3.
We have a query that uses joinedloads to fully populate some Recipe objects using a single query. The query creates a large SQL ...
1
vote
2answers
39 views
PostgreSQL, jsonb and primary/foreign keys: which performs better?
I'm looking at using PostgreSQL's jsonb column type for a new backend project that will mainly serve as an REST-ful JSON API. I believe that PostgreSQL's jsonb will be a good fit for this project as ...
0
votes
1answer
39 views
PostgreSQL: Returning ordered rows after a specific ID
Scenario:
I am displaying a table of records. It initially displays the first 500 with "show more" at the bottom, which returns the next 500.
Issue:
If between initial display and clicking "show ...
0
votes
2answers
16 views
Query only records that fall within a date range with postgresSQL (Redshift)
I am trying to grab all records from today's date to 14 weeks prior. I also need that same week but a year ago. I have the following:
WHERE date(date) >= date(dateadd(week,-14, current_date))
OR ...
0
votes
1answer
24 views
adding to table a column with serial numbers that based on information in two columns
I have a 100 rows table ("GendersHeight") with two columns: the 1st column ("gender") is a string ("male" or "female"). the 2nd column ("height") is a variable number. I sorted the table based on ...
0
votes
0answers
3 views
relation “branches” does not exist pgbench-tools and postgresql database scale returned “”
I've installed postgresql 9.1 on ubuntu 12.04 with pgpoolII-3.3.3 and pgPoolAdmin.
I'm trying to make a test with pgbench-tools to measure the performance of postgresql.
So I move to the directory ...
0
votes
0answers
20 views
How can I make postgres run in the background all the time?
I need to go into my Postgres config folder and type:
postgres -D .
To run my database. If I do not do this I cannot access my databases with Rails. On my other computer I never had to do this ...
1
vote
1answer
23 views
Group Data Hourly and Insert Into a Summary Table in Postgres
I have a database with approx 50 tables (for now), due to the amount of data that gets poured into a few tables, I have been tasked with creating an hourly summarization of the data and dump it into ...
0
votes
1answer
57 views
PQgetResult always returns NULL
I'm writing a PostgreSQL library in Objective-C, based on libpq (written in C). While I've built a synchronous querying method that uses PQexec sucessfully; my asynchronous method never receives the ...
1
vote
1answer
15 views
How can i add a task that will run after rake test:db:prepare in Rails
I am using rails 4.2 and i am trying to run test using the command rake test.I am trying to use plv8 extention so i create it manually from psql console, then when i run the test it seems like it has ...
-2
votes
1answer
18 views
What is the difference between a user and a role?
I know there are other threads that are similar, but I am not sure if they are relevant to Postgres.
I am reading the PostgreSQL documentation which it reads as follows:
Note: As explained in ...
2
votes
1answer
18 views
Ruby on Rails 4 - Using Rake on Heroku
I was moving my app from Nitrous.io which uses SQLite to Heroku for production which uses Postgres, and I got the following error. 2 things, I don't get why it won't do change_column, and I ...
0
votes
0answers
16 views
Slow update query on table with indexes
I have 4 indexes on my table. When Im running update query like this
UPDATE table SET column_without_indexes = true;
It runs very slow, like more than 10 min for 700k rows.
But if I drop all ...
0
votes
0answers
26 views
XML escape character import in postgres
i am using postgres function to import xml data to postgres by the use of xpath. this data might contain xml escape character such that < ; . how to solve this problem knowing that i want to ...
0
votes
1answer
17 views
PL/pgSQL control structures for lists / arrays
Is it possible to use something like this in Postgres? This is the example from PL/SQL what I want to do:
PROCEDURE CREATE_PAYMENT(P_AMOUNT IN NUMBER,
P_INVOICE_LIST IN ...
0
votes
0answers
20 views
PostgreSql not starting on Ubuntu 14.04 - 9.3/main (port 5432): down
I start it and says it started but it's not running.
postgres@zararlinuxvm:/var/run$ /etc/init.d/postgresql start
* Starting PostgreSQL 9.3 database server ...
1
vote
4answers
35 views
Deleting rows in a single database table based on the values of other rows
I have a fairly complex requirement I would like to solve using SQL in a Postgres DB. I'm sure this would be addressed in any order management system however I cannot find anything of a similar ...
0
votes
1answer
28 views
Listing a number twice in a list box when pulling data from a database using the and key word in the sql statment in C#
Sorry for the large heading, I don't know what is going on with my code. I am pulling all serial numbers for a given work order number and status code and populating a list box with the results. My ...
0
votes
2answers
31 views
How to force a merge join?
I have this query in my postgresql db
UPDATE main_table m
SET new_prod=false
FROM reseller_product p
WHERE p.distributor_id=896
AND p.sku = m.sku
AND p.reseller_id=8;
And it takes too long to ...
0
votes
0answers
33 views
Postgresql indexes not used
I'm trying to understand why indexes are not used by this query:
SELECT
te.idturno
FROM turno t
JOIN turnoestado te ON t.idturno = te.idturno
ORDER BY te.idturno, te.idturnoestado
Limit 100
Row ...
0
votes
2answers
33 views
If two DateTime stamps are the same in Rails
I have created a simple appointment system, and I now need to display something inside a loop if there's two or more appointments with the same date and time. The appointments are displayed in order ...
0
votes
1answer
41 views
Django database planning - time series data
I would like some advice on how to best organize my django models/database tables to hold the data in my webapp
Im designing a site that will hold a users telemetry data from a racing sim game. So ...
0
votes
1answer
30 views
Rails model columns and performance
I am using postgresql.
I started to realise that I have created too many columns for the User model, and most of them are boolean fields.
Correct me if I am wrong, if I just update one boolean ...
0
votes
2answers
36 views
Get specific rows from group by clause in PostgreSQL
If I've a table like below,
tid pid bid fl fq fo
---------------------------
7114 3823 2341 3 1 1
7114 3823 2340 0 0 0
7114 3823 2350 0 0 0
7114 3850 4515 0 0 0
7114 3474 ...
0
votes
0answers
24 views
deliminator column values in SQL table
I have a SQL table(3 columns: numeric,numeric,string) in PostgreSQL the form of -
100226, 118, "{{5662,"IBM Certified for On Demand Business - Solution Advisor - 816"},{5663,"MS Windows XP Desktop ...
3
votes
1answer
38 views
postgresql events per hours
I need to calculate events per working hours with different groupings (person, type of event, etc).
Here is a simplified schema for my problem.
create table person (
id integer PRIMARY KEY,
...
