Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

Syntax Error in Boolean Null values

A
Avante last edited by

I have a csv that contains Boolean variables (yes,no,blanks). How can I recode blanks so that it'd integrate in the data type. Or fo i have to change data type for blanks to be readable?
Reply Quote 0
1 Reply Last reply

magpie QA Engineer

You can change how COPY represents NULL values.

For example, assuming the following table:

CREATE TABLE hasbool (
   id integer PRIMARY KEY,
   is_cool boolean,
   name text NOT NULL
);

I can use blanks to represent NULL as in the following psql session:

\pset null '∅'
Null display is "∅".
COPY hasbool FROM STDIN (FORMAT 'csv', NULL ' ');

Enter data to be copied followed by a newline.

End with a backslash and a period on a line by itself, or an EOF signal.


1,TRUE,joe

2, ,paul

.

COPY 2


TABLE hasbool;
id │ is_cool │ name

════╪═════════╪══════

1 │ t       │ joe

2 │ ∅       │ paul

(2 rows)

2
Posts

0
Views

Suggested Topics

G

Grouping() equivalent in PostgreSQL?
SQL, Database Testing • sql server postgresql aggregate window func languagetions • • gionna

2

0
Votes

2
Posts

0
Views

P

ROLLUP was implemented in Postgres 9.5. Original answer for Postgres 9.4: The rollup() in group by rollup(i_category,i_class) causes aggregation in hierarchical steps. It's an extension of the ISO SQL standard in SQL Server that's not implemented in Postgres. For two items, you need three steps in standard SQL - as well as in Postgres: aggregate total group by i_category group by i_category, i_class The grouping() function comes with this extension and indicates whether a given column is aggregated in each row. It's used here to sort the total and group sums first in the result. rank() is a standard window function that works the same in Postgres, but we have to rewrite it due to the above adjustments. top 100 translates to FETCH FIRST 100 ROWS ONLY in standard SQL, which is implemented in Postgres as well as the shorter LIMIT 100 (FETCH FIRST syntax works in SQL Server for 2012+ versions, too.) It's a bit odd to cut off after 100 rows in this aggregate query. I am using a CTE to reuse the intermediary results in multiple aggregations below. Except for the LIMIT everything should basically work in SQL Server just as well: WITH cte AS ( SELECT sum(ss_net_profit) AS sum_profit , sum(ss_ext_sales_price) AS sum_price , i_category, i_class FROM ssv_itemv_storev_IJGBAgg WHERE d_year = 2001 AND s_state = 'TN' GROUP BY i_category, i_class ) SELECT sum(sum_profit)/sum(sum_price) AS gross_margin , NULL AS i_category, NULL AS i_class , 2 AS lochierarchy , 1 AS rank_within_parent FROM cte UNION ALL ( -- parentheses required! SELECT sum(sum_profit)/sum(sum_price) AS gross_margin , i_category, NULL AS i_class , 1 AS lochierarchy , rank() OVER (ORDER BY sum(sum_profit)/sum(sum_price)) AS rank_within_parent FROM cte GROUP BY i_category ORDER BY rank_within_parent, i_category -- last item = my addition to break ties ) UNION ALL ( SELECT sum_profit/sum_price AS gross_margin , i_category, i_class , 0 AS lochierarchy , rank() OVER (PARTITION BY i_category ORDER BY sum_profit/sum_price) AS rank_within_parent FROM cte ORDER BY i_category, rank_within_parent, i_class -- last item = my addition to break ties ) LIMIT 100;
Are null values allowed in any column of a table in SQL?
SQL, Database Testing • null sql standard • • Marcee

2

0
Votes

2
Posts

0
Views

E

Allowing Null Values Columns defined with a PRIMARY KEY constraint or IDENTITY property cannot allow null values. The nullability of a column determines whether the rows in the table can contain a null value for that column. A null value, or NULL, is different from zero (0), blank, or a zero-length character string such as "". NULL means that no entry has been made. The presence of NULL typically implies that the value is either unknown or undefined. Microsoft SQL Server's definition of NULL: A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown. Null values generally indicate data that is unknown, not applicable, or that the data will be added later. For example, a customer's middle initial may not be known at the time the customer places an order. SET ANSI_NULLS setting can change the behavior of NULL. In future versions this setting will be ON by default and you will not be able to turn it off. Details here: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql This is an article which go in depth about NULL. https://www.simple-talk.com/sql/t-sql-programming/how-to-get-nulls-horribly-wrong-in-sql-server/ Few example of NULL in TSQL here.
T

How to SUM values with same value by using WHERE IN()
SQL, Database Testing • mysql query • • terrea

2

0
Votes

2
Posts

0
Views

Z

The IN predicate will either evaluate to TRUE or FALSE (or NULL) exactly once for each row, so you won't get any multiplier effect from that. You can use a Common Table Expression (CTE, only supported in 8+) or a temporary table CREATE TABLE vo_plurep ( ID_PLUREP int not null , PRE_PRP int not null ); insert into vo_plurep (id_plurep, pre_prp) values (1,1),(2,2); CREATE TEMPORARY TABLE T (id_plurep int not null); insert into T (id_plurep) values (1),(1); SELECT SUM(PRE_PRP) AS IMPORTO FROM vo_plurep JOIN T ON T.id_plurep = vo_plurep.id_plurep IMPORTO 2 Fiddle
C

Transformation of one OBD into another
Software Programming • java postgresql database jdbc access • • Cleofas

2

0
Votes

2
Posts

0
Views

J

In principle, this task is possible. I see two paths.1st route (through Java)Build a connection with Access. For example, with JDBC-ODBC Bridge;Read the structure of the tables;Establish similar tables with a similar structure in PostgreSQL;Copy the data from Access.The path is extremely difficult, especially given that Java is not very good at working with certain Windows tools.2nd way (access tools)Access is very supportive of ODBC work. This allows the full export of any OBD through the graphic Access interface. The costs of work and time are such that, IMOW, it makes no sense to write a separate program.Something like that. ♪ ♪
N

Logical Replication to different schema name
SQL, Database Testing • postgresql replication • • Nykeriab

2

0
Votes

2
Posts

0
Views

P

This does not seem to be possible with built-in logical replication: https://www.postgresql.org/docs/current/logical-replication-subscription.html The tables are matched between the publisher and the subscriber using the fully qualified table name. Replication to differently-named tables on the subscriber is not supported. Since it uses fully qualified names, a different schema would count as a different name, and is not supported. pub/sub is just a user friendly wrapping around a more general facility, you might be able to create decoding plugins to give you finer control. I doubt that doing so is for the faint of heart, though.
C

How to enable NULL values in MySQL timestamp fields?
SQL, Database Testing • mysql backup mariadb • • chanisef

2

0
Votes

2
Posts

0
Views

O

ALTER TABLE table_name MODIFY dt TIMESTAMP NULL; This will alter the table with name table_name to set the column dt to nullable.
Y

Postgresql: invalid input syntax for integer: "false"
SQL, Database Testing • postgresql • • yajahira

2

0
Votes

2
Posts

0
Views

R

The UNION result set data types will be determined by logic that favours values that are not string literals -- it is described in detail in the manual, also note that, without explicit type specification, string literals are treated as being of type unknown. Specifically, these rules apply in your case: 5. Choose the first non-unknown input type which is a preferred type in that category, if there is one. ... 7. Convert all inputs to the selected type. Fail if there is not a conversion from a given input to the selected type. This basically means that if you have at least one numeric literal value in a particular (in your case fourth) column in your UNIONised query, Postgres will attempt to coerce the values in the same column in other SELECTs into an number, which obviously fails for the character value '2017-01-01'. Subsequently, if you have at least one character value in that column that cannot be cast into a number, you will have to use explicit character values for the fourth column in all SELECTs: insert into configurations (id, key, description, value) select 1,'key1','D1', '25' UNION select 2,'key2','D2', '365' UNION select 3,'key3','D3','2017-01-01' H/T to Andriy M for his comment that prompted me to read the manual more thoroughly.
M

help in correcting the syntax error of the below create table with as with multiple select
SQL, Database Testing • oracle ddl • • magpie

2

0
Votes

2
Posts

0
Views

K

You can get the result by using UNION ALL . Take help of following example for understanding: create table empnew as select * from employees union all select * from employees; As @a_horse_with_no_name perfectly mentioned !
A

Using a NULL value to signify that the value does not mattter. Not that it isn't known
SQL, Database Testing • database design null • • amarantha

2

0
Votes

2
Posts

0
Views

G

My approach for this is to think of what the fields are, which is storing information on computers. There is no case ever where having a fan would result in NULL, just a yes or no. In your case of using it as a major category for computers, I would avoid that, and instead use your queries and stored procedures to consider the filters. In the stored procedure you can have the WHERE clause look for the different combinations; Only Computers with fans -- SELECT [recommendations] FROM table WHERE [has_fan]=1 Only computers without fans --SELECT [recommendations] FROM table WHERE [has_fan]=0 All computers\don't care -- don't use a WHERE clause;SELECT [recommendations] FROM table
T

Read a csv file with sscanf
Software Programming • c language csv arc languagehivo • • terrea

2

0
Votes

2
Posts

0
Views

A

To achieve your purpose you must use:strtok(): This function allows you to cross a string from a given delimiter. What basically does is modify the original string by replacing the delimiter with a chain finisher.strcpy(): To copy the resulting subchain into the variable you are interested inThe code could be like this:strcpy(c.name, strtok(buff, ',')); strcpy(c.last_name, strtok(NULL, ',')); strcpy(c.telf, strtok(NULL, ',')); // ... Note that strtok only receives buff the first time, in the rest of calls you must receive NULL ( 0). This indicates to you strtok that must keep stumbling the chain.
D

Comparing Two Values when one or more could be NULL
SQL, Database Testing • postgresql null • • derk

2

0
Votes

2
Posts

0
Views

A

Equivalent and simpler: IF NEW.value IS DISTINCT FROM OLD.value THEN ... END IF;
K

pg_restore not restoring sequences
SQL, Database Testing • postgresql pg dump • • Kadir

2

0
Votes

2
Posts

0
Views

R

I find that when I use the "-Fc" option to pg_dump, I do get SQL statements to create sequences in the resulting dump file. pg_dump -Fc basted_data -f ./stuff.fc This is with old (8.4.20) PostgreSQL; I would hope that this behavior is unchanged in later versions. I was using the custom-formatted dump because, like the many people you encountered in researching your question, I wanted to restore a single table. I found that trying to restore a single table with the pg_restore "-t " option failed because of a missing sequence. But when I dumped out the entire "stuff.fc" backup to plaintext, I was able to search it and find SQL statements that created that sequence. When I cut/pasted them into a plaintext dump of the SQL generated by the "pgrestore -t", I was able to use the result to successfully create and load my single table. So, maybe pg_dump -Fc will work for you.
T

What is purpose of `ALLOW_CONNECTIONS` being set so no one can connect to the new Postgres database?
SQL, Database Testing • postgresql connect configuration • • tahishae

2

0
Votes

2
Posts

0
Views

E

It gives you a chance to revoke connect from unwanted users before doing alter database ... allow connections Also in all cases users with superuser permission are allowed to connect, so you can create an unconnectable database, then as superuser populate it and create constraints ans indices before making it publicly available.
Syntax error near '.`value)` != '') in 5.1.73
SQL, Database Testing • mysql • • carriann

2

0
Votes

2
Posts

0
Views

Y

`IF(status`.`value IS NULL, main_table`.`status, status`.`value)` should be replaced by IF(`status`.`value` IS NULL, `main_table`.`status`, `status`.`value`) But, as stated by Akina you should rid of all that backticks. Backticks are necessary only if you want to use some reserved word as identifier like TIME or DATE. In all other cases backticks are redundant and only obfuscate the code.
A

String postgresql manipulation?
Software Programming • postgresql regex • • authera

2

0
Votes

2
Posts

0
Views

G

Use regex:<<.*?>> You'll marry anything between the signs << and >> (including the signs).But also use the flag 'g' (global) to replace all occurrences (otherwise it will replace only the first):select regexp_replace('TESTE <<TESTE1>> TESTE <<TESTE2>>', '<<.*?>>','','g') http://sqlfiddle.com/#!17/3f2fb/5

Syntax Error in Boolean Null values

Suggested Topics

Grouping() equivalent in PostgreSQL? SQL, Database Testing • sql server postgresql aggregate window func languagetions • • gionna

Are null values allowed in any column of a table in SQL? SQL, Database Testing • null sql standard • • Marcee

How to SUM values with same value by using WHERE IN() SQL, Database Testing • mysql query • • terrea

Transformation of one OBD into another Software Programming • java postgresql database jdbc access • • Cleofas

Logical Replication to different schema name SQL, Database Testing • postgresql replication • • Nykeriab

How to enable NULL values in MySQL timestamp fields? SQL, Database Testing • mysql backup mariadb • • chanisef

Postgresql: invalid input syntax for integer: "false" SQL, Database Testing • postgresql • • yajahira

help in correcting the syntax error of the below create table with as with multiple select SQL, Database Testing • oracle ddl • • magpie

Using a NULL value to signify that the value does not mattter. Not that it isn't known SQL, Database Testing • database design null • • amarantha

Read a csv file with sscanf Software Programming • c language csv arc languagehivo • • terrea

Comparing Two Values when one or more could be NULL SQL, Database Testing • postgresql null • • derk

pg_restore not restoring sequences SQL, Database Testing • postgresql pg dump • • Kadir

What is purpose of `ALLOW_CONNECTIONS` being set so no one can connect to the new Postgres database? SQL, Database Testing • postgresql connect configuration • • tahishae

Syntax error near '.`value)` != '') in 5.1.73 SQL, Database Testing • mysql • • carriann

String postgresql manipulation? Software Programming • postgresql regex • • authera

Grouping() equivalent in PostgreSQL?
SQL, Database Testing • sql server postgresql aggregate window func languagetions • • gionna

Are null values allowed in any column of a table in SQL?
SQL, Database Testing • null sql standard • • Marcee

How to SUM values with same value by using WHERE IN()
SQL, Database Testing • mysql query • • terrea

Transformation of one OBD into another
Software Programming • java postgresql database jdbc access • • Cleofas

Logical Replication to different schema name
SQL, Database Testing • postgresql replication • • Nykeriab

How to enable NULL values in MySQL timestamp fields?
SQL, Database Testing • mysql backup mariadb • • chanisef

Postgresql: invalid input syntax for integer: "false"
SQL, Database Testing • postgresql • • yajahira

help in correcting the syntax error of the below create table with as with multiple select
SQL, Database Testing • oracle ddl • • magpie

Using a NULL value to signify that the value does not mattter. Not that it isn't known
SQL, Database Testing • database design null • • amarantha

Read a csv file with sscanf
Software Programming • c language csv arc languagehivo • • terrea

Comparing Two Values when one or more could be NULL
SQL, Database Testing • postgresql null • • derk

pg_restore not restoring sequences
SQL, Database Testing • postgresql pg dump • • Kadir

What is purpose of `ALLOW_CONNECTIONS` being set so no one can connect to the new Postgres database?
SQL, Database Testing • postgresql connect configuration • • tahishae

Syntax error near '.`value)` != '') in 5.1.73
SQL, Database Testing • mysql • • carriann

String postgresql manipulation?
Software Programming • postgresql regex • • authera