Tag Info

Hot answers tagged

9

Assumptions / Clarifications No need to differentiate between infinity and open upper bound (upper(range) IS NULL). (You can have it either way, but it's simpler this way.) NULL vs. infinity in PostgreSQL range types Since date is a discrete type, all ranges have default [) bounds. Per documentation: The built-in range types int4range, int8range, ...


9

This was posted to pgsql-hackers mailing list and I tried to answer in brief there. It seems if the target list (specified columns) matches the tuple descriptor of the relation exactly, that is, both in number of columns and order, then the underlying scan can return a tuple that's directly consumable by the enclosing Sort node. On the other hand, if the ...


6

I've come up with this: DO $$ DECLARE i date; a daterange := 'empty'; day_as_range daterange; extreme_value date := '2100-12-31'; BEGIN FOR i IN SELECT DISTINCT generate_series( lower(range), ...


5

Either use a simple integer and represent BC as negative: CREATE TABLE information ( id serial PRIMARY KEY, year integer NOT NULL ); or use a date that's constrained to the 1st of Jan and use 2014-01-01 etc in input/output: CREATE TABLE information ( id serial PRIMARY KEY, year date NOT NULL, CONSTRAINT year_must_be_1st_jan CHECK ( ...


5

Starting with MySQL 5.7, you can see the current transactions in the performance schema. See http://dev.mysql.com/doc/refman/5.7/en/events-transactions-current-table.html Now, to find out the THREAD_ID of the current connection, use SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = CONNECTION_ID() See ...


4

It is ugly, but you can try: SELECT a, b::text FROM unnest(ARRAY[(1,'hello'), (3,'world')]) AS t(a integer, b unknown); This way the type defined in AS matches the output of unnest(), which you can cast to your needs in the SELECT list. You can try this in a small SQLFiddle.


4

Run ANALYZE after the index has been added. And make sure the column deprovision has statistics. How to verify? Basic statistics in pg_class: SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE oid = 'schema_defs'::regclass; Data histograms per column in pg_stats (pg_statistics): SELECT attname, inherited, n_distinct, ...


4

Use the built-in function pg_postmaster_start_time select current_timestamp - pg_postmaster_start_time() as uptime More details in the manual: http://www.postgresql.org/docs/current/static/functions-info.html If you want seconds, use extract: select extract(epoch from current_timestamp - pg_postmaster_start_time()) as uptime


4

I don't see how this would be more useful than just CONNECTION_ID() ... but the InnoDB transaction ID (shown in SHOW ENGINE INNODB STATUS) is available using this: SELECT trx_id FROM information_schema.innodb_trx WHERE trx_mysql_thread_id = CONNECTION_ID(); This is available in MySQL 5.5 and up (and possibly with the InnoDB plugin on 5.1), but ...


4

The datcollate column of pg_database stores LC_COLLATE for this database An other page of the documentation about collations says: The collation feature allows specifying the sort order and character classification behavior of data per-column, or even per-operation. This alleviates the restriction that the LC_COLLATE and LC_CTYPE settings of ...


4

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 ...


4

I'm ignorant of postgres SQL syntax, however this appears to be an issue with either the TYPE numeric(10,0) not containing decimal granularity (numeric(10,2)) would work, or the fact that the currency is in a european format that utilizes commas instead of decimals.


4

You need to remove the status column from your index: CREATE UNIQUE INDEX builders_unique_house ON houses (builder_id) WHERE status IN ('PLANNING', 'CONSTRUCTING'); If you keep the status column, the index will contain e.g. ('PLANNING', 1) and ('CONSTRUCTING', 1) - that combination is unique. If you remove the status, only the builder_id will be ...


4

Generally, you can always wrap a query as subquery if you don't want to output all rows: SELECT title FROM (SELECT ...) sub; But you can also use expressions in ORDER BY, not just input or output columns. So there is no need for this (like @ypercube already commented). For the case at hand, it must be mentioned that you are using the additional module ...


4

You can cut this down quite a bit - the first thing to notice is that you don't need the information in the author_titles table at all to get your results: select author_id , sum(case max_sold when 1 then 30 when 2 then 60 when 3 then 100 else 0 end)/count(*) max_items_sold_wavg , sum(sold)/count(*) items_sold_wavg , created_at ...


3

The only sensible design I can think of - since books can have 20 authors - is to always use a junction table and don't bother storing the AuthorID in the book table some of the time (and don't even think about storing a comma-separated list of AuthorIDs, please, or adding Author2, Author3, ... columns). This will just make queries complex. ...


3

You can do this without generating a warning by creating a type and casting the records to it: create type t as (a integer, b varchar(255)); select * from unnest(array[(1,'hello'), (3,'world')]::t[]); ┌───┬───────┐ │ a │ b │ ├───┼───────┤ │ 1 │ hello │ │ 3 │ world │ └───┴───────┘ tested on 9.4 and 9.3 (SQLFiddle here)


3

Right, the TRUNCATE TABLE command you are performing "... acquires an ACCESS EXCLUSIVE lock on each table it operates on", so in the first SQL block you posted, any other clients attempting to access the table after that time will be blocked until your INSERT finishes and you COMMIT. You can use the same workaround as in your MySQL-specific code; Postgres ...


3

The question is: is this sufficient or there should be more grants (database connect, usage maybe)? Security may be hardened from the default mainly on these points: The pg_hba.conf file. It filters connections before any database privilege is considered. The default is relatively open for local connections, but it might be restricted to an explicit ...


3

If the table has an index, you may want to consider the cluster command instead of re-creating it or using vacuum full. This will: Have the same effect on dead tuples - it physically re-writes the whole table Retains any existing indexes Might improve performance more than just removing dead tuples, depending on whether you will benefit from the clustering ...


3

A common solution to this problem is to have a "shadow" or "search" column for proper names. This solution has the advantage of being portable. For example, here in Ireland, there are many people who are called MacGuire and there are those called McGuire. Depending on other names, some Irish people also use accents in a particular name and some don't. ...


3

You are mainly looking for an accent insensitive collation. According to this post on stackoverflow Does PostgreSQL support “accent insensitive” collations? there is a unaccent module that supports it. I haven't used it myself though.


3

On the old laptop run pg_dump to dump the data: pg_dump --username=your_pg_user_name --dbname=gxpgran --format=c --file=gxpgran.pgdump where your_pg_user_name is the name of the Postgres user you use to access your database. After installing Postgres on the new laptop and necessary Postgres user ("your_pg_user_name"), run pg_restore pg_restore ...


3

You get the message No relations found. because the user does not have the USAGE privilege on the schema (neither granted directly, nor through the public pseudorole). You can check this by comparing \dn+ mra_dev_schm_99999 on the two databases. This is what the documentation says about this: For schemas, allows access to objects contained in the ...


3

Actually, I want to give a comment about your question. I can't to do that because I have low reputation here. So, I decided to answer whether it fits with your question or not. Let's I start. Would you like determine "query is inserted" first? I have an assumption about your "query is inserted". "Query is inserted" is every query is inserted or affected ...


3

You need to replace the $1 with the column name from the select: The query: select GetAppDate(appt.startdatetime) from tablename appt passes the column startdatetime to the function, so your select was almost there, but no quite - you forgot to replace the $1 and you incorrectly replaced the call to the function' variable with the column name: select ...


3

In order to avoid overloading my server, the requests are queued and handled one at a time. That's the problem right there. You are not avoiding but causing overloading this way. Single row INSERT / UPDATE is dramatically more expensive than doing the same en bloc. Each statement has to be planned and executed separately. Depending on missing details ...


3

According to Luis Carvalho, one of the developers of PL/Lua, PL/Lua can use LuaJIT. However, you may find there are many more factors which affect the overall performance of the PL code you write, including the overhead of the language's bindings in PostgreSQL, data type conversions, familiarity of your developers with Lua vs. other languages and their ...


3

Postgres has distinct on that does just what you are looking for: select distinct on (id) id, value_a, value_b from t order by id, value_b; SQLFiddle here You haven't specified what you would like to see when the are multiple values of value_b for an id though.


2

Something like: select t.*, a=1 as "a=1", b=2 as "b=2", c=3 as "c=3" from table t where a=1 or b=2 or c=3;



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