All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

2
votes
2answers
61 views

Database “frozen” on ALTER TABLE

Our production environment just froze* this morning for a while when altering a table, adding a column actually. Offending SQL:ALTER TABLE cliente ADD COLUMN topicos character varying(20)[]; * ...
1
vote
0answers
41 views

How to identify file for “The system cannot find the file specified.” during a pg_ctl -D start?

I'm getting one message during pg_ctl -D start of the standby server. Perhaps more of a general troubleshooting question. This is what I'm seeing when I do a pg_ctl -D start: D:\>pg_ctl -D ...
4
votes
1answer
105 views

Why isn't this PosgreSQL LIKE query working?

PostgreSQL 9.2. [local] postgres@host=# select * from units where ip_address LIKE '192.168.43.%'; ERROR: 42883: operator does not exist: inet ~~ unknown LINE 1: select * from units where ip_address ...
4
votes
1answer
97 views

Foreign key with additional constraints?

There is a table called Item(id, name, cost) and Orders(id, bill_id, item_id, units), which is created to track orders placed, where same bill_id means it belongs to a single order. How to impose an ...
2
votes
1answer
54 views

Dynamic Column Label in Postgres 9.5

I am using postgres 9.5 and have built the following function: CREATE or REPLACE FUNCTION func_getratio_laglag(_numeratorLAG text, _n1 int, _denominatorLAG text, _n2 int, _table text) RETURNS ...
1
vote
1answer
40 views

Reuse subquery as in another result

In a typical social-network-like application I have the following query that gathers news from a set of followed channels. The main query gets the news, while the subquery gets the followed channels: ...
3
votes
1answer
66 views

Converting standard_conforming_strings from off to on when importing Postgres dump

I've got a Postgres 8.1 instance which has standard_conforming_strings set to off (== handles slashes as escape characters). Recent Postgres versions have this setting on by default (for good ...
0
votes
1answer
25 views

Setting up PostgreSQL Connection with DBVisualiser

Apologies for the rudimentary question, I'm new to setting up databases. Am aiming to create a new Postgres connection and load some CSV files into a table using DBVisualiser. Working on a Mac running ...
0
votes
1answer
54 views

contradiction of 3rd normal form on address table

I have an Address table and its columns are address_id, address_1, address_2, city, state, country, pin code .Its primary key is address_id . According to 3rd normal form no non-key attribute should ...
0
votes
0answers
20 views

Discrete range type without canonical function

I want to create and use a discrete range type but I'm not willing to create a canonical function as there are about 30 years that I don't write a single line of C code, and it seems it is the only ...
1
vote
2answers
35 views

Does the PostgreSQL COPY command have the option of choosing with fields to map the CSV columns to?

Does the PostgreSQL COPY command have the option of choosing with fields to map the CSV columns to? The PostgreSQL COPY command appears to expect that the target table matches its columns exactly. Am ...
0
votes
1answer
18 views

Export roles *with* privileges under PostgreSQL 9.3

New to backups under PostgreSQL. I've been backing up my database with the general commands, and it works well. Except no users or privileges are stored with it. I understand that I need to do that ...
0
votes
0answers
15 views

Date of create sequence, function

I'm using PostgreSQL 9.3 . Question: 1/ How to know date of create sequence, function ? 2/ How to know last date of change sequence, function ?
0
votes
2answers
48 views

Blank (but not null) columns - PostgreSQL 9.2

Doing by: select * from ja_customers where id = 8154501 I receive: You can see that all the required fields are "blank" with some invisible character, so they're not null, so they're valid ...
0
votes
0answers
22 views

List Schema Size - PostgreSQL 9.2

I'm trying to get a real schema size... But I'm getting different results using two queries. Can you please help with a real trusted query to get the size of a schema? Schema name: gorfs Query 1: ...
0
votes
0answers
29 views

database Deadlock again

Once again I have a deadlock problem with the following log: [17/03/16 16:42:20.020 -0300] FATAL OPERACIONES: SQLException: CreateEquipo: ERROR: deadlock detected Detail: Process 2923 waits for ...
1
vote
0answers
24 views

How to view phppgadmin as the application name in PostgreSQL's log files?

I am using PostgreSQL 9.2.2 (Windows) with phpPgAdmin 5.1. I need to monitor who connects to the server, and with which application. In the logging parameters of postgresql.conf, I use the following ...
1
vote
1answer
50 views

Create dynamic WHERE clause from a JSON field

We have tables like this: people_people ( "id" integer... "attributes" HSTORE ) object_permission ( "permissions" JSON ) An example of an object_permission.permissions is: ...
1
vote
2answers
52 views

PostgreSQL: Calculate balance working backwards from current balance

I have a balances table that stores the current_balance for bank accounts; and a transfers table that contains a row for every transfer to- and from an account (deposits are positive numbers, ...
1
vote
1answer
32 views

INSERT INTO using the RETURNING values from another INSERT

I'd like to do something like this: INSERT INTO table2(name) INSERT INTO table1(name, address) VALUES ('me', 'home') RETURNING name; And then I would expect a new record in table2 with 'me' in ...
0
votes
1answer
99 views

Permission denied to crate “pg_catalog.tablename” during pg_dump targetting another machine

I am trying to dump some tables from a 9.5 beta 2 to a 9.4.4 server. The form of the command I am using is the fairly standard: pg_dump -t table dbname | psql -h hostname -d dbname I am using the ...
1
vote
0answers
26 views

Postgresql 9.4 replication not working

I have setup my replication as per https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps. When I start my slave and check for ...
4
votes
4answers
146 views

Extract MIN and MAX values related to datetime values on Postgres 9+

I am trying to query a table that has a collection of student grades recorded over time. I would like to produce a result set that grabs the student id, the first grade, first grade date, last grade, ...
0
votes
0answers
16 views

pg_toast growth out - PostgreSQL 9.2

Trying to understand how can the pg_toast table be 90% of my entire DB? SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT ...
1
vote
1answer
31 views

Insert multiples rows/columns - PostgreSQL 9.2

I've got a table that has several columns, But I need to insert data into two columns of that table. I need to add data into those columns, from a select statement. Would it be something like: ...
0
votes
0answers
42 views

Moving slow sub-query to With clause

I am trying to optimize this query : SELECT sum(a-alias.s_count) AS count FROM a-table a-alias WHERE ( a-alias.created_at_minute_id >= (floor(extract(epoch from( select ...
1
vote
0answers
25 views

Moving extensions in AWS RDS (Postgres)

Initially we installed all extensions in default "public" schema. Now we have more and more other schemas - thus we want to add extensions and common functions to the schema common. Unfortunately AWS ...
1
vote
0answers
48 views

Postgresql string array vs join for user management schema?

I am creating a database schema for user management in PostgreSQL. As, it supports array, so I am trying with three approaches. Which one is best, querying with joins or getting/setting role data ...
0
votes
0answers
41 views

How to make sure vacuum is happening in postgres sql db

I have created a table with two columns and created index for the first column. Then inserted 2 million data using generate series function for first column (2nd column is having null). And executed ...
0
votes
1answer
37 views

PostgreSQL Bloat Warning: Vaccuum does not help

We use check_postgres to monitor our DB. We get this warning: POSTGRES_BLOAT WARNING: DB "foo" (db foo) index foo_text_index_id_uniq rows:? pages:485435 shouldbe:330673 (1.5X) wasted ...
6
votes
1answer
1k views

Why is PL/Python untrusted?

According to the docs: PL/Python is only available as an "untrusted" language, meaning it does not offer any way of restricting what users can do in it and is therefore named plpythonu. A trusted ...
0
votes
1answer
23 views

Constraint to prevent overlapping ranges in an array field in PostgreSQL

If I have a tztrange field in a table, I can make sure that no two rows have values that overlap using an exclusion constraint. To make sure that no two appointments can be scheduled at the same time, ...
0
votes
1answer
40 views

INSERT INTO + Selects - PostgreSQL 9.2 [duplicate]

I wanna add some data into two columns in a table. Would be something like: INSERT INTO dm.billables_links (billable_id) VALUES ((SELECT billable_id FROM junk.wm_260_billables2 WHERE info ilike ...
0
votes
1answer
45 views

Postgres Data Dump problem

I am trying to dump this data into Postgres: COPY address_types (game_id, device_type) FROM stdin; 1 Ps3 2 Xbox 3 Other \. But I always get this error: [Err] ERROR: syntax error at or ...
0
votes
2answers
45 views

Declare variable for SELECT statement in Postgres function

I have modified my function, but I have problems with declaring variables. I use Postgres 8.4. CREATE OR REPLACE FUNCTION requestcounterid(_mindate timestamptz, _maxdate timestamptz) RETURNS TABLE ...
1
vote
0answers
23 views

CTE ordering guarantees [duplicate]

I'm starting to make heavy use of CTEs for my postgres based application, and am wondering if selecting from a previous CTE statement guarantees any ordering? E.g. is the order of rows for this query ...
0
votes
1answer
29 views

Constraint Information - PostgreSQL 9.2

I was trying to delete some data from the table dm.billables: 1 - Select the id - PASS SELECT * INTO junk.clientid_10306 FROM dm.billables WHERE account_id = 10306; 2 - Delete the data from the ...
3
votes
1answer
55 views

SELECT very slow when no results and a LIMIT is specified

I am running into an issue where a SELECT query is very slow because it does not use an index when the number of final results is 0 and a LIMIT clause is specified. If the number of results is ...
1
vote
0answers
33 views

Accessing a row directly at a table in PostgreSQL by using the PostgreSQL's internal library

I am writing a PostgreSQL extension using the C language. I would like to do the following tasks: Store a set of rowids (or other row identifier, like ctid). Each rowid uniquely represents a row in ...
0
votes
0answers
9 views

Using the puppetlabs postgresql module with postgres plus advanced server (ppas 9.5)

I am new to puppet and I am trying to use the puppetlabs postgresql module to install and configure an EnterpriseDB (ppas9.5) database. I started by unsuccessfully trying to change the ...
1
vote
2answers
47 views

Using sub-query column in where clause

This question has been asked several times and even answered as well but it's not helpful to me, that's why I am posting it again. I have this query: select u.*,l.*, ( select count(cr.id) ...
0
votes
0answers
32 views

PostgreSQL not running every update on my script

I'm running a dozen update statements on a loop and some updates just won't happen (they return 0 affected rows), If i recall the script it will slowly update the rows, I don't know how to debug ...
0
votes
1answer
35 views

postgresql insert from select query, plus static values

Referring to How to insert values into a table from a select query in PostgreSQL?, I would like to INSERT into a table rows from another, specified by a SELECT DISTINCT, plus some static values, ...
3
votes
0answers
30 views

Should I do the maths within PostgreSQL for ACIDity?

It is my first question on Database Administration Stack Exchange. Please be kind if something is not done the nice way.. I would like to be advised concerning the way I'm working on my database, in ...
3
votes
0answers
62 views

Paid support for PostgreSQL

We're planning to sell a small ERP product that utilized PostgreSQL on the backend, targeting between 10 to 500 users per client. The management insisted on getting paid support for PostgreSQL, but ...
0
votes
0answers
25 views

Postgresql 42883 error code with encrypt function

Ok, the thing is that I'm doing a login controller on my database. Part of it is this function to create users that goes like this: create or replace function crear_usuario(par_usuario ...
0
votes
0answers
27 views

unique BIGINT as foreign key instead of primary key in distributed system

I am creating user management database schema. I am using Postgresql as database. Following is my approach. Please suggest if there is any performance issue if I use this structure. Requirement: ...
1
vote
1answer
61 views

Query to back up a PostgreSQL database

I made a CRUD web application that uses a small PostgreSQL database. It is a donors database with the columns name, telephone, blood type, etc. I have some SELECT and INSERT INTO queries, but now I ...
0
votes
1answer
35 views

How to return value for key from a jsonb column?

I've successfully created the below table on 9.5.1.0: CREATE TABLE json_test ( id serial primary key, data jsonb ); and successfully inserted this record: INSERT INTO json_test ...
0
votes
1answer
44 views

How to add a foreign key constraint to a postgresql table

I have a postgresql table called Message. It's columns are text and creation_time. How do I alter this table such that a new column called sender is added to it, which is a foreign key on the User ...