102

Index names in PostgreSQL Index names are unique across a single database schema. Index names cannot be the same as any other index, (foreign) table, (materialized) view, sequence or user-defined composite type in the same schema. Two tables in the same schema cannot have an index of the same name. (Follows logically.) If you do not care about the name of ...


31

In other words, you want subset to be unique if type = 'true'. A partial unique index will do that: CREATE UNIQUE INDEX tbl_some_name_idx ON tbl (subset) WHERE type = 'true'; This way you can even make combinations with NULL unique, which is not possible otherwise - as detailed in this related answer: PostgreSQL multi-column unique constraint and NULL ...


25

This is how I solved my problem. Upgrade Postgresql 8.4 to 9.4 in Centos 1. Yum Install PG9.4 2. wget http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-redhat94-9.4-1.noarch.rpm 3. yum install pgdg-redhat94-9.4-1.noarch.rpm 4. yum install postgresql94-server 5. service postgresql-9.4 initdb 6. chkconfig postgresql-9.4 on Backup Data 7. su - ...


21

There is no index support for LIKE / ILIKE in PostgreSQL 8.4 - except for left anchored search terms. Since PostgreSQL 9.1 the additional module pg_trgm provides operator classes for GIN and GiST trigram indices supporting LIKE / ILIKE or regular expressions (operators ~ and friends). Install once per database: CREATE EXTENSION pg_trgm; Example GIN index: ...


19

PostgreSQL 9.1+ Note that I'm working on Ubuntu 12.04, which uses postgresql 9.1. There, I needed to: sudo apt-get install postgresql-contrib And then in my database: postgres@ztrustee:~$ psql test psql (9.1.3) Type "help" for help. test=# CREATE EXTENSION pgcrypto; CREATE EXTENSION And now I can use pgcrypto functionality, gen_random_bytes(): test=# ...


16

1. Window functions plus subqueries Count the steps to form groups, similar to Evan's idea, with modifications and fixes: SELECT id_type , min(date) AS begin , max(date) AS end , count(*) AS row_ct -- optional addition FROM ( SELECT date, id_type, count(step OR NULL) OVER (ORDER BY date) AS grp FROM ( SELECT date, id_type ...


12

It will be available in 9.5. Here is actual git commit https://github.com/postgres/postgres/commit/08309aaf74ee879699165ec8a2d53e56f2d2e947 Discussion on pg hackers http://postgresql.nabble.com/CREATE-IF-NOT-EXISTS-INDEX-td5821173.html


10

Trigger functions behave just like other functions as far as privileges are concerned. With a minor exception: To create a trigger on a table, the user must have the TRIGGER privilege on the table. The user must also have EXECUTE privilege on the trigger function. UPDATE After feedback in the comments I did some research. There is an open TODO item in ...


9

This: INFO: "pg_toast_16874": found 22483 removable, 10475318 nonremovable row versions in 10448587 pages 22483 removable, 10475318 nonremovable row versions in 10448587 pages suggests that the underlying issue is that something can still "see" those rows so they can't be removed. The candidates for that are: Lost prepared transactions. Check pg_catalog....


8

Have you tried the encode(data bytea, format text) with escape format. In that syntax format can be any of these, base64 hex escape So encode(E'123\\000456'::bytea, 'hex') will output the bytea as hex-encoded.


8

In PostgreSQL 9.0 and later, PL/pgSQL is pre-installed by default. Version 9.0 also introduced CREATE OR REPLACE LANGUAGE: CREATE OR REPLACE LANGUAGE will either create a new language, or replace an existing definition. If the language already exists, its parameters are updated according to the values specified or taken from pg_pltemplate ... To ...


8

First things first — 8.4 is no longer supported, so consider upgrading. Autovacuum settings are documented. Let's focus on the settings that affects when autovacuum kicks in. As you might know, this process is responsible for both, vacuuming and analyzing tables. One of the settings affecting ANALYZE frequency is autovacuum_analyze_threshold. As you can ...


7

You have to install the additional module fuzzystrmatch. In PostgreSQL 9.1 or later, simply run CREATE EXTENSION once per database: CREATE EXTENSION fuzzystrmatch; In PostgreSQL 8.4 on Debian Squeeze, you would run as OS postgres matching the DB superuser postgres (so with peer authentication without PW) the SQL script provided by the package postgresql-...


7

You can do this as a simple subtraction of ROW_NUMBER() operations (or if your dates are not unique, though still unique per id_type, then you can use DENSE_RANK() instead, though it will be a more expensive query): WITH IdTypes AS ( SELECT date, id_type, Row_Number() OVER (ORDER BY date) - Row_Number() OVER (PARTITION BY ...


6

If you can live with losing some values to the maximum value, you could combine a sequence with a fixed offset to get the 20 digits. I would also define a check constraint on the table to to make sure that accidental inserts without using the default value insert the wrong value: create sequence my_sequence_name; create table foo ( id numeric(20,0) ...


6

Converting BYTEA to TEXT requires you to know the internal encoding of the text. Without knowing the encoding, there is nothing you can do. In a normal text column, the database stores the text as whatever SERVER_ENCODING is set as. For instance, in your example \n gets translated into \012. Well, that's a property of encoding. It's not objectively true for ...


6

Tested with Postgres 9.1 & 9.2. Most of it should work for 8.4 as well. Simple case Without intermediate states violating the constraint over the course of a single transaction, a partial UNIQUE index on a constant value does the job. Given this test case: CREATE TEMP TABLE jobs(jobs_id int primary key, status text); INSERT INTO jobs (jobs_id, status) ...


6

This is supplemental to Erwin's answer above, but PostgreSQL supports a bunch of types of indexes. These are not generally mutually exclusive. You can think of these as being: Index method (btree, GiST, GIN, etc). Choose one, if necessary (btree being the default) Partial or full. If partial use a where clause Direct or functional. You can index the ...


6

The documentation states the following: default_with_oids (boolean) This controls whether CREATE TABLE and CREATE TABLE AS include an OID column in newly-created tables, if neither WITH OIDS nor WITHOUT OIDS is specified. It also determines whether OIDs will be included in tables created by SELECT INTO. The parameter is off by default; in ...


6

On Postgres 8.4 you can use a RECURSIVE function. How do they do it The recursive function adds a level to each different id_type, by selecting dates one by one on descending order. date | id_type | lv -------------------------------------- 2017-01-10 07:19:21.0 3 8 2017-01-10 07:19:22.0 3 8 2017-01-10 07:19:23.1 ...


5

The simple answer - because views reference the OID and not the object name. The name is translated back in the internal function. A view in PostgreSQL can be thought of as an empty table with a select rewrite rule. Using your example, the internal query tree for the rewrite rule is located in the pg_rewrite.ev_action table/column and there you'll see the ...


5

Here is another method, which is similar to Evan's and Erwin's in that it uses LAG to determine islands. It differs from those solutions in that it uses only one level of nesting, no grouping, and considerably more window functions: SELECT id_type, date AS begin, COALESCE( LEAD(prev_date) OVER (ORDER BY date ASC), last_date ) AS end FROM ( ...


5

More out of academic interest than as a practical solution, you can also achieve this with a user-defined aggregate. Like the other solutions, this will work even on Postgres 8.4, but as others have commented, please upgrade if you can. The aggregate handles null as if it is a different foo_type, so runs of nulls would be given the same grp — that may or ...


4

I think the permissions are correct because otherwise you wouldn't have gotten here. My guess is that a modification you have made has caused the problem. This is particularly the case given that it is a segmentation fault. What you really need to do is look at the call stack at the time the core was dumped and see if you can isolate where in the code ...


4

Before doing anything else, read and act on: http://wiki.postgresql.org/wiki/Corruption . Most likely you have disk or file system problems. If you suspect any kind of DB corruption for whatever reason you should stop the DB and copy the entire database at the file system level before attempting any recovery. Once you've done that, then you can look ...


4

For followers, since this seems to be the canonical question for "converting bytea to text" (i.e. so you can actually see it in pgAdmin etc.) . Here's how to just get it viewable: select encode(table.your_column_name, 'escape') as name from table_name


4

You can use a conditional aggregate: select m.id, t.name, count(case when m.action = 0 then m.id end) as action1, count(case when m.action = 1 then m.id end) as action2 from table1 m left table2 t ON (m.id = t.id) where m.status <> '2' group by m.id, t.name; In the upcoming 9.4 release the count(case(...)) could be written a ...


4

Problem This is a more complex problem than is obvious on a quick glance. You are sorting by two columns, each from a different table, while you join on two other columns. This makes it impossible for Postgres to use the provided indexes and it has to default to (very) expensive sequential scans. Here is a related case on dba.SE: Can spatial index help a “...


4

This can be done with RECURSIVE CTE to pass the "begin time" from one row to the next, and some extra (convenience) preparations. This query returns the result you wish: WITH RECURSIVE q AS ( SELECT id_type, "date", /* We compute next id_type for convenience, plus row_number */ row_number() OVER (w) AS rn, lead(...


Only top voted, non community-wiki answers of a minimum length are eligible