Tagged Questions
0
votes
1answer
15 views
DBUnit ignores xml element
I am using DBUnit (version 2.4.9) for loading the data for integration tests. I'm getting a weird problem that 1 field (deleted) is not being set in the DB (postgres).
Here is my XML data load:
...
3
votes
2answers
24 views
Filtering on a value computed using a subquery
This query works :
select r.id, name, description, private, auth,
(select count (*) from message m where m.room = r.id) as messageCount
from room r left join room_auth a on a.room=r.id and ...
0
votes
1answer
14 views
Replacing trigger procedure without deleting triggers
I have trigger procedure:
CREATE OR REPLACE FUNCTION my_func() RETURNS trigger AS
$BODY$
// Do something :-)
RAISE NOTICE 'Done';
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
It is used for ...
1
vote
2answers
41 views
NOT NULL constraint over a set of columns
I have a table in Postgres which currently has a NOT NULL constraint on it's email column. This table also has a phone column which is optional. I would like the system to accept some records without ...
0
votes
3answers
25 views
PostgreSql: either a correct password or a BLANK password can access the database server
I have a VPS running a postgresql database server. Now i found either i enter a correct password or i leave the password blank, i can access the database from any remote computer. A wrong password ...
1
vote
1answer
41 views
Update table with ordered values
i need to update a table ordering by price and reassigning the ordered price.
The price and values are grouped by idcategory. Here is an example:
| ID | idcategory | price | value |
| 1 | ...
3
votes
1answer
71 views
Create new table structure from orginal table
I have a table which is called TrainingMatrix defined as below:
CREATE TABLE TrainingMatrix
(
"ID" text NOT NULL,
"TrainingName" text NOT NULL,
"Institute" text,
"ExpiryDate" date,
...
0
votes
1answer
21 views
Creating vs adding primary (foreign) key constrants
I wonder, is there any difference between these two ways of adding a foreign (or primary) key to a table?
--1
--a
ALTER TABLE my_table
ADD CONSTRAINT my_table_pk PRIMARY KEY(id);
--b
ALTER TABLE ...
1
vote
1answer
63 views
Rails 4, migration to change datatype of column from daterange to tsrange causing PG::DatatypeMismatch: ERROR:
I'm trying to change a column of type daterange to tsrange (I realized I need time as well as date) using a vanilla Rails migration
def self.up
change_column :events, :when, :tsrange
end
After ...
0
votes
3answers
43 views
Now() without timezone
I have a column added_at of type timestamp without time zone. I want it's default value to be the current date-time but without time zone. The function now() returns a timezone as well.
How do I ...
0
votes
2answers
26 views
a simple Affected Row with Delete in Posgresql
I did this code in order to verify the "delete" sentence, but I want to improve this way because I am sure that you know a better way.
Would you say to me how to make improve this code?
Thanks in ...
2
votes
1answer
49 views
PostgreSQL deadlock between select for update even ordering
I have two query that deadlock together
PERFORM id
FROM stack
WHERE id IN (SELECT tmp.stkid FROM tmp_push_bulk tmp WHERE tmp.stkid > 0)
ORDER BY id
FOR UPDATE OF stack
And
PERFORM stk.id
FROM ...
1
vote
1answer
32 views
postgres sql selecting by weekday including empty records in rails
I have a query which is 90% there, I'm querying data on whats going on for each day of the week which works fine for data that is there but I'd also like data for those that aren't (I want the query ...
1
vote
1answer
36 views
SqlServer to Postgres DDL migration/conversion
We're looking to migrate from MSSQL to Postgres. I'm intending on using sql servers bcp tool for generating csv that we'll import into postgres with the bulk copy features. We are however, having ...
0
votes
0answers
162 views
createdb: could not connect to database template1: FATAL: password authentication failed for user
Installed Postgresql via Homebrew. I wanted to create a new database after unzipping a ".tar". But I have this problem.
Michael-Adelekes-MacBook:wikiful madeleke13$ sudo su postgres -c
...
0
votes
2answers
48 views
Mirror one database to another in PostgreSQL
I know the way to set up a Master/Slave DB in Postgres is having 2 DB servers, but unfortunately i have only one server for now.
How can i mirror my production db into another "backup db" in ...
0
votes
2answers
61 views
Sync/ replicate more than two databases in postgresql?
I have 5 users which uses 5 different servers(using openerp), each one uses the same database copy. Whenever the user enters data to his database, then it should sync to the 6th server's database an ...
0
votes
1answer
20 views
Determining free space after vacuum
With PostgreSQL 9.2, is there a way to determine the amount of space considered available within the database? More specifically, the approximate amount of space that can be used before the disk ...
2
votes
1answer
43 views
pg_dump to dump table schema with constraint as compact
My pg_dump returning the constraints are in alter query at the end of the dump file. I want those to be inside the create table(...) section.
For example. I have created a table as below with a ...
4
votes
2answers
81 views
Loose index scan in Postgres on more than one field?
I have several large tables in Postgres 9.2 (millions of rows) where I need to generate a unique code based on the combination of two fields, 'source' (varchar) and 'id' (int). I can do this by ...
4
votes
1answer
72 views
Problems with a PostgreSQL upsert query
I'm trying to update the database by either updating or inserting a new record into the *vote_user_table*. The table is defined as follows:
Column | Type | Modifiers ...
0
votes
1answer
29 views
can't connect to postgres after restarting mac PG::ConnectionBad
It's been a long time since I restarted my mac, and now that I have restarted it, my rails apps will no longer connect to postgres.
PG::ConnectionBad
The problem is, i can't remember how i installed ...
0
votes
2answers
34 views
Postgresql - How to rank this data?
Here is my data:
client_addr | start
------------+-----------
1.2.3.4 | 12:54:06
1.2.3.4 | 12:55:00
5.6.7.8 | 12:54:06
5.6.7.8 | 13:00:00
5.6.7.8 | 11:00:00
9.9.9.9 ...
0
votes
1answer
29 views
Data Recovery: PostgreSQL showing base volume under postgres pg_default tablespace, but does not recognize separate databases
I had an instance of Postgres (v 9.2), running locally on Windows 7. I have yet to isolate the cause, but PG became corrupted in such a way that the server abruptly stopped, and the service would ...
1
vote
1answer
43 views
Getting osm2pgsql usage error while installing OSM Bright for TileMill
I'm trying to install OSM Bright for TileMill, following this tutorial:
https://www.mapbox.com/tilemill/docs/guides/osm-bright-mac-quickstart/#osm2pgsql
I installed PostGres.app + postgresql 9.2 on ...
2
votes
3answers
71 views
Retrieve last known value for each column of a row
Not sure about the correct words to ask this question, so I will break it down.
I have a table as follows:
date_time | a | b | c
Last 4 rows:
15/10/2013 11:45:00 | null | 'timtim' | 'fred'
...
1
vote
1answer
37 views
how to create dump for specific schema in postgres DB
I have a Postgres database "rafiu" with many schemas namely test1, test2, test3. In this I want to dump the test2 schema and its data. I tried with the following query
pg_dump -U postgres -n test2 ...
2
votes
2answers
119 views
How to find whether unique key constraint exists for given columns
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 I have a table tb(C1,C2,C3) and my ...
0
votes
1answer
52 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 ...
1
vote
1answer
26 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
1answer
32 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 ...
0
votes
1answer
36 views
How to select the key of a group order by column a where a column b is not ordering
is it possible to select the id of the groups ordered accending by column line_to where a column obj_id is not also ordered accending?
So the result for my test-data should be 200 because line_no 2 ...
1
vote
1answer
64 views
postgresql index fetch on partitioned table
I'm experiencing strange PostgreSQL behavior.
I have partitioned history table into smaller pieces based on time
History -> History_part_YYYY-MM
Check constraints:
...
2
votes
1answer
60 views
Using distinct dates and aggregate functions with postgres
I'm trying to get a set of averages for each month. I'm using DATE_TRUNC to convert the date time into month year only but I still can't get the distinct DATE to work. Seems to ignore it entirely.
I ...
1
vote
1answer
39 views
PostgreSQL : SSL with TCP connections
I am trying to configure SSL communication for PostgreSQL.
I have a self-signed cerificate and I am following these instructions as specified in URL ...
2
votes
2answers
53 views
Copying data from one table to another
I'm updating data by selecting data from table and inserting into another. However there are some constraints on the other table and I get this :
DETAIL: Key (entry_id)=(391) is duplicated.
I ...
2
votes
1answer
56 views
SQL postgres aggregation/pivot of data by weeks with totals
I have a table EventLogs which records a given Event's details such as the date of the event and the fee.
+------+----------+---------------------------+-------------------+
| id | place_id | ...
0
votes
2answers
46 views
Postgresql lock shared table with multiple users
So, I'm having a synchronization issue here; Let's explain the situation:
I have a table called Sendings, that consists of these columns:
id sending_object_id type destinatary
The id is the ...
1
vote
1answer
63 views
Which tool should we use to execute stored procedures against PostgreSql
I have some stored procedures that return ref cursors. The manual suggests a somewhat convoluted approach to execute them and view the results:
-- need to be in a transaction to use cursors.
BEGIN;
...
0
votes
1answer
58 views
Postgresql/SQL Run System Catalog function in Select Query
I have a select statement that is pulling back database information such as name, owner, owner's superuser status and tablespace. For Postgresql versions less than 9.2 I was also able to pull the ...
-1
votes
1answer
40 views
What is a 'Schema' in PostgreSQL?
As the question clearly indicates, what is actually a Schema in PostgreSQL that I see in the top level of the hierarchy in pgAdmin(III)?
0
votes
3answers
257 views
How to add the Postgresql jdbc driver 9.2-1003-jdbc3 with Maven?
I have found the Postgresql JDBC4 driver here : http://mvnrepository.com/artifact/org.postgresql/postgresql/9.2-1003-jdbc4.
But I can't seem to find the 9.2-1003-jdbc3.
Do you know of an online ...
1
vote
2answers
138 views
How to use dblink_connect and dblink_connect_u
I have two databases in Postgres and want to access the data from one database to other database tables. I tried like this:
dblink_connect('myconn','hostaddr=10.10.30.53 dbname=postgres user=postgres ...
0
votes
1answer
53 views
Insert statement that will not fire trigger
I am using Postgresql 9.2 and i need insert statement, from one table to another, that will not fire trigger (bulk insert?).
On this specific table many updates, inserts and deletes occurs. On each ...
0
votes
1answer
24 views
assigning default value for type
How to assign default value to following type of oracle statement into PostgreSQL 9.3?
CREATE OR REPLACE FUNCTION(....
...
DECLARE
v_var Table01.column01%TYPE := 'SLOW';
BEGIN
...
...
END;
3
votes
2answers
54 views
How to Increment values without giving parameters
How can i increment the value of meeting and sub-meeting ids whenever a new meeting is created
Table: "Employee"
CREATE TABLE employee
(
sno SERIAL,
emp_id INTEGER,
emp_name TEXT,
...
1
vote
2answers
140 views
Postgresql - ERROR : tuple concurrently updated
The following query is performed concurrently by two threads logged in with two different users:
WITH raw_stat AS (
SELECT
host(client_addr) as client_addr,
pid ,
usename
...
5
votes
1answer
323 views
How to get a DataBase Design for POSTGRESQL to create a ER-Diagram?
I am using postgresql 9.2
postgres=# select version();
version
-------------------------------------------------------------
PostgreSQL 9.2.4, compiled by Visual C++ ...
-1
votes
2answers
104 views
Postgresql - full table scan with complex filters taking too long [closed]
We have an issue with a large postgresql (9.2) database which is around ~1 Tb on a server cluster with 100Gb Ram and a shared_buffers settings of 33Gb.
The issue is that full table scans on our user ...
1
vote
2answers
31 views
Postgresql - How to convert one timestamp to another timestamp with its closer hour?
I have the following timestamp value : 2013-10-09 12:15:55.724+02
I would like to have this instead : 2013-10-09 12:00:00.000+02
I have tried the following :
to_char(creation, 'yyyy-mm-dd ...
