Hot answers tagged

20

On 18th of November, 1883 at 12:00 (new time), standard time was adopted by the American railroads. This means that before that time, Los Angeles used actual local time, based on mean solar time. After that, it was moved to its local time zone, which, being an integral offset of hours from the Greenwich Mean Time, was slightly different from the previous ...


10

Just add the constraint as NOT VALID From the manual: If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, [...] and they'll fail unless the new row matches the specified ...


9

If you have a serial column or an integer one that's automatically populated with a nextval (so that you are never supposed to insert new rows with an explicit value for that column), you could additionally check whether the value of that column is greater than a specific value: ( (("qb_id" IS NOT NULL) :: INTEGER + ("xero_id" IS NOT NULL) :: INTEGER + ...


6

The biggest difference in time in your execution plans is on the top node, the UPDATE itself. This suggests that most of your time is going to IO during the update. You could verify this by turning on track_io_timing and running the queries with EXPLAIN (ANALYZE, BUFFERS) The different plans are presenting rows to be updated in different orders. One is ...


5

The index is a sorted structure - if you need only a sufficiently small portion of the data in the table, it could be fetched from the index more efficiently. This needs a few prerequisites, though: PostgreSQL version 9.2 or newer, as index-only scans appeared in this version the index supports the query (the order of the columns of the index decides ...


5

Your CHECK constraint can be much simpler: ALTER TABLE billables ADD CONSTRAINT cc_at_least_one_mapping_needed_billables CHECK (qb_id IS NOT NULL OR xero_id IS NOT NULL OR freshbooks_id IS NOT NULL OR unleashed_id IS NOT NULL OR csv_data IS NOT NULL OR myob_id IS NOT NULL) NOT VALID; Or even ...


5

I am not exactly sure why the selectivity of an equality predicate is so radically over-estimated by the GiST index on the tstzrange column. While that remains interesting per se, it seems irrelevant to your particular case. Since your UPDATE modifies one third (!) of all existing 3M rows, an index is not going to help at all. On the contrary, incrementally ...


5

The right way to do this will probably be to have your application INSERT via a stored procedure (or in Postgres a function). If stored procedures are not an option, create a view and rewrite inserts against that view so that they affect some other table. CREATE TABLE t ( a integer, b integer ); CREATE VIEW v AS SELECT t.a, t.b FROM t; CREATE ...


4

The session information function pg_trigger_depth() would solve your problem with trigger recursion. Requires Postgres 9.2 or later. The manual: current nesting level of PostgreSQL triggers (0 if not called, directly or indirectly, from inside a trigger) Best used it in a WHEN clause to CREATE TRIGGER: CREATE OR REPLACE FUNCTION ...


4

This was not possible in Postgres up to version 9.1. From 9.2 onwards you can define a check constraint as NOT VALID (equivalent to WITH NOCHECK in MS SQL Server). See http://www.postgresql.org/docs/9.2/static/sql-altertable.html for more detail. I'm not generally happy with this sort of thing where it is at all possible to avoid. A compromise if you have a ...


4

As documented in the manual, string constants (or in general: anything that is not a number) need to be enclosed in single quotes: ALTER TABLE newarts ALTER COLUMN jurisdiction_id SET DEFAULT 'a82857b6-e336-4c6c-8499-4242';


3

The extract from pgadmin.log shows that for each output column of the result, pgadmin executes two queries against the catalog, one to obtain the formatted name of the type from its oid, another to obtain a potential base type. In your extract, if I'm counting right, the total number of such queries appears to be 106. From the ping time of 148 ms, let's ...


3

You don't need to do anything to get statement level consistency A query always sees a consistent state of the database regardless of the isolation level you use. Quote from the manual: This means that each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data. ...


3

The numbers reported by log_statement_stats come directly from the getrusage system call (assuming your OS has one). The numbers in square brackets are total for the session so far, the other numbers are deltas between the start and stop of the statement. The filesystem block size is not normalized to be the same as the PostgresSQL block size. It is in ...


3

This is the part of the execution plan where you expect an index being used: -> Seq Scan on paid gap (cost=0.00..20265.45 rows=204709 width=63) (actual time=0.024..215.813 rows=198575 loops=1) Filter: ((project_id = 1) AND ((country_iso_code)::text = 'gb'::text) AND ((source)::text = 'website'::text) AND (created_at <= ...


3

The error message you show would be the result of: VALUES ('3915105', E'\x1A', 44), -- and not '44' ('3915135', E'\x1A', 'fe88ff8f-6b4d-4e3d-8020-3475a101d25e') (Trimming some of the irrelevant columns.) Data types of columns in a free-floating VALUES expression are determined by the first row. String literals default to text, numeric literals ...


3

Mark all your existing rows as old: ALTER TABLE integrations.billables ADD COLUMN is_old BOOLEAN NOT NULL DEFAULT false; UPDATE integrations.billables SET is_old = true; And set up the constraint to ignore old rows: ALTER TABLE integrations.billables ADD CONSTRAINT cc_at_least_one_mapping_needed_billables CHECK ( NOT(("qb_id", "xero_id", ...


2

You can easily do what @a_horse_with_no_name suggests in his comment. But there is also an interesting way to do it, using PL/pgSQL as the function language. This uses a feature of the COPY command, introduced in PostgreSQL 9.3. It can now take a command as target/source, exactly where you'd use a filename or STDIN/STDOUT in normal cases: COPY ...


2

If you just need to look at something relative to $PGDATA you can use pg_ls_data SELECT pg_ls_dir('pg_xlog'); Otherwise, a simple function like this: CREATE OR REPLACE FUNCTION ls(location text) RETURNS text AS $BODY$ use warnings; use strict; my $location = $_[0]; my $output = `ls -l $location`; return($output); $BODY$ LANGUAGE ...


2

Many variants have been posted, but the simplest check to find violating rows that have NULL in every column of the set is: SELECT * FROM integrations.accounts WHERE (qb_id,xero_id,freshbooks_id,myob_id,ppy_id) IS NULL; The WHERE expression evaluates to TRUE if and only if every single column IS NULL. Details: NOT NULL constraint over a set of columns ...


2

It seems you just need the query: UPDATE sensors SET seconds=(SELECT sensors.starttime-"timestamp" FROM secure_sanity WHERE id=*id*) WHERE seconds=0; For that, it's overkill to write a plpgsql function. Or if you really want one, just replace this *id* by a function parameter put the above UPDATE query inside a CREATE FUNCTION / BEGIN ...


2

If you don't specify FOR EACH ROW in your CREATE TRIGGER statement, it will default to FOR EACH STATEMENT. In this case, the OLD and NEW records will never be assigned - in the end, which row should they refer to, if you change, for example, a hundred of them? So, create your trigger as follows: CREATE TRIGGER TR1 AFTER DELETE ON ...


2

Use date_part to get the hour part of your timestamp, then add it to your GROUP BY clause. select camera_id, count(*) as snapshot_count, date_part('hour', created_at) as hr from snapshots where created_at >= TIMESTAMP 'yesterday' AND created_at < TIMESTAMP 'today' group by camera_id, hr I generated an example table: CREATE TABLE ...


2

OIDS From Docs http://www.postgresql.org/docs/9.4/static/datatype-oid.html The oid type is currently implemented as an unsigned four-byte integer. Therefore, it is not large enough to provide database-wide uniqueness in large databases, or even in large individual tables. So, using a user-created table's OID column as a primary key is ...


2

There are a couple of misunderstandings floating around here. OIDs were included with every row by default every row in very old versions of Postgres. The default was soon changed to not include OID columns in user-defined tables. Quoting the Postgres 8.1 documentation: default_with_oids (boolean) This controls whether CREATE TABLE and CREATE ...


2

According to a grep in the sources, this error ERROR: compressed data is corrupt happens in case of a decompression failure of a LZ-compressed TOAST'ed value. See http://doxygen.postgresql.org/tuptoaster_8c.html#abcb4cc32d19cd5f89e27aeb7e7369fa8 At the row-level storage, large values are stored as pointers to tables in the pg_toast schema containing ...


2

Given your "irregular" data, you might want to look at Magneto (see here and here). Originally designed for the clothing industry which can have many different products with many different attributes. It might just be a fit for your needs. There is a community edition so you can look at the code and (esp.) the database table structures and adapt to your own ...


2

As you were hinted in comments above, your RBAR approach might be very inefficient. Consider the suggestions there. Also, I am not going into details about the different approaches of UPSERT, as it is a very broad topic, especially when one wants to do concurrency-safe. PostgreSQL 9.5 helps a lot in this regard. So, to your actual question: it is not ...


2

Read committed (especially section 13.2.1) is the default read level in PostgreSQL. This read level will give you a snapshot of what has been committed before your transaction starts. It will allow other transactions to read and write to your table, you just won't be able to see any writes made after the start of your transaction. Does this only apply ...


2

If you want to see the impact of a single query on I/O, you can also use EXPLAIN (ANALYZE, BUFFERS). Since disk reads are heavily dependent on what is already cached, results are obviously going to vary across runs. Example on a table without index, where you can see 32 blocks were cached and 65562 blocks were read: explain (analyze, verbose, buffers) ...



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