Tag Info

Hot answers tagged

15

No. No gain at all. The manual explicitly states: Tip: There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database ...


7

Let's first distinguish between keys and indexes, key is part of the logical model and is often implemented with an unique index. You can however create a unique index without creating a key, but that can not be referenced by a foreign key. A candidate key is something that uniquely identifies a row in a table, in SQL one of the candidate keys are normally ...


5

Yes having a unique index on the EmailAddress column should be ok. The only problem would be if someone gave up the email address after signing up for your service but didn't tell you, then whoever the owner of the email address tries to sign up. But that's a pretty rare edge case. As to if a Unique Index allows null values that'll depend on your database ...


5

Yes, it should be fine. You should use connection pooling though, as pg uses a fair amount of memory per connection (about 10MB AFAIK). Your SaaS application should connect as one user, then set role to the real user. This allows you to use connection pooling, as the connection string will be the same, but use different users. You should reset role when ...


4

Postgres currently does not use parallelism when executing a single query. The entire execution engine is single threaded for each query (you can of course still execute multiple queries in parallel). This lack of "intra query parallelism" is one of the great drawbacks of running Postgres or MySQL. Basically, it makes those engines unfit for purpose on large ...


4

Add a second column which will hold a hash of the text value. Create the index on the hash. Even if there is a hash collision there will be only a few rows to read and perform a full comparison on the text vlaues.


4

The feature of Postgres to be able to use the primary key of a table with GROUP BY and not need to add the other columns of that table in the GROUP BY clause is relatively new and works only for base tables. The optimizer is not (yet?) clever enough to identify primary keys for views, ctes or derived tables (as in your case). You can add the columns you ...


4

From Database setup: pgAgent stores its configuration in the ‘postgres’ database in your cluster And it creates there a schema named pgagent with a handful of tables prefixed by pga_ It's probably good enough to connect to the postgres database and interpret the result of: select 1 from information_schema.schemata where schema_name='pgagent'; ...


4

Your column is not of type bigint it's an array of integers. And if you read the error message carefully it tells you exactly that: operator does not exist: bigint[] < integer It says that the < operator is not defined to compare an array of bigints against a single integer value. I can only guess what you are trying to do. I assume you are ...


4

The constraint has a name, whether you specified it or not. From psql, \d foo will list all table constraints, along with their automatically assigned names. You can also find these by querying the catalog tables directly: SELECT conname FROM pg_constraint WHERE conrelid = 'foo'::regclass AND contype = 'c' The table name can be schema-qualified if ...


3

@Lennart's answer is one way to negate your query, but it only selects países that are associated with other fabricantes_distribuidores. If you want to include países that are not linked with any distribuidor, too, you have to add an outer join (I use a left outer join as per convention): SELECT DISTINCT nom.pais_id FROM ...


3

You need to store the value that you want to return somewhere: CREATE OR REPLACE FUNCTION f_get(ikey text) returns integer AS $func$ DECLARE l_id integer; BEGIN LOCK TABLE foo IN SHARE ROW EXCLUSIVE MODE; INSERT INTO foo (type) SELECT ikey WHERE NOT EXISTS ( SELECT * FROM foo WHERE type=ikey ) returning id into l_id; --< store ...


3

Try EXTRACT(DOY FROM TIMESTAMP "TIMESTAMP") -- TIMESTAMP is a reserved word in Postgres so you'll need to put your conflicting column name in double quotes (better yet -- change it so it does not conflict). Reference: http://www.postgresql.org/docs/current/static/functions-datetime.html Edit Given the updated question, the query might look like: SELECT ...


3

if in a row col1 is greater then or equal two col1 in another row, then the same relation is valid between the two corresponding col2 entries In which case you can reformulate your query to look like: SELECT * FROM table WHERE col2 >= val1 AND col2 <= val2; because you can find the lower bound for col2 from the lower bound for col1, like this: ...


2

Having the unique index on EmailAddress is fine. As you have already stated that there is validation in your application for having Email Address as required field, I would say as the other validation would be from database is not accept a user with out an Email address and prevent duplicate entry as well and these validation will be imposed with this ...


2

The comments so far are roughly correct, but to give an authoritative answer from looking at src/backend/tablecmds.c: If you're only performing ALTER TABLE ... SET TABLESPACE ... ;, then ATExecSetTableSpace() will be invoked to handle the SET TABLESPACE, and it uses copy_relation_data() to perform a WAL-logged block-by-block copy of the table. However, if ...


2

The column id is defined as SERIAL which is similar to the AUTO_INCREMENT property supported by some other databases. So you should omit it in the INSERT statement or use the DEFAULT keyword and the new id will be generated automatically. See section 8.1.4. Serial Types: http://www.postgresql.org/docs/9.3/static/datatype-numeric.html


2

create table foo ( some_date date not null check (some_date > date '1998-02-01'); ); More details in the manual: http://www.postgresql.org/docs/current/static/datatype-datetime.html http://www.postgresql.org/docs/current/static/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS


2

You want RETURN QUERY EXECUTE for dynamic SQL. With RETURN QUERY you're trying to use a string literal as the start of a query. That's nonsensical and raises a syntax error. Also, use format with the %I and %L specifiers for identifiers and literals, instead of using concatenation. Otherwise you have SQL injection issues in your function.


2

I assume you mean not associated to a certain neg.fabricante_distribuidor_id and neg.producto_solicitud_id = 1. The easiest solution is to negate the predicate in the where clause: WHERE NOT ( neg.fabricante_distribuidor_id = 1 AND neg.producto_solicitud_id = 1 ) You may prefer: WHERE neg.fabricante_distribuidor_id <> 1 OR ...


2

For general preferences you have the DateStyle and LC_TIME settings. But it would be madness to always truncate fractional seconds automatically - hiding part of the relevant information. A possible syntax shortcut would be to create a tiny function once: CREATE OR REPLACE FUNCTION my_ts(timestamp) RETURNS text LANGUAGE sql IMMUTABLE AS $$SELECT ...


2

There is more than one possibility here. One is what Peter Eisentraut suggested in the post you linked, using a writable CTE which deletes the rows from the table which will be processed or inserted elsewhere in the same query. This approach may be viable with small datasets. However, there are chances that some table bloat will occur with time, or ...


2

You're trying to use peer authentication (the default for unix sockets) from a user other than the user identity you are connecting as. You could: Create a user with the same name as your unix user and connect as that; Change the authentication mode in pg_hba.conf; ALTER USER postgres PASSWORD 'blah' then connect over TCP/IP with md5 password ...


2

Using the dblink extension, this is possible. Just create a connection inside your function, supplying the database name: SELECT dblink_connect('conn', format('hostaddr=127.0.0.1 port=5435 dbname=%I user=test password=test', 'foreign_test')); SELECT dblink_exec('conn', 'CREATE TABLE t (id integer)'); SELECT dblink_disconnect(''); Note that using the ...


2

The index data order on disk for text columns depends on the locales provided by the underlying operating system. The same locales (that is, with the same name) may differ between operating systems on the order rules, even on simple things. As an example this question: PostgreSQL 9.1 streaming replication problem: replica fails to use an index properly ...


2

No, but there is a workaround. :) I found parsel (parallel select) plpgsql function, which splits your query based on primary key, then connects to the database via dblink extension and waits for all subqueries. https://gist.github.com/mjgleaso/8031067 Author also wrote article about this function: ...


2

Using generate_series() and ctes. Tested in SQL-Fiddle: create table t ( tid serial primary key, i int default 0, name text default 'Jack' ) ; with ins as (insert into t (i, name) -- all the columns except any serial values (default, default) returning i, name ) insert into t (i, name) select ins.i, ins.name from ins ...


2

It's vital that if you forcibly terminate PostgreSQL then all postgres processes must be killed. If you don't terminate them all then the shared memory block may still be held and the database files may still be open and being written to. In this case starting PostgreSQL again may cause massive database corruption, which is why PostgreSQL tries to stop you ...


2

@a_horse already explained how to avoid the error message you saw. Here is a simple variant of the related version we have been referring to: Is SELECT or INSERT in a function prone to race conditions? CREATE OR REPLACE FUNCTION f_email_insel(_email text, OUT email_id int) AS $func$ BEGIN LOOP BEGIN -- start inner block inside loop to handle ...


2

As far as I'm aware (up to 9.2), you can not. They may have implemented that change in 9.3 or 9.4 ... but typically you would use a CTE (WITH) or just the same aggregate function.. SELECT some_agg(x) as y, some_func(some_agg(x)) from z; I seem to recall that postgres is smart enough to know that it can reuse the some_agg(x) without having to calculate it ...



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