Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

Filter by
Sorted by
Tagged with
3
votes
1answer
6k views

PostgreSql allocate memory for each connection

When we configure the memory setting for the Postgres DB what is the recommended memory allocation for each connection ? Is there any formula to apply ? I know 25% of the memory of the Server should ...
3
votes
1answer
842 views

PostgreSQL - VACUUM FULL does not free space back to the OS

On a Postgres 9.1 installation, I'm trying to reduce the amount of Postgres' disk usage by dropping records from tables and then running VACUUM FULL on those tables. My understanding of VACUUM FULL is ...
3
votes
1answer
966 views

`uuid_generate_v1mc` in Postgres “uuid-ossp” module: What is random multicast MAC address?

The uuid-ossp module extension (plugin) for Postgres offers this alternative method for generating a UUID value. uuid_generate_v1mc() This function generates a version 1 UUID but uses a random ...
3
votes
1answer
6k views

Correct use of VOLATILE COST (and ROWS) indications in Postgresql stored procedure

While looking at several examples of pl/python and pl/pgsql, I have seen many - but not all - using volatile cost. ie: CREATE OR REPLACE FUNCTION my_function() RETURNS setof record AS $BODY$ -- ...
3
votes
2answers
11k views

How to show Large Objects (LOB) contents? [duplicate]

How could I get to have a look into LOB contents? \d ticketgrantingticket Table "public.ticketgrantingticket" Column | Type | Modifiers ----------...
3
votes
1answer
4k views

Assignment of a column with dynamic column name

I get the name of a column to set in a (BEFORE UPDATE) trigger, I want to set it to the OLD value and ignore anything coming in. I've tried the following: CREATE OR REPLACE FUNCTION ...
3
votes
1answer
2k views

PostgreSQL 8.4 no levenshtein fuzzymatching?

I'm using debian squeeze package, it says function does not exist?
3
votes
2answers
7k views

ERROR: could not find array type for datatype information_schema.sql_identifier

I am trying to run the below sql command: SELECT ARRAY( SELECT column_name FROM information_schema.columns WHERE table_name ='gis_field_configuration_stage' ); and I get the below ...
3
votes
1answer
4k views

How do I query PostgreSQL enums like in MySQL?

With the following table in MySQL: CREATE TABLE bob(foo ENUM('a','b','c')); INSERT INTO bob (foo) VALUES ('a'),('b'),('c'),('a'),('a'); SELECT * FROM bob WHERE foo >= 2; +------+ | foo | +------...
3
votes
1answer
211 views

GROUP BY uninterrupted sequence of logs for same location

I'm having a hard time describing my problem or finding it via Google. I want to be able to group rows based on location and employee, but the group should be split if a row with the same location ...
3
votes
2answers
117 views

PostgreSQL: … LIMIT 1 much slower than fetching all rows?

If I fetch only one row, the query takes much longer: 1433 ms vs 23 ms Is there a work around? Slow: EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM "modwork_ticket" WHERE "modwork_ticket"."...
3
votes
3answers
304 views

Join table on some conditions

I've got a table called activities. And a table messages. Activity has many messages, and one message has one activity. This are the message columns : id sender_id receiver_id body activity_id I'm ...
3
votes
2answers
108 views

Using PostgreSQL functions instead of joining?

I don't have a question on how to do something but if I should. Sorry if there's a more appropriate forum. I have a function: CREATE OR REPLACE FUNCTION get_name(user_id UUID) RETURNS TEXT AS $$ ...
3
votes
2answers
125 views

Use PostgreSQL builtin operator <@ after including extension intarray

I added the intarray extension to my PostgreSQL database in order to make use of the - operator, but I still want to use the builtin <@ operator, not intarray's <@ operator. Is there a way to do ...
3
votes
1answer
7k views

How can I configure PostgreSQL 9.0 to start automatically under Windows XP?

Yesterday I installed PostgreSQL 9.0 on Windows XP, and after the installation the DB was running. After booting the system this morning, the data base server did not start automatically. How do I ...
3
votes
2answers
2k views

Connection timeout errors from Postgres Update Monitor (postgres 9.6)

I am running PostgreSQL 9.6 on windows 10 It seems it has an update monitor that does not use any information about the proxy agent from Windows (we have a corporate proxy). It also does not look at ...
3
votes
2answers
330 views

Within a trigger is there a way to tell if an update or insert came from a trigger or not?

In PostgreSQL 9.3.5, I'm trying to prevent a trigger from executing again due to an update or insert within the current trigger or another trigger that executes upon update or insert. So I want to ...
3
votes
0answers
1k views

PostgreSQL continuous archiving - use snapshot for base backup on AWS?

I'm new to AWS and PostgreSQL admin and have been poring over the docs trying to come up with a good backup strategy for a fairly simple setup. I have a single Amazon Web Services EC2 instance that ...
3
votes
1answer
493 views

Postgres partial index on IS NULL not working

Postgres version Using PostgreSQL 10.3. Table definition CREATE TABLE tickets ( id bigserial primary key, state character varying, closed timestamp ); CREATE INDEX "state_index" ON "tickets"...
3
votes
1answer
191 views

How should I model a database structure to store information about words?

I have an application where the user can click on any word in a text, which will bring back related information about the base word and the specific inflection (word form) of the word clicked. I've ...
3
votes
2answers
254 views

Need to export table into 2 columns, one repeating, one with rest stacked

Have a table that has one column I want to duplicate, corresponding to several columns I want to stack on top of each other. I have a table like this (foo): |col1 |col2 |col3 | | 1 | A |...
3
votes
2answers
3k views

How to get the schema name of a table of type regclass in PostgreSQL?

In writing a function to test if a column col_name exists in a table _tbl, I'd like to extract the table's schema name, which is passed into the function as a regclass parameter (for security??). ...
3
votes
2answers
3k views

Vacuum vs Auto Vacuum

I am trying to find answers for my below few queries which will help me in fine tuning my postgres DB. I did some googling but was not able to find answer. 1) Out of vacuum or Auto vacuum which is ...
3
votes
1answer
3k views

Attributes from an hstore-column as separate columns in a view?

Let's assume we have a table where it has two columns, one an ID and one an hstore column, which stores key/value pairs. You could query that column as such: SELECT name, attributes->'device' as ...
3
votes
1answer
6k views

Optimizing slow performance of simple SELECT query

I have an app called 'Links' where 1) users congregate in groups and add others, 2) post content for each other in the said groups. Groups are defined by a links_group table in my postgresql 9.6.5 DB, ...
3
votes
2answers
5k views

Casting an array of texts to an array of UUIDs

How can I cast an array of texts into an array of UUIDs? I need to do a join between two tables: users and projects. The users table has an array field named project_ids containing the project IDs ...
3
votes
2answers
2k views

Assign the result of current_setting('the_setting') to a variable in PL/pgSQL

I have a function where I need to get a configuration parameter and assign it to a variable that I will use later in the function. The problem is that when the configuration parameter is not ...
3
votes
1answer
1k views

Prepared statement overhead

I am using libpqxx to communicate with a PostgreSQL 9.3 server. For multiple table inserts, if they are split into individual prepared statements, they cost approximately 2ms each. If they are all ...
3
votes
1answer
904 views

querying a linked PostgreSQL database in SQL Server 2008

We will be deploying an application with a Postgres back-end and need to provide report writing capabilities in SQL Server. I am exploring options for real-time or near real-time reporting in this ...
3
votes
1answer
7k views

Postgresql query history hacking

I believe that PostgreSQL can log slow or unsuccessful queries. Probably I can set also PostgreSQL to log all queries executed. Contrarily, I am interested to know if there is a way that a malicious ...
3
votes
1answer
528 views

Preserve order of array elements after join

I have a query that returns a CTE looking like +-----------+-------------+ | node_id | ancestors | |-----------+-------------| | 1 | [] | | 2 | [] | | 3 |...
3
votes
1answer
134 views

PostgreSQL + query planner + amount of entities

I'm working on a college assignment about how the query planner uses statistics and makes the most optimal query plan. I have read the 57.1. Row Estimation Examples article and I know how PostgreSQL ...
3
votes
1answer
153 views

Trigger function using current row and current table name as variables (final part)

Like detailed in my first question I have a Postgres 9.1 database with multiple tables that have the exact same column names, they only vary in their column values: tbl_log_a tbl_log_b tbl_log_c ... ...
3
votes
1answer
2k views

Automatic index creation for primary vs. foreign keys in Postgresql

For PostgreSQL 9.4 (http://www.postgresql.org/docs/9.4/static/ddl-constraints.html) it is stated that A foreign key must reference columns that either are a primary key or form a unique constraint....
3
votes
1answer
462 views

Do the different “types” of autovacuum have different performance characteristics?

In monitoring the autovacuum activity on my database, I've noticed that autovacuums tagged with (to prevent wraparound) seem to take longer than "regular" autovacuums. That got me started trying to ...
3
votes
1answer
7k views

postgres and passwordless authentication

We have users for out postgres database and none have passwords... Do not know the history behind it. When I try to use clients to connect to these databases, for instance, pgadmin III, the ...
3
votes
1answer
6k views

pg_restore ERROR: “Relation does not exist” and creating new database

I have made a backup of my specific tables that I want to restore into a new database using: call pg_dump -Fc -h server -d database -U user -p password -v -f dump.sql -t public.table1 -t public....
3
votes
1answer
686 views

Low cardinality fields: integer or string

A Ticket has the statuses: new in_progress on_hold closed I could make ticket.status a string (on_hold) or an unique int(2). It is indexed. Int pros: smallest size in index Int con: low clarity ...
3
votes
2answers
734 views

How do I use window functions (“over”) together with crosstab in Postgres?

Table: application_app applied_date | applied_class | id -------------------------------+---------------+----- 2013-03-06 15:59:49.22774-05 | BOSFAL13 | 13 2013-05-12 16:56:...
3
votes
2answers
2k views

Why seq-scan can be much faster than index-scan and index-only-scan in this simple query?

I am using PostgreSQL 9.4.4. I have a query like this: SELECT COUNT(*) FROM A,B WHERE A.a = B.b a and b are the Primary Keys of tables A and B, so there are B-indexes on a & b By default, ...
3
votes
1answer
562 views

Have postgres' pg_dump export an index

I have an index that is very expensive to calculate, and I'm looking for ways to have it be restored or transfered in a simple copy fashion. It would be done along with the underlying data of course. ...
3
votes
2answers
821 views

Filter and append data to row

My query and my data set (copied from sqlfiddle): CREATE TABLE one ( id int4 primary key, p_id int4, k_id int4 ); CREATE TABLE two( id int4 primary key, p_id int4, k_id int4, ...
3
votes
1answer
41 views

How to do this binary appearance analysis

I have a table named results: integer: id json: websites An example of websites column is: [{"url": "http://google.com"}, {"url": "http://example.com"}] What I want to do is generate a binary ...
3
votes
1answer
442 views

How to create function(table_name) that returns a set of the table

I have a set of tables that share the same fields (some timestamps and users names used for version control). I need to create a function with dynamic sql that uses a table_name and a timestamp as ...
3
votes
1answer
98 views

How do I write a Postgres SQL command based on metadata of the tables themselves?

I want to retrieve all the records that were modified in the past hour of running the command. It is purely a meta data command (with no regard to the content of any tabular column data). I want to ...
3
votes
1answer
135 views

Is 'internallength' always useful in custom type definition in Postgres?

In our database we use a custom type to store 3D locations: CREATE TYPE float3d AS ( x real, y real, z real); In many examples I see INTERNALLENGTH used as a parameter with the definition of ...
3
votes
3answers
3k views

Replicating only significant records to specific database

I have a remote database which holds data for various databases in various locations. I want to do database replication, but only data specific to a certain database should be replicated to it. I can ...
3
votes
1answer
5k views

Alter default privileges for role

I have database called Blackacre and two roles who have access to it: johnrole and janerole. janerole has been granted ALL privileges and johnrole has only been granted the SELECT privilege. Problem ...
3
votes
1answer
789 views

Use of integer instead of interval (of one type)

Our DB design presently has a interval column which will only be storing days (no other interval type) so it is making sense to use INT2 (smallint) instead of interval. Reference to documentation. ...
3
votes
1answer
3k views

PostgreSQL CREATE EXTENSION file not found error on Mac OS

On PostgreSQL 9.2.3 when trying this: CREATE EXTENSION btree_gist; I get this error: ERROR: could not open extension control file "/opt/local/share/postgresql92/extension/btree_gist.control": No ...