Looking at the PostgreSQL JDBC 9.1 documentation, connection parameters, the correct property name in the JDBC url is ApplicationName: ApplicationName = String Specifies the name of the application that is using the connection. This allows a database administrator to see what applications are connected to the server and what resources they are using through views … Read more
Use an anonymous code block: DO language plpgsql $$ BEGIN RAISE NOTICE ‘hello, world!’; END $$; Variables are referenced using %: RAISE NOTICE ‘%’, variable_name;
The difference only arises when you define a constraint as DEFERRABLE with an INITIALLY DEFERRED or INITIALLY IMMEDIATE mode. See SET CONSTRAINTS.
From PostgreSQL documentation: Another common reason for copying template0 instead of template1 is that new encoding and locale settings can be specified when copying template0, whereas a copy of template1 must use the same settings it does. This is because template1 might contain encoding-specific or locale-specific data, while template0 is known not to. You can … Read more
tl;dr: 1 GB (each character (really: codepoint) may be represented by 1 or more bytes, depending on where they are on a unicode plane – assuming a UTF-8 encoded database). You should always use text datatype for arbitrary-length character data in Postgresql now. Explanation: varchar(n) and text use the same backend storage type (varlena): a … Read more
To append an item to an array in PostgreSQL you can use the || operator or the array_append function. With || operator UPDATE table SET array_field = array_field || ‘{“new item”}’ WHERE … With array_append function UPDATE table SET array_field = array_append(array_field,’new item’) WHERE … Also, you can visit this page for array, http://www.postgresql.org/docs/current/interactive/functions-array.html I … Read more
This is not possible with a simple DEFAULT value, as the manual clearly states: The value is any variable-free expression (subqueries and cross-references to other columns in the current table are not allowed). You could use a trigger instead: CREATE OR REPLACE FUNCTION trg_foo_b_default() RETURNS trigger LANGUAGE plpgsql AS $func$ BEGIN — For just a … Read more
According to PostgreSQL Limits it’s “250 – 1600 depending on column types”. See note under the table. The column types affect it because in PostgreSQL rows may be at most 8kb (one page) wide, they cannot span pages. Big values in columns are OK because TOAST handles that, but there’s a limit to how many … Read more
This will create user admin with password test101 on localhost: psql -c “CREATE USER admin WITH PASSWORD ‘test101’;”
Here is my take on it. select * from ( SELECT id, ROW_NUMBER() OVER(PARTITION BY merchant_Id, url ORDER BY id asc) AS Row FROM Photos ) dups where dups.Row > 1 Feel free to play with the order by to tailor the records you want to delete to your specification. SQL Fiddle => http://sqlfiddle.com/#!15/d6941/1/0 SQL … Read more