Have you tried setting the datestyle setting of the server? SET datestyle=”ISO,DMY”; You are using the psql meta-command \copy, which means the input file is local to the client. But it’s still the server who has to coerce the input to matching data-types. More generally, unlike the psql meta-command \copy which invokes COPY on the … 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
Looking at the source for PostgreSQL 9.2.1: Source: postgresql-9.2.1\src\backend\utils\adt\json.c: /* * Input. */ Datum json_in(PG_FUNCTION_ARGS) { char *text = PG_GETARG_CSTRING(0); json_validate_cstring(text); /* Internal representation is the same as text, for now */ PG_RETURN_TEXT_P(cstring_to_text(text)); } Update for PostgreSQL 9.3.5: The code has changed in the json_in function, but the json internal representation is still text: Source: … Read more
There are no “table variables” in plpgsql. That’s something you would find in SQL Server. Use a temporary table instead: BEGIN CREATE TEMP TABLE table_holder AS SELECT * FROM table_holder WHERE <some condition> ORDER BY <some expression> ; … END A temporary table exists for the lifetime of a session. To drop it at the … Read more
class ScheduledPayment(Base): id = Column(Integer, primary_key=True) invoice_id = Column(Integer) is_canceled = Column(Boolean, default=False) __table_args__ = ( Index(‘only_one_active_invoice’, invoice_id, is_canceled, unique=True, postgresql_where=(~is_canceled)), )
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
Sanitize function What you currently have can be simplified / sanitized to: CREATE OR REPLACE FUNCTION func_a (username text=””, databaseobject text=””) RETURNS ???? LANGUAGE plpgsql AS $func$ BEGIN RETURN QUERY EXECUTE format (‘SELECT * FROM %s v1 LEFT JOIN %I v2 USING (id)’ , CASE WHEN username=”*” THEN ‘view1’ ELSE ‘view3’ END , databaseobject); END … Read more
Try SELECT * FROM table WHERE arr @> ARRAY[‘s’]::varchar[]
@Igor is quite right and a couple of OR‘ed expression are fast and simple. For a long list of columns (a, b, c, d, e, f, g in the example), this is shorter and just as fast: CHECK (NOT (a,b,c,d,e,f,g) IS NULL) db<>fiddle here Old sqlfiddle How does it work? A more verbose form of … Read more
GRANT on the database is not what you need. Grant on the tables directly. Granting privileges on the database mostly is used to grant or revoke connect privileges. This allows you to specify who may do stuff in the database if they have sufficient other permissions. You want instead: GRANT ALL PRIVILEGES ON TABLE side_adzone … Read more