PostgreSQL 9.3 : 2013 release of PostgreSQL
0
votes
1answer
24 views
Errors importing data using COPY comand at postgresql 9.3.5
I am trying import a database table to Postgres 9.3.5 database server using COPY command as follows:
COPY comment (generatedid, id, "timestamp", message, bugreport_id, personcontainer_id) FROM ...
1
vote
2answers
51 views
Combine results of two queries, where the second relies on the first
I have one query which is returning
name(text), total, created_at
And another query which is returning the same thing
name(text), total, created_at
The second query usually is returning more ...
1
vote
1answer
17 views
changing postgres passwords without plaintext passwords in logs
I do not have access to a Postgres installation, so I cannot check.
I am a security guy, and I'm seeing plaintext passwords in the logs:
create user user1 with password 'PLAINTEXT PASSWORD'
How ...
0
votes
1answer
47 views
How to avoid duplications using a function
I am using this function in a bulk insert to avoid duplication of url paths.
CREATE OR REPLACE FUNCTION "univ"."gc_landing"(IN _name text, OUT landing_id int4)
RETURNS "int4"
AS $BODY$
DECLARE
...
1
vote
1answer
16 views
Using psql with string variable that has two periods
psql -d mydb -U me -h localhost -f db_log.sql -v db_user_string='Me' -v version="1.7.3"
into the script
begin;
create table db_log (
db_owner varchar(255) not null,
db_user varchar(255) ...
2
votes
2answers
40 views
UNIQUE constraint on large VARCHARs - PostgreSQL
I have a column defined like:
data_url character varying(32768) NOT NULL
and UNIQUE constraint on that column:
CONSTRAINT unique_data_url UNIQUE (data_url)
When a large object is being inserted ...
0
votes
1answer
60 views
Random unguessable primary key which preserves correct order
I want primary keys in my table to be random and unguessable while still preserving correct order (order in which documents were inserted to database). Firebase uses this algorithm: ...
2
votes
2answers
54 views
PostgreSQL CHECK constraint is evaluated wrongly
Following schema allows to violate the CHECK constrait, but only once. Then the constraint is correctly enforced.
drop database if exists example;
create database example;
create table example_table ...
0
votes
2answers
49 views
How to rollback transaction if row changed?
I am using Postgres 9.3.
When I update row in a table my flow looks like this:
Fetch old row (ex. SELECT * FROM tbl WHERE id = 1)
Validate new data in scope of old data (old row fetched in point 1) ...
0
votes
0answers
8 views
How to execute cursor in PostgreSQL?
This is my program for cursor, but I am not getting how to execute the cursor on a database.
emp=# CREATE or replace function cursor_demo() returns integer as $$
declare
emp_rec employee%rowtype;
...
1
vote
1answer
23 views
How do I debug an Idle Query?
I have a batch query that I'm running daily on my database. However, it seems to get stuck in idle state, and I'm having a lot of difficulty debugging what's going on.
The query is an aggregation on ...
1
vote
2answers
28 views
Speed of WAL send/receive when replicating
1/ Description:
I have a cluster include two machines
Master: postgresql 9.3 run on Centos
Slave : postgresql 9.3 run on Centos
Master ----> Slave : relpica from Master to Slave by asynchronous ...
0
votes
1answer
20 views
Avoid PG::TRDeadlockDetected
I am using the below function in a bulk insert, usually the bulk insert has around 60 rows and each row will have the below function. But from time to time I get PG::TRDeadlockDetected: ERROR: ...
0
votes
1answer
11 views
Can duplicate pg_hba entries result in a connection error?
I have received the following error when attempting to connect to an Arch Linux server:
Sequel::DatabaseConnectionErrorreplication#monitor
PG::Error: could not connect to server: Connection refused ...
1
vote
0answers
59 views
How to speed up query which is using <@> operator?
I am using earthdistance extension (along with cube extension).
I have following query:
SELECT "User"."id"
,"User"."displayName"
,"User"."firstName"
,"User"."lastName"
...
0
votes
0answers
20 views
Insert into a specific partition, create if not exists
So I have my master table:
p_reports: id(pk), web_id, other(data)
At the moment I create my partitions which inherits from p_reports by using:
CREATE TABLE IF NOT EXISTS ...
0
votes
1answer
37 views
Setting PostgreSQL data_directory in postgresql.conf not working
I'm attempting to configure PostgreSQL 9.3 on a Centos 6.6 box to keep the configuration files (postgresql.conf, pg_hba.conf, pg_ident.conf) in a different directory than the data directory. According ...
0
votes
0answers
37 views
Postgres Won't Start - incomplete startup packet
I have an issue that is preventing a Postgres server from starting. The server fell over after it ran out of disk space, how ever this has now been resolved although now Postgres will not start. It ...
0
votes
0answers
34 views
Duplicate key value violates unique constraint
I'm new to PostgreSQL. I'm currently working on an open source software package which is java based using postgresql 9.3.
For some requirement I populated a table (organisationunit) without using the ...
1
vote
0answers
31 views
Postgresql: Partition table by hour
I have a system that inserts several million records over the course of 24 hours. Users don't care about the data that's older than 24 hours, and typically query data over a small time frame (5 to 60 ...
3
votes
1answer
63 views
How can I implement a sequence for each foreign key value?
In a PostgreSQL (9.3) database, I have a one-to-many relationship between Accounts and Customers, so within the Customers table, I have a globally-unique id for the primary key of each Customer, plus ...
0
votes
1answer
36 views
How to convert time without time zone to timestamp without time zone in PostgreSQL?
I received this error when trying to alter a column of type time to type timestamp:
PG::CannotCoerce: ERROR: cannot cast type time without time zone to timestamp without time zone
It's not ...
0
votes
0answers
18 views
How do I alter my role to be a superuser?
I'm working my way through the steps in this slide to configure Postgresql & PostGIS.
I configured my user role as directed on this slide. Note, the new role should not be a superuser.
That ...
2
votes
1answer
61 views
How to speed up select distinct?
I have a simple select distinct on some time series data:
SELECT DISTINCT user_id
FROM events
WHERE project_id = 6
AND time > '2015-01-11 8:00:00'
AND time < '2015-02-10 8:00:00';
And it ...
0
votes
1answer
26 views
Compare only day / month / year portion of a timestamp field
I am creating a view for a certain table, where rows have timestamp fields (planned_ts). The view needs to be sorted according to these timestamps, but since the application is only interested by the ...
0
votes
0answers
21 views
How to create counter for table column (not id) which reset to 1 every month?
I suppose that I need to do something like this:
ALTER SEQUENCE product_id_seq RESTART WITH 1
But I want to do this automatically every month.
1
vote
1answer
80 views
Reuse SELECT query by adding results in array?
I have written a PostgreSQL function, which returns a specific order of products. Now I would like, not only to show, but to put the results of the first SELECT query to an array as well, so I can ...
0
votes
1answer
37 views
Connect to remote EC2 Postgresql database
I want to setup a connection between a webserver and a PostgreSQL 9.3 server. Both are hosted on Amazon EC2 servers and are part of the same security group. I can't get the connection working. The ...
2
votes
0answers
53 views
Optimizing a single table, write-once, read-many PostgreSQL database
I've just finished constructing a table of ~835 million rows using Google's ngram dataset, aggregated on the years in which they occurred so that each 2-,3-,4-, and 5-gram is represented by a single ...
0
votes
0answers
35 views
Append elements to array only if the element does't exist
WITH upd AS (
UPDATE univ.products
SET source = source::int[] || _source
WHERE project_id = _project_id AND product_id = _products AND NOT(_source = ANY(source))
RETURNING id
)
, ins AS (
...
1
vote
1answer
31 views
invalid memory alloc request size on simple query with array_agg on Postgres 9.3
Postgresql 9.3 - Debian 7 54gb RAM 8 cores (On google compute engine)
I have a huge table called search_token (~50M rows) and I'm trying to execute a very simple query that is:
select ...
0
votes
0answers
14 views
Exception handler on multiple row insert PostgreSQL [duplicate]
I'm trying to create a function which inserts rows from one database table to another, this is the part of the function which goes wrong:
BEGIN
INSERT INTO suppliers("id","name", "url", "logo", ...
0
votes
1answer
120 views
AWS RDS PostgreSQL dump/restore - syntax error
I am trying to import a postgresql database from an EC2 instance to RDS on the same subnet, so far I am doing the following...
1) Dump EC2 DB:
pg_dump --host localhost --port 5432 -Fc ...
1
vote
2answers
46 views
How to update the parent/child of all rows in the tree (ltree)?
Here are my configurations:
DB type: postgresql (9.3 if that's necessary)
Table name: product_sections
Column names: section_id (integer) & section_path (ltree)
Detail:
I have one reference ...
0
votes
0answers
22 views
pg_restore certain tables only
I'm looking for a way to use pg_restore to restore a newly-created database from a dump file, but only certain tables from that file. (There are a lot of extra tables in the database that are slow to ...
0
votes
1answer
23 views
PostgreSQL determine column type when data_type is set to ARRAY
I came across an oddity where PostgreSQL sets the data_type to ARRAY in the information_schema.columns table. I did the following query to gain some insight:
SELECT * FROM information_schema.columns
...
1
vote
1answer
16 views
Upgrade PostgreSQL 9.1 to 9.3 and server no longer starts
Technically, I didn't upgrade from 9.1 to 9.3, I built a new server with Ubuntu 14.04 and used my old 9.1 config files. PostgreSQL wouldn't start:
* Starting PostgreSQL 9.3 database server
* The ...
0
votes
0answers
49 views
PostgreSQL Performance - Many Large Databases
I run an internal (development) database server containing 25 databases each ~71GB in size. There are usually between 50 and 100 active connections at any given time. What can I do to help improve ...
0
votes
0answers
34 views
Can I manage NULL = 'SomeDefault' in the database so applications and SQL don't have to remember this optimization?
I have a table with lots of default values, and want to store very common values as NULL to save space, without forcing my SQL statements or application logic to become overburdened with conditionals.
...
1
vote
1answer
50 views
PostgreSQL: Foreign key on string column
I'm currently in a process of redesigning a legacy database. One of the tables primary key is a varchar(254). I need to create a table relating to it.
So for example:
create table "Item"
(
...
1
vote
1answer
67 views
Maximum sum() of overlapping ranges
Essentially my question is: how does one do aggregate operations involving overlapping ranges in PostgreSQL 9.3 (or 9.4)? The specific problem I have at hand is that given a range, I want to find the ...
0
votes
0answers
37 views
PostgreSQL and array of custom type
One of the table I have looks like this
CREATE TABLE orders_suppliers_contacts
(
supplier_id bigint NOT NULL,
name character varying(64) NOT NULL,
phone character varying(18),
notes character ...
1
vote
3answers
79 views
Slow query when adding additional where clause
Here's my setup (Postgres 9.3)
Posts
project_id
Messages
post_id
kind
updated_at
I'm trying to get the most recently updated 100 messages that belong to posts (on a specific project) that are ...
0
votes
2answers
64 views
PostgreSQL: Use column with hash based on other columns as unique index?
My previous problem is still not fixed: We cannot set a unique index because NULL values are allowed...
we use this table in our database:
CREATE TABLE offer (
offer_id serial PRIMARY KEY
, ...
1
vote
2answers
67 views
Return the id after insert or select
I want to build a function which will insert an email if the email value doesn't exist in the table and return the email_id of the row. How can I do this?
Also how can I return the id if the email was ...
0
votes
0answers
392 views
Upgrade Postgres 9.3 to 9.4 after Ubuntu 14.04 to 14.10 upgrade
I've just recently upgraded my Ubuntu install from 14.04 to 14.10. This automatically pushed Postgres 9.4 onto the system. I'm trying to migrate my cluster like this:
sudo pg_dropcluster 9.4 main ...
0
votes
0answers
24 views
Using Code to Access Database Dump via pgAdmin
I looked over this question on how to import a data dump using pgadmin and found the following code:
CREATE OR REPLACE FUNCTION db_to_csv(path TEXT) RETURNS void AS $$
tables RECORD;
gmdeclare
...
0
votes
1answer
109 views
PostgreSQL and MONEY data type for currency values
For a project, I created a table with a column price MONEY NOT NULL column. And I thought it would handle decimals properly, unlike a floating number (i.e. IEEE rounding issues), but I end up having ...
1
vote
0answers
57 views
postgresql 9.3 with Ubuntu 14.04.1 LTS throw 's ERROR: invalid page in block 105 of relation base/16385/17407
This is Ubuntu 14.04.1 LTS with postgresql 9.3 in production, Now i got this below error's in log.
# tail -f /var/log/postgresql/postgresql-9.3-main.log
2015-01-08 14:10:16 IST CONTEXT: automatic ...
0
votes
1answer
17 views
Postgres Error for table
I want to create this table, but i can’t,
Look this error.
CREATE TABLE ATTR(
window character varying(64) NOT NULL
);
ERROR: syntax error at or near "window"
LINE 2: window ...
