Basically correct. The important difference is that SET is an SQL command while the other two are psql meta-commands – indicated by the leading \. SET is an SQL command to change run-time parameters. It is executed on the server and has nothing to do with psql per se. \set is a psql meta-command: Sets … Read more
It is not possible, and may not ever be possible, to assign a foreign key to a json property. It’d be a major and quite complicated change to PostgreSQL’s foreign key enforcement. I don’t think it’s impossible to do, but would face similar issues to those experienced by the foreign-keys-to-arrays patch. With 9.4 it’ll be … Read more
Pass the returned user_id set as array. Create the function to accept an integer array create function get_timeinstate ( user_id_set integer[], another_param… Then call it passing the array generated by array_agg get_timeinstate( ( select array_agg(userid) from “UserState” where ctime>’2014-07-14′::timestamp ), another_param ); Inside the function: where “UserState”.userid = any (user_id_set) BTW if you are using … Read more
Citus Data has developed an open source columnar store extension for PostgreSQL. It is available under the Apache License v2.0. It supports PostgreSQL 9.3 and higher. First, creation the extension and a foreign server: CREATE EXTENSION cstore_fdw; CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw; Next, create some foreign tables: CREATE FOREIGN TABLE customer_reviews ( customer_id … Read more
It’s exactly the same thing. No need to pick one instead of the other. Usually default keyword is handy when you have computer-generated code. It makes life easier to just use every single column in the insert clause and just use default when you don’t have a specific value for certain column. Other than that, … Read more
About the permissions: Don’t forget that to access a file you need permissions on all directories in the path. So if, for example, the OS user postgres does not have permissions on the /home/MyUser directory, you get the observed error message. About \copy: You have to use the -c option to supply a command to … Read more
uuid is a specific datatype. To you it looks like text, but it’s not. You cannot compare uuid using string functions (uuid like “abc%”), or compare it with text. As Tamer suggests, you can cast it first, if you need to compare. SELECT * FROM (SELECT ‘A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11’::uuid as my_uuid) foo WHERE my_uuid::text like ‘a%’ For … Read more
The key is to switch the local timezone to the desired display timezone, for the duration of the transaction: begin; set local timezone to ‘EST5EDT’; select to_char(‘2012-05-29 15:00:00’::timestamp at time zone ‘CDT’, ‘YYYY-MM-DD HH24:MI:SS TZ’); end; The result is: 2012-05-29 16:00:00 EDT Note that with set [local] timezone it is required to use full time … Read more
Options for text search and indexing include: full-text indexing with dictionary based search, including support for prefix-search, eg to_tsvector(mycol) @@ to_tsquery(‘search:*’) text_pattern_ops indexes to support prefix string matches eg LIKE ‘abc%’ but not infix searches like %blah%;. A reverse()d index may be used for suffix searching. pg_tgrm trigram indexes on newer versions as demonstrated in … Read more
pl/python has access to the all the modules that the normal Python interpreter would have as long as they are in the $PYTHONPATH on the server (and the user that runs the postgres service). Does import lucene work if you run it in the Python interpreter on the server? If your module is installed somewhere … Read more