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
28
votes
1answer
16k views

Is there a maximum length constraint for a postgres query?

The app we are building might execute quite a big insert queries. Is there limit that my postgres query can have only a certain number of characters?
28
votes
3answers
13k views

Monitoring Progress of Index Construction in PostgreSQL

Is there a way to monitor the progress of the creation of an index in PostgreSQL. I am creating an index on a large table and I would like to see how fast this is occurring. Is there a way to monitor ...
28
votes
1answer
22k views

Need for indexes on foreign keys

I'm struggling with indexes, primary keys and foreign keys... And the need of having them all. If I have two tables, both of them have an integer as a primary key. The first table references ...
28
votes
2answers
1k views

How to handle bad query plan caused by exact equality on range type?

I'm performing an update where I require an exact equality on a tstzrange variable. ~1M rows are modified, and the query takes ~13 minutes. The result of EXPLAIN ANALYZE can be seen here, and the ...
27
votes
4answers
65k views

Checking whether two tables have identical content in PostgreSQL

This has already been asked on Stack Overflow, but only for MySQL. I'm using PostgreSQL. Unfortunately (and surprisingly) PostgreSQL does not seem to have something like CHECKSUM table. A PostgreSQL ...
27
votes
2answers
24k views

Preventing PostgreSQL from starting on boot in Ubuntu

I am using PostgreSQL 9.2 and it runs automatically when I start the system (Kubuntu). How can I avoid this? I need to start it manually, and I don't want it to start unattended because I need to ...
27
votes
2answers
96k views

How to get the timestamp column in only milliseconds from PostgreSQL?

I have a column "created" with type timestamp without time zone default now() in a PostgreSQL database. If I select colums, it has a nice and readable format per default: SELECT created FROM mytable;...
27
votes
2answers
23k views

Differences between MATCH FULL, MATCH SIMPLE, and MATCH PARTIAL?

I've noticed a MATCH SIMPLE and MATCH FULL, but I don't understand what they do. I see default is MATCH SIMPLE; but, how do the other MATCH clauses to the FOREIGN KEY constraint function?
27
votes
3answers
24k views

upgrading from postgres 9.1 to 9.3 on ubuntu server

I have my production server(ubuntu 13.10) running with postgresql 9.1. I want to use few features of 9.3, hence want to upgrade. Could someone help me with upgrading from 9.1 to 9.3 so that there is ...
27
votes
1answer
23k views

postgresql: how do I dump and restore roles for a cluster?

Where are roles stored in a cluster, and how do I dump them? I did a pg_dump of a db and then loaded it into a different cluster, but I get a lot of these errors: psql:mydump.sql:3621: ERROR: role "...
26
votes
4answers
3k views

Can spatial index help a “range - order by - limit” query

Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes. We have the following table with a tree structure (Nested Set model) of words and their frequencies:...
26
votes
2answers
32k views

Optimizing a Postgres query with a large IN

This query gets a list of posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others. With this style of query, the obvious optimization ...
26
votes
3answers
74k views

Troubleshooting high CPU usage from postgres and postmaster services?

I'm using an open source (RHEL 6.2) based machine running SIEM software. When I run the top command, I see postgres and postmaster both with 96% CPU usage. Is there a way to pin-point or see what ...
26
votes
1answer
10k views

JSONB with indexing vs. hstore

I am trying to decide on the database design, with as few assumptions (regarding how the web app actually evolves) as possible at this stage. As a first step, understanding that JOINS are expensive, ...
26
votes
1answer
26k views

Transform all columns records to lowercase

I'm using PostgreSQL 9.1 and I have a users table with a login column. login names are case-sensitive, for example Bob, MikE, john. I would like to transform all these records into lowercase. ...
26
votes
4answers
24k views

I need to run VACUUM FULL with no available disk space

I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is ...
26
votes
3answers
126k views

Forgotten PostgreSQL Windows password

This morning I’ve been trying to connect the PostgreSQL database on my Windows 7 Professional desktop. The default value is ‘postgres’, but sure enough I forgot what password I used when I originally ...
26
votes
2answers
20k views

How to wrap long lines when SELECTing SQL text columns?

I'm selecting from a table with long text columns. I'd like to wrap long lines to a maximum line length. From: SELECT * FROM test; test_id | text --------+-----------...
26
votes
2answers
10k views

Source several files at once with psql

I want to execute several sql scripts sequentially with psql as a single transaction to set up my database schema. What is the best way to do this? In the past I know I had a master script that I ran ...
25
votes
1answer
17k views

PostgreSQL roles versus users, grant permissions

I'm currently learning the differences between PostgreSQL and MySQL as I've got a new project and I also intend to migrate my existing software from MySQL to PostgreSQL. I've actually started creating ...
25
votes
3answers
46k views

How do I list all tables in all schemas owned by the current user in Postgresql?

I can list all tables in all schemas using > \dt *.* but that also lists system tables that greatly outnumber my tables that I care about. I'd like all the tables (and possibly views) created by ...
25
votes
1answer
54k views

Postgres function assign query results to multiple variables

I need to assign values to 2 variable as below in Postgres function. a := select col1 from tbl where ... b := select col2 from tbl where ... How can I assign 2 values to 2 variables in one line ...
25
votes
5answers
67k views

PostgreSQL: How to create full copy of database schema in same database?

How I can copy my public schema into the same database with full table structure, data, functions, fk, pk and etc. My version of Postgres is 8.4 P.S. I need to copy schema NOT database
25
votes
1answer
17k views

Indexes: integer vs string performance if the number of nodes is the same

I am developing an application in Ruby on Rails with the PostgreSQL (9.4) database. For my use case, columns in tables will be looked up very frequently, as the whole point of the application is ...
25
votes
1answer
34k views

Index optimization with dates

I have a large table of objects (15M+ row) in PostgreSQL 9.0.8, for which I want to query for outdated field. I want to divide the query by millions, for scalability & concurrency purposes, and I ...
25
votes
2answers
35k views

Reference column alias in same SELECT list

I'm converting an old MS-Access-based system to PostgreSQL. In Access, fields that were made up in SELECTs could be used as parts of equations for later fields, like this: SELECT samples.id, ...
25
votes
1answer
2k views

PostgreSQL DELETE FROM fails with `Error: attempted to delete invisible tuple`

The error Trying to delete tuples containing invalid timestamps with DELETE FROM comments WHERE date > '1 Jan 9999' OR date < '1 Jan 2000' OR date_found > '1 Jan 9999' OR date_found < '1 ...
24
votes
4answers
19k views

Does updating a row with the same value actually update the row?

I have a performance-related question. Let's say I have a user with first name Michael. Take the following query: UPDATE users SET first_name = 'Michael' WHERE users.id = 123 Will the query actually ...
24
votes
2answers
12k views

Force PostgreSQL clients to use SSL?

I have configured ssl = on in postgresql.conf (and installed a certificate etcetera). Does this ensure that all clients will always connect over SSL? (I.e. does ssl = on it make it impossible to ...
24
votes
2answers
13k views

What is the impact of LC_CTYPE on a PostgreSQL database?

So, I've few Debian servers with PostgreSQL on it. Historically, those servers and PostgreSQL are localized with the Latin 9 charset and back then it was fine. Now we have to handle things like Polish,...
24
votes
2answers
13k views

How should I index a UUID in Postgres?

I'm new to PostgreSQL and somewhat new to databases in general. Is there an established way of how we should index UUID values in Postgres? I'm split between using hashing and using a trie, unless ...
24
votes
3answers
5k views

Constraint to enforce “at least one” or “exactly one” in a database

Say we have users and each user can have multiple email addresses CREATE TABLE emails ( user_id integer, email_address text, is_active boolean ) Some sample rows user_id | email_address ...
23
votes
6answers
43k views

Alter table on live production databases

How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)? I know the correct way is to ...
23
votes
3answers
49k views

How do I install pgcrypto in PostgreSQL 8.4?

I'm using Ubuntu Server 10.10 and I have installed PostgreSQL 8.4 using apt-get install postgresql. I would like to use the built-in sha1() function, but it seems that I have to install pgcrypto first....
23
votes
2answers
38k views

Improve performance of COUNT/GROUP-BY in large PostgresSQL table?

I am running PostgresSQL 9.2 and have a 12 column relation with about 6,700,000 rows. It contains nodes in a 3D space, each one referencing a user (who created it). To query which user has created how ...
23
votes
3answers
17k views

Foreign key constraint on array member?

Suppose I have a table containing job roles: CREATE TABLE roles ( "role" character varying(80) NOT NULL, CONSTRAINT "role" PRIMARY KEY (role) ); Suppose I further have a table, users, and each ...
23
votes
1answer
30k views

How to reclaim disk space on PostgreSQL?

I have local installation of 9.1 database with few tables which had cca. 300 mio records and the database grew to about 20 GB. Afterwards I issued delete from command to delete all records from it (I ...
23
votes
1answer
14k views

How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL?

According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause some ...
23
votes
1answer
13k views

Using MongoDB and PostgreSQL together

My current project is essentially a run of the mill document management system. That said, there are some wrinkles (surprise, surprise). While some of the wrinkles are fairly specific to the project,...
23
votes
1answer
8k views

Why is my PostgreSQL ORDER BY case-insensitive?

I have Postgres 9.4.4 running on Debian and I get the following ORDER BY behavior: veure_test=# show LC_COLLATE; lc_collate ------------- en_US.UTF-8 (1 row) veure_test=# SELECT ...
23
votes
1answer
2k views

Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where?

I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common ...
22
votes
5answers
77k views

SELECT DISTINCT on multiple columns

Supposing we have a table with four columns (a,b,c,d) of the same data type. Is it possible to select all distinct values within the data in the columns and return them as a single column or do I ...
22
votes
4answers
21k views

Set names to attributes when creating JSON with row_to_json

Is it possible to rename default f1, f2, f3... names when using row_to_json function for only some columns? I can do row_to_json(customers) returning {"id_customer":2,"first_name":"bla","...
22
votes
3answers
8k views

How is LIKE implemented?

Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it? The naive approach would be to inspect ...
22
votes
4answers
42k views

save/export PGAdmin server list configuration settings

I have a ton of configured connections listed in the Server Groups, is there a way I can save this? not just save the passwords but the Server Group Settings
22
votes
2answers
37k views

How to set Postgresql database to see date as “MDY” permanently

How can I set my database to see the 'date' as "MDY" without running: SET datestyle = "ISO, MDY"; every-time I'm trying to access it? I'm using Postgresql version 9.1, Ubuntu 12.04. My system locale,...
22
votes
2answers
13k views

What happens in PostgreSQL checkpoint?

Here's part of my checkpoint log: 2014-03-26 11:51:29.341 CDT,,,18682,,532854fc.48fa,4985,,2014-03-18 09:15:24 CDT,,0,LOG,00000,"checkpoint complete: wrote 15047 buffers (1.4%); 0 transaction log ...
22
votes
2answers
10k views

How to store time series data

I have what I believe is a time series data set (please correct me if I'm wrong) that has a bunch of associated values. An example would be modeling a car and tracking its various attributes during a ...
22
votes
1answer
52k views

How to connect to an remote PostgreSQL database on Ubuntu using pgAdmin3?

I'm trying to setup a PostgreSQL database on an Ubuntu machine. I would like to be able to access it using pgAdmin3 from a remote machine. How do I setup this? I have installed the PostgreSQL ...
22
votes
2answers
18k views

What is the Maintenance DB asked by pgAdmin?

pgAdmin dialog for adding new database connections asks for a "Maintenance DB". In order to be able to connect, I set it to the database I want to connect (and also, I have the rights to connect). So,...