Tag Info

Hot answers tagged

6

You can use the EXCEPT operator. For example, if the tables have identical structure, the following will return all rows that are in one table but not the other (so 0 rows if the tables have identical data): (TABLE a EXCEPT TABLE b) UNION ALL (TABLE b EXCEPT TABLE a) ; Or with EXISTS to return just a boolean value or a string with one of the 2 possible ...


5

The size of the physical table is typically (except for opportunistic pruning of removable pages from the end of the table) not reduced by running VACUUM (or VACUUM ANALYZE). You need to run VACUUM FULL to actually shrink the table. That's not necessarily what you want to do on a regular basis if you have write load on your table. Dead rows provide wiggle ...


5

One option is to use a FULL OUTER JOIN between the two tables in the following form: SELECT count (1) FROM table_a a FULL OUTER JOIN table_b b USING (<list of columns to compare>) WHERE a.id IS NULL OR b.id IS NULL ; For example: CREATE TABLE a (id int, val text); INSERT INTO a VALUES (1, 'foo'), (2, 'bar'); CREATE ...


4

When you create an expression index, it causes PostgreSQL to gather statistics on the that expression. With those statistics on hand, it now has an accurate estimate for the number of aggregated rows that the query will return, which leads it to make a better plan choice. Specifically in this case, without those extra statistics it thought the hash table ...


3

In Debian/Ubuntu, the functionalities of pg_ctl are provided by pg_ctlcluster. Quoted from its manpage: NAME pg_ctlcluster - start/stop/restart/reload a PostgreSQL cluster SYNOPSIS pg_ctlcluster [options] cluster-version cluster-name action -- [pg_ctl options] where action = start|stop|restart|reload|promote DESCRIPTION ...


3

Would a different kind of column be faster? For example an integer No. timestamp and timestamptz are just unsigned 64-bit integers internally anyway. Is there some way to not lock the column? It doesn't lock the column. It takes weak table lock that doesn't really block anything except DDL, and takes a row level lock on the row you're updating. ...


2

If the user has: Physical hardware access; Root / "Administrator" access to the system; or PostgreSQL superuser rights; then they effectively have total control and can do whatever they feel like. It's a little trickier when starting with superuser access, but the PostgreSQL superuser is not isolated from the underlying OS user that PostgreSQL its self ...


2

You will need to replicate the table from the read/write master. PostgreSQL's built-in physical streaming replication can only replicate the whole database server instance. The replication systems that support single-table replication require the ability to use a trigger to write a record of changes. This can only be done on the upstream read/write master. ...


1

VACUUM ANALYZE makes the difference in your example. Plus, as @jjanes supplied, the additional statistics for the functional index. Per documentation: pg_statistic also stores statistical data about the values of index expressions. These are described as if they were actual data columns; in particular, starelid references the index. No entry is made ...


1

Disabling thread safety will mean that libpq can't be safely used with multi-threaded applications unless the app is very careful to only interact with it using a single thread. This isn't a concern for the great majority of apps.


1

The composite type is clean design, but it does not help performance at all. First of all, float translates to float8 a.k.a. double precision in Postgres. You are building on a misunderstanding. The real data type occupies 4 byte (not 8). It has to be aligned at multiples of 4 bytes. Measure actual sizes with pg_column_size(). SQL Fiddle demonstrating ...


1

The parameter INTERNALLENGTH is only applicable to the creation of a new base type, which is a rather specialized operation for advanced users. It would require to provide input and output function etc. What you display is the creation of a new composite type, which is a more common operation. There is no parameter INTERNALLENGTH for that purpose. Read the ...


1

It turns out that I was putting my pidfile in /var/run which is fine for running it but when server is rebooted the file is deleted and pgbouncer can't find the file and it gives the this error FATAL @src/main.c:553 in function write_pidfile(): /var/run/pgbouncer/pgbouncer.pid: No such file or directory So simply moving the file to another location and ...


1

This is some kind of misunderstanding. The query in your question already returns what you are asking for. I only changed minor details: SELECT 'Inspections'::text AS data_label ,count(i.reporting_id) AS daily_count ,d.day AS date_column FROM ( SELECT day::date FROM generate_series(date '2013-01-01' , date ...


1

This sort of question (have new project, how do I...) appears frequently here. I will urge you to do what I would urge everybody who has a new project to do - stand on the shoulders of giants. Check out any/a few/all Open Source projects that do what you are interested in doing (or similar) and learn from their database schemas and adapt what has already ...


1

PostgreSQL does not have "auto-increment" fields in the sense of MySQL's AUTO_INCREMENT, but I'm guessing you mean SERIAL. If so, yes, what you describe is possible, but please, please don't do this. A SERIAL is just shorthand for a CREATE SEQUENCE and a default value. e.g. CREATE TABLE blah( id serial primary key ); is actually shorthand for: ...



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